Syntax Error In Join Operation VBA SQL (1 Viewer)

[sphynx]

Hi again, sorry for all the post asking question rather than answering them im still a little green!

I have the following code setup to alter a query before opening a form within VBA, I have the code place in a buttons OnClick Event and the code is referenced via a separate module

The code runs fine except for when x = "DESPATCH", on x = "DESPATCH" I get a "Run Time 3135" "Syntax Error in Join Operation". Its driving me mad as the syntax is exactly the same as the rest of the code

Public Sub SkillQrySelect()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim x As String
Set db = CurrentDb
Set qdf = db.QueryDefs("MasterSkillDisplayLvl1_Qry")

x = Forms![TrainingDataEntry_Frm]![DEPARTMENT]

If x = "DESPATCH" Then GoTo 1
If x = "DESSERTS" Then GoTo 2
If x = "FINISHING" Then GoTo 3
If x = "HIGH RISK" Then GoTo 4
If x = "HYGIENE" Then GoTo 5
If x = "LOW RISK" Then GoTo 6
If x = "PACKAGING" Then GoTo 7
If x = "PRODUCTION" Then GoTo 8
If x = "QA" Then GoTo 9
If x = "STOCK" Then GoTo 10


1
Set db = CurrentDb
Set qdf = db.QueryDefs("MasterSkillDisplayLvL1_Qry")
strSQL = "SELECT MasterStaffList_Tbl.FULLNAME, Lvl1DespatchSkills_Tbl.[1S1], Lvl1DespatchSkills_Tbl.[1D1],Lvl1DespatchSkills_Tbl.[1S2]," & _
"Lvl1DespatchSkills_Tbl.[1D2], Lvl1DespatchSkills_Tbl.[1S3], Lvl1DespatchSkills_Tbl.[1D3], Lvl1DespatchSkills_Tbl.[1S4]," & _
"Lvl1DespatchSkills_Tbl.[1D4], Lvl1DespatchSkills_Tbl.[1S5], Lvl1DespatchSkills_Tbl.[1D5], Lvl1DespatchSkills_Tbl.[1S6]," & _
"Lvl1DespatchSkills_Tbl.[1D6], Lvl1DespatchSkills_Tbl.[1S7], Lvl1DespatchSkills_Tbl.[1D7], Lvl1DespatchSkills_Tbl.[1S8]," & _
"Lvl1DespatchSkills_Tbl.[1D8], Lvl1DespatchSkills_Tbl.[1S9], Lvl1DespatchSkills_Tbl.[1D9], Lvl1DespatchSkills_Tbl.[1S10]," & _
"Lvl1DespatchSkills_Tbl.[1D10], Lvl1DespatchSkills_Tbl.[1S11], Lvl1DespatchSkills_Tbl.[1D11], Lvl1DespatchSkills_Tbl.[1S12]," & _
"Lvl1DespatchSkills_Tbl.[1D12], Lvl1DespatchSkills_Tbl.[1S13], Lvl1DespatchSkills_Tbl.[1D13], Lvl1DespatchSkills_Tbl.[1S14]," & _
"Lvl1DespatchSkills_Tbl.[1D14], Lvl1DespatchSkills_Tbl.[1S15], Lvl1DespatchSkills_Tbl.[1D15], Lvl1DespatchSkills_Tbl.[1S16]," & _
"Lvl1DespatchSkills_Tbl.[1D16], Lvl1DespatchSkills_Tbl.[1S17], Lvl1DespatchSkills_Tbl.[1D17], Lvl1DespatchSkills_Tbl.[1S18]," & _
"Lvl1DespatchSkills_Tbl.[1D18], Lvl1DespatchSkills_Tbl.[1S19], Lvl1DespatchSkills_Tbl.[1D19], Lvl1DespatchSkills_Tbl.[1S20]," & _
"Lvl1DespatchSkills_Tbl.[1D20], Lvl1DespatchSkills_Tbl.[1S21], Lvl1DespatchSkills_Tbl.[1D21], Lvl1DespatchSkills_Tbl.[1S22]," & _
"Lvl1DespatchSkills_Tbl.[1D22], Lvl1DespatchSkills_Tbl.[1S23], Lvl1DespatchSkills_Tbl.[1D23], Lvl1DespatchSkills_Tbl.[1S24]," & _
"Lvl1DespatchSkills_Tbl.[1D24], Lvl1DespatchSkills_Tbl.[1S25], Lvl1DespatchSkills_Tbl.[1D25], Lvl1DespatchSkills_Tbl.[1S26]," & _
"Lvl1DespatchSkills_Tbl.[1D26], Lvl1DespatchSkills_Tbl.[1S27], Lvl1DespatchSkills_Tbl.[1D27], Lvl1DespatchSkills_Tbl.[1S28]," & _
"Lvl1DespatchSkills_Tbl.[1D28]" & _
"FROM MasterStaffList_Tbl INNER JOIN Lvl1DespatchSkills_Tbl ON MasterStaffList_Tbl.FULLNAME = Lvl1DespatchSkills_Tbl.FULLNAME1 " & _
"WHERE (((MasterStaffList_Tbl.FULLNAME)=[Forms]![TrainingDataEntry_Frm]![NAME]));"
qdf.SQL = strSQL
'DoCmd.OpenQuery "MasterSkillDisplayLvL1_Qry"
GoTo 11

