Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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?

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
indyng
Asked:
indyng
  • 7
  • 6
  • 4
  • +3
1 Solution
 
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
0
 
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?

regards
Jack
0
 
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
0
Independent Software Vendors: 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!

 
ericiCommented:
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
 
Jagdish_BodaniCommented:
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
 
Jagdish_BodaniCommented:
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
 
Jagdish_BodaniCommented:
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
 
indyngAuthor Commented:
I figured it out.
0
 
Jagdish_BodaniCommented:
Hi,

Are you giving credit to anyone and accepting answer?
0
 
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
0
 
jadedataMS Access Systems CreatorCommented:
Management can PAQ and refund the question if none of the solutions provided were used.
0
 
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
   
    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
 
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.
0
 
jadedataMS Access Systems CreatorCommented:
Nice custom procedure there,... think you've learned any of that since you been at EE???!  :)
0
 
indyngAuthor Commented:
I owe all the knowledge I hav thus far to EE!! This is a great site and all you Experts are awesome
0
 
jadedataMS Access Systems CreatorCommented:
so next month you start answering a few questions i hear!!
0
 
indyngAuthor Commented:
haha..maybe in a few years when I know as much as you guys
0
 
ornicarCommented:
----------------------------------------------------------------------------------------
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
 
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0
 
Computer101Commented:
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now