SQL Update Issue

[Guest]

Hi all,

I am trying to run an SQL statement via code and am not able to get it to
work.
I have an open form, it captures a number in field "Item_ID" and an after
update event is called running the following VB code:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = Now() WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

I do not get any error messages, but I cannot get field Step1 to update with
a current date.

I went into the query editor, made an update query with the following SQL
and got it to work:
UPDATE Items SET Items.Step1 = Now()
WHERE (((Items.Item_ID)=[Item id?]));

Why will the VB not work? Any help always appreciated, thanks!
-gary

 

[AccessVandal via AccessMonster.com]

Hi Gary,

Try this.

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) = (Forms![RECEIVING_PRODUCTION_Step_Check_In]!
[Item_ID].value))"

Leave a space before "Where".

update event is called running the following VB code:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = Now() WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

I do not get any error messages, but I cannot get field Step1 to update with
a current date.

 

[Guest]

Thanks for the reply, unfortunately, no luck. I now have the following code:

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

it is not updating Step1, however (and unfortunately), I am not getting any
error messages - any thing else we can try? Please let me know, thanks!
-gary

Hi Gary,

Try this.

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) = (Forms![RECEIVING_PRODUCTION_Step_Check_In]!
[Item_ID].value))"

Leave a space before "Where".

update event is called running the following VB code:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = Now() WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

I do not get any error messages, but I cannot get field Step1 to update with
a current date.

 

[AccessVandal via AccessMonster.com]

Sorry about the where clause, try again with this.

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE Items.Item_ID = “ & Forms![RECEIVING_PRODUCTION_Step_Check_In]!
[Item_ID].value

Is Step1 datatype = DateTime?

If so, add – SET Items.Step1 = #†& Now() & “#†_

And I assuming Item_ID datatype = Numeric.

Gary Dolliver wrote:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

 

[David F Cox]

Since you do not get an error message I would assume that the VB is working.
You have proven that you can set step1 to Now().

I would suspect that
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value)) is not the
same as [Item id?] was. If it looks the same I would try a trim() on it.

 

[Guest]

Hi David,
Thanks for the info - They do relate to the same field (at least I believe
so) as the SQL relates to the actual field on the form, and the [item id?]
from the query is a pop up that asks for the Item_ID to be entered (much like
the text box on the form). Does that make sense?
-gary

Since you do not get an error message I would assume that the VB is working.
You have proven that you can set step1 to Now().

I would suspect that
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value)) is not the
same as [Item id?] was. If it looks the same I would try a trim() on it.

Hi all,

I am trying to run an SQL statement via code and am not able to get it to
work.
I have an open form, it captures a number in field "Item_ID" and an after
update event is called running the following VB code:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = Now() WHERE ((Items.Item_ID)
=
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

I do not get any error messages, but I cannot get field Step1 to update
with
a current date.

I went into the query editor, made an update query with the following SQL
and got it to work:
UPDATE Items SET Items.Step1 = Now()
WHERE (((Items.Item_ID)=[Item id?]));

Why will the VB not work? Any help always appreciated, thanks!
-gary

 

[Guest]

Hi again,
Thank you! I am not sure what is really that different, but it works!
Thank you, I spent all night with this one...
-gary

Sorry about the where clause, try again with this.

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE Items.Item_ID = “ & Forms![RECEIVING_PRODUCTION_Step_Check_In]!
[Item_ID].value

Is Step1 datatype = DateTime?

If so, add – SET Items.Step1 = #†& Now() & “#†_

And I assuming Item_ID datatype = Numeric.

Gary Dolliver wrote:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

 

[Guest]

Hi again,
I wanted to add in one more condition, but not sure where I could do it. I
would like a message to pop up if Step1 already has a value, and only allow
the update to work if Step1 = null
Where would I add this?
Thanks again!
-gary

Sorry about the where clause, try again with this.

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE Items.Item_ID = “ & Forms![RECEIVING_PRODUCTION_Step_Check_In]!
[Item_ID].value

Is Step1 datatype = DateTime?

If so, add – SET Items.Step1 = #†& Now() & “#†_

And I assuming Item_ID datatype = Numeric.

Gary Dolliver wrote:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = " & Now() _
& " WHERE ((Items.Item_ID) =
(Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value))"

 

