Can't select records using combo box 2

[dwmatteson]

I'm working on an issue-tracking program as a warm-up to a larger project, and I've run into an obstacle that has me pulling out hair (usually mine, occasionally others' ).

I have three tables: Users, Techs, and Tickets. The Tickets table is a bridge table for the many-to-many relationships between users and techs.

I've created a form that I'm planning on using to look up existing tickets by user. Ideally, I'd like to select the user's name from a combo box and have their tickets displayed in a subform. The problem I'm having is that I can't actually select users using the combo box! They all appear in the list of records, but I can't click on any of them to select them.

Here are the details of the form:

Main form: based on Users table, consisting of combo box with user's names (the one I can't use to select records), the selected user's phone number, and their email address.

Subform: based on Tickets table, consisting of ticket details/resolution, and a combo box linked to the Techs table that displays the technician's name. The ticket subform is linked to the main form by UserID.

Pretty much all of this was generated with wizards, with some tweaking for formatting purposes. I'd appreciate any help you folks can offer with getting the combo box to work!

Thanks in advance,

Don

 

[rajeessh]

I am not so sure of the problem
But in the SubForm ->Properties u have Master Link Fields
and Child Link Field.
Make Master Link Field as the combo control in the Main Form
and Child Link Field as the control names for User in SubForm
If u dont have one have a hidden right there.
This may prove to work out

 

[dwmatteson]

Oh, right. I forgot to mention that the main form and the subform are linked by UserID.

My thinking was that as I changed the UserID on the main form using the combo box, I'd be able to bring up the associated records (from the Tickets and Tech tables) in the subform. The problem is that when I click on the combo box on the main form (which brings up UserIDs and name), it won't let me select another user by clicking on their information. It just sits there as if I never clicked at all!

Thanks for the reply!

Don

 

[sko]

By any chance is the combo box locked property set to "yes"?

This list expands and you can see the values,right?

 

[dwmatteson]

Thanks for taking the time to respond. The combo box isn't locked, and it expands so I can see all of the values.

Right now, I think the problem might relate to one of the choices I made when setting up the combo box using the wizard. I told Access to populate the combo box using fields from an existing table (Users), and later on, it gave me a choice of saving the value in a field in the Users table or holding the value for later use. I told it to save the value in a field in the Users table (the source field).

When I tried using the "hold the value for later use" option, I had no difficulty changing the user, and the associated tickets popped up just fine.

The only problem that I still have is that the user contact information is stuck on the first record in the table, regardless of what user is selected. (I can change it manually, but it doesn't match the user name from the combo box.) I think I might have to do some sort of requery for the main form to update the contact info when the user name is changed.

I'm not convinced that this is the solution, but it seems to have potential as a workaround. I'd love it if anybody could shed some insight into why the original design wasn't working. (I'm still very new to this stuff and I want to learn as much as I can!)

Thanks again!

Don

 

[sko]

Use the wizard to create a new combo box. This time choose the "Find a record based on values" option. Choose your UserID and Name again. Now if you're feeling brave (or you are using a test database) delete your old combo box and try the new one. If the subform details don't happen to come up make sure you are still linking by userID and that you included userID in your new combo box.


Your original "save the value in a field" design is used in a data entry form. Suppose you are adding a ticket. One of the fields in your ticket table should be the userID. On your data entry form it might be nice to have a combo box listing all the users to select from. This combo box would include userID and user Name. You would set the combo box properties to hide the id and show the name (which would be more meaningful to the data entry person) but the "value you would save" is userID and it would be saved in the ticket table (not the user table).

HTH

 

[rajeessh]

Better to have a bound hidden and use combo box as a unbound control to select user Sir

 

[dwmatteson]

I tinkered with things again last night, and I ended up using something like (if not exactly) the solution that rajeessh proposed.

I created an unbound combo box that's populated from the Users table. I then created a hidden text box called UserID that copied the UserID value from the combo box. This text box was bound to the subform, which let the main form and the subform synchronize based on the UserID. Not exactly pretty, but it seems to have worked.

Many thanks again to sko and rajeessh for taking the time out to help an Access newbie!

Don