Data Conversion Error

[padrepio]

Could someone please assist. Following code generates the run-time error '3421' Data Conversion Error at line: Set rst = qdf2.OpenRecordset("SELECT EmployeeName FROM EmployeeQuery WHERE EmployeeID=" & strWhere3).

EmployeeID is an autonumber PK, I've also tried declaring strwhere3 as an integer

Dim varItem As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strWhere3 As String
Dim v2 As Variant
Dim qdf2 As DAO.QueryDef
Dim prm2 As DAO.Parameter
     
Set dbs = CurrentDb()
Set qdf2 = dbs.QueryDefs("EmployeeQuery")
     
For Each prm2 In qdf2.Parameters
prm2.Value = Eval(prm2.Name)
Next prm2
     
     'Set rst = qdf2.OpenRecordset
 
For Each v2 In Me.ListFrom.ItemsSelected
       strWhere3 = Me.ListFrom.ItemData(v2)
       Set rst = qdf2.OpenRecordset("SELECT EmployeeName FROM EmployeeQuery WHERE EmployeeID=" & strWhere3)
 
rst.Edit
rst!StatusOut = -1
rst.Update
Next v2
    Me.ListFrom.Requery
 
 
Set rst = Nothing
Set dbs = Nothing

 

[The_Doc_Man]

Your item qdf2 isn't eligible to be the object against which you open a recordset. That could be the source of the data conversion error.

You should open that recordset from CurrentDB, not qdf2 - and you should open it outside the loop. Then inside the loop use .FindFirst "[EmployeeID]=" & <whatever> to select your desired record.

 

[June7]

Why are you even using QueryDefs? What is purpose of looping parameters?

If the recordset is retrieving only one record for one employee to update, then I expect opening inside the loop is appropriate. As Doc said, reference the database object, not the QueryDefs object. But why even use Recordset? An UPDATE SQL will accomplish this and eliminate all the recordset code.

dbs.Execute "UPDATE tablename SET StatusOut = True WHERE EmployeeID=" & Me.ListFrom.ItemData(v2)

I suspect StatusOut could be calculated when needed and therefore no need to save to table.

 

[MajP]

Your item qdf2 isn't eligible to be the object against which you open a recordset.

What? That is a wrong statement. Of course you can open a recordset from a querydef, currentdb, other recordset etc.
The problem is clearly
expression .OpenRecordset(Type, Options, LockEdit)
your first parameter is a type not a name. This is different from other openrecordset methods for other objects
expression .OpenRecordset(name,Type, Options, LockEdit)

 

[MajP]

The more I think of it, this is kind of BS. MS makes this look like a pseudo procedure overload where you call the same procedure with different parameters. But this is obviously two different procedures for two different classes with the same name. Kind of confusing, but makes sense. If you have a querdef why would you need to pass the name, it is already in the qdf.

 

[padrepio]

QueryDefs used as there were underlying parameters. June7's code failed to work without the QueryDefs code in place and then it dawned on me that I could use the SQL UPDATE code on a query without parameters. Works a treat!!!

I'm using recordset method to update another listbox but struggling with the SQL syntax (plus lack of sleep trying to sort all this). Also can't fathom out how to establish a WHERE clause as the 'EmployeeQuery' isn't related to the table I'm attempting to update (I know, I've got to sort)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("EmployeeQuery", dbOpenDynaset)
       For Each varitem In Me.ListFrom.ItemsSelected
rs.AddNew
rs!EmployeeName = Me.ListFrom.Column(1, varitem)
rs!EmployeeDept = Me.ListFrom.Column(2, varitem)
rs.Update
Next varitem

 

[June7]

Why UPDATE a query and not the table?

What do you mean by 'update another listbox' - listboxes are not updated, tables are updated.

Again, why use recordset instead of UPDATE? Have to do a search on the recordset to find relevant record or inside loop open recordset filtered to one record. What table to you want to UPDATE? If it's not EmployeeQuery then why do you open recordset on that dataset?

 

[padrepio]

Taken advice on board and used following, thanks for the prompts:

For Each v2 In Me.ListFrom.ItemsSelected

       sEmployeeName = Me.ListFrom.Column(2, v2)
       sDepartment = Me.ListFrom.Column(3, v2)

dbs.Execute "INSERT INTO EmployeeTbl (nameofemployee,department)VALUES ('" & sEmployeeName & "', '" & sDepartment & "')"
 
Next v2

 

[The_Doc_Man]

MajP -

What? That is a wrong statement. Of course you can open a recordset from a querydef, currentdb, other recordset etc.

The object to the left of the .Openrecordset needs to be a DATABASE object, not some other type of object. I may or may not have stated my comments awkwardly, but using a querydef as the left-hand object for a .OpenRecordset is not supposed to be legal. You cannot open a recordset against it. If I confused matters by saying it awkwardly, I'm sorry that sometimes I write according to my stream of unconsciousness, but I know what I was saying.

https://msdn.microsoft.com/en-us/library/office/ff820966.aspx

Since the OP has gotten guidance to solve the problem, GREAT! We can let this one go.

 

[Galaxiom]

The object to the left of the .Openrecordset needs to be a DATABASE object, not some other type of object. I may or may not have stated my comments awkwardly, but using a querydef as the left-hand object for a .OpenRecordset is not supposed to be legal. You cannot open a recordset against it.

Incorrect.

https://msdn.microsoft.com/en-us/library/office/ff822070.aspx

 

[MajP]

querydef.openrecordset
tabledef.openrecordset
recordset.openrecordset
database.openrecordset

These methods all work as I previously stated. I swear to God I do not make this stuff up. If I correct something, I usually know what I am talking about.

https://msdn.microsoft.com/en-us/library/office/ff836703.aspx
https://msdn.microsoft.com/en-us/library/office/ff196402.aspx

There is utility for all of these especially the Recordset.openrecordset where you can pass a reference to a recordset and modify its sort or filter property and and open a new recordset without having to have knowledge on the underlying sql.