Simple Query Question

[Jim318]

Hello,

I have not used VBA with Access before and I need to write something that will take the user selections from a form and alter the query fields based upon the user selection from the list boxes of the form (The List Boxes actual contain the exact name of the table fields).

If anyone could provide me a simple way of how to go about writing this in VBA I would be grateful.

Thanks,
Jim

 

[jfgambit]

You need to re-write the QueryDef utilizing VB...

Want a sample?? give me an e-mail address and I will send it to you.

Basically, it looks like the following:

Private Sub cmdSearch_Click()
On Error GoTo ErrClose

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef


Me.lstSearch.BackColor = 12632256
Set dbNm = CurrentDb()
'Set SQL Definition for lstSearch
strSQL = "SELECT tblCustomers.[Agreement#], tblCustomers.[Vin#], tblClaims.[Claim#], tblCustomers.CustLName, tblCustomers.VehicleSaleDate, tblCustomers.Program " & _
"FROM tblCustomers LEFT JOIN tblClaims ON tblCustomers.[Agreement#] = tblClaims.[Agreement#] "
strWhere = "WHERE"
strOrder = "ORDER BY tblCustomers.[Agreement#];"

' set where clause conditions
If Not IsNull(Me.txtAgree) Then
strWhere = strWhere & " (tblCustomers.[Agreement#])= '" & Me.txtAgree & "' AND"
End If
If Not IsNull(Me.txtVIN) Then
strWhere = strWhere & " (tblCustomers.[VIN#]) Like '*" & Me.txtVIN & "*' AND"
End If
If Not IsNull(Me.txtClaim) Then
strWhere = strWhere & " (tblClaims.[Claim#]) = '" & Me.txtClaim & "' AND"
End If
If Not IsNull(Me.txtLName) Then
strWhere = strWhere & " (tblCustomers.CustLName) Like '*" & Me.txtLName & "*' AND"
End If
If Not IsNull(Me.txtPDate) Then
strWhere = strWhere & " (tblCustomers.VehicleSaleDate) = #" & Me.txtPDate & "# AND"
End If
strWhere = Mid(strWhere, 1, Len(strWhere) - 4) ' remove ' and'

Me.lstSearch.RowSource = strSQL & " " & strWhere & " " & strOrder
'Set qryDef = dbNm.QueryDefs("qry&quot
'qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
Me.lstSearch.BackColor = 16777215
Me.Repaint
Me.lblSelect.Visible = True

If IsNull(Me.lstSearch.RowSource) Then
Else
Me.lblSelect.Visible = True
End If

Exit_cmdClose_Click:
Exit Sub

ErrClose:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[Jim318]

Thank you! This is very helpful to me.

Jim