Creating Recordset for Form Source 2

[nag9127]

I am not sure how to go about this and I'm not sure if I can explain this very clearly either but here goes. I have a table that has item numbers no duplicates and a second table that has item manufacturing templates, of which there can be more than one per item number (one to many relationship). I have created a query which produces a nonduplicated list of the items that have templates through a select distinct query on the templates table focusing on the item number. What I want to do is create a non duplicated lookup of the items that have templates (hence the select distinct query) and then have the selection from that lookup produce a second lookup that shows the templates pertaining to that item, allowing selection for the editing of any one of the templates that pertain to that particular item only. The selection of the template would produce the detail associated with that template for editing within a form. Any help on the technique to accomplish this would be much appreciated. I've been experimenting with this all day and I think I might know the concepts, but I'm just not clear on how to get it done and I'm really not making any progess. Thanks for any help!

 

[Remou]

Have you considered a form based on the the 'select distinct' query and a subform based on the templates table? You could use a combo box to find the required record and either a form that can be accessed from the subform or FollowHyperlink to open the template, depending on what form this template takes.

 

[nag9127]

I'll work on that this morning and report back on any difficulty. Thanks Remou!

 

[nag9127]

As a followup question, is it possible to have a global variable populated with a recordset based on some criteria and then have a form perform a combo box lookup on the contents of the recordset (global variable) or do recordsets not allow this type of action? Are recordsets limited to step through looping type actions as opposed to random selection from a lookup? Is it possible to have a global variable populated with a recordset based on certain criteria and then have the global variable be the control source for a form?

 

[KenReay]

Have a look at

Combo Box - dependant on another Combo Box faq702-4289

I think it relevant to what you are trying to do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[Remou]

In addition to KenReay's comment, you can change the recordsource of a form at anytime and to any valid SQL string.

 

[nag9127]

Thanks for the info, Ken Reay and Remou! I will do some homework.
What about my question about recordsets. Are recordsets limited to step through looping type actions as opposed to random selection from a lookup? I am speaking about recordsets created during an event through a variable with code based on specific criteria... rs = <field> where <field> = <form control> type criteria. Thanks again for the info!

 

[Remou]

You can do that:

Dim rs As DAO.Recordset

Set rs=CurrentDB.OpenRecordset("Select " & fld & " From " _
& tbl & " Where " & fld2 & " Like '" & fld3 & "'")

 

[nag9127]

Then can I bring a record from the recordset into a form for editing by using a combo box looking up the values from the recordset. Is that possible?

 

[Remou]

What are you using ADO or DAO? Can you sketch how you see this working?

 

[nag9127]

I'm using DAO. No need to get into the details right now. I am just getting into the nuts and bolts of it today. As my plan becomes more clear, I will share the details with you. I have some reference material that I am looking at and it appears there are several ways to do this, some more complicated than others. I appreciate your assistance and I will post back with my resolution of this form requirement. Thanks to both of you for your help!