Too few parameters problem

[Darrenc]

Hello to all you Access Experts, i'm having a little problem with some code that i'm trying to modify.
There's something wrong with the following code that i can't figure out.

    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Me.ToolingPartNumber.SetFocus
    strSQL = "SELECT Description FROM Pk_item WHERE PartNumber = " & ToolingPartNumber.Text & ";"
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    rst.MoveFirst
    
    Me.PartDescription.Enabled = True
    Me.PartDescription.SetFocus
    Me.PartDescription.Text = rst!Description

I've checked all the table and field names, and they are all correct, i've also made sure i've got all the correct references ticked.

The code falls over at

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

With the error "Too few parameters. Expected 1"

I'm sure there's a very simple solution.

Thanks for your help.

Darren.

 

[workmad3]

i expect there is... i also expect that it involves looking at the help for the openrecordset to see what parameters it needs

 

[Laoujin]

Try this:

InputBox "p", "t", strsql
(or debug.print strSQL)

This will allow you to see and copy the query. Once copied create a new query and paste the query for a more detailed error message.

Either "Description" is not a field in Pk_Item or PartNumber is a char and not an int field.

 

[Darrenc]

Thanks for the replys.

Laoujin you are right about PartNumber being a char and not an int. Guess the next question is what do i change in the code to get it to accept a char.

Thanks again for your time.

 

[workmad3]

try

strSQL = "SELECT Description FROM Pk_item WHERE PartNumber = '" & ToolingPartNumber.Text & "';"

to get it to accept a char

 

[Darrenc]

I've copied and pasted your suggestion workmad3, now i'm getting a 'type mismatch' error.

When looking at the debug, its still falling over at

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

I can see in the debug screen that the strSQL section is saying:

strSQL = "SELECT Description FROM Pk_item WHERE PartNumber = 'P74PCT521BSC';"

Its getting the part number ok, i guess the format is still wrong?

 

[KeithIT]

http://www.access-programmers.co.uk/forums/showthread.php?t=89505&highlight=openrecordset

 

[Darrenc]

I tried your suggestion Pat, but alas the problem switches to a 'type mismatch'.
This is starting to give me a major headache, i know the code works on another database we are using, and now i know the only difference between them is that on the working database the code is passing through an int, not a char.
I don't want to give up on it just yet, it would be a really useful piece of code to get working, Just FYI i've got the code linked through a click event, which is the same on the working database.

Private Sub Command41_Click()
    
    On Error GoTo Errorhandler
    
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Me.ToolingPartNumber.SetFocus
    strSQL = "SELECT PkDescription FROM Pk_item WHERE PartNumber = '" & ToolingPartNumber & "';"
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    rst.MoveFirst
    
    Me.PartDescription.Enabled = True
    Me.PartDescription.SetFocus
    Me.PartDescription.Text = rst!PkDescription
    
    
    Exit Sub
    
Error1Exit:
    rst.Close
    DoCmd.CancelEvent
    Exit Sub

Error2Exit:
    Me.Comments.SetFocus
    rst.Close
    DoCmd.CancelEvent
    Exit Sub

Error3Exit:
    DoCmd.CancelEvent
    Exit Sub

        
Errorhandler:
    
    Select Case Err.Number
        Case 3021
            MsgBox "Part Number does not exist", vbOKOnly, "Error"
            Resume Error1Exit
        Case 94
            MsgBox "Description does not exist", vbOKOnly, "Error"
            Resume Error2Exit
        Case 3075
            MsgBox "Part Number must be entered", vbOKOnly, "Error"
            Resume Error3Exit
        Case Else
            MsgBox "Error " & Err.Number & " - " & Err.Description, vbOKOnly, "Error"
            Resume Error3Exit
        End Select

End Sub

Thats the whole code & event, I’ve changed the field 'description' to 'Pkdescription' just rule out any naming conflicts.

I apreciate peoples help on this, i just wish i had better VB skills so i could understand everything the code is doing.

 

[Darrenc]

Solution!

I've had to return to this problem again, i needed to use the same code, and again i was getting the same problem.

Runtime error '13'

Type Mismatch

This time i stumbled upon the solution
I knew that all the data types matched, and i knew it wasn't some naming problem.
What fixed my problem was how i set my database and rescordset.
All i did was set the database and recordset a DAO.

    Dim dbs As [COLOR="Red"]DAO.[/COLOR]Database
    Dim rst As [COLOR="red"]DAO.[/COLOR]Recordset
    Dim strSQL As String

I won't pretend to fully understand why this has fixed my problem, maybe its because i've referenced DAO.Database in other modules?

I thought i'd use the same thread, hopefully this might help someone else.