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
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