[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Retrieving a record for display on a form.

My aim is for a user to enter a value onto a form and for it to retrieve a record to be displayed in the appropriate fields on the form. From there the user can edit them (or enter new values) and save the table.

The problem is that I can only ever display the very first record.

How do I retrieve/select a record from my table that I have searched and then allow my form to display the data?

Here’s the details:-

Data base is called TestBooking.accdb and is on the company server
And I have a Query that is linked to another database (to which I am not the owner).

Using a combo box the user will enter a value (eg “79880”).
“79880”  is the 4th  record of 8 records (just for testing).
Record 1 is just “0” in the join field [Works Order No] with a load of blank data in the remaining fields. This allows a blank form to be initially displayed to the user.

If I have tried this VBA code :-

G_WON is a global variable as a string.
[Works Order No] is the field for which I need to get the remaining record fields to be displayed.

Private Sub GetRecord_BeforeUpdate(Cancel As Integer)

    Dim rs As DAO.Recordset

    Set db = OpenDatabase("s:\test booking\TestBooking.accdb")
    Set rs = db.OpenRecordset("TestBooking Query", dbOpenDynaset)
    Set frm = Forms!TestBooking                  ‘TestBooking is the name of my main form
    G_WON = Me.LiveMSG                                                      ‘User input from a combobox is stored in the ‘table ‘in field [LiveMSG].
    Me.LiveMSG = ""                                                                      ‘This simply clears the field after use – just tidying ‘up!

    rs.FindFirst [Works Order No] & " = " & G_WON                                      ‘|       
    If Not rs.NoMatch Then                              ‘|      This test returns true so my search
        MsgBox ("no match")                              ‘|      criteria must have been located ok!
    Else                                          ‘|
        Me.Bookmark = rs.Bookmark                        ‘|      I’m not sure if bookmark is the right
                                                      ‘|      thing to do
    End If                                          ‘|      
     Set rs = Nothing                              
MsgBox([Works Order No])                                                                      ‘This displayes ‘0’ – the 1st record not the ‘record that I have searched for

     End Sub
  • 2
  • 2
2 Solutions
Rey Obrero (Capricorn1)Commented:
change this line

rs.FindFirst [Works Order No] & " = " & G_WON  


if [Works Order No] is Number Data type, use this

                   rs.FindFirst "[Works Order No] = " & G_WON  

if [Works Order No] is Text Data type, use this

                 rs.FindFirst "[Works Order No] = '" & G_WON & "'"
Rey Obrero (Capricorn1)Commented:
for the "FindFirst" to work,
* your form must be bound to a table or query.
* the form is NOT in Data Entry status

what is the recordsource of the form?
usatrfeAuthor Commented:
Record source is "TestBooking Query" .
This is a query that links my own table called "TestBooking" with another table (on the company server) called "WorkCard".
The join is made where the fields ([WONO] in WorkCard & [Works Order No] in TestBooking) are equal.

Form Data Entry property is set to NO.

When I run the Sub it look like the FindFirst is actually working because the rs.NoMatch is true (IE: the if 'Not rs.NoMatch' returns false).

I will modify my code to your earlier suggestion. Incidentally,  [Works Order No] is a text string.
Use the AfterUpdate event of the combo.
usatrfeAuthor Commented:
The combination of
rs.FindFirst "[Works Order No] = '" & G_WON & "'"
and the use of AfterUpdate on the ComboBox has worked.

Thanks very much

Featured Post

Industry Leaders: 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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now