combo box not selecting properly

[groengoen]

I have a tools stores database and am working on a form to issue tools from one location to another. I have set up combo boxes for:
from_location
to_location
tools_id

the tools_id combo box is based on a query which searches a tools_in_stock table for all the tools which are available in that location using the from_location value. This works ok i.e. the tools_id combo box is filled with those tools available at that location.

The problem is that whenever I select a tool in the tool_id combo box, it always reverts to the first tool in the box. I can't select any other tool.

the code for the query behind the tool_id box is:

SELECT tbl_tools_in_stock.loc_Id, tbl_tools.description, tbl_tools.serial_no
FROM (tbl_tools_in_stock INNER JOIN tbl_location ON tbl_tools_in_stock.loc_Id = tbl_location.loc_Id) INNER JOIN tbl_tools ON tbl_tools_in_stock.tools_Id = tbl_tools.tools_Id
WHERE (((tbl_tools_in_stock.loc_Id)=[Forms]![frm_issue_to_location]![from_location_id]));


Is there some way of allowing the user to only select any tool from tools available via this combo box?

 

[ajetrumpet]

Do you have a sample to post on this?

 

[groengoen]

I will send you on one shortly. Thanks for your interest

 

[groengoen]

Sorry, my database seems to exceed the limit for attachments even when I zip it. I will try to get a shrunken copy

 

[groengoen]

Here is the zipped database. Thanks for your patience

 

[ajetrumpet]

Here are some thoughts on this:

1) Change the rowsource of it to this:

SELECT qry_get_loc_tools.description FROM qry_get_loc_tools;

...instead of the entire query itself. The query has JOINS in it, which may be a problem here. I think that's the case, and it's probably a rule with them, but I'm not sure, as I never use actual objects as rowsources with form controls (that doesn't work too well in general, by the way). Multi-column controls are meant to pull specified records, not entire recordsets. Just FYI, I guess.

2) Gid rid of the control source of the combo box (or change the data type of the field). The field of the underlying table that binds this box is set to NUMBER, LONG INTEGER. You've got text values in this field. That combination doesn't float.

3) The only two tables that are needed for your query are table_tools_in_stock and table_tools. The other one (tbl_location) is excess baggage. It's not needed for the query to run properly. If you drop it, the JOIN becomes:

FROM tbl_tools INNER JOIN tbl_tools_in_stock ON 
   tbl_tools.tools_Id=tbl_tools_in_stock.tools_Id

WHERE...

Come to think of it, why does this query even exist? Any SQL you have written in it can be used as the actual rowsource property for the box anyway...

<edit>
Something else I forgot to mention too: I ran end to an endless loop of errors when I was testing the file with the above suggestions. It would be a good idea to cut the After_Update event out of the box before you experiment. Either that, or comment it, so it doesn't run.

 

[groengoen]

Here are some thoughts on this:

1) Change the rowsource of it to this:

SELECT qry_get_loc_tools.description FROM qry_get_loc_tools;

... Multi-column controls are meant to pull specified records, not entire recordsets...

I just meant to populate the control so that I could select an individual record. Maybe I am trying to do too much here but I just wanted to ensure that the control only showed those tools which were in the selected location, rather than all the tools in the tools table.

2) Gid rid of the control source of the combo box

I got rid of the control source.

2) The field of the underlying table that binds this box is set to NUMBER, LONG INTEGER. You've got text values in this field.

The problem about the text values is more apparent than real, as I had an underlying source which just showed the description rather than the ID. I now show 2 columns and it looks better

3) The only two tables that are needed for your query are table_tools_in_stock and table_tools. The other one (tbl_location) is excess baggage.

Good idea. I got rid of this additional table.

Come to think of it, why does this query even exist? Any SQL you have written in it can be used as the actual rowsource property for the box anyway...

The query exists because I am passing the from_location into it as a parameter, which shows up as a criterion. As I say, maybe I am being too ambitious. Is there another way of bringing up all the tools in a particular location and then selecting one?

I did most of the above and got nothing in my query

 

[ajetrumpet]

Is there another way of bringing up all the tools in a particular location and then selecting one?

I did most of the above and got nothing in my query

Please post the updated copy, so I can see what you've done.

And yes, I think the ambition got to you.

 

[groengoen]

I have figured something out by following some of your principles actually. I stuck the SQL into the row source and included a condition that the tools in stock must not be zero. This seems to be working a whole lot better. I must set about debugging the rest now, Thanks very much for your help.

Geoff.