10
Set db = CurrentDb
Set qdf = db.QueryDefs("MasterSkillDisplayLvL1_Qry")
strSQL = "SELECT MasterStaffList_Tbl.FULLNAME, Lvl1StockSkills_Tbl.[1S1], Lvl1StockSkills_Tbl.[1D1],Lvl1StockSkills_Tbl.[1S2]," & _
"Lvl1StockSkills_Tbl.[1D2], Lvl1StockSkills_Tbl.[1S3], Lvl1StockSkills_Tbl.[1D3], Lvl1StockSkills_Tbl.[1S4]," & _
"Lvl1StockSkills_Tbl.[1D4], Lvl1StockSkills_Tbl.[1S5], Lvl1StockSkills_Tbl.[1D5], Lvl1StockSkills_Tbl.[1S6]," & _
"Lvl1StockSkills_Tbl.[1D6], Lvl1StockSkills_Tbl.[1S7], Lvl1StockSkills_Tbl.[1D7], Lvl1StockSkills_Tbl.[1S8]," & _
"Lvl1StockSkills_Tbl.[1D8], Lvl1StockSkills_Tbl.[1S9], Lvl1StockSkills_Tbl.[1D9], Lvl1StockSkills_Tbl.[1S10]," & _
"Lvl1StockSkills_Tbl.[1D10], Lvl1StockSkills_Tbl.[1S11], Lvl1StockSkills_Tbl.[1D11], Lvl1StockSkills_Tbl.[1S12]," & _
"Lvl1StockSkills_Tbl.[1D12], Lvl1StockSkills_Tbl.[1S13], Lvl1StockSkills_Tbl.[1D13], Lvl1StockSkills_Tbl.[1S14]," & _
"Lvl1StockSkills_Tbl.[1D14], Lvl1StockSkills_Tbl.[1S15], Lvl1StockSkills_Tbl.[1D15], Lvl1StockSkills_Tbl.[1S16]," & _
"Lvl1StockSkills_Tbl.[1D16], Lvl1StockSkills_Tbl.[1S17], Lvl1StockSkills_Tbl.[1D17], Lvl1StockSkills_Tbl.[1S18]," & _
"Lvl1StockSkills_Tbl.[1D18], Lvl1StockSkills_Tbl.[1S19], Lvl1StockSkills_Tbl.[1D19], Lvl1StockSkills_Tbl.[1S20]," & _
"Lvl1StockSkills_Tbl.[1D20], Lvl1StockSkills_Tbl.[1S21], Lvl1StockSkills_Tbl.[1D21], Lvl1StockSkills_Tbl.[1S22]," & _
"Lvl1StockSkills_Tbl.[1D22], Lvl1StockSkills_Tbl.[1S23], Lvl1StockSkills_Tbl.[1D23], Lvl1StockSkills_Tbl.[1S24]," & _
"Lvl1StockSkills_Tbl.[1D24], Lvl1StockSkills_Tbl.[1S25], Lvl1StockSkills_Tbl.[1D25], Lvl1StockSkills_Tbl.[1S26]," & _
"Lvl1StockSkills_Tbl.[1D26], Lvl1StockSkills_Tbl.[1S27], Lvl1StockSkills_Tbl.[1D27], Lvl1StockSkills_Tbl.[1S28]," & _
"Lvl1StockSkills_Tbl.[1D28]" & _
"FROM MasterStaffList_Tbl INNER JOIN Lvl1StockSkills_Tbl ON MasterStaffList_Tbl.FULLNAME = Lvl1StockSkills_Tbl.FULLNAME1 " & _
"WHERE (((MasterStaffList_Tbl.FULLNAME)=[Forms]![TrainingDataEntry_Frm]![NAME]));"
qdf.SQL = strSQL
'DoCmd.OpenQuery "MasterSkillDisplayLvL1_Qry"
GoTo 11
11

Set qdf = Nothing
Set db = Nothing
End Sub

Any help would be greatly appreciated also any advice or pointer on the construction of my code would be welcomed.

 

[Banana]

To be frank, I think you have bigger problems than that.

The problem is that I do not think your tables are normalized because you shouldn't have that many fields in a given table, and certainly not fields named like 1D2, 1D4, 1D6....

To that end, you would be better off to go and read up on normalization and database theory and try again with a clean database.

 

[sphynx]

I have taken yours and others advice and research and normalised my database. Its much easier to work with now

Thanks for the advice