Tech Support Guy banner
Status
Not open for further replies.

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

2K views 10 replies 3 participants last post by  kanfaar 
#1 ·
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.
 
See less See more
#2 ·
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.
 
#3 ·
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.
 
#4 ·
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.
 
#5 ·
Well, the good news is: I wasn't seeing things, so I'm just that little bit less crazy than people tell me :)
 
#6 ·
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
 
#7 ·
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.
 
#8 ·
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.
 
#9 · (Edited)
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

#11 ·
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.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top