Recordset.FindFirst

[Guest]

Hello.
I have a form (single form view) with a subform (data sheet view).
On the form I have a Case# field and I have create a "Previous" and "Next"
button. I find myself having to use the Previous and Next button all the
time. Do you know of a way that I could add a text box where I can type a
case number then click an Ok button next to so that the Correct Case# can
appear on the form?


Thanks. Your help is greatly appreciated.

Iram/mcp

 

[Guest]

Rather than creating extra controls on your form, why not use the built-in
search functionality?

You could press Ctrl+F while on the field or the Binoculars button in your
menu bar. What I often do is creat a custom shortcut menu with the binoculars
button on it. The user then right-clicks on the field and clicks the button
on the popup menu.

Steve

 

[Guest]

I have used the binoculars button on different forms but find it a little
cumbersome.
I would like to use the Recordset.FindFirst code after update to find a
record instead. Thanks anyways.

IRam/mcp

 

[Guest]

Ok, so you want a textbox and a button?

Here's the code for your button click event:
Private Sub MyButton_Click()
' Find the record that matches the user input.
Dim rs As Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Case#] = " & Nz(Me![MyTextbox], 0)
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "No Match!"
End If

Set rs = Nothing

End Sub

Edit to your own button name and textbox name...

Steve

 

[Guest]

Hi Iram,

You could add a combobox to your form with the control wizard active. Chose
the 3rd option "find a record on my form ..."

Luck
Jonathan

 

[Allen Browne]

FWIW, I've had better results from:
Set rs = Me.RecordsetClone
rather than:
Set rs = Me.Recordset.Clone

You might like to explicitly save changes before moving record, i.e.:
If Me.Dirty Then Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, so you want a textbox and a button?

Here's the code for your button click event:
Private Sub MyButton_Click()
' Find the record that matches the user input.
Dim rs As Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Case#] = " & Nz(Me![MyTextbox], 0)
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "No Match!"
End If

Set rs = Nothing

End Sub

Edit to your own button name and textbox name...

Steve

I have used the binoculars button on different forms but find it a little
cumbersome.
I would like to use the Recordset.FindFirst code after update to find a
record instead. Thanks anyways.

IRam/mcp

 

[Guest]

Jonathan- I didn't know about those three wizard options when creating a
combo box. The third option works for the form that I am working on.

SteveM and Allen Brown- I will be using your code on another form where I
would like a text box and button.

Thank you all for your help.

Iram/mcp

 

[David W. Fenton]

FWIW, I've had better results from:
Set rs = Me.RecordsetClone
rather than:
Set rs = Me.Recordset.Clone

I just don't understand why you'd bother to set a recordset variable
for the recordsetclone, instead of just using:

With Me.RecordsetClone
...
End With

It's much more efficient and doesn't require any cleanup. Given that
this recordset exists before you use it, I don't see what benefit
there is to assigning a pre-existing object to a newly created
variable.