update table from form

[tina]

Hi
I would like to be able to update table dbo_datalots field StatusFlag to " "
where LotNumber in form matches field LotNumber in table (text fields) the
form has more than one lot number i(t prompts for stockcode and like serial
number )I need to update all records shown in form
tried following but syntex is wrong
Private Sub Command29_Click()


Dim strSQL As String

strSQL = "UPDATE dbo_DataLots " _
& "SET StatusFlag = ' ' " _
& "WHERE [LotNumber] = " & Me.LotNumber
CurrentDb.Execute strSQL, dbfailonerror

End Sub
Thankyou
Tina

 

[Stefan Hoffmann]

hi Tina,

I would like to be able to update table dbo_datalots field StatusFlag to " "
where LotNumber in form matches field LotNumber in table (text fields)

Is LotNumber a String or number (e.g. Long)? If it is a String, you need
extra quotes:

strSQL = "UPDATE dbo_DataLots " & _
"SET StatusFlag = ' ' & " _
"WHERE [LotNumber] = '" & Replace(Me.LotNumber, "'", "''") & "'"


mfG
--> stefan <--

 

[Dale Fye]

Tina,

What is the DataType of your StatusFlag field? Is it text (if so, how many
characters)? After trying to answer your previous question, I realized that
if the field is a text field, Access will not let you actually store blanks,
so you are going to have to use some other character in that field.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Stefan Hoffmann]

hi Dale,

What is the DataType of your StatusFlag field? Is it text (if so, how many
characters)? After trying to answer your previous question, I realized that
if the field is a text field, Access will not let you actually store blanks,
so you are going to have to use some other character in that field.

This shouldn't raise a syntax error.


mfG
--> stefan <--

 

[Dale Fye]

Stefan,

Tina didn't say she was getting a syntax error, she said the syntax was
wrong. Which could mean that it wasn't doing what she thought it should do.

She posted another message yesterday, in which I gave her the syntax to
update a field based on a Like statement in here WHERE clause. But it didn't
occur to me until this morning that Access will not allow you to store a
space in a text field. It will allow leading spaces, followed by text, but
it will not allow a single space, or trailing spaces (it is as if it uses
RTRIM to trim all trailing spaces before it saves a text field).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Stefan Hoffmann]

hi Dale,

Tina didn't say she was getting a syntax error, she said the syntax was
wrong.

Obviously I'm not a native speaker, makes this a difference when it
comes down to programming?

Which could mean that it wasn't doing what she thought it should do.

I think this is true.

She posted another message yesterday, in which I gave her the syntax to
update a field based on a Like statement in here WHERE clause. But it didn't
occur to me until this morning that Access will not allow you to store a
space in a text field. It will allow leading spaces, followed by text, but
it will not allow a single space, or trailing spaces (it is as if it uses
RTRIM to trim all trailing spaces before it saves a text field).

I've tested it right now:

Neither AllowZeroLength nor Required have an influence on an UPDATE
statement like

UPDATE table
SET textField = ' '

btw, I'm using Access 2003.


mfG
--> stefan <--

 

[tina]

Hi
all my fields are text / strings and am trying to get form to update table
as when get it to run query only updating first record.
query sql is
UPDATE dbo_DataLots SET dbo_DataLots.StatusFlag = " "
WHERE (((dbo_DataLots.LotNumber)=[forms]![serial]![LotNumber]));

command in form is
Private Sub update_Click()
On Error GoTo Err_update_Click

Dim stDocName As String

stDocName = "SERIAL UPDATE"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_update_Click:
Exit Sub

Err_update_Click:
MsgBox Err.Description
Resume Exit_update_Click

End Sub
AS this was only updating selected record i tried to run using
strSQL = "UPDATE dbo_DataLots " & _
"SET StatusFlag = ' ' & " _
"WHERE [LotNumber] = '" & Replace(Me.LotNumber, "'", "''") & "'"
as suggested this only updates selected record aswell how can I updat eall
records shown in form
Thanks
Tina

 

[Dale Fye]

Tina,

As I mentioned in my post to Stefan. I don't think you are going to be able
to set the value of your [StatusFlag] field to " ". Access will not allow a
space character as the only character in a text field. You could set it to
an empty string "", but not to a space.

Your query "Serial Update" will only update the record currently displayed
on the form. You could do this just as easily by setting the value of the
field (see code below), but as I mentioned above, it will only work if you
set the value to "", not " ".

If you want to set the value of [StatusFlag] for multiple records which meet
some criteria, then, as I mentioned in my post to you on the other thread,
you will need to add another (unbound) control to your form (call it
txt_Criteria), I usually put these in the forms header. Then, you can enter
a criteria for your [LotNumber] field so that the code behind the command
button would update [StatusFlag] for multiple records.

Private Sub update_Click

Dim strSQL as string
Dim strCriteria as string

strCriteria = "[LotNumber] Like '" & me.txt_Criteria & "'"
msgbox "This will update " _
& "DCOUNT("[LotNumber]", "dbo_DataLots", strCriteria) & " " _
& "records."
strSQL = "UPDATE dbo_DataLots " _
& "SET [StatusFlag] = '' " _
& "WHERE [LotNumber] Like '" & me.txt_Criteria & "'"
Currentdb.execute strsql, dbfailonerror

End Sub

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi
all my fields are text / strings and am trying to get form to update table
as when get it to run query only updating first record.
query sql is
UPDATE dbo_DataLots SET dbo_DataLots.StatusFlag = " "
WHERE (((dbo_DataLots.LotNumber)=[forms]![serial]![LotNumber]));

command in form is
Private Sub update_Click()
On Error GoTo Err_update_Click

Dim stDocName As String

stDocName = "SERIAL UPDATE"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_update_Click:
Exit Sub

Err_update_Click:
MsgBox Err.Description
Resume Exit_update_Click

End Sub
AS this was only updating selected record i tried to run using
strSQL = "UPDATE dbo_DataLots " & _
"SET StatusFlag = ' ' & " _
"WHERE [LotNumber] = '" & Replace(Me.LotNumber, "'", "''") & "'"
as suggested this only updates selected record aswell how can I updat eall
records shown in form
Thanks
Tina

She posted another message yesterday, in which I gave her the syntax to
update a field based on a Like statement in here WHERE clause. But it didn't
occur to me until this morning that Access will not allow you to store a
space in a text field. It will allow leading spaces, followed by text, but
it will not allow a single space, or trailing spaces (it is as if it uses
RTRIM to trim all trailing spaces before it saves a text field).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.