|
|
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
|
|
hi Tina,
tina wrote:
[Quoted Text] > 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 <--
|
|
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.
"tina" wrote:
[Quoted Text] > 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
|
|
hi Dale,
Dale Fye wrote:
[Quoted Text] > 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 <--
|
|
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" wrote:
[Quoted Text] > hi Dale,
>
> Dale Fye wrote:
> > 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 <--
>
|
|
hi Dale,
Dale Fye wrote:
[Quoted Text] > 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 <--
|
|
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
[Quoted Text] > 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" wrote:
>
> > hi Dale,
> >
> > Dale Fye wrote:
> > > 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 <--
> >
|
|
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.
"tina" wrote:
[Quoted Text] > 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.
> >
> >
> >
> > "Stefan Hoffmann" wrote:
> >
> > > hi Dale,
> > >
> > > Dale Fye wrote:
> > > > 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 <--
> > >
|
|
|