[AccessVandal via AccessMonster.com]

You can try this code. Add in your afterupdate event.

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

'check the current record by Item_ID
strSQL = "SELECT Item_ID, Step1 FROM Items WHERE Item_ID = " _
& Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].Value _
& " AND Step1 IS NULL"

rst.Open strSQL, con, adOpenKeyset, adLockOptimistic

'if Step1 is NOT null, do update
If rst.EOF = True Then 'if EOF = True than there are no recordset
DoCmd.runsql "UPDATE Items SET Items.Step1 = #" & Now() & "#" _
& " WHERE Items.Item_ID = " _
& Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].Value
Else
MsgBox "Your message Step1 is not null", vbInformation, "Not Null"
End If

rst.Close
con.Close

Not sure about the query on “Step1 IS NULL†, you’ll need to test it out.

 

[AccessVandal via AccessMonster.com]

Please ignore this comment - 'if Step1 is NOT null, do update

It should be,

'There are no records, do update if EOF = True.

The query pick the Item_ID and IS NULL condition. If Step1 is not Null, than
there is record, thus, rst.EOF = False. The If Then Else condition will skip
the Docmd.RunSql command and the Msgbox to pop the message. If Step1 is Null
and there is no record, than the rst.EOF = True, the Docmd.RunSql will
execute.

 

[onedaywhen]

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

'check the current record by Item_ID
strSQL = "SELECT Item_ID, Step1 FROM Items WHERE Item_ID = " _
& Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].Value _
& " AND Step1 IS NULL"

rst.Open strSQL, con, adOpenKeyset, adLockOptimistic

'if Step1 is NOT null, do update
If rst.EOF = True Then 'if EOF = True than there are no recordset
DoCmd.runsql "UPDATE Items SET Items.Step1 = #" & Now() & "#" _
& " WHERE Items.Item_ID = " _
& Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].Value
Else
MsgBox "Your message Step1 is not null", vbInformation, "Not Null"
End If

rst.Close
con.Close

I hope you don't mind my saying but I see this as a classic example of
a procedural mindset, rather than the set-based mental model the SQL
language requires.

SQL is a declarative language, "What, not how." A SQL query/statement
should be one 'line' of code but really it's a specification of *what*
you want to happen. By using two database round trips you are kinda
telling the system *how* to do its job, "Only run this line of code if
this other bit of code returns an empty set" as if you don't trust it
to short circuit on its own.

One 'line' would be more appropriate e.g. something like (untested):

UPDATE Items
SET Step1 = NOW()
WHERE Item_ID = [Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
AND NOT EXISTS (
SELECT *
FROM Items AS T2
WHERE T2.Item_ID = Items.Item_ID
AND T2.Step1 IS NULL
);

Jamie.

--

 

[AccessVandal via AccessMonster.com]

Jamie,

I did consider the subquery. At that moment, I don’t have any clues as to
“how†to get the code to run with that subquery and the message box.

If he needs that, well…….. I think the command object might do.

 

[onedaywhen]

this as a classic example of
a procedural mindset, rather than the set-based mental model the SQL
language requires.

See the top story today on

http://thedailywtf.com/forums/thread/96909.aspx

"In essence, this process would select a giant set of results from the
database, loop through each row, and then insert a new row into another
table. JM knew he could do better and rewrote the procedure to use a
single query ... It ran in less than five minutes instead of several
hours."

Jamie.

--

 

[Guest]

Hi everyone,
Thanks for all the information with this. I am trying to have this run as
quickly as possible, as this will be part of a barcode scanning process. I
want it to look up a specific (scanned) item_id, and if Step1 is already
populated with data, to have it stop and pop up a message. If it is not
populated, I would like it to proceed and make the update. I have the update
working, thanks to AccessVandal, I would now like to implement the "check"
feature and have it run the quickest way possible, as this table will be
growing quickly.

I tried the SQL below:
DoCmd.RunSQL "UPDATE Items SET Items.Step1 = NOW()" _
& " WHERE Item_ID =
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value " _
& " AND NOT EXISTS ( " _
& " SELECT * " _
& " FROM Items AS T2 WHERE T2.Item_ID = Items.Item_ID AND T2.Step1 IS NULL); "

I am not sure what T2 is, is this a temp table we have created?
Thank you again!

-gary

 

[AccessVandal via AccessMonster.com]

Hi Gary,

You’ll need to read up more on VB.

Use the ampersand “ & “ to assign the value.

I need to get back to the other PC on Monday. In the mean time use this,

DoCmd.RunSQL "UPDATE Items SET Items.Step1 = #“ & NOW()" & “#†_
& " WHERE Item_ID = “ _
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID].value " _
& " AND EXISTS ( " _
& " SELECT * " _
& " FROM Items AS T2 WHERE T2.Item_ID = Items.Item_ID AND T2.Step1 IS NULL) "

