How to save data from different forms in different tablets

[Rahmonic]

Hello,

Just to give you all a brief overview. I am making a Form to save Fish data. This one Form contains 4 Sub-Forms

Main Form
1). User Input (Data, Name etc.)

Sub-Forms (in tab form)
1). Conditions (for ex. Location, Objects used, Which pump etc..)
s16.postimage.org/42234m34l/Condition.png
2). Regular Fish (Name, count etc.)
s21.postimage.org/f13t21h6v/Regular.png
3). Special Fish (same as above)
s8.postimage.org/43dhv0ycl/Special.png
4). Algae
Similar thing

So now you all have some idea of what I am working on, I need help on some parts.

Conditions & Algae content saves perfectly into their tables. I guess because all fields are connected already and there are no IF statements.

But in the case of Regular Fish and Special Fish. Depending on the fish they select in that drop down menu, I want to save in those specific tables. I have tables for each fish.

This is the code I am using.

Public Function round_whitefish1()

CurrentDb.Execute _
"INSERT INTO RoundWhitefish (ID, Total, Finclip, Lamprey,B1,B2,B3,B4,B5,B6,B7,B8, B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,B24,B25,B26,B27, B28,B29,B30,Label,Data, Dcomments) VALUES ('" _
& Form_P1.DayID.Value & "','" & Me.txtCount1.Value & "','" & Me.chkTag1.Value & _
"','" & Me.chklamp1.Value & "','" & Me.txtB1.Value & _
"','" & Me.txtB2.Value & "','" & Me.txtB3.Value & _
"','" & Me.txtB4.Value & "','" & Me.txtB5.Value & _
"','" & Me.txtB6.Value & "','" & Me.txtB7.Value & _
"','" & Me.txtB8.Value & "','" & Me.txtB9.Value & _
"','" & Me.txtB10.Value & "','" & Me.txtB11.Value & _
"','" & Me.txtB12.Value & "','" & Me.txtB13.Value & _
"','" & Me.txtB14.Value & "','" & Me.txtB15.Value & _
"','" & Me.txtB16.Value & "','" & Me.txtB17.Value & _
"','" & Me.txtB18.Value & "','" & Me.txtB19.Value & _
"','" & Me.txtB20.Value & "','" & Me.txtB21.Value & _
"','" & Me.txtB22.Value & "','" & Me.txtB23.Value & _
"','" & Me.txtB24.Value & "','" & Me.txtB25.Value & _
"','" & Me.txtB26.Value & "','" & Me.txtB27.Value & _
"','" & Me.txtB28.Value & "','" & Me.txtB29.Value & _
"','" & Me.txtB30.Value & "','" & Me.chkLabel1.Value & _
"','" & Me.cboTest1.Value & "','" & Me.txtcomment1.Value & _
"');"
End Function

For some reason it’s just not saving the Regular and Special fish data, its saving everything else.

And lastly I want to have same ID as the main form in every sub-form. So for example when I am looking I can open specific date by specific person and all the entries shows up.

I hope I was clear enough. Thank you in advance guys, your help is much appreciated. I am not a Access savvy

 

[Rahmonic]

Sorry I couldn't post the pictures directly because I am new in he form. So you will have to open them manually. Copy/Paste link

 

[Pat Hartman]

You don't need two fish tables. All you need is one. In the table you use to define fish, add a column that identifies a fish as "special". That will allow you to eliminate all the special purpose code. Queries can select all fish, special fish, or ordinary fish depending on what your particular need at the time.

 

[Rahmonic]

You don't need two fish tables. All you need is one. In the table you use to define fish, add a column that identifies a fish as "special". That will allow you to eliminate all the special purpose code. Queries can select all fish, special fish, or ordinary fish depending on what your particular need at the time.

Thanks for the reply Hartman,

I am not sure if I get what you mean. Right now I have 20 tables (all with same characteristics) but with different fish names.

Lets say I remove all different tables and just have one table name "Fish" where whatever the user selects saves in one table with different names. But when there are multple fish entries. How can I assign them the same ID as the main form.

When I do query, I want to be able to select specific event "entry" so it can show me all the data related to that ID.

 

