VBA forms/parameters No Errors; No Results

[Teddie]

Hello, I am trying to create a form that has multiple parameters. A user needs to enter a parameter but if one is left blank that is okay.

Private Sub DomesticQueryRun2_Click()

    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim LSQL As String
    Dim LTrackNum
 
    'Open connection to current Access database
   Set db = CurrentDb()

If Len(Forms![test]![TrkNbr]) > 0 Then
   LTrackNum = "Y"
    Else: LTrackNum = "N"
    End If

If LTrackNum = "Y" Then

LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]

Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr]
End If

Set Lrs = db.OpenRecordset(LSQL)

Lrs.Close
Set Lrs = Nothing
Set db = Nothing


End Sub

[puppydogbuddy]

You did not state what problem you are having with your code? If I had to guess, I would guess that either Cust_Nbr or Shp_trk_nbr are text data types requiring text syntax as shown below, not the numeric syntax you used.

Cust_Nbr = '" & Forms![test]![CustNbr] & "'" OR Shp_trk_nbr = '" & Forms![test]![TrkNbr] & "'"

[PianoMan64]

Hello, I am trying to create a form that has multiple parameters. A user needs to enter a parameter but if one is left blank that is okay.
[code=vb]
Private Sub DomesticQueryRu n2_Click()

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LTrackNum

'Open connection to current Access database
Set db = CurrentDb()

If Len(Forms![test]![TrkNbr]) > 0 Then
LTrackNum = "Y"
Else: LTrackNum = "N"
End If

If LTrackNum = "Y" Then

LSQL = "select * from customer WHERE customer.Cust_N br = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]

Else: LSQL = "select * from customer WHERE customer.Cust_N br = " & Forms![test]![CustNbr]
End If

Set Lrs = db.OpenRecordse t(LSQL)

Lrs.Close
Set Lrs = Nothing
Set db = Nothing


End Sub[/code]

Teddie:

I'm assuming what you're trying to do is have 2 text boxes on your form, and fill what which ever ones you want and click on some button to do a search? If so, then here's what you need to do in your code:

You have to build the LSQL variable with the items you need based on the fact if there is anything in them any or all the fields. i.e.:

Private Sub DomesticQueryRun2_Click()

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
[b] Dim Criteria As String[/b]
Dim LTrackNum

'Open connection to current Access database
Set db = CurrentDb()

'If Len(Forms![test]![TrkNbr]) > 0 Then
'LTrackNum = "Y"
'Else: LTrackNum = "N"
'End If
[b]LSQL = "Select * From CUSTOMERS WHERE "[/b]
[b]If IsNull(Me.trknbr) And IsNull(Me.custnbr) Then
		'Show all records
		LSQL = Left(LSQL, Len(LSQL) - 6) ' To strip off Where Statement
Else
[/b][b] ' This section will build the criteria section of the SQL Statement that you're going to
' to be passing to the OpenRecordset. When the LSQL and Criteria Section are merged at the
' Bottom of the IF section, you can step through it and see how it works.

' I've also use the shortcut to refer to items on your form using me. syntax. This is alot
' shorter than using the Forms references that you were using before.

		If Len(Me.trknbr) > 0 Then
			If Len(Criteria) > 0 Then
				Criteria = Criteria & "AND customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
			Else
				Criteria = Criteria & "customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
			End If
		End If
		If Len(Me.custnbr) > 0 Then
			If Len(Criteria) > 0 Then
				Criteria = Criteria & "AND customer.Cust_Nbr = " & Me.custnbr & vbCrLf
			Else
				Criteria = Critiera & "customer.Cust_NBR = " & Me.custnbr & vbCrLf
			End If
		End If
End If

LSQL = LSQL & Criteria
								

[/b]    'If LTrackNum = "Y" Then

'LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr] & " OR Shp_trk_nbr = " & Forms![test]![trknbr]

'Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr]
'End If

'create a ListControl and name it CustResults.

Then do the following
set me.custResult.recordset = db.OpenRecordset(LSQL,dbOpenSnapShot)

'This way you can have the results of your search display on your screen.

'Make sure to set the properties of the ListControl to allow for all the columns in the return result as well as setting the widths for each column. If you need help with that, let me know.
'Set Lrs = db.OpenRecordset(LSQL)

'Lrs.Close
'Set Lrs = Nothing
Set db = Nothing


End Sub

[Teddie]

Thank you PianoMan. I used your code and am getting a "Compile error: Method or data memeber not found" on the line
Set Me.custResults. Recordset = db.OpenRecordse t(LSQL, dbOpenSnapshot)
Any idea how to fix this?

Okay, after googling the above error I changed Me.custResults. Recordset to Me!custResults. Recordset and am now getting a Run-Time error '3061' Too few parameters. Expected 1.

Also, can you help me with setting the properties of the ListControl.

Thanks so much!! I've been pulling my hair out for the last week trying to get this to run.