Sql error (1 Viewer)

[shaz123]

Can anyone see what is wrong with this, stingy3 represents the cylinder number entered into the inputbox. The error message that keeps coming up is
'Too few parameters.Expected1.'

Any ideas

Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = stringy3")

 

[pdx_man]

So you have:

stingy3 = InputBox "Enter Cylinder Number"
Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = stringy3")

???

Try:
Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = " & CDec(stringy3))

Also, You posted this in the SQL Server forum. This should be in the Modules & VBA forum for MS Access.

 

[shaz123]

Thankyou for that, the error message does not appear anymore, But i cannot carry out the function that i was trying to do, maybe my coding is set out wrong. If you look at the coding below, if the cylinder number entered within the inputbox (stringy3) is the same as a cylinder number already in the table it should edit that record, stating that it has been returned and the date of return. However if it a cylinder that is not already in the table then it should just add a new record into the table, confirming the clyinder number and customer etc.

However when i run this code even though the cylinder number within the table matches the cylinder number inputted into Stringy3, it still adds a new record instead of updating the existing record in the table.

Can you see if i am missing anything


Code:

Private Sub Command22_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String
Dim String3 As Integer


Set db = CurrentDb

Stringy3 = InputBox("Are There Any Returns", "Please Scan/Enter Cylinder Serial Number")
Stringy4 = InputBox("Enter the date you wish to return the cylinder")

Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = " & CDec(Stringy3))

If Stringy3 = [Cylinder Number] Then
rs.Edit
rs![R Status] = Returned
Stringy4 = rs![Date Of R Status]

Else

rs.AddNew
rs![Cylinder Number] = Stringy3
rs!Status = "Returned"
rs!CustNo = Me!CustNo


rs.Update

End If

rs.Close
db.Close

 

[pdx_man]

Should this:

If Stringy3 = [Cylinder Number] Then

be

If Stringy3 = rs![Cylinder Number] Then


What datatype is [Cylinder Number]? If it is a text field, then:
Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = '" & Stringy3 & "'")

 

[shaz123]

Hi Thanxs for that, I have changed the coding as suggested, however even if the cylinder number within the table matches the inputed cylinder number (Stringy3) a new record is still added were as i wanted that cylinder record to edited. Also if the cylinder number does not match the inputed cylinder numver in (stringy3) then i revcieve this error meesage "runtime error '3021' no current record, were it is then supposed to add a new record to the table with the cylinder information. Any ideas!!!!!

 

[pdx_man]

What datatype is [Cylinder Number]?

Have you tried stepping through the code and checking the values?

Change to this:

SqlStr = "SELECT * From tbl_Delupdate Where [Cylinder Number] = '" & Stringy3 & "'"
Debug.Print SqlStr
Set rs = db.OpenRecordset(SqlStr)

And put a code break on the line where you set the recordset and press Ctrl-G to bring up the Debug window (Immediate). Copy this out to a new query and run it. Does it return records? What do you have to change to get it to return records. This has to be reflected when setting up SqlStr.

Also, change the

If Stringy3 = [Cylinder Number] Then

to

If rs.RecordCount > 0 Then