Type MisMatch Error?? 1

[WB786]

I am getting the type mismatch error on this line: Set rs = db.OpenRecordset(SQLstr, dbReadOnly)

I am using Access 2002 and Sql 2000 - the whole code is listed below. Thanks.

Private Sub GetRecord_Click()
If Not IsNull(Me.empid) Then

Const dbReadOnly = 4
Dim db As Database
Dim rs As Recordset
Dim SQLstr As String
Dim USERID As String

USERID = Forms!frmTermEmp_IN![empid]

SQLstr = "SELECT * From tblEmployees WHERE tblEmployees.Emp_Tracker= '" & USERID & "';"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQLstr, dbReadOnly)

Do 'for every record in selection:
If rs(&quot;Emp_FirstName&quot <> &quot;&quot; Then Forms!frmTermEmp_IN![Emp_FirstName] = rs(&quot;Emp_FirstName&quot
If rs(&quot;Emp_LastName&quot <> &quot;&quot; Then Forms!frmTermEmp_IN![Emp_LastName] = rs(&quot;Emp_LastName&quot


rs.MoveNext
Loop Until rs.EOF

rs.Close
db.Close
Else
MsgBox &quot;Please Select An Employee First&quot;, vbCritical, &quot;Error&quot;
End If
End Sub

 

[DBAMJA]

Try changing Dim rs As Recordset to Dim rs as DAO.Recordset



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[jhall156]

I think maybe your where clause is using single quotes which works fine in, say, VFP but not in DAO. You might try

&quot;SELECT * From tblEmployees WHERE tblEmployees.Emp_Tracker= &quot; & CHR(34) & USERID & CHR(34) & &quot;;&quot;



JHall

 

[WB786]

DBAMJA,

Thank you that worked!!!

 

[WB786]

One more question: I got this code from someone at my previous job and the way she had her database was to add entry to a tblterms (local table) but what I want to do is just pull up the record from tblEmployees and update the existing record on sql. Is that possible? Thanks.

Wb

 

[DBAMJA]

It is really not as difficult as it may seem. Are the changes that you are making coming from a form or are they just global changes (ie: the same update for every employee)?

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[WB786]

It is not a global change. What I was trying to do was that a manager would pull up the employee by their login id then click on get record and the coding above would pull up the employee info then the manager could update their record and then close the form.

Thanks,

WB

 

[DBAMJA]

What might be easier in the long run is using a form that has a combo box on it with the employee names and ids and when the manager picks an id the form is filled in from the combo box. That way the data is being updated as the manager makes changes.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[WB786]

That is how I was going to do it first but then the combo box becomes to wide as there are close to 15 fields that need to be pulled up for each given employee. Is there way then to make the combo box to show only the employee name and id and not the other 13 fields but I am still able to bound to those columns.

Thanks,
WB

 

[DBAMJA]

When you drop the combo box on your form, be sure that you have Access set to run the combo box wizard. It will allow you to pick what fields you want in the combo box and it will also ask you if you want to look up the record associated with the value in the combo box. I use this all of the time and it works just great.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.