Programmatically select field in Access Parameter query?

[spiralmind]

I am attempting to build an Access database UI which includes an input form used to enter one set of information for each customer in the database, and "units sold" information for ten services for each customer. I also need to retrieve this information by service and/or customer in another form which is used for data lookup and ad-hoc report generation.
To do this, however I need to find an effective way to "tie" each customer imput record to its 10 services, while maintaining the ability to run a parameter query to look up each service individually. I have tried to set a parameter expression in the "fields" line of the query builder, but this always prints whatever I type into the parameter dialog onto the query literally, instead of selecting the correct field.
I have also attempted to build a second table for the services and to use a parameter query to enter the "customer" imput form's autoID number alongside the name of each service in the second table.(My apologies for not being able to place a diagram here, I know this isn't a very clear explanation of what I have tried to accomplish)The append query worked as it should, but when I built a select query to return the current form ID and service name (based on the name of the textbox whose onGotFocus event triggered the select query) it would open the query with the correct record, but provided no way to enter the actual "units" information through the form - though it can be typed in through the query - so the recordset isn't locked.
I am wondering if anyone out there knows a better way to do this, perhaps with arrays or some other recordset object, or if anyone can give me some ideas on what I may have done wrong in my first two attempts. Perhaps I am trying to do something beyond the capabilities of Access or my limited programming experience? I do not know.

- Thankyou for reading this incredably long post (even more so for taking the time to answer it) - the Spiralmind

 

[thornmastr]

Often times the best solution to a problem are the most simplistic, at least in their approach. Assuming, for the moment, two tables, tblCustomerInfo and tblUnitinfo.
The primarky key (pk) of tblCustomerInfo will be the forward key(fk) of tblUnitInfo .

Consider using a form based on the underlying recordset of tblCustomerInfo which contains a subform based on tblUserInfo. The master/child relationship of main to master would be the pk of the mainform, and the fk of the subform. So, for every available record within the main form, all related records in the subform will also be available to you. This process will also work very nicely when you are adding records to both the main form and the subform.

Hopefully, while not as long or as detailed as your question, (which by the way was very well done) this will get you started in the right direction. If you run into snags, problems etc, post back.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com