I'm trying to execute an update SQL using the Do While but I'm running into a "Type Mismatch" error. Where am I going wrong?

You can't use an update query this way.

What is it you are trying to accomplish? What's the goal?

George

I have 4 records on my continuous subform that I'm trying to update the other 3 when one is checked to true.

Hi,

If you want to update the table, all you should need is:

Dim strSQL As String
strSQL = "UPDATE tblEmployeeSkills SET YesNoCheck = -1 WHERE EmpID=Forms!frmEmployeeSkills.frmEmployee1.Form!EmpID AND CapacityID >=112 AND MaterialID=1 AND RangeID=1"
CurrentDb.Execute strSQL, dbFailOnError

However, I'm not sure if that would work with your current setup.

Hope that helps...

That's clearer. But it still leaves some details out.

I can guess that the continuous subform is probably bound to a table called tblEmployeeSkills and that the subform is linked to the main form in which it is embedded via the EmpID. Is that reasonably close to your actual situtation?

It also appears from your SQL that you want to update one field, called "YesNoCheck" from false to true in the table named tblEmployeeSkills.

You are selecting one employee for whom you want to update the records by applying the criteria "tblEmployeeSkills.EmpID)=[Forms]![frmEmployeeSkills]![frmEmployee1]![EmpID"

And in the table where this EmployeeID is found, you want to update all records for that employee where they have a CapacityiD >=112, where they have a MaterialID = 1 and a RangeID =1.

How do you know that that will result in a recordset consisting of 4 records? Does every employe have four skill records that meet those criteria?

If that is the case, your recordset lines are superfluous, as you can simply execute the update sql directly against the table:

Currentdb.Execute "UPDATE tblEmployeeSkills SET tblEmployeeSkills.YesNoCheck = -1 WHERE tblEmployeeSkills.EmpID = " & [Forms]![frmEmployeeSkills]![frmEmployee1]![EmpID] & " AND
tblEmployeeSkills.CapacityID>=112) AND tblEmployeeSkills.MaterialID=1 AND tblEmployeeSkills.RangeID=1"

I have to assume that there must be some reason for including the recordset stuff, so please explain further what is supposed to be happening.

Thanks.

George

Edited by: GroverParkGeorge on Fri Sep 18 22:51:41 EDT 2009.

To everyone, here's what the poster wants to accomplish. Using the recordsetclone is the best way to do what he wants.

Dim rs As Object, intCapacityID as integer
Set rs = Me.RecordsetClone
intCapacityID=Me.CapacityID
Do While not rs.eof
if rs("CapacityID")>=intCapacityID then
.Edit
rs("YesNoCheck") = -1
.Update
.MoveNext
Loop
End With

Just my 2 cents.

Thanks for finding the reference, and yes, that looks right to me, too.

Just while I'm passing - thought I should mention that, since we're working with the RecordsetClone, you should really perform a MoveFirst before iterating through. There's no guarantee at all that it will be at the start of the recordset if it's an object you work with in general (or have simply run this procedure already).

Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then rs.MoveFirst 'new line
... 'as you were

FWIW if Chris were here he might point out (and I don't disagree) the unnecessary step of both assigning an object variable to the recordset and then using it in a With Block. The latter alone is sufficient.



Must dash.

Cheers!

I forgot that part, rs.MoveFirst. This is very important. Thanks Leigh Purvis.