Should I use just one or many queries???

[Vol]

I have a form in which users may find a particular prospect. Inside an option group, I have text boxes that give the parameter in which to search for each option(field). My problem is, the list box which the query fills in can only be bound to one query. This is fine, but I would like to have the query run off of whichever box is checked. ie...if option1(HomePhone) is checked, the query will run and use the info in the corresponding text box to find the prospect. If option2(LastName) is checked, the query will run off of that corresponding text box. I've tried all sorts of macros and i'm new to the VBA...any suggestions??? X-)

Thanks!
Crystal

C. Szymanski
V0lup2us@aol.com

 

[raskew]

The '+ Operator' (look in the help file) is helpful in this situation.

Say you are looking for postal/ZIP code information and you have four combo boxes (cboCity, cboCounty, cboState, cboZip), which you may or may not fill-in.
You would like to create a query from the available information only, and use it to fill a fifth combo box (cboAll)

'******************************
Dim db as Database
Dim strSQL as string, where as string

'evaluate contents of each combo box, including only when
'the combo box is not null
 
where = Null
where = where & " AND [CityName]= '" + Me!cboCity + "'"
where = where & " AND [CountyName]= '" + Me!cboCounty + "'"
where = where & " AND [Abbrev] = '" + Me!cboState + "'"
where = where & " AND [Zipcode] = '" + Me!cboZip + "'"

docmd.DeleteObject acQuery, "qryTemp" 'removes previous query

strSQL = "SELECT DISTINCT CityName, CountyName, Abbrev, " _
& "Zipcode FROM qryAll" & " where " + Mid(where, 6) & ";"

Set qd = db.CreateQueryDef("qryTemp", strSQL)

Me!cboAll.RowSource = strSQL
Me!cboAll.Requery
'******************************

If you had filled in only cboState with 'KS', your strSQL would look like this:
SELECT DISTINCT CityName, CountyName, Abbrev, Zipcode FROM qryAll where [Abbrev] = 'KS';

However, if you also included cboCity with 'Franklin', your strSQL would look like this:
SELECT DISTINCT CityName, CountyName, Abbrev, Zipcode FROM qryAll where [CityName]= 'Franklin' AND [Abbrev] = 'KS';

The + Operator is the key to including only those combo box selections which are not null.

 

[Vol]

OK...so I hate to sound completely blonde, but will this build a temp query for me? or should I refer to the one I already have set up??
Thanks!
C-
C. Carpenter
V0lup2us@aol.com

 

[raskew]

The code will build the query for you. Referring back to the code,

.... this line delete any existing qryTemp

docmd.DeleteObject acQuery, "qryTemp" 'removes previous query

....this line is the complete SQL statement, with the Where statement build based on the content of the combo boxes

strSQL = "SELECT DISTINCT CityName, CountyName, Abbrev, " _
          & "Zipcode FROM qryAll" & " where " + Mid(where, 6) & ";"

....and this line creates the QueryDef for qryTemp, based on strSQL:

Set qd = db.CreateQueryDef("qryTemp", strSQL)

I suggest removing your text-boxes from the option group and just let them stand-alone. There's no need for the user to check a box. They just to fill in data in a particular control and the code will recognize and include it in the Where statement.

 

[Quehay]

If I understand your question correctly, it is not combining a number of parameter entries, but rather choosing which pertains that you want.

My suggestion:

Create two different queries. They are the same except that one has the "home phone" field as part of the query and the other has "bus. phone" (whatever you want as separate possibilities). In the criterion entry for the PH# fields put "Forms!frmProspects.cboPh#" (you fill in the specifics).

Create a form level variable to hold the option group selection, e.g. Private mQueryChoice as String.

For your option group frame do a Select case based on the values of each option button:

Home Phone = 1 Bus. Phone = 2 etc.

Select Case fraPhoneChoice (the frame for the group)

Case 1
mQueryChoice = "qry_HomePhone"
Case 2
mQueryChoice = "qry_BusPhone"

End Select

Now put a command button that runs the following on Click:

'DAO object variables
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(mQueryChoice)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)

Now either query will run based on the option selection (make sure you have a default option value).

Be sure to set all the object variables = Nothing in your
error exit label.

If this is too cryptic ask for details. You could also just change the SQL statement based on the selection, but this is an easy way to get it done.

 

[Quehay]

It always helps to read the question. . .you want to fill a list box for display: OK, this will be easy: Take each of the queries developed above and look at the SQL view. Take the SQL statement of the one you want for the default query (corresponding to the default option choice) and copy the text. In the RowSource property of the listbox delete the existing SQL statement and paste the default query. The form will load with this query as default. In the AfterUpdate event of the option frame do the Select Case and in response to the different option (Case 2) have a line that says, using the alternate SQL statement (you could make it tighter by putting the two SQL statements into str variables and putting the respective variables into the line where the literal SQL statment is now):

lstResults.RowSource = "SELECT from etc"

 

[Vol]

Quehay...The first option you provided me with is more of what I was looking for. Thanks!!!
Ok, so i've got it all set up and it looks fine to me, but i'm getting a run time error...

'Too few parameters, expected 1'

On debug, it highlights...
Set rs = qdf.OpenRecordset(dbOpenSnapshot)

Any ideas as to why this would be happening? Thanks again for your help. *:->*
C-
C. Carpenter
V0lup2us@aol.com