Solved

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

Posted on 2003-11-20
20
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

624 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