Same Row Source But Different Values

[jsully82]

Hi All,

Having a frustrating issue, which I haven't been able to find a solution to. I work for a small company and the task has fallen to me to create a basic database to track when different media is created (no prior MS Access experience).

Without getting into extraneous detail, I have set up a few bound forms to accept information related to each media production event. The part I'm struggling with is having two separate combo boxes (sharing the same row source from another table) display independent values. The combo box has all the names of the individuals allowed to create certain media types. When media is created, another (different) person from that list must verify. Since the listing is the same for both the creator and the verifier, I have both combo box row sources set to the same form that lists these individuals. When a selection is made in the creator combo box, however, it automatically populates the verifier combo box with the same name. I need to be able to make these selections independently, but using the same pool of names.

I'm thinking I can just create another form with the same name listing and point the row source for the second combo box at that, but that doesn't seem to be an efficient solution, particularly since whenever the list changes I will have to make changes in two locations.

There has to be an easy way to make the two combo box selections independent of eachother while using the same row source! Please help!

Thanks,

John

 

[jdraw]

John,

Please give readers the 30,000 foot overview of your business in simple plain English. What are the things involved and how do they relate one to another? Always get a good business description written down and vetted before jumping in to Forms, queries etc.

Before you can address How to do something, you have to define WHAT it is.

 

[micks55]

Yep, read it three times and nothing is clear...
Maybe it's this. You have a table with a field called Originator and another field called Verifier. Maybe the recordsource for both combo boxes is the same field name. You might want identical combo's but bound to different fields.
Alternatively, re-type the qustion and give us a simple example of what you're trying to do.
ps. I don't get online often so pls don't think I am ignoring you.

 

[jsully82]

John,

Please give readers the 30,000 foot overview of your business in simple plain English. What are the things involved and how do they relate one to another? Always get a good business description written down and vetted before jumping in to Forms, queries etc.

Before you can address How to do something, you have to define WHAT it is.

Thank you for your response, my apologies for being unclear. I didn't include some of the business-related details because I was trying to be concise with my Access question: Is it possible to use the same row source (another form's ID field) in two separate combo boxes independently? As mentioned in my original post, when I select from the combo box listing, it fills both combo boxes with the same value.

Additional business detail and description of problem:

I work in a small company that develops binary images for communication terminals. When a new image is created, it is commonly burned to a CD. When this occurs, for configuration control purposes these activities must be logged. Right now, we use an Excel spreadsheet that everyone in the lab has access to and populates the appropriate fields with the required data (name of creator, name of verifier, .bin file size, date, etc.) We have had instances of some of these fields not being properly filled and believe some rudimentary content control will resolve this (hence our usage of Access). We intend to eventually develop a more polished application to retrieve data automatically, but this is a quick band-aid solution in the interim and we quite frankly don't have have the time and resources to expend on this effort at the moment. I was tasked with developing a basic MS Access database for the time being. I'm an engineer/mathematician with no database experience, but I'm the young guy so I get to do the work no one wants in addition to my normal duties - I digress.

I've created a nice input form which has the requisite fields as described above. I have a separate form which accepts lab technician data (First/Last name, other personnel-related details that don't matter for purposes of this question), which is locked, password protected and will be editable only by whomever we decide should be able to adjust who can burn and verify terminal images. On the input form, among the fields I have two combo boxes. Once clicked, a listing of all names (using ID from the personnel form as a row source) pops up and the image creator selects his name. Directly below that field, the verifier name is selected (this cannot be the same person - eventually I will implement some logic or perhaps a cascading box that removes the first name from the verifier list.)

Anyway, problem is, when I select say, "Smith, John" for the Image Creator combo box, the combo box directly below (for the Image Verifier) is also immediately filled with "Smith, John". To restate my question: Is there a way to independently select names from each of these combo boxes even though the row source for both of them is the same (i.e. Personnel form ID).

 

[micks55]

You need to trim this down to a specific question using fewer words but I'll try to move forward.
Read my previous reply. If it fills both combos with the same value then they are linked and I still suspect they are bound to the same field.
Tell us the Name, Row Source and Control Source of each combo. If the Row Source is a query, you need to give the actual SQL which you can copy from the SQL view in the query grid.

 

[jsully82]

Yep, read it three times and nothing is clear...
Maybe it's this. You have a table with a field called Originator and another field called Verifier. Maybe the recordsource for both combo boxes is the same field name. You might want identical combo's but bound to different fields.
Alternatively, re-type the qustion and give us a simple example of what you're trying to do.
ps. I don't get online often so pls don't think I am ignoring you.

If I follow you correctly, I believe this is very nearly what I'm trying to do. Table exsists with field called Image Creator and Image Verifier. These two fields are populated by selecting the associated combo box, which is derived from another table which stores various personal details, but for the combo boxes, I only need first and last name to show. The combo box Control Source is the ID from the this secondary table. My Row Source is

 SELECT [EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

Whenever I select a name for one of these two combo boxes, the other combo box is instantly populated with the same name (but I need them to be two different selections). This is of course related to the fact I'm pulling from the same secondary table for these two combo boxes, but how do I break the association between the two combo boxes, so the different names can be selected indepdently for each box instead of the same name automatically populating. There has to be an easy solution to this and it's driving me nuts!


Thanks for your assistance.

 

[jsully82]

You need to trim this down to a specific question using fewer words but I'll try to move forward.
Read my previous reply. If it fills both combos with the same value then they are linked and I still suspect they are bound to the same field.
Tell us the Name, Row Source and Control Source of each combo. If the Row Source is a query, you need to give the actual SQL which you can copy from the SQL view in the query grid.

Yes, they are bound to the same field and row source is Table/Query (please see the post above this one where I provided additional detail). Is there a way to use the same listing for combo box selection purposes without creating another table for the second combo box? I'm not sure about what to do with your SQL suggestion, forgive my lack of database expertise.

 

[Royce]

You need to set the 'Control Source' to different fields in the underlying table. The 'Row Source' will be the same for both combos.

 

[jsully82]

You need to set the 'Control Source' to different fields in the underlying table. The 'Row Source' will be the same for both combos.

That's the thing, I want to use the same Control Source (the ID field of table with all of my people listed), but display different names in each combo box depending on the individual selection. Is there no way to do this without creating a separate control source with the names listed again? Seems inefficient and I can't believe Access has no way to differentiate between the two combo boxes even if using the same control source. I'm assuming there's a code solution that is beyond my level of expertise?

 

[Royce]

Sounds like you don't understand what the different properties do. The Control Source determines the field where the choice is stored. The Row Source determines where the choices come from. Your storing the users selection in the same for both combos. Won't work. Play with it. You'll figure it out.

 

[jsully82]

Sounds like you don't understand what the different properties do. The Control Source determines the field where the choice is stored. The Row Source determines where the choices come from. Your storing the users selection in the same for both combos. Won't work. Play with it. You'll figure it out.

Thank you! I knew it was something easy I was missing. Works like a charm now. You are correct, I misunderstood the purpose of the control source. :banghead:

Thanks to all for your assistance!