Query Error in VBA Access 2000

[dewylie]

Forgive me as I am new to this forum and to Access, but here is my code. I can't get the Employee name to come up, only the Employee ID number.

Private Sub List2_AfterUpdate()
    With Me.List4
        .RowSource = "SELECT InventoryID, Platform, EmployeeID " & _
        "FROM Inventory " & _
 
(This is where I put my inner join to join the tables, Inventory and Employees).
 
       "WHERE Company = " & Chr(34) & Me.List0 & Chr(34) & _
        "AND Application = " & Chr(34) & Me.List2 & Chr(34) & _
        "ORDER BY InventoryID, Platform, EmployeeID"
 
(Then I placed Employees.Employee in place of EmployeeID and changed that in the SELECT portion of the above rowsource).
 
        .ColumnCount = 3
        .ColumnWidths = "0;1.5 IN;1.5 IN "
        .ColumnHeads = True
        .Requery
         End With
    With Me.List2
     End With
        Me.Label7.Caption = Me.List4.ListCount & _
        " Platforms and Employees in " & Me.List2 & ", " & _
        Me.List0
End Sub

I get a syntax error, but can't figure out why.
Any suggestions?
dewylie

[NeoPa]

I suggest you post the code you're actually using with the error message and an indication of the line it highlights when reporting the error.

[MMcCarthy]

Try this ..

Private Sub List2_AfterUpdate()
    With Me.List4
        .RowSource = "SELECT InventoryID, Platform, Employee " & _
        "FROM Inventory INNER JOIN Employees " & _
"ON Inventory.EmployeeID=Employees.EmployeeID " & _
"WHERE Company = " & Chr(34) & Me.List0 & Chr(34) & _
        " AND Application = " & Chr(34) & Me.List2 & Chr(34) & _
        " ORDER BY InventoryID, Platform, Employee;"
 
.ColumnCount = 3
        .ColumnWidths = "0;1.5 IN;1.5 IN "
        .ColumnHeads = True
        .Requery
         End With
    With Me.List2
     End With
        Me.Label7.Caption = Me.List4.ListCount & _
        " Platforms and Employees in " & Me.List2 & ", " & _
        Me.List0
End Sub

[dewylie]

Thank you, thank you, thank you, mmccarthy.
I just tried it and it works PERFECTLY! You are a lifesaver!!!

[MMcCarthy]

Thank you, thank you, thank you, mmccarthy.
I just tried it and it works PERFECTLY! You are a lifesaver!!!

Not a problem. I'm glad it worked out for you.

Mary