Append query from a form

[jw5107]

Below is a SQL that I pasted from design view of an Append query - I am tryin'to run this query from a button on a form. I keep gettin' a "join expression error" or a "syntax error on the FROM part".....
Any suggestions or examples I can mess with...??
thanks in advance..!!
jw5107

DoCmd.RunSQL "INSERT INTO CoverSheetMstr ( FleetID, SchdDocID, CvrSheetID, CoverNo, MxLocID, ACID, MatID, Appendix, SchdDate )" & _
"SELECT DISTINCTROW PartMstr.FleetID, DocMstr.SchdDocID, CoverSheetNos.CvrSheetID, CoverSheetNos.CoverNo, MxLocMstr.MxLocID, AircraftMstr.ACID, PartMstr.MatID, " & Me!Appendix & ", " & Me!SchdDate & " " & _
"FROM FleetMstr " & _
"LEFT JOIN CoverSheetNos" & _
"ON FleetMstr.FleetID = CoverSheetNos.Fleet" & _
"INNER JOIN AircraftMstr ON FleetMstr.FleetID = AircraftMstr.FleetID" & _
"INNER JOIN DocMstr" & _
"INNER JOIN PartMstr" & _
"ON DocMstr.SchdDocID = PartMstr.SchdDocID" & _
"ON FleetMstr.FleetID = DocMstr.FleetID" & _
"INNER JOIN MxLocMstr" & _
"ON FleetMstr.FleetID = MxLocMstr.FleetID" & _
"WHERE (((PartMstr.FleetID) = " & Me!cbofleet & ") And ((DocMstr.SchdDocID) = " & Me!cboFind & ") And ((CoverSheetNos.CvrSheetID) = " & Me!cboCoverShtNo & ") And ((AircraftMstr.ACID) = " & Me!cboAC & ") And ((MxLocMstr.MxLocID) = " & Me!cboGTWY & "))

 

[dhookom]

You are missing a ton of spaces between your strings. You have one after "FROM FleetMstr " but are missing them after most other lines.

Try Dim strSQL first and then build your statement in the strSQL variable. You can then use

Debug.Print strSQL
DoCmd.RunSQL strSQL

This will help you debug on your own in the future.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

You are missing a few spaces.

"INSERT INTO CoverSheetMstr ( FleetID, SchdDocID, CvrSheetID, CoverNo, MxLocID, ACID, MatID, Appendix, SchdDate )[COLOR=black yellow] [/color]" & _
"SELECT DISTINCTROW PartMstr.FleetID, DocMstr.SchdDocID, CoverSheetNos.CvrSheetID, CoverSheetNos.CoverNo, MxLocMstr.MxLocID, AircraftMstr.ACID, PartMstr.MatID, " & Me!Appendix & ", " & Me!SchdDate & " " & _
"FROM FleetMstr " & _
"LEFT JOIN CoverSheetNos[COLOR=black yellow] [/color]" & _
"ON FleetMstr.FleetID = CoverSheetNos.Fleet[COLOR=black yellow] [/color]" & _
"INNER JOIN AircraftMstr ON FleetMstr.FleetID = AircraftMstr.FleetID[COLOR=black yellow] [/color]" & _
"INNER JOIN DocMstr[COLOR=black yellow] [/color]" & _
"INNER JOIN PartMstr[COLOR=black yellow] [/color]" & _
"ON DocMstr.SchdDocID = PartMstr.SchdDocID[COLOR=black yellow] [/color]" & _
"ON FleetMstr.FleetID = DocMstr.FleetID[COLOR=black yellow] [/color]" & _
"INNER JOIN MxLocMstr[COLOR=black yellow] [/color]" & _
"ON FleetMstr.FleetID = MxLocMstr.FleetID[COLOR=black yellow] [/color]" & _
"WHERE (((PartMstr.FleetID) = " & Me!cbofleet & ") And ((DocMstr.SchdDocID) = " & Me!cboFind & ") And ((CoverSheetNos.CvrSheetID) = " & Me!cboCoverShtNo & ") And ((AircraftMstr.ACID) = " & Me!cboAC & ") And ((MxLocMstr.MxLocID) = " & Me!cboGTWY & "))"

I also notice that you are specifying field names with variables from your code. Specifically "Me!Appendix" and "Me!SchdDate". You need to be sure that those are in fact valid field names in your source tables.

 

[jw5107]

dhookum & Golom,

Schweeeeeeett..!!! Appreciate the fast reponse from both of ya..!!!
I have done what dhookum suggested and now I'm finding out what Golom mentioned....!!
I know get an error stating "argument not optional" and it highlights Me!cboGTWY... When I take that out it then gives the same error, but highlights Me!cboAC..??
I don't know what to do to fix it....!!!
I want the WHERE statement to utilize a few controls on the form for criteria...
Can ya hold my hand some more....???
Thanks again..!!
jw5107

 

[dhookom]

Are each of your value from form controls numeric? If they might be text values, you need to include quotes. For instance if ACID is text, use:

And ((AircraftMstr.ACID) = """ & Me!cboAC & """) And ...

Dates must be delimited with "#".

I also recommend cutting the line into smaller chunks like:

strSQL = "INSERT INTO CoverSheetMstr ( FleetID, SchdDocID, CvrSheetID, CoverNo, MxLocID, ACID, MatID, Appendix, SchdDate ) " 
strSQL = strSQL & "SELECT DISTINCTROW PartMstr.FleetID, DocMstr.SchdDocID, CoverSheetNos.CvrSheetID, CoverSheetNos.CoverNo, MxLocMstr.MxLocID, AircraftMstr.ACID, PartMstr.MatID, " & Me!Appendix & ", " & Me!SchdDate & " "
strSQL = strSQL & "FROM FleetMstr " & _

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]