Type Mismatch error SQL query

[mchong71]

So I'm trying to copy databases over to another in order to revamp them. I'm having trouble with two different databases. Both are used to pull data from an existing table and the information is used to pull other data. You should note that the same code is used in the original database and is very much functional, it is only when I copy it over do I get this problem.

Here is the code from the first database:

This line "Set prm = dbs.QueryDefs("Qry - Inventory Balance").Parameters("prtNum")" finds a part number and updates the quantity of the parts available in a form, but instead gives me a type mismatch error

Private Sub getQtyAvailable()
'the following DAO code is necessary and should not be removed. If an upgrade takes place,
'it will not interfere and should still work... as long as the Microsoft DAO reference library 3.6 is linked.
Dim dbs As Database, rst As Recordset, prm As Parameter
Set dbs = CurrentDb
Set prm = dbs.QueryDefs("Qry - Inventory Balance").Parameters("prtNum")
On Error GoTo Error_NotInList
prm.Value = Part_Number.Value
Set rst = dbs.QueryDefs("Qry - Inventory Balance").OpenRecordset(dbOpenDynaset)
Text125.Value = rst.Fields(0)
Exit_Command296_Click:
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set prm = Nothing
Exit Sub
Err_Command296_Click:
Resume Exit_Command296_Click
Error_NotInList:
Text125.Value = 0

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set prm = Nothing
End Sub


Here is the code of the second database:

Pretty self explanatory....used in adding a user to the database, set rs=db.OpenRecordset(sql) yield the error.


sql = "SELECT username FROM Tbl_Login WHERE username = '" & Text3.Value & "'"
Set rs = db.OpenRecordset(sql)
If rs.EOF Then
If Option35.Value = -1 Then
MsgBox "Please note that Admin accounts cannot be deleted from the database for security reasons."
End If
Response = MsgBox("Are you sure you want to add this user to the database?", vbYesNo)
If Response = vbYes Then
sql = "INSERT INTO Tbl_Login (Username, Password, Email, Privileges) VALUES ('" & Text3.Value & "' , '" _
& Text31.Value & "' , '" & Text33.Value & "' , '" & privileges & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
MsgBox "User " & Text3.Value & " added to database"
Call Option20_Click


ANY IDEAS WOULD BE GREATLY APPRECIATED!

 

[boblarson]

You need to disambiguate your declaration:

rst As Recordset

should be

rst As DAO.Recordset

 

[mchong71]

You need to disambiguate your declaration:

rst As Recordset

should be

rst As DAO.Recordset

I did try this but I got the same error....any other ideas?

 

[mchong71]

I did try this but I got the same error....any other ideas?

So I stand corrected...you were right!!! Thank you so much. It was actually putting DAO.Parameter but none the less you are a God send!