SQL UPDATE Statement in VBA (Troubles) (1 Viewer)

[Josh Hill]

Hello, All:

I have a database wherein two tables are related by a field called "sno"; it is an automatically generated number in table1 and in table2 it's a number field. It's the primary key in table1, also.

I have a form that contains a few textboxes:

user (autopopulated from another form based on login name)
group (autopopulated from another form w/DLookUp in code based on login)
sno (autopopulated from another form w/DLookUp in code based on login)
stockid (unbound textbox)
promoid (unbound textbox)
individual (unbound textbox)

The user logins in and moves to a new form, where they can fill in some information (stockid OR promoid, not both; individual [which is the person to whom the project will be assigned]). The code behind the form takes the information from the textboxes and uses it to update the appropriate table.

If, for example, stockid is filled in as "123," and the individual value is "Joe Smith," then the code should run an UPDATE SQL statement to fill in the table2 fields corresponding to the WHERE condition statements.

Here's the code:

Private Sub Command10_Click()

Dim db As Database
Set db = CurrentDb
Set frm = Forms!frmtestindassign

PID = frm.PromoID
SID = frm.stockid
ind = frm.individual
sno1 = frm.sno
sno2 = DLookup("tblusers.sno", "tblusers", "tblusers.users = forms!frmtestindassign.individual")

If IsNull(PID) = False And IsNull(SID) = True Then
db.Execute "Update tblpromo set sno = 15 where stockid = '" & SID & "';"
MsgBox "The IF statement ran."
ElseIf IsNull(PID) = True And IsNull(SID) = False Then
db.Execute "update tblstock set sno = 15 where stockid = '" & SID & "';"
MsgBox "The ElseIf statement ran."
Else: MsgBox "Nothing ran. The code doesn't work."
End If

End Sub

The code seems to execute fine, but when I look in the table, there's nothing in the field that should have been updated. There's no error, no nothing. I click the button that executes this code, it runs, and ... nothing.

If someone would help me to figure out where the syntax for my update statement is wrong, I'd be deeply indebted.

Thanks in advance!

Josh Hill

 

[izyrider]

lots of potential issues

SID is numeric?
drop both ' in '" & SID & "'

also in:
IsNull(PID) = True And IsNull(SID) = False
i would be tempted to explicitly parenthesise thus:
((IsNull(PID) = True) And (IsNull(SID) = False))
just to make sure it's evaluating the way you think it is.

no Option Explicit? why?
...and with Explicit
Dim frm As Form

your DLOOKUP is wrong!
if .users is string, try:
DLookup("tblusers.sno", "tblusers", "tblusers.users = '" & forms!frmtestindassign.individual & "'")
if not string you must drop the ' but you still need the " & forms!blah

is frm = Me ??? if yes, Me is faster

fix the above and post back if it still wont fly.

izy

 

[Josh Hill]

Izy, thank you so much for your reply. I appreciate the investigation.

I'm sorry about not being complete enough in the information provided.

While both PID and SID in the code house numbers, the field is data type "text" because of the way the data is stored right now. However, I can easily change that if it will make a difference -- I'm currently planning to control data formatting at the form level, so the table shouldn't care one way or another; changing to numeric data from text is not a problem.

The code still doesn't work. I've made the changes you suggested as shown below:

Private Sub Command10_Click()

Dim db As Database
Set db = CurrentDb
Set frm = Forms!frmtestindassign

PID = frm.PromoID
SID = frm.stockid
ind = frm.individual
sno1 = frm.sno
sno2 = DLookup("tblusers.sno", "tblusers", "tblusers.users = '" & Me.individual & "'")

If ((IsNull(PID) = False) And (IsNull(SID) = True)) Then
db.Execute "Update tblpromo set sno = " & sno2 & ";"
MsgBox "The IF statement ran."
ElseIf ((IsNull(PID) = True) And (IsNull(SID) = False)) Then
db.Execute "update tblstock set sno = 15 where stockid = '" & SID & "';"
MsgBox "The ElseIf statement ran."
Else: MsgBox "Nothing ran. The code doesn't work."
End If

End Sub

The codes doesn't render an error message, it simply doesn't update the table, and I can't figure out why. I checked to make sure I didn't have any record locks or anything goofy happening, but it still will not work. The code executes, the msgbox pops up, the Sub exits fine ... and the table is not updated. I suspect a problem with the UPDATE statement, but know so little about SQL, and in particular how it works with Access and is executed therein, I can't really say.

I appreciate any/all help provided.

God bless,
Josh

 

[Surjer]

Not sure if this matters but I always use the chr(34) for text queries

DLookup("tblusers.sno", "tblusers", "tblusers.users = " & chr(34) & Me.individual & chr(34))

db.Execute "update tblstock set sno = 15 where stockid = " & chr(34) & SID & chr(34)

Another thing just thought of


If PID and SID are DIM'd as strings then shouldnt you check for "" ?

like this
ElseIf PID = "" And SID <> "" Then

 

[checoturco]

try with

DoCmd.RunSQL " update tblpromo set sno =15
where stockid =" & Forms!<forms_name>!stockid &";"

 

[izyrider]

"" and chr$(34) both evaluate to ' so its a personal preference thing and not the cause of your problem

there is no WHERE in "Update tblpromo set sno = " & sno2 & ";"

apart from that, from what you've said so far, your code looks reasonable
...which msgbox shows when you run ??


debug stuff to experiment with

dim strSQL as string
.
.
msgbox DLookup("tblusers.sno", "tblusers", "tblusers.users = '" & Me.individual & "'"),,"This is the return from DLOOKUP() - is it OK??"
.
.
strSQL = "Update tblpromo set sno = " & sno2 & ";"
msgbox strSQL,,"my If SQL - does it look OK ??"
currentdb.execute strSQL
msgbox currentdb.recordsaffected & " records got updated"
.
.
strSQL = "update tblstock set sno = 15 where stockid = '" & SID & "';"
msgbox strSQL,,"my Else SQL - does it look OK ??"
currentdb.execute strSQL
msgbox currentdb.recordsaffected & " records got updated"

still no joy? ...instead of the msgbox above, try
debug.print strSQL
then copy/paste the SQL into query SQL view and see if it runs there and updates your table

and as a general principle it is always nice to trap for those ugly nulls and empty strings.

depending on the data you are playing with this can be anything like
if len(nz(myStuff,"") = 0 then
if isnull(myStuff)
if len(myStuff) = 0
blah blah and all other combinations of isdate() isnumeric() isnull() nz() len() etc

izy

 

[Josh Hill]

Hey guys, thank you all SO much for your responses!

It turns out the problem was two-fold.

First:
Using db.execute SQLstring didn't work; that was part of the problem. Changing it to DoCmd.RunSQL SQLstring made it happen. Thanks for the tip, checoturco.

Second:
The (now) executing UPDATE statement gave the error that it could not update the records because of key violation. After examining the DB carefully, I found an old table which, while not in use, still had relationships with the other tables in the DB. Removing this table's relationships allowed the UPDATE statement to run and successfully set the data as necessary.

I will now try to find out if an INSERT INTO statement is a better choice, since the warning about updating the records will be an annoyance to the client.

Thanks again, all, for your time and efforts, and God bless.
Josh

 

[Surjer]

I will now try to find out if an INSERT INTO statement is a better choice, since the warning about updating the records will be an annoyance to the client.

Docmd.SetWarnings False

do your code


Docmd.SetWarnings True

 

[Josh Hill]

Thanks, Jerry, works like a champ!

God bless all,
Josh