How do I find a record in a form using VBA?

Hi Experts,

I want to be able to find a record in a form that is in form view. I know I can use the built in DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70, but isn't there another way so the "replace" option of the find record pop up window is not displayed?


How do I NOT use this windows default "Find Record" pop up window and read in a number of the record that needs to be found from a text box on the form instead. I'd like to use a button to activate the find record event. Thanks

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

indyngAuthor Commented:
The second option of:

How do I NOT use this windows default "Find Record" pop up window and read in a number of the record that needs to be found from a text box on the form instead. I'd like to use a button to activate the find record event.

Is what I really want. THANKS
jadedataMS Access Systems CreatorCommented:
Hey indyng!

  I'm sorry but I am completely not understanding what you are trying to accomplish...
  How about if we start with what you DO WANT and work from there?

indyngAuthor Commented:
Okay, sorry for the confusion. What I want is the following:

At the click of a button called "Recent Order Recall" the following is done:

- The OrderID of the most recent order for a CustomerID(selected from a combo box) is read in from a table called "Orders"
- The most recent order is the order that was entered today. If no order was entered for the CustomerID today then display message saying "no order entered today for customer"

- The table "Orders" contains the field names: CustomerID, OrderID, OrderDate, OrderTime

- This OrderID is used to recall the details of the order and the order is displayed in a form so the order taker can modify the order if the customer requests a change.

I already have the forms and tables set up. All I need is a VBA code to do what I have stated. Sorry if this still sounds confusing. I'll try my best to make it clearer. Thanks
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

You want to use the recordsetclone property to find the record, then set teh current form's recordset to the recordset clone...

Sub SupplierID_AfterUpdate()
    Dim rst As Recordset
    Dim strSearchName As String

    Set rst = Me.RecordsetClone
    strSearchName = Str(Me!SupplierID)
    rst.FindFirst "SupplierID = " & strSearchName
        If rst.NoMatch Then
            MsgBox "Record not found"
            Me.Bookmark = rst.Bookmark
        End If
End Sub

here is solution:

(1) Table T1 with two Fields Fld1 and Fld2.

(2) Create  an unbound form "CriteriaForm" with two text boxes name them CriteriaField1 and CriteriaField2.  Alos add a Command Button in this form. Name it CMD1

(3) Create a query  Q1 based on table T1.  Add both fields Fld1 and Fld2.   Add this criteria under Fld2:

(4) Create another Form called "MainForm" based on query Q1 created in step 3.  Add both  fields Fld1 and Fld2 in this form.

(5) Go back to CriteriaForm and add following code to On Click event of Commnad Button CMD1:

Private Sub CMD1_Click()
DoCmd.OpenForm "MainForm"
End Sub

(6) Open form CriteriaForm in form view.   Key Value in criteriaField2  (value which you searching).  Click on command button CMD1.  It will open Main Form and find a record which meets your value in criteriaField2.

you can add more than one criteria and add all kind of code to go back and forth between two forms.

Hope this is what you were looking for and this solution works for you.


Sorry while I was typing my earlier response, you gave more details about your tables etc.  

Solution I have given is very generic and so simple with one line of code.  I belive in less and less code so that anyone can understand what you doing.  In my method there is extra form and query created but this makes everything very easy to understand without writting too much code.

Hi again,

if you are looking for order entered today than Fld2 will be date type and in CriteriaField2 add a formula :


(Current system date)

 or you can key any date in criteria Field2 and click on buton will find order for that date.

indyngAuthor Commented:
I figured it out.

Are you giving credit to anyone and accepting answer?
jadedataMS Access Systems CreatorCommented:
We would be interested (as would future searchers) in how you made this requirements a reality.
You've been asking some very good questions of late and if you figured it out, the answer will likely be magic.
:) Jack
jadedataMS Access Systems CreatorCommented:
Management can PAQ and refund the question if none of the solutions provided were used.
indyngAuthor Commented:
Thanks jadedata. I will post the VBA code I used. I used various codes from other projects I have done implementing the help I received from you Experts. I did not use any of the solutions provided here. Thanks for responding anyways.

Here is the code:

Private Sub OrderRecall_Click()
On Error GoTo Err_OrderRecall_Click

Dim strControl As String
Dim rst1 As DAO.Recordset
Dim strSQL As String
Dim TempCustomerID As Double

Const Red As Integer = 255

TempCustomerID = Forms!Orders.CustomerID

If Not IsNull(Forms!Orders.CustomerID) And IsNull(Forms!Orders.ItemCount) Then

    CustomerID.ForeColor = Red
    Forms!Orders.CustomerID.Locked = True   ' Lock the CustomerID field so the customer cannot be changed
                                        ' on the recalled order.
    CurrentDb.Execute "DELETE * FROM [Orders] WHERE ([OrderID]=" & Me("OrderID") & ");", dbFailOnError

    strSQL = "SELECT * From [Recent Order Qry] WHERE [CustomerID]= " & TempCustomerID & ";"

    Set rst1 = CurrentDb.OpenRecordset(strSQL)

    RecentOrderID = rst1!OrderID

    strControl = "OrderID"

    DoCmd.GoToControl strControl

    DoCmd.FindRecord RecentOrderID, acEntire, False, , False, acCurrent, True

    CustIDRadio = True
    EditDescrip.Visible = True
End If

    Exit Sub

    MsgBox Err.Description
    Resume Exit_OrderRecall_Click
End Sub

' The DELETE query used because a new order record was saved before the recent order record of the customer can be recalled. If it was not deleted first, then the query selecting the recent order would select this new record instead.
jadedataMS Access Systems CreatorCommented:
ok then.  Customer service with PAQ on your answer and refund on request.  Make sure to include a link to this question in your post.
jadedataMS Access Systems CreatorCommented:
Nice custom procedure there,... think you've learned any of that since you been at EE???!  :)
indyngAuthor Commented:
I owe all the knowledge I hav thus far to EE!! This is a great site and all you Experts are awesome
jadedataMS Access Systems CreatorCommented:
so next month you start answering a few questions i hear!!
indyngAuthor Commented:
haha..maybe in a few years when I know as much as you guys
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)


Cleanup Volunteer

jadedataMS Access Systems CreatorCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
PAQed, with points refunded (500)

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.