Form vb error

[Help]

I receive this error at Set bdNm = CurrentDb()
Compile error:
Object required

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As
String
Dim dbNm As String
Dim qryDef As String
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource

strSQL = "SELECT Elemental Analysis Request Tbl.UserID,
SELECT Elemental Analysis Request.Submitter, SELECT
Elemental Analysis Request.Date, SELECT Elemental
Analysis Request.Date, SELECT Elemental Analysis
Request.SampleID " & _
"FROM SELECT Elemental Analysis Request"

strWhere = "WHERE"

strOrder = "ORDER BY SELECT Elemental Analysis
Request.UserID;"


'Set the WHERE clause for the Listbox RowSource if
information has been entered into a field on the form
If Not IsNull(Me.txtSubmitter) Then '<--If the textbox
txtSubmitter contains no data THEN do nothing
strWhere = strWhere & " (SELECT Elemental Analysis
Request.Submitter) Like '*" & Me.txtSubmitter & "*'
AND" '<--otherwise, apply the LIKE statment to the
QueryDef
End If

If Not IsNull(Me.txtproject) Then
strWhere = strWhere & " (SELECT Elemental Analysis
Request.Project) Like '*" & Me.txtproject & "*' AND"
End If

If Not IsNull(Me.txtMonth) Then
strWhere = strWhere & " (SELECT Elemental Analysis
Request.Month) Like '*" & Me.txtMonth & "*' AND"
End If

If Not IsNull(Me.txtdate) Then
strWhere = strWhere & " (SELECT Elemental Analysis
Request.Date) Like '*" & Me.txtdate & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" &
strOrder

End Sub



Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox

DoCmd.OpenForm "Elemental Analysis Request
Form", , , "[Submitter] = " & Me.lstCustInfo, , acDialog

End Sub

Thanks in ADVANCE

 

[Gerald Stanley]

Access is objecting to the fact that you have declared dbNm
as a string variable then tried to set it to CurrentDb.

Hope This Helps
Gerald Stanley MCSD

 

[dan artuso]

Hi,
Do you know what that statement does?
It returns a reference to the current database.
This will not be a string but a database object.

So you have to declare it as such:
Dim dbNum As Database

The same goes for your querydef variable, declare it as a
querydef not a string.