Update SQL not updating ...

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

Guest

Hi there,

I am not an ACCESS expert so I may easily have an error in here.

I am trying to update a field based on criteria. The funtion is being called
just fine and according to my msgbox's it is getting the correct info and
making the sql statement correctly (to my knowledge) but it is not updating
the field. I am guessing it is the string within a string part. Here is a
copy of the sql statement with a few added spaces to see what is what a bit
easier:

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = ' " & ProjectID & " ' AND [tbl Phase
ID].[Phase] = ' " & SPID & " ';"

Below is the entire function which i am calling
---------------------------------------------------------------------------------------------
Public Function UpdateProductionStatus(ProjectID As String, PhaseID As String)

Dim stSQL As String
Dim SPID As String ' Shortened Phase ID

SPID = Mid(PhaseID, 1, 3)

MsgBox (SPID)
MsgBox (ProjectID)

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = '" & ProjectID & "' AND [tbl Phase
ID].[Phase] = '" & SPID & "';"

MsgBox (stSQL)

DoCmd.RunSQL (stSQL)

MsgBox ("Action Done!")

End Functio
 
Instead of

DoCmd.RunSQL (stSQL)

try

CurrentDb.Execute stSQL, dbFailOnError

You should get an error message that'll give some idea of what Access thinks
the problem is.
 
hmmm, I did that and it is not producing any error.

I have checked many times the types of fields/spelling and the actual record
being updated in the database. Everthing seems to be correct and the record
certainly exists.

Anyone else see any errors?





Douglas J. Steele said:
Instead of

DoCmd.RunSQL (stSQL)

try

CurrentDb.Execute stSQL, dbFailOnError

You should get an error message that'll give some idea of what Access thinks
the problem is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lythandra said:
Hi there,

I am not an ACCESS expert so I may easily have an error in here.

I am trying to update a field based on criteria. The funtion is being
called
just fine and according to my msgbox's it is getting the correct info and
making the sql statement correctly (to my knowledge) but it is not
updating
the field. I am guessing it is the string within a string part. Here is a
copy of the sql statement with a few added spaces to see what is what a
bit
easier:

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = ' " & ProjectID & " ' AND [tbl Phase
ID].[Phase] = ' " & SPID & " ';"

Below is the entire function which i am calling.
---------------------------------------------------------------------------------------------
Public Function UpdateProductionStatus(ProjectID As String, PhaseID As
String)

Dim stSQL As String
Dim SPID As String ' Shortened Phase ID

SPID = Mid(PhaseID, 1, 3)

MsgBox (SPID)
MsgBox (ProjectID)

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = '" & ProjectID & "' AND [tbl Phase
ID].[Phase] = '" & SPID & "';"

MsgBox (stSQL)

DoCmd.RunSQL (stSQL)

MsgBox ("Action Done!")

End Function
Click to expand...
Click to expand...
 
Your SQL statement looks fine so long as the fields Job & Phase are text
fields. Have you tried creating the statement using the Query wizard? If not
its worth a go and look to see what the SQL looks like.

BW
 
Yup, they are both text fields and using the query builder is how i did this
one. :-)

Made it in there, stuck in some criteria placeholders, went to sql view and
copy/pasted it then made formatting changes and stuck in the correct criteria.

Well thanks all, I'll give my brain a rest today then and look at it again
tomorrow morning when I am fresh. I am obvously doing something wrong
somewhere and I'll pick it apart word by word later.

BeWyched said:
Your SQL statement looks fine so long as the fields Job & Phase are text
fields. Have you tried creating the statement using the Query wizard? If not
its worth a go and look to see what the SQL looks like.

BW

Lythandra said:
Hi there,

I am not an ACCESS expert so I may easily have an error in here.

I am trying to update a field based on criteria. The funtion is being called
just fine and according to my msgbox's it is getting the correct info and
making the sql statement correctly (to my knowledge) but it is not updating
the field. I am guessing it is the string within a string part. Here is a
copy of the sql statement with a few added spaces to see what is what a bit
easier:

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = ' " & ProjectID & " ' AND [tbl Phase
ID].[Phase] = ' " & SPID & " ';"

Below is the entire function which i am calling.
---------------------------------------------------------------------------------------------
Public Function UpdateProductionStatus(ProjectID As String, PhaseID As String)

Dim stSQL As String
Dim SPID As String ' Shortened Phase ID

SPID = Mid(PhaseID, 1, 3)

MsgBox (SPID)
MsgBox (ProjectID)

stSQL = "UPDATE [tbl Phase ID] SET [tbl Phase ID].[Production Started] =
True WHERE [tbl Phase ID].[Job] = '" & ProjectID & "' AND [tbl Phase
ID].[Phase] = '" & SPID & "';"

MsgBox (stSQL)

DoCmd.RunSQL (stSQL)

MsgBox ("Action Done!")

End Function
Click to expand...
Click to expand...
 
Lythandra said:
See any errors?
Click to expand...

You have an extra space before and after each of your variables in the string,
so that if your ProjectID were "Smith deck repairs" and SPID were "Follow up,
" your where clause would look like this:

WHERE [tbl Phase ID].[Job] = ' Smith deck repairs ' AND [tbl Phase ID].[Phase]
= ' Follow up ';
 
Thanks for the tip but I had added the spaces in the first part of the post
to make it a bit easier to see what was what. I check it with the msgbox (I
love using them) and it does not have any unneeded spaces. I double checked
it also after your post. I wish you were right tho. :-(

Granny Spitz via AccessMonster.com said:
Lythandra said:
See any errors?
Click to expand...

You have an extra space before and after each of your variables in the string,
so that if your ProjectID were "Smith deck repairs" and SPID were "Follow up,
" your where clause would look like this:

WHERE [tbl Phase ID].[Job] = ' Smith deck repairs ' AND [tbl Phase ID].[Phase]
= ' Follow up ';
Click to expand...
 
Lythandra said:
I check it with the msgbox (I
love using them) and it does not have any unneeded spaces.
Click to expand...

Open the tbl Phase ID table in datasheet view and filter the Job column on
your intended ProjectID. Next, filter the Phase column with the first 3
characters of the SPID. If this filtering doesn't come up with any records,
you need to find out which of your assumptions is wrong. Pick a record you
think *should* have come up in your filtering and check the Job and Phase
columns for any extra *white space* like carriage returns.

Filtering on LIKE '*something*' instead of = 'something' often shows the
missing records.
 
Thanks for the help all.

I rebuilt it from scratch and now it works perfectly.

The odd thing is tho that nothing has changed (every single letter is the
same), yet it works fine now.

Sometimes Computers annoy me ....
 
Back
Top