Stumped! Multi line entry form auto-populate??

[mcamp]

Ok guys, I am in need of some brain storming again I am stumped.

I have a data entry form attached to a DailyEntry table. Many dates, many locations. Basically this company enters in multiple lines of info for a specific date for multiple locations. a sign in sheet more specifically. ie on 4/15/2011 these 15 kids signed in at Location1, same date another 15 kids signed in at Location 2, etc.

The first field of entry is the Family ID. Upon typing in this family ID i need the second combo box to populate with the children attached to this family id. After choosing which child from the second combo box I need it to populate the remaining boxes with that childs info.

I have a ChildData table that has all of this info. After updating the first combo box i need the second combo box to query the children belonging to that family id.

I am just stumped for ideas. I was thinking a multi line subform, but i get confused when thinking about the subform and the uniqueness of each line of data.

any ideas? SOS!

http://lordkazeoneink.com/thc/shot1.bmp
http://lordkazeoneink.com/thc/shot2.bmp
http://lordkazeoneink.com/thc/shot3.bmp

 

[mcamp]

how about afterupdate of field1 I update the sql in the controlsource property of field2 with field1 value. then after update of field to runsql to get the values for that child and put them in the remaining text boxes via VBA.

only thing is this code would be duplicated tons of times for each field1 and field2 combo box


???

 

[Montez659]

I think that a subform would be a good solution. It would populate when the parent record is selected.

If you are dead set against using a subform (which I think is the best approach) then check out this link about cascading comboboxes to see if it shoots you in the direction of what you are looking for:

http://mymsaccessblog.blogspot.com/2008/03/how-to-synchronize-two-combo-boxes-on.html

 

[mcamp]

got it. setup a table with all possible dates from here utill 2016 as PK. then a table2 with all the needed fields including date, ID as FK.

make mainform based on possible dates table, subform based on table2, date defaulting to date selected on main form.

then multi column cascade boxes, with after update event set to update the text boxes on the subform with the corresponding columns pulled in the cascade boxes.

then another nested subform populating the childs info based on the id and last name selected in the first subform.

*just incase someone somewhere along the line needs to know

quick note: when making a casecade box on a subform based on a query with the query criteria also located on the subform - the correct sytax for the query is

[form]![subformControl] ie [form]![Combo47]

.

forms!FormName!form!subformName!subformControlName does not work. however if the query is attached to a combo box on the main form with its criteria on the subform you do need to use this one.

 

[Montez659]

Glad you got what you needed!

 

[mcamp]

Glad you got what you needed!

thanks man i appreciate it!