Problem getting value of current record using me.variable

[gbmatay1]

Access 2013

I'm having problems picking up the value of a variable in the current record displayed on a form

I'm using the following code picked up from Allen Brown's Website:

Dim rs As DAO.Recordset

If Not IsNull(Me.CustomerID) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'CustomerIDLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.CustomerID
![Description] = "Last customerID, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.CustomerID
.Update
End If
End With


rs.Close
End If
Set rs = Nothing

Whichever record select through my form I get the value of CustomerID from the first record in the table not the currently selected record.


Thanks

[NoellaG]

Hi,

you open a new independant recordset, containing all records in the table. One solution to do this: get the id from the current record on the form like lngID = me.[ID] and then pass this trrough to your code as a parameter and than filter your recordset on "..... where ID = " & lngID
Other solution: work with a recordset clone and bookmarks.

kind regards
Noëlla

[ssanfu]

You have field names: "Variable", "Value" and "Description". These are reserved words in Access and shouldn't be used for object names.

This line is incorrect

Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)

Should not have the red parenthesis.
This line is correct

Set rs = CurrentDb.OpenRecordset("tblSys", dbOpenDynaset)

.FindFirst "[Variable] = 'CustomerIDLast'"

This line says "Find the string 'CustomerIDLast' in the field [Variable] in the recordset rs".
It looks like you will always find the same record unless you have some way of changing the record with the value of "CustomerIDLast" in the field [Variable] to a different value.
.