PS - I have two possible solutions for you.

1. Drop SQL, use recordset to manipulate data.
2. Use the Command Object method.

Just ignore the link for now, doesn’t help you in anyway regarding current
your problem.
This code just retrive one row only. So nothing to worry about.

 

[AccessVandal via AccessMonster.com]

Hi Gary,

If you want to drop the SQL, here is the sample using recordset method.

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

‘open the table Items
rst.Open "Items", con, adOpenKeyset, adLockOptimistic
'Control Name - [Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
‘just in case if is null
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then
MsgBox "No Item_ID to Update", vbCritical, "No Item_ID"
Exit Sub
Else
' if Item_ID is not null
rst.Find "Item_ID = " & Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID],
, adSearchForward
End If

Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

‘check Step1 isNull?
If IsNull(rst!Step1) Then
rst!Step1 = Now() ‘assumed DataType is “DateTimeâ€
rst.Update
Else
MsgBox "Your Message- Step1 Is Not Null", vbInformation, "Not Null"
End If

Set rst = Nothing
con.Close


I would suggest you reduce the length your form name to “frmRPStepChkIn†or
shorter.


Here is another sample for the command object method by SQL.

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim RA As Long

'open the connection
Set con = CurrentProject.Connection

'set the SQL string
strSQL = "UPDATE Table1 " _
& "Set Step1 = #" & Now() & "#" _
& " WHERE Item_ID = " & [Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
_
& " AND EXISTS (" _
& " SELECT Item_ID, Step1 " _
& " FROM Items AS T2 " _
& " WHERE T2.Item_ID = Items.Item_ID " _
& " AND T2.Step1 IS NULL)"
Debug.Print strSQL ‘check SQL string – to delete after use
‘check isnull just in case
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then
MsgBox "No Item_ID to Update", vbCritical, "No Item_ID"
Exit Sub
Else
'if Item_ID is not null
With cmd
.CommandText = strSQL
.CommandType = adCmdUnknown
.ActiveConnection = con
.Execute RA
End With
End If

'check record RA - to delete “Debug.Print†after use
Debug.Print "There are/is " & RA & " records"

'RA=0, no record was updated, Step1 was not null
If RA = 0 Then 'assume logic of no records
MsgBox "Your Message - Step1 Is not Null", vbInformation, "Not Null"
End If

con.Close
Set cmd = Nothing

Note: You can use the parameter for the command object method instead of
using SQL to open a single recordset.

 

[onedaywhen]

I am not sure what T2 is, is this a temp table we have created?

It's a table correlation name.

See this description of how a SELECT query should work:

http://groups.google.com/group/comp.databases/msg/bc4be7309a3fcf6a?hl=en&

"Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query... Nested query expressions follow the
usual scoping rules you would expect from a block structured language
like C, Pascal, Algol, etc. Namely, the innermost queries can reference
columns and tables in the queries in which they are contained."

This is what I mean about "What, not how." We are not telling the SQL
product to create a temp table called T2, the system decides best *how*
to fulfil the request. We use a correlation table to be able to use the
same table twice in the query and distinguish between them.

Jamie.

--

 

[Guest]

thank you so much - this has all been very informative for me! I have one
more annoying question in regards to recordset method.
For the line:
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward
is there a way to have it return a custom error message if the Item ID does
not exist in the table (we already have a check for a null response, but not
one for an incorrect ID)? Currently, I get the runtime error 3021 with the
option to end or debug (an option I would not like the user to have) Can I
create a custom error message and exit out and return to the entry screen?
Thanks again, I am new to this...
-gary

Hi Gary,

If you want to drop the SQL, here is the sample using recordset method.

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

‘open the table Items
rst.Open "Items", con, adOpenKeyset, adLockOptimistic
'Control Name - [Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
‘just in case if is null
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then
MsgBox "No Item_ID to Update", vbCritical, "No Item_ID"
Exit Sub
Else
' if Item_ID is not null
rst.Find "Item_ID = " & Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID],
, adSearchForward
End If

Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

