Access - Combo Boxes (Table Design, not Forms) - Not working

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
I have a relatively simple database I'm using to re-acquire MS Access knowledge and am running into a struggle with a Combo Box in one of my Tables. Unfortunately, Google and thread searches only seem to reveal Combo-Box set-up in Forms. The problem I'm seeing there is that the combo box source is the query and then you have to worry about writing the code to update the table with the newly selected value and it's special field on the form that doesn't work the way the rest of them do. Seems like this special struggle to me that otherwise should be handled in the table design. Granted, most Combo Boxes do a simple lookup of all values from a given table and just show them all. In this case, I need to keep a job-specific list of values that can/will change dependent on the current selected job.

Here's the query I want to populate the Combo Box:

SELECT [Job Panel Templates].[Job Panel Template]
FROM [Job Panel Templates], [Job Orders]
WHERE [Job Panel Templates].Job_ID = [Job Orders].Job_ID AND [Job Orders].ID=[Order_ID]; <--This last [Order_ID] is from the current record.

When I put this SQL into a query, it'll prompt me for the Order_ID, I enter the value from the current order and it pulls the correct list. However, when it's put into the Row Source property of the Template field in my table, NADA!!!

I'm sure there's some simple little trick I'm missing, but I can't find it. I'm also beginning to suspect record locks get in the way of reading the current row and/or it won't work at the Table level because you could always change the Order_ID. But, I also know it doesn't actually run the query until you click the down-arrow which means you're not editing the Order_ID field. I don't know and it's driving me a little loopy.
 

Xsage

Dave
Joined
Jan 8, 2016
Messages
300
I haven't used Access in a good while, but I'm taking a stab because this question has gone a little while unanswered. It seems to me, when you enter the query into the Row Source property, [Order_ID] is not defined so it can't return anything, you either need to reference a static resource, or switch to forms to allow dynamic input. You can test if this is the case by entering a static and existing ID number in place of [Order_ID] to see if the rows get populated.
 

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
Hey XSage, thank you for following-up on this. Your suspicions precisely mirror mine: As mentioned, entering that string into the SQL portion of a new query results in me being prompted for a value and the query runs fine upon entering a value. It also runs fine hard-coding the value into the query.

So, I suppose it's just not possible from the current record.

Thanks again.
 

Xsage

Dave
Joined
Jan 8, 2016
Messages
300
Ah okay, I competely understand, when I read it initially I only got half the problem. :giggle:

I've been playing around on Access and built my own very simplified version of what you are doing and I'm running into the same issue. So at least I'm on the same page now. Even if I provide a table and column reference for the value of Order_ID, it pops up with a prompt to enter a value rather than using the value in the table. I would have imagined it would work as the query should execute for every row considering it is called a "Row Source". I suspect there needs to be some VBA to control this rather than relying soley on SQL.

I'll continue playing with it and let you know if I get an where.
 

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
Well, the good news is: I wasn't seeing things, so I'm just that little bit less crazy than people tell me :)
 

Chawbacon

Jack
Joined
Jul 9, 2018
Messages
569
Ah okay, I competely understand, when I read it initially I only got half the problem. :giggle:

I've been playing around on Access and built my own very simplified version of what you are doing and I'm running into the same issue. So at least I'm on the same page now. Even if I provide a table and column reference for the value of Order_ID, it pops up with a prompt to enter a value rather than using the value in the table. I would have imagined it would work as the query should execute for every row considering it is called a "Row Source". I suspect there needs to be some VBA to control this rather than relying soley on SQL.

I'll continue playing with it and let you know if I get an where.
Hey Xsage,

Would you mind posting your simplified database for this?
I am very busy with other projects; but, I would like to take a closer look at this one when I have a moment over the next few days.

Thanks,

Chawbacon
 

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
Well, I gave up on that particular effort, or I would be more than happy to provide a copy of that, LOL. I appreciate your not giving up on it when I clearly did. I'm glad to see you're finding the same thing.

I 'solved' the issue by putting it in a form.
 

Xsage

Dave
Joined
Jan 8, 2016
Messages
300
I broke mine trying to find a solution to the point I can't even get it working how kanfaar described the original problem properly haha.

Its simple enough to set up though, you just need two tables with a common column for example:

ComboBoxTable:

IDNameType
1Option11
2Option21
3Option32

Orders:

IDTypeComboBox
11
22


If you set the combobox rowsource to an SQL query and select the names based on if the type in table 1 = the type in table 2, it reproduces the issues kanfaar was facing.

I even tried an SQL CASE statement but I don't think Access can handle those in the rowsource field.
 

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
Hmmm, hopes are not running high on that query idea. Seemed like something to try for a minute, before I realied that I am already querying a different table. Sixes and half-dozens and all, I'm thinking, but may give it a whirl, just to see if it comes out any different.

Thanks again.

[Edit] I think part of the issue is that (whenever you're selecting the drop-down) the record is automatically dirty and, therefore, isn't yet committed. I set up this little thing do demonstrate what I was hoping to do and still can't get what I was wanting out of it. Basically, I have a list of standard items that could apply to all jobs, but a list of custom items that would only apply to particular jobs. The [LookupTable] in the attached doesn't have the Standard items, you'll just need to assume the items in there came from a separate list and others can be added to the job as needed. [/Edit]
 

Attachments

Last edited:

Chawbacon

Jack
Joined
Jul 9, 2018
Messages
569
Give this a whirl. I changed the query and added a relationship between the tables behind the lookup query.

Sorry that I do not have time to explain deeper. Too much on my plate here lately.

Good Luck!
 

Attachments

kanfaar

Thread Starter
Joined
Aug 18, 2011
Messages
169
Nice! It's at least providing a list now. Unfortunately, it's providing a list of every lookup item where there is an associated Job_ID, not limiting it to the current job.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top