Update record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to update more than one field in a record using text boxes on a
form as the source for the new data. Below is the code I am trying.
Dim RegistrationSQL as String
RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels SET
tblRegisteredAndUniqueLabels.RegisteredLabel =" & Me.RegisteredLabel & "
tblRegisteredAndUniqueLabels.CheckDigit =" & Me.CheckDigit & " " & "WHERE
((tblRegisteredAndUniqueLabels.UniqueLabel)=" & Me.UniqueLabel & ");"

I can get the statement to work if I update only one field but I can not get
the statement to work when I add more than one field.

Thank you for your help

Allan
 
It may be because the comma is missing between the two fields. Try
RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels SET
tblRegisteredAndUniqueLabels.RegisteredLabel =" & Me.RegisteredLabel & ",
tblRegisteredAndUniqueLabels.CheckDigit =" & Me.CheckDigit & " " & "WHERE
((tblRegisteredAndUniqueLabels.UniqueLabel)=" & Me.UniqueLabel & ");"

Hope This Helps
Gerald Stanley MCSD
 
I can get the statement to work if I update only one field but I can not get
the statement to work when I add more than one field.
Click to expand...

You need a comma and some spaces to delimit things:

RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels " _
& "SET tblRegisteredAndUniqueLabels.RegisteredLabel =" _
& Me.RegisteredLabel _
& ", tblRegisteredAndUniqueLabels.CheckDigit =" _
& Me.CheckDigit & " _
& " WHERE ((tblRegisteredAndUniqueLabels.UniqueLabel)=" _
& Me.UniqueLabel & ");"

This assumes that UniqueLabel and RegisteredLebel are numeric fields;
otherwise you need quotes around both the inserted values and the
criteria.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John
I am still having trouble with one update and it is a date/time field that I
want to update to Now. I will not include all the code, just the end part.
The code was working until I added the date.

I have it in the code as follows:
, tblRegisteredAndUniqueLabels.ShipmentRegistrationDate =" & Now & " " &
"WHERE ((tblRegisteredAndUniqueLabels.UniqueLabel)=" & Me.UniqueLabel & ");"

Thank you for your help. I have spent hours today, trying to get this to work.
Allan
 
Gerald
It worked. I have been working most of today to try and get this working and
it was only a comma. I have posted another part that is not working on the
second reply to my post.

Thank you very much for your help

Allan
 
I did get the code working (double quotes around Now), however, now I have
one long piece of code and I would like to break it into smaller lines. When
I try using & _ the code stops working. The following is the final code that
works (all in one line).
RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels SET
tblRegisteredAndUniqueLabels.RegisteredLabel =" & Me.RegisteredLabel & ",
tblRegisteredAndUniqueLabels.CheckDigit =" & Me.CheckDigit & ",
tblRegisteredAndUniqueLabels.ShipmentRegistrationEmployeeNumber =" &
Me.EmployeeNumber & ", tblRegisteredAndUniqueLabels.ShipmentRegistrationShift
=" & Me.Shift & ", tblRegisteredAndUniqueLabels.ShipmentRegistrationDate ="""
& Now & """ " & "WHERE ((tblRegisteredAndUniqueLabels.UniqueLabel)=" &
Me.UniqueLabel & ");"

How can I break the code into smaller lines?

Thanks again
Allan
 
I did get the code working (double quotes around Now), however, now I have
one long piece of code and I would like to break it into smaller lines. When
I try using & _ the code stops working. The following is the final code that
works (all in one line).
RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels SET
tblRegisteredAndUniqueLabels.RegisteredLabel =" & Me.RegisteredLabel & ",
tblRegisteredAndUniqueLabels.CheckDigit =" & Me.CheckDigit & ",
tblRegisteredAndUniqueLabels.ShipmentRegistrationEmployeeNumber =" &
Me.EmployeeNumber & ", tblRegisteredAndUniqueLabels.ShipmentRegistrationShift
=" & Me.Shift & ", tblRegisteredAndUniqueLabels.ShipmentRegistrationDate ="""
& Now & """ " & "WHERE ((tblRegisteredAndUniqueLabels.UniqueLabel)=" &
Me.UniqueLabel & ");"

How can I break the code into smaller lines?
Click to expand...

The VBA line continuation signal is a blank followed by an underscore
followed by a carriage return-line feed. It's best not to carry a long
string constant across multiple lines; instead have separate string
literals on each line. Also be sure that you have syntactically
required blanks within the strings where they're needed. It's
optional, but I prefer to put the ampersand on the new line so it's
easier to visually identify continuation lines in the code.

Note also that the proper delimiter for Date/Time fields is # rather
than ". " works but only because Access will try to translate a String
to a Date. Try:

RegistrationSQL = "UPDATE tblRegisteredAndUniqueLabels SET " _
& "tblRegisteredAndUniqueLabels.RegisteredLabel =" _
& Me.RegisteredLabel _
& ", tblRegisteredAndUniqueLabels.CheckDigit =" & Me.CheckDigit _
& ", tblRegisteredAndUniqueLabels." _
& "ShipmentRegistrationEmployeeNumber =" _
& Me.EmployeeNumber _
& ", tblRegisteredAndUniqueLabels.ShipmentRegistrationShift =" _
& Me.Shift _
& ", tblRegisteredAndUniqueLabels.ShipmentRegistrationDate = #" _
& Now & "# WHERE ((tblRegisteredAndUniqueLabels.UniqueLabel)=" _
& Me.UniqueLabel & ");"

I couldn't do the line with

tblRegisteredAndUniqueLabels.ShipmentRegistrationEmployeeNumber

right in Agent without splitting the fieldname in two, but you
can in VBA! Maybe you should shorten your fieldnames.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top