Update Current Record

[khill]

Greetings,I am new to SQL and would really appreciate some help.I have a form named "Family Information". Underlying this form is a table Family Info Table. Among other fields, Family Info Table has four fields FamilyID#, Lastname, Firstname, DateUpdated. I want to update the "DateUpdated" field for the record that is currently displayed on the form.I think that I need to do this using DoCmd.RunSQL.I tried to do this by updating the DateUpdated field using the FamilyID# of the displayed record. When I was unable to do this, I decided to see if I could get DoCmd.RunSQL to do something very simple.Here is what I tried.'DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = 'Roy & Dora'"The above command worked perfectly and updated just one record. Yes!!!Now I tried the following command to update the displayed record but I got an error. I am quite sure that it has something to do with the single quote marks but I do not know how to make it work because inserting a single quote makes the following text appear like a comment.The following command does not work and I would really appreciate help for this novice.DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = " & Me.FirstName & "'"Thank you,Ken Hill

[maelstorm]

DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = " & Me.FirstName & "'"It seems like u hav missed a single quote before the me.firstnameTry this :DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = ' "& Me.FirstName & " ' "

[khill]

DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = " & Me.FirstName & "'"It seems like u hav missed a single quote before the me.firstnameTry this :DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = ' "& Me.FirstName & " ' "

<{POST_SNAPBACK}>

Thanks maelstorm!!!I worked!!!I thought that I tried entering a single quote in all spots, including that one, but, obviously, I didn't.As I move on now to try to make it update the record based on the unique FamilyID#, I may be coming to you again.Gratefully,Ken

[khill]

Thanks maelstorm!!!I worked!!!I thought that I tried entering a single quote in all spots, including that one, but, obviously, I didn't.As I move on now to try to make it update the record based on the unique FamilyID#, I may be coming to you again.Gratefully,Ken

<{POST_SNAPBACK}>

Hi,I am back.The following command worked great!!'DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = '" & Me.FirstName & "'"Because firstname is not unique, I decided to use the unique value FamilyID# (datatype = autonumber). I ran the following code and got the error "Method or data member not found" and the .FamilyID# was highlighted (not the Me, just .FamilyID#).DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE Firstname = '" & Me.FamilyID# & " '"I did not expect that that command would work because Firstname is text whereas FamilyID# is a number but I thought that I would try it anyways.I have tried many combinations; 1)I have taken out the single quotes completely 2)I have put just one single quote at the start 3)I have put just one single quote at the end. No matter what I try, I get the very same error. Once again, I would really appreciate your help.Ken Hill

[maelstorm]

You cannot compare like the way u have done, firstname cannot be compared to family#, thats y its showing error, u can compare like this, DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE FamilyID# = ' " & Me.FamilyID# & " ' "

[khill]

You cannot compare like the way u have done, firstname cannot be compared to family#, thats y its showing error, u can compare like this, DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE FamilyID# = ' " & Me.FamilyID# & " ' "

<{POST_SNAPBACK}>

Hi,That was really dumb of me to make that glaring mistake.I used the command that you suggested above and I got the error "Method or data member not found". As I said earlier, the Firstname field, that I used in the command that worked, was text whereas FamilyID# is a number. I have tried removing all of the single quotes and putting them back in at two different places. Still the same error.I really appreciate your help.Ken Hill

[khill]

Hi,That was really dumb of me to make that glaring mistake.I used the command that you suggested above and I got the error "Method or data member not found".  As I said earlier, the Firstname field, that I used in the command that worked, was text whereas FamilyID# is a number.  I have tried removing all of the single quotes and putting them back in at two different places.  Still the same error.I really appreciate your help.Ken Hill

<{POST_SNAPBACK}>

DoCmd.RunSQL "UPDATE [Family Info Table] SET Lastname = 'Adams' WHERE FamilyID# = ' " & Me.FamilyID# & " ' "Does anyone know how to make the above command work so that I do not get the error "Method or data member not found"? The command works for text fields but FamilyID# is a number. Those single quotes are somehow lousing things up.I would appreciate any help that you can offer.Thanks,Ken Hill