Button on form to update record in table using vba

[Christian]

Hi NG,

I'm trying to get below code to work:

Private Sub SetExcelPath_Click()
'Update an existing record.'
DoCmd.SetWarnings False
Sql = "UPDATE TblUserSettings SET TblUserSettings.ExportExcelPath=" &
Me.ExportExcelPath & " " & "WHERE (((TblUserSettings.RowID)=1));"
DoCmd.RunSQL Sql
DoCmd.SetWarnings True
End Sub

My problem is getting the SQL string right. I believe above code would work
if I just could get the string to be:

UPDATE TblUserSettings SET TblUserSettings.ExportExcelPath="C:\users\excel\"
WHERE (((TblUserSettings.RowID)=1));

But the code gives me:
UPDATE TblUserSettings SET TblUserSettings.ExportExcelPath=C:\users\excel\
WHERE (((TblUserSettings.RowID)=1));

ie the "" marks are missing around the path C:\users\excel.

I've tried different combinations but can't get the quotations marks in
place.

Any suggestions are warmly welcome.
- Chr

 

[Nikos Yannacopoulos]

Christian,

Try:

Sql = "UPDATE TblUserSettings SET TblUserSettings.ExportExcelPath='"
Sql = Sql & Me.ExportExcelPath & "' WHERE (((TblUserSettings.RowID)=1));"

Note: single quotes for text delimiters in VB.

HTH,
Nikos

 

[Christian]

Thanks Nikos,
It works! Now I got a small new problem. After the update is made and if I
try to edit the textbox field I get this info box:

"Microsoft Access
The data has been changed.
Another user edited this record and saved the changes before you attempted
to save your changes.
Re-edit the record.
[OK]"

can it be avoided by added some more code to refresh the form or by setting
a parameter in the form?
- Chr

 

[Christian]

The error msg is no longer there after I closed the db and opened it again.