What am I doing wrong

[Gina Whipp]

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!

 

[Tom Ellison]

Dear Gina:

The best way to debug a situation like this (in my opinion) is to take
a look at the contents of strSQL just before attempting to run the
query. Put a break point on the DoCmd.RunSQL and display the value in
the immediate pane. Past this into a new query and see if it runs.
If not, you may see the error in the string by examination, or you can
use the error message generated from trying to run it. Yet another
alternative is to post that string back here in the newsgroup so we
can try to help with that. Finally, of course, you would then alter
the code that generates the string to eliminate the error.

Looking at your code, it appears that, in the case where you're doing
an INSERT, your SELECT statement is meant to generate a single row
with values taken from controls or variables. One thing I see there
is a missing comma after AS F3. The technique above should show you
this kind of thing rather readily.

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts