modify a field in the record selected in combo box

[RECrerar]

I have a combobox on my form that lists all the users stored in the database. The data for these are stored in a table called Users. On of the fields in the table is FrequentUser which is of a yes/no datatype.

When a user is selected in the combobox I would like to check if the FrequentUser field is true and if not set it to true.

I can check to see the field is true but can not figure out how to modify it. Do I have to create a recordset of just the record selected before I can change the data in it?

If anyone can help it would be appreciated

Robyn

 

[pbaldy]

Presuming the form is not bound to that table, you can either open a recordset on that record or execute an update query using the combo in the criteria.

 

[RECrerar]

Ooh a responce, I had literally just finished what I think is a solution. What I have done is use a record set with the following code

If DLookup("[BusDevMgr]", "Users", "UserID=" & Me.Bus_Dev_Mgr.Column(0)) = False Then
    Dim SQL As String
    SQL = "SELECT * FROM Users WHERE UserID =" & Me.Bus_Dev_Mgr.Column(0)
    Dim recordset As DAO.recordset
    Set recordset = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
    recordset.Edit
    recordset("BusDevMgr").Value = True
    recordset.Update
    recordset.Close
    Set recordset = Nothing
End If

Where the field name FrequentUser in my first post is now called BusDevMgr. This appears to work, is that the type of thing you had in mind?

 

[pbaldy]

Yes, but I'd avoid 2 calls to the data by dropping the DLookup and just opening the recordset. You can then test the field and change it if appropriate.

 

[RECrerar]

that sounds logical, I will change the code in line with your suggestion.

Thanks