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