Command to search records (1 Viewer)

lozzyme94

New member
Local time
Today, 05:53
Joined
Oct 16, 2012
Messages
9
Hi there
I'm looking for a bit of help - im running access 2010

I've got a command button (on a form)which searches text entered into a text box
so command75 search text69 which displays the results for our asset -this works perfectly

the code I've got is:
Private Sub Command75_Click()
Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0)
If Me.Recordset.NoMatch Then
MsgBox "No Record Found"
End If
End Sub

how can I change this to search Contact names as well in the same text box with the same button?

so I can enter 68 and get the asset or enter Charlie and get their assigned asset up?

thanks
Lauren
 

Notedop

Registered User.
Local time
Today, 13:53
Joined
Jul 5, 2012
Messages
19
Not sure if this would work, change [contact names] to appriate field to search.

Code:
 Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0) & " or [contact names]=" & Nz(Me![Text73], 0)

Else you could do the same search whenever no records have been found:

Code:
Private Sub Command75_Click()
Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0)
If Me.Recordset.NoMatch Then
Me.Recordset.FindFirst "[contact names]= " & Nz(Me![Text73], 0)
if me.recordset.nomatch then 
MsgBox "No Record Found"
end if
End If
End Sub
 

lozzyme94

New member
Local time
Today, 05:53
Joined
Oct 16, 2012
Messages
9
thanks for your reply

but it doesn't want to work

Run-time error '3464':
data type mismatch in criteria expression

Private Sub Command75_Click()
Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0) & " or [contact name]=" & Nz(Me![Text73], 0)
If Me.Recordset.NoMatch Then
MsgBox "no record found"
End If
End Sub

thanks
 

Notedop

Registered User.
Local time
Today, 13:53
Joined
Jul 5, 2012
Messages
19
try this:

Code:
[SIZE=4]Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0) & " or [contact name]='" & Nz(Me![Text73], 0) & "'"
[/SIZE]
 

Notedop

Registered User.
Local time
Today, 13:53
Joined
Jul 5, 2012
Messages
19
btw, what I find easier is adjusting the recordsource of the form.
You can create sql queries based on user input. This way you can customize your searches and only show those results matching the search values.

below example I use in a db:

Code:
Me.RecordSource = "SELECT Table_Orders.* FROM Table_Orders WHERE (((Table_Orders.Case_closed)=False) and (cdmv_status = '" & txt_cdmv.value &" '));"
Me.Requery
Me.Refresh

or a more extensive search. Btw you don't have to write the sqltext to a qrydef first, can be directly to recordsource.

Code:
Dim sqltext As String
Dim qdfNew As DAO.QueryDef
Dim i As Integer
Dim j As Integer
If option_history.value = True Then
sqltext = "select * from TBL_SCER_REPORT_LIVE_history"
Else
sqltext = "select * from TBL_SCER_REPORT_LIVE"
End If
' if i = 0 then it will not include the AND statement in the query
' if j = 0 then it will not include the WHERE statement in the query
i = 0
j = 0
On Error Resume Next
If IsNull(txt_so_ss.value) Then
GoTo next1
Else
If j = 0 Then sqltext = sqltext & " WHERE "
sqltext = sqltext & " [so/ss] = '" & txt_so_ss.value & "'"
i = 1
j = 1
GoTo next1
End If
next1:
If IsNull(txt_holdtype.value) Then
GoTo next2
Else
If j = 0 Then sqltext = sqltext & " WHERE "
If i <> 0 Then sqltext = sqltext & " And "
sqltext = sqltext & " [hold type] = '" & txt_holdtype.value & "'"
i = 1
j = 1
GoTo next2
End If
next2:
If IsNull(txt_status.value) Then
GoTo next3
Else
If j = 0 Then sqltext = sqltext & " WHERE "
If i <> 0 Then sqltext = sqltext & " And "
sqltext = sqltext & " [status] = '" & txt_status.value & "'"
i = 1
j = 1
MsgBox sqltext
GoTo next3
End If
next3:
If IsNull(txt_company.value) Then
GoTo next4
Else
If j = 0 Then sqltext = sqltext & " WHERE "
If i <> 0 Then sqltext = sqltext & " And "
sqltext = sqltext & " [customer name] = '" & txt_company.value & "'"
i = 1
j = 1
MsgBox sqltext
GoTo next4
End If
next4:
If IsNull(txt_so.value) Then
GoTo next5
Else
If j = 0 Then sqltext = sqltext & " WHERE "
If i <> 0 Then sqltext = sqltext & " And "
sqltext = sqltext & " [order number] = " & txt_so.value & ""
i = 1
j = 1
MsgBox sqltext
GoTo next5
End If
next5:
If IsNull(txt_srnum.value) Then
GoTo next6
Else
If j = 0 Then sqltext = sqltext & " WHERE "
If i <> 0 Then sqltext = sqltext & " And "
sqltext = sqltext & " [srnum] = '" & txt_srnum.value & "'"
i = 1
j = 1
MsgBox sqltext
GoTo next6
End If
next6:
'reset the query definition
With CurrentDb
.QueryDefs.Delete ("frm_search")
Set qdfNew = .CreateQueryDef("frm_search", sqltext)
.Close
End With
'refresh the recordsource
Me.RecordSource = "frm_search"
DoCmd.ShowAllRecords
 

lozzyme94

New member
Local time
Today, 05:53
Joined
Oct 16, 2012
Messages
9
at the moment it comes up with
run-time error '3070'
the Microsoft access database engine does not recognize 'name' as a valid field name or express

searching for a number works but no name will
thanks
 

Notedop

Registered User.
Local time
Today, 13:53
Joined
Jul 5, 2012
Messages
19
and did you try the second ?

Code:
Private Sub Command75_Click()
Me.Recordset.FindFirst "[asset]= " & Nz(Me![Text73], 0)
If Me.Recordset.NoMatch Then
Me.Recordset.FindFirst "[contact names]= " & Nz(Me![Text73], 0)
if me.recordset.nomatch then 
MsgBox "No Record Found"
end if
End If
End Sub
 

lozzyme94

New member
Local time
Today, 05:53
Joined
Oct 16, 2012
Messages
9
Yes
however,
I've just tired using the same text box but different button and that doesn't work either.
It says the same error - does not recognize etc.
thanks
 

Notedop

Registered User.
Local time
Today, 13:53
Joined
Jul 5, 2012
Messages
19
can you post the database or a part of it?
I can take a look at it when I finish work in an hour or so.
 

lozzyme94

New member
Local time
Today, 05:53
Joined
Oct 16, 2012
Messages
9
zipped and attached

thanks
 

Attachments

  • New Database PDA - Copy.zip
    336.6 KB · Views: 113

aarif786000

New member
Local time
Today, 18:23
Joined
Nov 6, 2012
Messages
3
Datatype mismatch error showing whenever you try to compare two different datatype so if field datatype is number just compare it "FieldName=" & formName.ControlName, if it text Datatype then "FieldName='" & formName.ControlName & "'"

Here you can see that i have added sigle quote on text datatype.
So correct your quotation while compare you textbox value to text datatype field.
 

Users who are viewing this thread

Top Bottom