Find button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

Im sure there is a really simple answer to this question?.

Ive used the find button on a number of forms and it works well as the form
is tied to the table that the search is conducted on.

However i want to put a find button on my switchboard for simple finds, ive
tried to adapt the code the wizard puts on the other forms but i can get it
to work at all?

can anyone help...
 
Hi,


You can use a DLookup, supplying the three arguments, including the
second argument, the table name. All the arguments are strings.


Hoping it may help,
Vanderghast, Access MVP
 
Michel,

thanks for the advice, but i dont know how to do what you have suggested
could you give me some example code please

Paul
 
HI,


Dim x As Variant
x=DLookup( " FieldValueToReturn ", " TableName ", " ConditionField1 =
44 AND ConditionField2 = 'alpha' " )
If IsNull(x) then
MsgBox "Sorry, not found"
End If




Hoping it may help,
Vanderghast, Access MVP
 
Michel, i think im almost there, this is where i am at the moment. ive got a
button on my main form called QuickSearch. when i click it an input box
opens for me to enter what i want to search for. then i need the value
entered in the input box to be my searched for in frmMain in all fields in
frmMain at the moment ive got it trying to look at the field IncumbentSurname
but that doesnt work either. if the search finds the record i need to open
up the form at the record it finds... any thoughts? also i dont know what 44
or 'alpha' represents in the code you sent me?

Private Sub cmdQuickSearch_Click()
On Error GoTo Err_cmdQuickSearch_Click

Dim QuickSearch As Variant

QuickSearch = InputBox("Enter the item below that you wish to search for")
If QuickSearch = DLookup("IncumbentSurname", "tblMain",
"IncumbentSurname=44 and IncumbentSurname = 'alpha' ") Then
DoCmd.OpenForm Forms!frmMain, acNormal, , , , acWindowNormal
Else
MsgBox "Sorry, not found"

End If

Exit_cmdQuickSearch_Click:
Exit Sub

Err_cmdQuickSearch_Click:
MsgBox err.Description
Resume Exit_cmdQuickSearch_Click

End Sub
 
Hi,


You said that your form was not bound, so I assumed there was no
"recordset". We can replace the Lookup with a Count if the existence, or
absence is to be tested:



QuickSearch= InputBox( ...)
If 0=len( Trim(QuickSearch & vbNullString) ) then
MsgBox "No value to look for."
'... exit sub ...
End If

' the user supplied something. If the sting get a "
' in it, it has to be doubled to not be miss-interpreted
' as a delimiter

QuickSearch=Replace( QuickSearch, """", """""" )

' if the count is 0, it is not found
If 0 = DCount("*", "tblMain", _
"IncumbentSurname=""" & _
QuickSearch & """" ) Then

MsgBox "Sorry, not found"

Else
MsgBox "It exists"

End If




On the other hand, if you want to open a recordset, you can do it with a
criteria. Something like:



QuickSearch= InputBox( ...)
If 0=len( Trim(QuickSearch & vbNullString) ) then
MsgBox "No value to look for."
'... exit sub ...
End If

' the user supplied something.

QuickSearch=Replace( QuickSearch, """", """""" )

' open a recordset, with a criteria (WHERE clause)

Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset("SELECT * FROM tblMain WHERE " & _
" IncumbentSurname=""" & _
QuickSearch & """" )

If rst.EOF then
MsgBox :"Sorry, not found"
Else
... you have the record(s) matching the criteria in rst

End If





Hoping it may help,
Vanderghast, Access MVP
 
Back
Top