Simple Query/Form Not Working...

[jesusoneez]

I have a table filled with computer monitor information (tblMonitorManagement). I had query that uses combo boxes (cboSerialNo, cboRoom, cboDepartment) on a form for search parameters. The query is called qryMonitorManagement_Sub. The results are displayed in a subform (frmMonitorManagement_Sub). This works for both my desktop and also my laptop based forms/queries/tables.

Although this form works when using cboRoom and cboDepartment, it does not work when using cboSerialNo, so I decided to simplify it down to get to the root of the problem...here's what I did.

I created a new form, created a combo box (again called cboSerialNo), and had it look up the serial numbers in tblMonitors. Simple.

I created a query called qryMonitors_Test, added all fields from tblMonitors (not using the * option). I then set the SerialNo field criteria to the contents of the cboSerialNo on the new form. Simple. For now I'm ignoring searching on anything else.

I ran the query with no search parameters before I added the criteria, and it obviously displayed all the records in tblMonitors. Fine.

After adding the criteria (using Build...to prevent typos), I used the form to select a serial number, run the query and get no results returned.

If I copy and paste the same serial number and change the SerialNo criteria in the query to search for that it works fine. As soon as I tell it to search for the combo box contents it returns no data.

This is driving me completely bat-poo. It's nothing I've not done a squillion time before but it just refuses to work. I've got to assume it's a query issue as opposed to a form issue, but things don't get much simpler than this should be!

 

[RuralGuy]

If it is a text field, do you surround your criteria with quotes?

 

[jesusoneez]

The criteria is trying to get the value from the combo box. This is the criteria in question.

[Forms]![frmMonitorManagement]![cboSerialNo]

The field is a text field (in the table). If I remove the above criteria and physically say ="15GTBLAH67", (as an example serial number) it works fine.

 

[RuralGuy]

What is the Bound Column of your ComboBox and what is the SQL for the RowSource?

 

[jesusoneez]

Combo box Row Source:
SELECT tblMonitors.AssetID, tblMonitors.SerialNo FROM tblMonitors ORDER BY [SerialNo];

Combo box Bound Column:
1

I thought AssetID might be the problem. I'm using the Combo Box Wizard and this is the SQL it gives me. If I edit it to look like this;

SELECT tblMonitors.SerialNo FROM tblMonitors ORDER BY [SerialNo];

the Combo Box contains no data.

 

[jesusoneez]

Got it! Changed my bound column to 2 and it works fine. Kind of annoying that Access makes the bound column 1 when using the wizard, despite me saying specifically that I want to use the data from SerialNo field.

Grrrr...

Thanks for your help.

 

[RuralGuy]

Thanks for posting back with your success!