Solved

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

Posted on 2003-11-20
20
439 Views
Last Modified: 2012-05-04
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?

Or,

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

0
Comment
Question by:indyng
  • 7
  • 6
  • 4
  • +3
20 Comments
 
LVL 1

Author Comment

by:indyng
Comment Utility
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
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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?

regards
Jack
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
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
0
 
LVL 1

Expert Comment

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

http://msdn.microsoft.com/library/en-us/vbaac10/html/acprorecordsetclone.asp?frame=true#example

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"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close
End Sub
0
 
LVL 1

Expert Comment

by:Jagdish_Bodani
Comment Utility
Hi,

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:
      [Forms]![CriteriaForm]![CriteriaField2]

(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.

0
 
LVL 1

Expert Comment

by:Jagdish_Bodani
Comment Utility
Hi,

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.

0
 
LVL 1

Expert Comment

by:Jagdish_Bodani
Comment Utility
Hi again,

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

=Date()  

(Current system date)

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

0
 
LVL 1

Author Comment

by:indyng
Comment Utility
I figured it out.
0
 
LVL 1

Expert Comment

by:Jagdish_Bodani
Comment Utility
Hi,

Are you giving credit to anyone and accepting answer?
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Management can PAQ and refund the question if none of the solutions provided were used.
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
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
   
    rst1.Close

    CustIDRadio = True
   
    EditDescrip.Visible = True
    Forms!Orders.EditDescrip.Caption = "CUSTOMER SELECTION IS NOW LOCKED FOR ORDER EDITTING."
   
End If

Exit_OrderRecall_Click:
    Exit Sub

Err_OrderRecall_Click:
    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.
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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.
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Nice custom procedure there,... think you've learned any of that since you been at EE???!  :)
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
I owe all the knowledge I hav thus far to EE!! This is a great site and all you Experts are awesome
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
so next month you start answering a few questions i hear!!
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
haha..maybe in a few years when I know as much as you guys
0
 
LVL 9

Expert Comment

by:ornicar
Comment Utility
----------------------------------------------------------------------------------------
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/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now