Ah, I think the problem was when I was creating the table for some reason I randomly put a : after positions and didn't realize it. I've since gone back and corrected them and it has caused such a huge headache. Apparently I'm missing somewhere in my SQL strings that I need to change because I've managed to break everything when I changed the table header names. Now when I open my settlement form I get the "Enter Parameter Value" prompt and it's asking for "tbl2Positions.Position" which doesn't exist. I renamed both columns in that table to "PositionID" and "PositionDesc" as suggested. I figured I simply just didn't change it correctly in my SQL string but I can't find anywhere that asks for "tbl2Positions.Position"...
Code:
Private Sub cmd_update_Click()
Dim SQL As String
Dim StartDate As String
Dim EndDate As String
Dim DriverName As String
StartDate = Me.txt_begdate.Value
EndDate = Me.txt_enddate.Value
DriverName = Me.cbo_driver.Value
SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles, tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc, tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID " _
& "FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads " _
& "ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID) ON tbl2Positions.PositionID = tbl1_Loads.PositionID) " _
& "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and tbl1_Loads.[DropOffDate] <= #" & EndDate & "#" _
& "GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc, tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID " _
& "ORDER BY tbl1_Loads.LoadID"
Me.sfrm_settlement.Form.RecordSource = SQL
Me.sfrm_settlement.Form.Requery
Me.Form.Requery
End Sub
If I hit cancel I get this:
Code:
Run-time error '2467':
The expression you entered refers to an object that is closed or doesn't exist.
If I type '1' or just press OK I get one more "Enter Parameter Value" popup asking for 'tbl1Loads.PositionID'. I can press ok then I get this error:
Code:
Run-time error '3131':
Syntax error in FROM clause.
And once again I don't know where the error is stemming from. I changed every instance I can find that has the column name.
Either way thanks for pointing that out, obviously I'd rather have to figure all this out now than later.
I'll keep fiddling with things, if I figure out when I messed up I'll post an update.