Listbox; Populate and Requery

[Tezcatlipoca]

Ok, this really should be the simplist job of all but my I'm having a mental block and simply cannot get my code to work.

I have a master table, TblFileData, that contains all records. The salient columns in this are fldCode and fldName, fldType and fldDepartment

I have a listbox on a form, lstSearch, together with two dropdown boxes, dpbType and dpbDep, and a search button, btnSearch.
The listbox has 4 columns, two of them hidden (at 0cm).

What should happen is that the form loads up, and the user uses the two dropdown boxes to decide on the type of record they want. these two dropdown boxes already have all the available values populating them.

Once the user has decided, they should click the search button and the listbox should fill out with the filtered results. If the dropdowns have not being used (i.e. they are both blank), all records should fill out in the listbox.


The problem is I'm buggering up my SQL. I have the RowSource of the listbox filled out as:

SELECT TblFileData.fldCode, TblFileData.fldName, fldFileType, fldDepartment FROM TblFileData WHERE fldDepartment = [forms!][frmMain][dpbDep] AND fldFileType = [forms!][frmMain][dpbType];

The OnClick of my button is a simple:

Me.lstSearch.Requery

But the listbox never seems to fill out with anything. I know problem is in my WHERE statement, since I can take it out and the box fills up and can be requeried, but I'm not good enough with SQL to know why it isn't working.

Can anyone help, please?

 

[vbaInet]

You just needed the bang ! in the right places.

SELECT fldCode, fldName, fldFileType,  fldDepartment FROM TblFileData WHERE fldDepartment =  [forms][COLOR=Red][B]![/B][/COLOR][frmMain][COLOR=Red][B]![/B][/COLOR][dpbDep] AND fldFileType = [forms][COLOR=Red][B]![/B][/COLOR][frmMain][COLOR=Red][B]![/B][/COLOR][dpbType]

 

[Tezcatlipoca]

Ah, thank you! I knew it was something idiotically simple I was missing; just couldn't for the life of me see what it was.

Thanks again

 

[vbaInet]

You're welcome!