Simple but annoying Combo Problem 1

[Frank72]

Hows it going

On a form i have 3 combos each bound to tables with values (sex,model,location) and indexes. the combos column count is 2, the bound columns are 1. I want to use these combos as parameters or criteria in a query. I also want to enable them to be left blank hence selecting all records in that field as opposed to just e.g. Dublin or Male.
do I need the indexes or can i just use the names in the combos Dublin, Male, Pajero . I have used the Is Null method in thwe QBE to acheive the all records effect but still not working but that is porbably another fault somewhere else. Please help this seemed so simple to start with and is turning into a nightmare

any help greatly appreciated.

thanks

Frank

 

[birgitd]

Hi Frank,

It kinda depends on what you need to do whether you need the "index" field in your combo box or not. Typically, the user needs the "name" value, but you insert the "index" value into your table as a foreign key value.

For example, one of your combo boxes is Location (let's say it has 2 rows - 1: US, 2: Canada). When you insert a row into your table, do you insert US/Canada, or 1/2? Ideally, from a database standpoint, you should be inserting 1/2 into a field called LocationID. If you are inserting US/Canada, then you don't need the "index" value in your combo box.

A cheap hack for building your sql dynamically using the 3 combo boxes (any or all of which can be null), is to build the where clause into a variable and then insert that variable into your sql string.

For example: cmbLocation = NULL, cmbSex = 1, cmbModel = 6

Declare variables txtlocation, txtsex, txtmodel, txtwhere

Then (using whatever mechanism you have to run the query .. button, etc) load the variables in order:

if ISNULL(cmbLocation) = True, then
txtlocation = ""
Else
txtlocation = "location = " & cmbLocation & "and"
End if

(repeat for other 2 values -- the 3rd won't have an "and" at the end)

Now, build your where clause:

txtwhere = "where" & txtlocation & " " & txtsex & " " & txtmodel

The gotcha here is that you may end up with a trailing "and" at the end of your statement. You'll need to remove that ... you can use RIGHT() or REPLACE() to take care of that.

Hope that helps,

Birgit