please correct my code..new to ado

[SamDeMan]

i get an error something about the join. please let me know whats wrong. i am new to ado.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Application.SetOption "Auto Compact", True
Dim db As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim dteMyDate As Date, strMySql As String

Set db = CurrentProject.Connection

Me!FacilityID.DefaultValue = 1
strMySql = "SELECT Max(AgencyLogTbl.DateWorked)+1 FROM AgencyLogTbl"
strMySql = strMySql & " INNER JOIN AgencyLogTbl ON AgencyPriceListTbl.PriceListID ="
strMySql = strMySql & " AgencyLogTbl.PricelistID WHERE ((AgencyPriceListTbl.FacilityID)='"
strMySql = strMySql & Me!FacilityID & " ')"

Set rs1 = db.Execute(strMySql)
MsgBox rs1(0), vbCritical, "Still Testing"
Me!Date.DefaultValue = rs1(0)


dteMyDate = Me!Date
Me!DateWorked = dteMyDate

rs1.Close
Set db = Nothing
Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub

thanks,

sam

 

[SamDeMan]

i found the mistake. sorry guys. the table in the join was repeated twice, instead of using two different tables.

thanks,

sam