Retrieving a record for display on a form.

Posted on 2011-05-10
Last Modified: 2012-05-11
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
Question by:usatrfe
    LVL 119

    Accepted Solution

    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 & "'"
    LVL 119

    Expert Comment

    by:Rey Obrero
    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?

    Author Comment

    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.
    LVL 77

    Assisted Solution

    Use the AfterUpdate event of the combo.

    Author Closing Comment

    The combination of
    rs.FindFirst "[Works Order No] = '" & G_WON & "'"
    and the use of AfterUpdate on the ComboBox has worked.

    Thanks very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    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…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 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

    16 Experts available now in Live!

    Get 1:1 Help Now