Solved Syntax error from clause (1 Viewer)

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I cannot correct it now with
Code:
Private Sub cmdAllAthletes_Click()
Dim s1 As String

s1 = ""
s1 = s1 & "INSERT INTO Tbl_CourseAttendances "
s1 = s1 & "(courseID, CourseDate, athleteID) "
s1 = s1 & " SELECT Parent.[courseID], " & Format(Parent.[CourseDate], "\#yyyy\/mm\/dd\#") & ","
s1 = s1 & " athleteID"
s1 = s1 & " FROM Tbl_CourseSessions"
s1 = s1 & " INNER JOIN Tbl_CourseAthletes"

s1 = s1 & " ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID"  ''''error

s1 = s1 & " WHERE Tbl_CourseSessions.courseID = " & Parent.[courseID]
Debug.Print s1
CurrentDb.Execute s1
End Sub

shows attached picture .. not again records inserted
 

Attachments

  • 2.jpg
    2.jpg
    69.4 KB · Views: 108

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
What is this ''''error all the time? -(
Post the debug output. -(
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
Put s
What is this ''''error all the time? -(
Post the debug output. -(
How do i do it. do you mean immediate window?
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
My DB
 

Attachments

  • Apollon Club 6-5-2022.accdb
    5.5 MB · Views: 108

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
Yes, take the output of the debug.print and post it here, if you cannot work out what needs to be changed.
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I dont know how to output that maybe this you mean?
Code:
INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT Parent.[courseID], #2022/06/20#, athleteID FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36

i am thinking maybe .. athleteid is in subform
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
Copy from select onwards, paste into a query window.
Does that produce anything?
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I dont know how to output that maybe this you mean?
Code:
INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT Parent.[courseID], #2022/06/20#, athleteID FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36

i am thinking maybe .. athleteid is in subform
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I did that, it asked parameters parent.courseID, i entered 36 for my previus tries courseID and produced i buch or records in table Attendance
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
I think parent.courseid needs to be concatenated, same with atheleteid?

Or just use courseid. Sql has know knowledge of parent.
 
Last edited:

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I did that, it asked parameters parent.courseID, i entered 36 for my previus tries courseID and produced i buch or records in table Attendance
no it worked as charm. it put athletes all right away.
Thanks for your efforts i ll try later back here on the problems .. Have to go
Sorry my English is not so good. Problem is not solved i meant it didnt ask something about t athletes when i run query as you said. Still command doesn't work. It worked only when i run query from select on asking parameters because i copied with original syntax Parent.courseID
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:06
Joined
Feb 28, 2001
Messages
27,001
it asked parameters parent.courseID

Here's a little tip for you: When Access asks for a named parameter, it is because you used that name in a query and Access can't find it. It is usually one of two reasons: (a) spelled the name wrong, or (b) the thing exists but not where you thought it did. In the latter case, it needs to be qualified with something so that Access can locate it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
Doc. The parent would refer to the parent form, as o/p stated atheleteid was in subform?
I think I would go with full concatenation of values.
 

cheekybuddha

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
2,237
Code:
s1 = "INSERT INTO Tbl_CourseAttendances " & vbNewLine & _
     "  (courseID, CourseDate, athleteID) " & vbNewLine & _
     "SELECT " & vbNewLine & _
     "  " & Me.Parent.[courseID] & ", " & vbNewLine & _
     "  " & Format(Me.Parent.[CourseDate], "\#yyyy\-mm\-dd\#") & ", " & vbNewLine & _
     "  athleteID " & vbNewLine & _
     "FROM Tbl_CourseSessions s " & vbNewLine & _
     "INNER JOIN Tbl_CourseAthletes a " & vbNewLine & _
     "        ON s.courseID = a.courseID " & vbNewLine & _
     "WHERE s.courseID = " & Me.Parent.[courseID] & ";"
Debug.Print s1
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2013
Messages
16,553
Shouldn’t parent. Courseid be separate as well since there is no table called parent or use tblcoursesessions instead of parent
 

cheekybuddha

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
2,237
I just had a quick look at your db in Post#1.

Your table design is slightly off.

Tbl_CourseAttendances is not necessary.

Change Tbl_CourseAthletes to TblAthleteSessions.

FK to Tbl_athletes.athleteID
FK to Tbl_CourseSessions.coursesessionID

That's all you need.
You get the CourseID and CourseDate via Tbl_CourseSessions.coursesessionID.
You get the athlete data via Tbl_athletes.athleteID
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
I just had a quick look at your db in Post#1.

Your table design is slightly off.

Tbl_CourseAttendances is not necessary.

Change Tbl_CourseAthletes to TblAthleteSessions.

FK to Tbl_athletes.athleteID
FK to Tbl_CourseSessions.coursesessionID

That's all you need.
You get the CourseID and CourseDate via Tbl_CourseSessions.coursesessionID.
You get the athlete data via Tbl_athletes.athleteID
Thank you for viewing and replying about structure. Though thinking of tracking the attendees would you suggest to have in the new table TblAthleteSessions the field attendance? And if so why not keeping Tbl_CourseAttendance and have
FK to Tbl_athletes.athleteID
FK to Tbl_CourseSessions.coursesessionID. Also another question ..:
with the existing structure i think it is automated after put in Tbl_CourseAthletes, which athlete is in which course, as for having them as a ready combination to a table with the dates where those athletes are tracked for attendance..?
 

Manos39

Registered User.
Local time
Yesterday, 23:06
Joined
Feb 14, 2011
Messages
248
Thank you for viewing and replying about structure. Though thinking of tracking the attendees would you suggest to have in the new table TblAthleteSessions the field attendance? And if so why not keeping Tbl_CourseAttendance and have
FK to Tbl_athletes.athleteID
FK to Tbl_CourseSessions.coursesessionID. Also another question ..:
with the existing structure i think it is automated after put in Tbl_CourseAthletes, which athlete is in which course, as for having them as a ready combination to a table with the dates where those athletes are tracked for attendance..?
And also, code you suggested is working as charm...!
 

Users who are viewing this thread

Top Bottom