GohDiamond
"Access- Imagineer that!"
- Local time
- Today, 17:30
- Joined
- Nov 1, 2006
- Messages
- 550
Hello All,
I'm trying to get VBA to basically run a query by using DoCmd.RunSQL. The code runs from a button located on my main form.
I've defined two variables and I want to update the current record in two tables related tables.
Table 1 is called EntryData ' This is the table for the related subform'
Table 2 is called StudentData ' This is table for the Main Form'
In a query I'd code the SQL like this in order to correct the SSN in the subform table first ' I'm not using cascaded updates for a reason, it's a long story'
UPDATE EntryData SET EntryData.SSN = [CorrectSSN]
WHERE (((EntryData.SSN)=[WrongSSN]));
In VBA here's what I have so far:
Private Sub RepairSSN_Click()
Dim WrongSSN As Double
Dim CorrectSSN As Double
'SSN is being stored as a DOUBLE number with a format mask, it's another long story'
WrongSSN = Me!SSN 'This is the SSN that needs to be changed'
CorrectSSN = InputBox("Enter the Correct SSN for the Student") 'This is the Correct SSN to update to'
So I think I'm just stuck in some kind of syntax hell:
DoCmd.RunSQL ("UPDATE EntryData SET EntryData.SSN = [CorrectSSN] WHERE EntryData.SSN = [WrongSSN]")
This keep asking me to input the [CorrectSSN] and the [WrongSSN], but I want it to use the variables already defined. They exist correctly. I've verified them by setting breakpoints in the code.
How can I get the syntax to read from the defined variables?
And while we're at it here's the other table statement:
DoCmd.RunSQL "UPDATE StudentData SET StudentData.SSN = [CorrectSSN], StudentData.Xtra1 = Date(), StudentData.Xtra2 = [currentuser] WHERE StudentData.SSN)=[WrongSSN]"
This does the same thing as the first statement PLUS adds the Date and UserID to fields in that table as a modification stamp.
Thanking you all in advance for help solving this one.
Cheers!
Goh
I'm trying to get VBA to basically run a query by using DoCmd.RunSQL. The code runs from a button located on my main form.
I've defined two variables and I want to update the current record in two tables related tables.
Table 1 is called EntryData ' This is the table for the related subform'
Table 2 is called StudentData ' This is table for the Main Form'
In a query I'd code the SQL like this in order to correct the SSN in the subform table first ' I'm not using cascaded updates for a reason, it's a long story'
UPDATE EntryData SET EntryData.SSN = [CorrectSSN]
WHERE (((EntryData.SSN)=[WrongSSN]));
In VBA here's what I have so far:
Private Sub RepairSSN_Click()
Dim WrongSSN As Double
Dim CorrectSSN As Double
'SSN is being stored as a DOUBLE number with a format mask, it's another long story'
WrongSSN = Me!SSN 'This is the SSN that needs to be changed'
CorrectSSN = InputBox("Enter the Correct SSN for the Student") 'This is the Correct SSN to update to'
So I think I'm just stuck in some kind of syntax hell:
DoCmd.RunSQL ("UPDATE EntryData SET EntryData.SSN = [CorrectSSN] WHERE EntryData.SSN = [WrongSSN]")
This keep asking me to input the [CorrectSSN] and the [WrongSSN], but I want it to use the variables already defined. They exist correctly. I've verified them by setting breakpoints in the code.
How can I get the syntax to read from the defined variables?
And while we're at it here's the other table statement:
DoCmd.RunSQL "UPDATE StudentData SET StudentData.SSN = [CorrectSSN], StudentData.Xtra1 = Date(), StudentData.Xtra2 = [currentuser] WHERE StudentData.SSN)=[WrongSSN]"
This does the same thing as the first statement PLUS adds the Date and UserID to fields in that table as a modification stamp.
Thanking you all in advance for help solving this one.
Cheers!
Goh
