Solved Syntax error from clause

[Manos39]

Hello,
Trying to get better i have much trouble providing code for my DB..
and i would like help on insert into fired from command button "cmdAllAthletes" on the subform of my form Fr_CourseSessions
I would like to fill rows to Tbl_CourseAttendances for athletes registered in tennis course sessions filled from the above form "Fr_CourseSessions"
My code is:

Private Sub cmdAllAthletes_Click()
    Dim strSQL As String
   
    strSQL = "INSERT INTO Tbl_CourseAttendances " & _
        "(courseID, CourseDate, athleteID) " & _
        "SELECT athleteID, " & Parent.[CourseID] & ", #" & _
        Format(Parent.[CourseDate], "yyyy-mm-dd ") & _
        "# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes WHERE Tbl_CourseSessions.courseID = " & Parent.[CourseID]
   
    CurrentDb.Execute strSQL

same sql (which is working from query) is:

INSERT INTO Tbl_CourseAttendances ( courseID, CourseDate, athleteID )
SELECT Tbl_CourseSessions.courseID, Tbl_CourseSessions.CourseDate, Tbl_CourseAthletes.athleteID
FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID;

but it has errors in from clause
Also i would like my subform to show athletes registered in the specific session when chosen from "Fr_CourseSessions" and maybe a more efficient
way to automate the process ( seeing the subform takes space in my form) maybe after inserting a new session then attendance rows are filled
(maybe Form "Fr_CourseSessions" AfterUpdate? Dont know what it could be better!?
I attach my DB

 

[CJ_London]

doesn't look the same to me, you have a different order of fields, no 'ON' clause and include a where clause

put
debug.print strSQL

before you execute - see what it actually looks like

 

[The_Doc_Man]

First, you say you get errors on the FROM clause. What error do you get?

Second, I believe that an "ON" clause is mandatory once you use keyword JOIN. Your first exhibit (involving variable "strSQL") doesn't have an ON clause. You are treading on dangerous (i.e. confusing as all heck) ground because without an ON clause and with a WHERE clause that only applies to one field in one of the participant tables, Access could find many reasons to be upset with you. You might accidentally get a Cartesian JOIN (a.k.a. permutation JOIN) which would look really whacky and would give you records that you would swear don't go together.

 

[Manos39]

Sincerely i dont know how to accomplish your suggestions i am struggling reading pieces of information about it. Thats why i need help

 

[arnelgp]

same sql (which is working from query) is:

Copy your Query (Copy/Paste).
Modify the new query, add a Where Clause on SQL design:

...
WHERE Tbl_CourseSessions.courseID = [Forms]![yourMainFormName]![CourseID]

use DoCmd.OpenQuery "theNewQueryName" in substitute to your Currentdb.Execute command.

 

[Gasman]

Sincerely i dont know how to accomplish your suggestions i am struggling reading pieces of information about it. Thats why i need help

You have been on here since 2011, so presumably should have basic debugging skills?
Go to the code window, Put a Debug.Print strSQL after you set it and before you try to use it.
Click to the left of the debug.print line and a red dot should appear. That is called a Breakpoint. The code will stop at breakpoints.
Execute the code, the code window should open to that breakpoint.
Then press F8. F8 allows you to step through the code line by line.
Once that Debug.Print line is executed, go to the Immediate Windows (Ctrl + G) and you will see the result of the debug.print and the value of strSQL.
You should now be able to see your error. If not copy that output, open a query window, paste it into the SQL view and run and see if that highlights your problem. If you still cannot see it, paste that output back here.

Also see the Debugging link in my signature for visual instructions.

 

[Manos39]

I shall try all of suggestions thank you

 

[Manos39]

After what to suggested ..what i managed doing was a mess
I tried with Debug.Print strSQL after:

Private Sub cmdAllAthletes_Click()
    Dim strSQL As String
   
    strSQL = "INSERT INTO Tbl_CourseAttendances " & _
        "(courseID,CourseDate,athleteID) " & _
        "SELECT  " & Parent.[CourseID] & ",  #" & _
        Format(Parent.[CourseDate], "dd-mm-yyyy ") & _
        athleteID & _
        "# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID;"
        WHERE Tbl_CourseSessions.CourseID = [Forms]![Fr_CourseSessions]![CourseID]
   
   
   
    CurrentDb.Execute strSQL

my sql produced was

INSERT INTO Tbl_CourseAttendances (athleteID ,courseID, CourseDate) SELECT athleteID, 42, #2022-06-01 # FROM Tbl_CourseSessions WHERE Tbl_CourseSessions.courseID = 4

but the right one -from my working append query- is:

INSERT INTO Tbl_CourseAttendances ( courseID, CourseDate, athleteID )
SELECT Tbl_CourseSessions.courseID, Tbl_CourseSessions.CourseDate, Tbl_CourseAthletes.athleteID
FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID;

 

[CJ_London]

don't think you are looking at the same thing - for example your sql string is constructed with

"INSERT INTO Tbl_CourseAttendances " & _
"(courseID,CourseDate,athleteID) "

and

Format(Parent.[CourseDate], "dd-mm-yyyy ")

but the strsql is showing

INSERT INTO Tbl_CourseAttendances (athleteID ,courseID, CourseDate)

and

#2022-06-01 #

which is the correct format of yyyy-mm-dd

One thing you do need to do is lose the space between the date and the # - it should be

#2022-06-01#

And I don't see a debug.print strsql in your code

 

[plog]

Agree with CJ, but you have bigger issues. I'm looking at Qr_Attendance and I see 2 things:

1. Date and time in seperate fields. It's called a DateTime field for a reason--it can and should hold both pieces of data. That means CourseDate and CourseTime should not be in seperate fields but together in one.

2. Unusual primary key-named fields. Tbl_CourseSession has a field called coursesessionID but it isn't the primary key. Why? What field type is that and when, if ever, do you use it? Same issue with Tbl_CourseAthletes. In both you have a seemingly primary key, but instead have created a composite key using 2 other fields.

3. You JOIN on half a composite key--twice. Both Tbl_CourseSessions and Tbl_CourseAthletes have composite primary keys (primary key made of multiple fields. When you JOIN those 2 tables you are only doing on half of each's key (courseID). This has got to create unwanted duplicates.

Since courseId is not a unique field in either table it presumably has multiple similar values in both tables. So, when you link the tables like that you have got to be creatin duplicates you don't want in Qr_Attendance.

I think you need to fix that before you move to VBA. Edit Qr_Attendance, to sort by courseID and then by courseDate, run it and tell me you don't see aren't unnecessary duplicates.

 

[Manos39]

Agree with CJ, but you have bigger issues. I'm looking at Qr_Attendance and I see 2 things:

1. Date and time in seperate fields. It's called a DateTime field for a reason--it can and should hold both pieces of data. That means CourseDate and CourseTime should not be in seperate fields but together in one.

2. Unusual primary key-named fields. Tbl_CourseSession has a field called coursesessionID but it isn't the primary key. Why? What field type is that and when, if ever, do you use it? Same issue with Tbl_CourseAthletes. In both you have a seemingly primary key, but instead have created a composite key using 2 other fields.

3. You JOIN on half a composite key--twice. Both Tbl_CourseSessions and Tbl_CourseAthletes have composite primary keys (primary key made of multiple fields. When you JOIN those 2 tables you are only doing on half of each's key (courseID). This has got to create unwanted duplicates.

Since courseId is not a unique field in either table it presumably has multiple similar values in both tables. So, when you link the tables like that you have got to be creatin duplicates you don't want in Qr_Attendance.

I think you need to fix that before you move to VBA. Edit Qr_Attendance, to sort by courseID and then by courseDate, run it and tell me you don't see aren't unnecessary duplicates.

In order to make easier for planning course sessions i needed to have seperate time field in Tbl_CourseSession. I did as you advised and removed unnecessary extra ID (autonumber keys) from my tables since i have those composite keys in those tables. i have done so to eliminate dublicates. Also i run Qr_Attendance and there aren't dublicates. There couldnt be dublicates because records are in Tbl_CourseAthletes a) are depended on records of Tbl_CourseSession b) depended on Tbl_CourseAthletes records of Athletes Registered in the specific courses (which are unique in the specific Year, as records are valued from composite keys courseID and academicyearID). Also i tried reading on how to accomplish what i needed on how to convert SQL syntax to VBA string syntax, using utility DB called Sql2vba from Allen Browne http://allenbrowne.com/ser-71.html and forum's https://www.access-programmers.co.uk/forums/threads/sql-to-vba-and-back-again.293372/ and put code in Fr_CourseSessions cmdAllAthletes like:

Private Sub cmdAllAthletes_Click()
CurrentDb.Execute "INSERT INTO Tbl_CourseAttendances ( courseID, CourseDate, athleteID ) " & _
" SELECT Tbl_CourseSessions.courseID, Tbl_CourseSessions.CourseDate, Tbl_CourseAthletes.athleteID " & _
" FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID"

End Sub

which works without annoying messages and fills records in Tbl_CourseAttendances (so far i didn't see dublicates)

That code also works as an AfterUpdate event on same form (Fr_CourseSessions)
For my previous request i would like to have the button cmdAllAthletes on that form, after input a new course, to update Tbl_CourseAttendances eg having as source main form record, and subform's athleteID
This is my DB with the changes and Tbl_CourseAttendances snapsots

 

[SHANEMAC51]

My code is:

Private Sub cmdAllAthletes_Click()
Dim s1 As String

s1 = ""
s1 = s1 & "INSERT INTO Tbl_CourseAttendances "
s1 = s1 & "(courseID, CourseDate, athleteID) "
s1 = s1 & " SELECT athleteID, " & Parent.[CourseID] & ", "
s1 = s1 & Format(Parent.[CourseDate], "\#yyyy\/mm\/dd\#")   ''''error
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

 

[Manos39]

Private Sub cmdAllAthletes_Click()
Dim s1 As String

s1 = ""
s1 = s1 & "INSERT INTO Tbl_CourseAttendances "
s1 = s1 & "(courseID, CourseDate, athleteID) "
s1 = s1 & " SELECT athleteID, " & Parent.[CourseID] & ", "
s1 = s1 & Format(Parent.[CourseDate], "\#yyyy\/mm\/dd\#")   ''''error
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

Thank you.. I shall try that

 

[Manos39]

Private Sub cmdAllAthletes_Click()
Dim s1 As String

s1 = ""
s1 = s1 & "INSERT INTO Tbl_CourseAttendances "
s1 = s1 & "(courseID, CourseDate, athleteID) "
s1 = s1 & " SELECT athleteID, " & Parent.[CourseID] & ", "
s1 = s1 & Format(Parent.[CourseDate], "\#yyyy\/mm\/dd\#")   ''''error
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

sorry but it didn't work as above, and considering field order i changed it as:

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\#") ''''error & ","
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

but.. it still did't do the work..

 

[Gasman]

Show the output of the debug.print.

 

[Manos39]

With SHANEMAC51 way no error just no records inserted immediate window

INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT athleteID, 36, #2022/06/12# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36
INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT athleteID, 36, #2022/06/12# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36
INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT athleteID, 36, #2022/06/12# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36
INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT athleteID, 36, #2022/06/12# FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36

My corrections: 1) attached picture

and : marked yellow on /// CurrentDb.Execute s1

and immediate window

INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT Parent.[courseID], #2022/06/11#athleteID FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36

 

[The_Doc_Man]

OK, looking at the output, you have a field-order mismatch.

INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID) SELECT athleteID, 36, #2022/06/12# FROM

You name the fields in the order course ID, course date, athlete ID - but you supply them in the order athlete ID, course id, course data. So things are going into the wrong fields. When you use the INSERT INTO ... SELECT syntax, the fields have to be presented in the same order that they are named.

 

[Manos39]

OK, looking at the output, you have a field-order mismatch.

INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID) SELECT athleteID, 36, #2022/06/12# FROM

You name the fields in the order course ID, course date, athlete ID - but you supply them in the order athlete ID, course id, course data. So things are going into the wrong fields. When you use the INSERT INTO ... SELECT syntax, the fields have to be presented in the same order that they are named.

Saw that so i corrected it 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\#") ''''error & ","
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

but there must be a string problem which i m not able to correct as far as this came out

INSERT INTO Tbl_CourseAttendances (courseID, CourseDate, athleteID)  SELECT Parent.[courseID], #2022/06/11#athleteID FROM Tbl_CourseSessions INNER JOIN Tbl_CourseAthletes ON Tbl_CourseSessions.courseID = Tbl_CourseAthletes.courseID WHERE Tbl_CourseSessions.courseID = 36

date and athleteid are stucked

 

[Gasman]

You have removed the space before atheleteid as well. -(
That is why a debug is needed. You should be able to see what needs to be changed?

 

[Gasman]

Shouldn't need to format the date either, I would have thought?