Dynamic sql statement using values from combo boxes

[jamato]

I am creating a form that will search a table based on up to three parameters and display the results in a list in which the user can select from. The table has 4 fields.
fields 1-3 are the parameters and the 4th is a corresponding document name based on the first three fields.

example:
fld1 fld2 fld3 fld4
A B C abc.doc
A B D abd.doc
if a user searches for "abc" you get abc.doc
if a user searches for "ab " (does not select a third parameter) you get abc.doc abd.doc

I created three combo boxes (one for each parameter). The values chosen in each box are then used in a query to populate a fourth combo box.

This works fine if the user selects an item from each of the three combo boxes. However, I cannot get the query to work of a user does not use all paramters. I need to allow for an "All" select in the first three combo boxes.

Probably a simple solution to this, but I am very rusty on my programming skills to begin with and am relatively new to access.

 

[LaurieHamlin]

Does the user click on a button to get the *.doc to open? If so, try setting up a default value for the third parameter by code in the onClick event.

If Len(Me.Controls.Field("fld4&quot = 2 Then
str = fld4.Text
str = str & "c"
fld4.Text = str
End If

Or if they must enter all 3:
If Len(Me.Controls.Field("fld4&quot = 2 Then
MsgBox("You must enter the third paramter.&quot
End If

Hope that helps.

 

[LaurieHamlin]

Sorry, (it's Monday morning). It should be

If Len(recordset.Controls.Field("fld4&quot = 2 Then
str = fld4.Text
str = str & "c"
fld4.Text = str
End If

Or if they must enter all 3:
If Len(recordset.Controls.Field("fld4&quot = 2 Then
MsgBox("You must enter the third paramter.&quot
End If

 

[jamato]

Actually, the user can choose all, some or none of the parameters.

That is where my problems lies, I cannot figure out how to make the where statement in my query change to handle a scenerio where a user does not use all of the parameters.

My where statement needs to be able to dynamically change from basically
"select fld4
from table
where fld1=combo_box1 and fld2=combo_box2 and fld3=combo_box3;"

to (for example if the user does not choose a fld3 parameter)

"select fld4
from table
where fld1=combo_box1 and fld2=combo_box2;"

Note that the fld3=combo_box3 is not present in the 2nd select because the user does not want to limit the search by that parameter

 

[jamato]

May have solved my own prob. sorta a hack or spagetti code if you will. I made a bunch of if statements that took care of all possibilities. in each if I have a rowsource=&quot;<the correct sql statement>&quot;

seems to work, just kindof a cheesy way of doing it. If anyone knows of the &quot;right&quot; way of doing this, feel free to reply.

thanks

 

[JaneB19]

Hi jamato,

I know it was almost a year ago since you posted this thread but I was wondering if you eventually managed to sort your problem, and if so how you managed it (if you can remember)?

I'm wanting to creating the same sort of SQL statement, but to generate a report and your help would be greatly appreciated!

Thank you in advance

Jane

 

[calandrelli]

I know this is an old post but here is a simplistic example how I solved the issue.

I set the form's "On-Open" event to display all reords in the table.

 Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = ("SELECT Table1.Name, Table1.State, Table1.Date FROM Table1;")
End Sub

Then I added a combobox, textbox and commandbotton. The command button's on-click event writes the dynamic sql based on the results of the combo and text box as follows....

Private Sub Command12_Click()
Dim Whereclause As String
Whereclause = "WHERE "

If Me.Combo8 <> "" Then
     Whereclause = Whereclause & "Table1.State = " & Chr(34) & (Me.Combo8) & Chr(34) & " AND "
End If
If Me.Text10 <> "" Then
     Whereclause = Whereclause & "Table1.Date > " & Chr(35) & (Me.Text10) & Chr(35) & " AND "
End If
If Right(Whereclause, 4) = "AND " Then
     Whereclause = Left(Whereclause, Len(Whereclause) - 4)     ' strip off 'AND '
Else
     Whereclause = Left(Whereclause, Len(Whereclause) - 6)     ' strip off 'WHERE '
End If

Me.RecordSource = ("SELECT Table1.Name, Table1.State, Table1.Date FROM Table1 " & Whereclause & ";")

End Sub

 

[Tigerlili3]

The following Microsoft Knowledge Base article may also be helpful...Article 209645

http://support.microsoft.com/?kbid=209645