I am trying to take data from an open form and link it to a record on a table using OpenRecordset. Since I have similar code elsewhere I have to assume it has something to do with pulling the data from the form unless I am missing something else Or the fact that this one uses a JOIN.
Code:
Set rsConsults = dbs.OpenRecordset("SELECT Scheduler_Sheet.ConsultNumber, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, tblConsultAlias.[Service Name], Scheduler_Sheet.Program FROM Scheduler_Sheet INNER JOIN tblConsultAlias ON Scheduler_Sheet.Clinic = tblConsultAlias.Clinic WHERE (Scheduler_Sheet.[ConsultNumber]) = '" & Forms!frmToChart!ConsultNumber & "'")
I get an error 'Datatype mismatch in criteria expression'.
I also tried assigning the data from the form to a variable and got the same error...
Code:
Dim FromForm As String
FromForm = Forms!frmToChart.Form!ConsultNumber
Set rsConsults = dbs.OpenRecordset("SELECT Scheduler_Sheet.ConsultNumber, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, tblConsultAlias.[Service Name], Scheduler_Sheet.Program FROM Scheduler_Sheet INNER JOIN tblConsultAlias ON Scheduler_Sheet.Clinic = tblConsultAlias.Clinic WHERE (Scheduler_Sheet.[ConsultNumber]) = '" & FromForm & "'")
I do use the following code elsewhere and it works fine:
Code:
Set RSReceiveName = CurrentDb.OpenRecordset("SELECT VHALogin, Fname, Lname, VistaAccess, VistaVerify, SaveVistaLogin FROM tblUpdatedEmployees WHERE (tblUpdatedEmployees.[VHALogin]) = '" & GetUserName & "'")