Using code to change contents of a field

[diversoln]

I know that dLookup reads the contents of a specified field and stores it to a variable. I'd like to do just the opposite.

I'd like to take the contents of a variable and store it to a specified field in a specified record in a specified table.

The table has two fields: CoreSN and Comment

I'd like to locate the unique record where CoreSN = CoreSN_On_My_Form, then replace the contents of the Comment field for that record with info stored as a variable.

This has to be easy but the Access Help makes it look so complicated....

 

[hanzo]

U can consider using action queries(Update) to update records.

1st Create a query to retrieve the particular record displayed on the form.
2nd include the identifier of your form using the build function in the criteria row of the query and the field u want to update to.
3rd turn the select query into a update action query.
4th create a button to activate the action query.

Read up more on action query to find out more using MS Access Help.

All the best and Good Luck.

 

[simongallop]

The other way is to use code linked to either a button or the OnLostFocus event.

The code would be something along the lines of:

sub change()
Dim MyRS as recordset
Dim MySearch as string
set MyRS=CurrentDB.OpenRecordset("TableName")
MySearch = "[CoreSN]=" & CoreSN_On_My_Form
MyRS.findfirst MySearch
MyRS.Edit
MyRS("Comment") = variable_with_comment
MyRS.Update
myrs.close
end sub

Note for the MySearch string if CoreSN is numeric then stick to the syntax but if it is a string then you will need to stick it in single quotes ie

MySearch = "[CoreSN]='" & etc & "'"