[Pat Hartman]

Your column names are too cryptic for me to decode so I'll make up my own.
tblFish
FishID (autonumber PK)
FishName
SpecialFlg
etc.

tblFishSightings
SightingsID (autonumber PK)
FishID
DateSighted
Location
etc.

tblFish defines all types of fish and contains one row per type of fish. So, if there are 20 types of fish you are tracking, this table will have 20 rows. I added a SpecialFlg field to the Fish table because you said there were some fish you wanted to treat differently.

tblFishSightings defines when/where you saw a particular type of fish. This table will contain a row for each sighting so there may be 100 different sightings for a given day with the same fish type sighted at different times.

 

[Rahmonic]

Your column names are too cryptic for me to decode so I'll make up my own.
tblFish
FishID (autonumber PK)
FishName
SpecialFlg
etc.

tblFishSightings
SightingsID (autonumber PK)
FishID
DateSighted
Location
etc.

tblFish defines all types of fish and contains one row per type of fish. So, if there are 20 types of fish you are tracking, this table will have 20 rows. I added a SpecialFlg field to the Fish table because you said there were some fish you wanted to treat differently.

tblFishSightings defines when/where you saw a particular type of fish. This table will contain a row for each sighting so there may be 100 different sightings for a given day with the same fish type sighted at different times.

I am assuming you have seen the pictures I have posted?
I understand what you saying, but what will be the ID that connects everything in each event.

For Example, first "event"

Main Form - Name: Mark, Date: March 06, 2013 etc. etc.

Sub Form (Condition) - Location: xyz, Equipment used: xyz...etc.
Sub Form (Regular Fish) - Name1: xyz, quantity: 000, lenghth: 000
Name2: zyx "" "" """""""""""""""""""""""""
Name3: abc "" """"""""""""""""""""""""""""
Sub Form (Special Fish) - Name1: AAA ...................................
Name 2: BBB ...............................

So now my question is, when I pull out this event, Mark, March 06 blah blah blah

There should be something thats common in each entry. So everything shows up.

I am sorry if I am being noob but i just can't figure out even if its too simple :lol:

 

[Pat Hartman]

You don't need two subforms. You defined a fish as "special" when you added the name of the fish to the fish table and set the SpecialFlg to True. The Fish subform needs a combo to select the Fish from the fish table and then the other columns would be typed.

I can't see any pictures so I haven't seen your forms.

 

[Rahmonic]

You don't need two subforms. You defined a fish as "special" when you added the name of the fish to the fish table and set the SpecialFlg to True. The Fish subform needs a combo to select the Fish from the fish table and then the other columns would be typed.

I can't see any pictures so I haven't seen your forms.

I have 4-5 special fish, and around 20 regular fish. I need two different forms for both fish. That's the problem.

Please check the pictures I have posted in the orignal post. Since I am new I can't post them directly, so I have links. Just copy paste. I think that will give you more idea about what I am trying to do.

Thanks for again

 

[Pat Hartman]

No. You don't ned separate tables. I told you how to define a single table and still identify "special" fish so you can segregate them for reporting.

You appear to have unnormalized tables. This can result when you define forms as you want the data reported.

Your data entry forms should look like lists rather than grids. You can make reports that have grids but don't let your output format dictate your storage format.

 

[Rahmonic]

No. You don't ned separate tables. I told you how to define a single table and still identify "special" fish so you can segregate them for reporting.

You appear to have unnormalized tables. This can result when you define forms as you want the data reported.

Your data entry forms should look like lists rather than grids. You can make reports that have grids but don't let your output format dictate your storage format.

I hope you have now seen the pictures. The problem is, I HAVE to make the form look like what you see in the picture, I can't change. I totally understand what you saying.

Now forget about about that, and tell me know thing. Look at this picture
imageupload.ca/?di=0MEJ

This is totally rough, but will give you general idea of what I am asking. So I have one form, and I want to add multiple fish. And I want both fish connect to same ID. So how can I keep the ID sane, everything else I can change it. Right now whatever I am entering in first portion, its replicating on the bottom. I want them to be independent but it should save under same table with same ID of that perticular form.