Syntax problem with Upsert function

Swillsy

Registered User.
Local time
Today, 13:54
Joined
Jun 10, 2008
Messages
68
Hi guys,

table involved is called [Schedule Details] >> i know its not good to do this now
I keep getting an error 3464 with the update section.

Heres the code:
Private Sub InputClose_Click()
DoCmd.SetWarnings False

Dim strSQL1 As String
Dim strSQL2 As String
Dim ArgID As String
Dim ArgRoom As String
Dim ArgStart As Date
Dim ArgEnd As Date
Dim ArgCon As String
Dim NameCheck As String

ArgID = Forms!AvailDisplayInput![DisplayStudentIDlbl]
ArgRoom = Forms!AvailDisplayInput![DisplayRoomIDlbl]
ArgStart = Forms!AvailDisplayInput![DisplayStartDatelbl]
ArgEnd = Forms!AvailDisplayInput![DisplayEndDatelbl]
ArgCon = Forms!AvailDisplayInput![Statuslbl]

strSQL1 = "INSERT INTO [Schedule Details]( StudentID,RoomID,StartDate,EndDate,Confirmed ) VALUES ('" & ArgID & "','" & ArgRoom & "', '" & ArgStart & "', '" & ArgEnd & "','" & ArgCon & "')"
strSQL2 = "UPDATE [Schedule Details] SET [Schedule Details].StudentID = '" & ArgID & "'," & _
"[Schedule Details].RoomID = '" & ArgRoom & "'," & _
"[Schedule Details].StartDate = '" & ArgStart & "'," & _
"[Schedule Details].EndDate = '" & ArgEnd & "'," & _
"[Schedule Details].Confirmed = '" & ArgCon & "' WHERE ([Schedule Details].StudentID = '" & ArgID & "')"


If Not (IsNull(DLookup("StudentID", "[Schedule Details]", "[Schedule Details].StudentID = " & Forms!AvailDisplayInput!DisplayStudentIDlbl & ""))) Then

DoCmd.RunSQL strSQL2 <<<<<<<<<heres where it stops:(
Else
DoCmd.RunSQL strSQL1

DoCmd.close acForm, "AvailDisplayInput"

DoCmd.SetWarnings True
End If

Can anyone see where I'm going wrong its been driving me mad
 
It looks like it doesn't like strSqQL2. Could be an error in the formatting

Try putting in MSgbox(strSQL2) just before DoCmd.RunSQL strSQL2 so you can see exactly what you are trying to run. This should let you see if all quotes etc are where you want them.
 
Just a quick update the problem is definitly occuring in this section:

strSQL2 = "UPDATE [Schedule Details] SET [Schedule Details].StudentID = '" & ArgID & "'," & _
"[Schedule Details].RoomID = '" & ArgRoom & "'," & _
"[Schedule Details].StartDate = '" & ArgStart & "'," & _
"[Schedule Details].EndDate = '" & ArgEnd & "'," & _
"[Schedule Details].Confirmed = '" & ArgCon & "' WHERE ([Schedule Details].StudentID = '" & ArgID & "')"
 
Basicly i dont know where you are going but all i see r valied syntaxes only 1 problem , you deal with numbers and dates as strings useing ' single quots ' to insert them which might be the case there . now if that didnt help opening your eyes , please post me the structure of the table so i'd be able to help:)
 
Also post the output from the msgBox that I asked you to insert.
 
Sorry nIGHTmAYOR I am a self taught vb noob I'm afraid,
however 'great success' I have it working now was a problem in the strSQL2 statement defining the ArgID etc.

Its extremly ugly but its working now for a training session I have tomorrow morning.

Private Sub InputClose_Click()
DoCmd.SetWarnings False

Dim strSQL1 As String
Dim strSQL2 As String
Dim ArgID As String
Dim ArgRoom As String
Dim ArgStart As Date
Dim ArgEnd As Date
Dim ArgCon As String
Dim NameCheck As String

ArgID = Forms!AvailDisplayInput![DisplayStudentIDlbl]
ArgRoom = Forms!AvailDisplayInput![DisplayRoomIDlbl]
ArgStart = Forms!AvailDisplayInput![DisplayStartDatelbl]
ArgEnd = Forms!AvailDisplayInput![DisplayEndDatelbl]
ArgCon = Forms!AvailDisplayInput![Statuslbl]

strSQL1 = "INSERT INTO [Schedule Details]( StudentID,RoomID,StartDate,EndDate,Confirmed ) VALUES ('" & ArgID & "','" & ArgRoom & "', '" & ArgStart & "', '" & ArgEnd & "','" & ArgCon & "')"
strSQL2 = "UPDATE [Schedule Details] SET [Schedule Details].StudentID = Forms!AvailDisplayInput![DisplayStudentIDlbl] ,[Schedule Details].RoomID = Forms!AvailDisplayInput![DisplayRoomIDlbl],[Schedule Details].StartDate = Forms!AvailDisplayInput![DisplayStartDatelbl],[Schedule Details].EndDate = Forms!AvailDisplayInput![DisplayEndDatelbl],[Schedule Details].Confirmed = Forms!AvailDisplayInput![Statuslbl] WHERE ([Schedule Details].StudentID = Forms!AvailDisplayInput![DisplayStudentIDlbl])"


If Not (IsNull(DLookup("StudentID", "[Schedule Details]", "[Schedule Details].StudentID = " & Forms!AvailDisplayInput!DisplayStudentIDlbl & ""))) Then

DoCmd.RunSQL strSQL2
DoCmd.close acForm, "AvailDisplayInput"
DoCmd.close acForm, "AvailabilityDisplay"
DoCmd.OpenForm "AvailabilityDisplay", acNormal

DoCmd.SetWarnings True
Else
DoCmd.RunSQL strSQL1
DoCmd.close acForm, "AvailDisplayInput"
DoCmd.close acForm, "AvailabilityDisplay"
DoCmd.OpenForm "AvailabilityDisplay", acNormal

DoCmd.SetWarnings True
End If
End Sub

the good news is I'm starting a part time Computer Science degree next week so you wont have to see these messy things anymore:)

Thanks for help guys, very rapid indeed!
 
now please oh please note the following , stSQL1 also will fail , only its condition havent been met yet :)
 
