Solved Syntax error from clause

[Manos39]

I cannot correct it now with

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

 

[Gasman]

What is this ''''error all the time? -(
Post the debug output. -(

 

[Manos39]

Put s

What is this ''''error all the time? -(
Post the debug output. -(

How do i do it. do you mean immediate window?

 

[Manos39]

My DB

 

[Gasman]

Yes, take the output of the debug.print and post it here, if you cannot work out what needs to be changed.

 

[Gasman]

My DB

I am on my phone atm, pc is being overhauled.

 

[Manos39]

I dont know how to output that maybe this you mean?

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]

Copy from select onwards, paste into a query window.
Does that produce anything?

 

[Manos39]

I dont know how to output that maybe this you mean?

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]

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]

I think parent.courseid needs to be concatenated, same with atheleteid?

Or just use courseid. Sql has know knowledge of parent.

 

[Manos39]

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

 

[Gasman]

My DB

Seriously?
How is anyone meant to know where to go to see the code?

 

[The_Doc_Man]

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]

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]

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]

Shouldn’t parent. Courseid be separate as well since there is no table called parent or use tblcoursesessions instead of parent

 

[cheekybuddha]

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]

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]

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