Solved

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

Posted on 2003-11-20
20
440 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
ID: 9792151
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
ID: 9792189
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
ID: 9792261
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
ID: 9792318
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
ID: 9792383
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
ID: 9792394
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
ID: 9792446
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
ID: 9792958
I figured it out.
0
 
LVL 1

Expert Comment

by:Jagdish_Bodani
ID: 9793448
Hi,

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

Expert Comment

by:jadedata
ID: 9797635
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 32

Expert Comment

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

Author Comment

by:indyng
ID: 9797807
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
ID: 9797852
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
ID: 9797862
Nice custom procedure there,... think you've learned any of that since you been at EE???!  :)
0
 
LVL 1

Author Comment

by:indyng
ID: 9797974
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
ID: 9798031
so next month you start answering a few questions i hear!!
0
 
LVL 1

Author Comment

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

Expert Comment

by:ornicar
ID: 10066421
----------------------------------------------------------------------------------------
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
ID: 10095309
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
ID: 10147175
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

932 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

15 Experts available now in Live!

Get 1:1 Help Now