Combo Box: Display Values from 2 tables and enter onto a 3rd.

[ThePixelMines]

I'm a newbie. Please be nice....

I'm setting up a database to manage a list of Clients, their respective projects, and the billing of said projects.

See the relationships here:

http://www.thepixelmine.com/forumfiles/tektips/ProdRelation.jpg

In a Time Card form I have a subform managing the TimeCardHours entries. What I'm trying to accomplish is a combo box that displays 2 columns: one of the client's name (Clients.ClientName) and the project name (Projects.ProjectName) but then enters the ProjectID into the appropriate field (TimeCardHours.ProjectID).

From my searching it seems like I need to JOIN function, but I can't quiet figure out how to write it.

Please help.

Check it, Fool!

http://www.thepixelmine.com

 

[kjv1611]

When you say:
"but then enters the ProjectID into the appropriate field (TimeCardHours.ProjectID)."

Are you saying you want the correct value entered into a 3rd column of your combo box, or into a text field in your form or subform?

---

Are you sure you need one combo box with 3 columns showing?

---

--

"If to err is human, then I must be some kind of human!" -Me

 

[randy700]

Let Access do the work for you. Design a new query, put in the necessary tables and select the required items from those tables. Look at the output. Once you've got what you want, go to SQL view to find the code.


Randy

 

[ThePixelMines]

Okay, I tried the Query Wizard (did this previously through the ellipses at the end of Row Source).

I've also revised my visual aides to perhaps illustrate my point a little better.

http://www.thepixelmine.com/forumfiles/tektips/ProdRelation_flow.jpg

I ended up with something like this:

SELECT Projects.ProjectID, Clients.ClientName, Projects.ProjectName FROM Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID ORDER BY Clients.ClientName;

It isn't working for me. I get a "Type mismatch in expression" warning. It also seems that I'm missing some reference to how it displays. I've used the following successfully:

SELECT Directors.DirectorID, [Directors.FirstName] & " " & [Directors.LastName] FROM Directors ORDER BY [DirectorID];

...to associate a Director to a timecard...but trying (what I thought to be) the same syntax didn't yield the results I'm searching.

I'll be watching the post as I read through the 'help' sections on queries and combo box displays if anyone feels like telling me what I'm doing wrong.

Thanks!

Check it, Fool!

http://www.thepixelmine.com

 

[ThePixelMines]

Okay, so there's progress. It would appear that I had not ensured referential integrity causing my values to not match. Once I corrected that in the relationships I was able to get a decent result with:

SELECT Projects.ProjectID, Clients.ClientName, Projects.ProjectName FROM Clients INNER JOIN Projects ON Clients.ClientID=Projects.ClientID ORDER BY Clients.ClientName;

It wasn't working previously due to the mismatch in field 'data type.' Once I corrected that it was working fine.

Adjusting my Column Count and Column width enabled me to not display my ProjectID. Now I just need to find a way to (once chosen) display the ProjectName and not the ClientName (which is what it's doing currently.

Check it, Fool!

http://www.thepixelmine.com

 

[kjv1611]

Okay, if you want your combo box to show different things at different times, here's the easiest/simplest way I can think of:

Create 2 Queries.
Use the one for your recordsource before the selection is made, and then change the combo-box's recordsource to the second query after the selection is made.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ThePixelMines]

KJV,

Thank you for the reply.

It would turn out that my folly was centered around the "Enfore Referential Integrity." Once I had made sure each of the linked fields matched in type I got rid of the "Type mismatch in expression" warning and Queries made a lot more sense. My queries weren't working because of that mismatch (my autonumbers had been matched up with a 'text' field and not a 'number' field). Now they work.

I do have one related question. I've got it to hide the Primary Key and display only the (in this case) Clients.ClientName and Projects.ProjectName, but once the selection is made it only shows the 2nd column of the three choices. Is there any way to (again, once selected) show columns 2 and 3?

Check it, Fool!

http://www.thepixelmine.com

 

[randy700]

Place a text box next to your combo box. Make it's visible porperty FALSE. Set it's value to =comboboxname.column(2). In the after update event of the combo box, set the text box visible property to TRUE.


Randy

 

[ThePixelMines]

Everyone who was kind enough to help,

I ended up just switching the order in the query to the following so that it would at least just show the Project and not the Client once clicked. This worked well enough for me.

SELECT Projects.ProjectID, [highlight]Projects.ProjectName, Clients.ClientName[/highlight] FROM Clients INNER JOIN Projects ON Clients.ClientID=Projects.ClientID ORDER BY Clients.ClientName;

Thanks to everyone who replied!

Check it, Fool!

http://www.thepixelmine.com