‘check Step1 isNull?
If IsNull(rst!Step1) Then
rst!Step1 = Now() ‘assumed DataType is “DateTimeâ€
rst.Update
Else
MsgBox "Your Message- Step1 Is Not Null", vbInformation, "Not Null"
End If

Set rst = Nothing
con.Close


I would suggest you reduce the length your form name to “frmRPStepChkIn†or
shorter.


Here is another sample for the command object method by SQL.

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim RA As Long

'open the connection
Set con = CurrentProject.Connection

'set the SQL string
strSQL = "UPDATE Table1 " _
& "Set Step1 = #" & Now() & "#" _
& " WHERE Item_ID = " & [Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
_
& " AND EXISTS (" _
& " SELECT Item_ID, Step1 " _
& " FROM Items AS T2 " _
& " WHERE T2.Item_ID = Items.Item_ID " _
& " AND T2.Step1 IS NULL)"
Debug.Print strSQL ‘check SQL string – to delete after use
‘check isnull just in case
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then
MsgBox "No Item_ID to Update", vbCritical, "No Item_ID"
Exit Sub
Else
'if Item_ID is not null
With cmd
.CommandText = strSQL
.CommandType = adCmdUnknown
.ActiveConnection = con
.Execute RA
End With
End If

'check record RA - to delete “Debug.Print†after use
Debug.Print "There are/is " & RA & " records"

'RA=0, no record was updated, Step1 was not null
If RA = 0 Then 'assume logic of no records
MsgBox "Your Message - Step1 Is not Null", vbInformation, "Not Null"
End If

con.Close
Set cmd = Nothing

Note: You can use the parameter for the command object method instead of
using SQL to open a single recordset.

Gary Dolliver wrote:
Hi everyone,
populated with data, to have it stop and pop up a message. If it is not
populated, I would like it to proceed and make the update. I have the update
working, thanks to AccessVandal, I would now like to implement the "check"
feature and have it run the quickest way possible, as this table will be
growing quickly.

 

[AccessVandal via AccessMonster.com]

Hi Gary,

You can try to use the sample code below.

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset

On Error GoTo Err_YourEventName_Click

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

‘your code here

Exit_YourEventName_Click:
Exit Sub

Err_YourEventName_Click:

If Err.Number = 3021 Then
MsgBox “No Records for Item_ID.â€, vbInformation, "No Records"
Resume Exit_printout_Click
End If

Or you can add this, (I’m not sure where your error stop or start)

Else
' if Item_ID is not null
rst.Find "Item_ID = " & Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID],
, adSearchForward
If Err.Number = 3021 Then
MsgBox “No Records for Item_ID.â€, vbInformation, "No Records"
Exit Sub ‘exit the event
End If

Gary Dolliver wrote:
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward
is there a way to have it return a custom error message if the Item ID does
not exist in the table (we already have a check for a null response, but not
one for an incorrect ID)? Currently, I get the runtime error 3021 with the
option to end or debug (an option I would not like the user to have) Can I
create a custom error message and exit out and return to the entry screen?
Thanks again, I am new to this...

 

[AccessVandal via AccessMonster.com]

Hi Gary,

Please note the error.

If Err.Number = 3021 Then
MsgBox “No Records for Item_ID.â€, vbInformation, "No Records"
'Resume Exit_printout_Click

Resume Exit_YourEventName_Click