Try the following (note the #'s):
Code:
strSQL1 = "INSERT INTO [Schedule Details] " & _
    "( StudentID,RoomID,StartDate,EndDate,Confirmed ) " & _
    "VALUES ('" & ArgID & "','" & ArgRoom & "', [b][i]#[/i][/b]" & ArgStart & "[b][i]#[/i][/b], [b][i]#[/i][/b]" & _
    ArgEnd & "[b][i]#[/i][/b],'" & ArgCon & "')"
strSQL2 = "UPDATE [Schedule Details] " & _
    "SET [Schedule Details].StudentID = '" & ArgID & "'," & _
    "[Schedule Details].RoomID = '" & ArgRoom & "'," & _
    "[Schedule Details].StartDate = [b][i]#[/i][/b]" & ArgStart & "[b][i]#[/i][/b]," & _
    "[Schedule Details].EndDate = [b][i]#[/i][/b]" & ArgEnd & "[b][i]#[/i][/b]," & _
    "[Schedule Details].Confirmed = '" & ArgCon & _
    "' WHERE ([Schedule Details].StudentID = '" & ArgID & "')"
 
another hint ArgID might be numeric , so remove the single quotes , yet we'll never know he never posted the table structure :)
 
But, this one worked:

strSQL1 = "INSERT INTO [Schedule Details]( StudentID,RoomID,StartDate,EndDate,Confirmed ) VALUES ('" & ArgID & "','" & ArgRoom & "', '" & ArgStart & "', '" & ArgEnd & "','" & ArgCon & "')"

And they're all strings.

Wayne
 
Hi Wayne - yes strSQL1 works fine which is why I was confused that strSQL2 was having problems with the objects. It is all working fine now thanks for your help:)
 
S,

Judging by the fact that it was fixed by changing the references to:

Forms!AvailDisplayInput![Statuslbl]

I'd say that you had single-quotes in at least one of your data fields
when you tried the Update SQL. That data probably wouldn't have
been able to be inserted with SQL1.

Wayne
 

Users who are viewing this thread

Back
Top Bottom