help with coding

[sheela]

Hi:

I am trying to run the following code under a command
button click event:
But this code is always giving an error with "error number
3061: Too few parameters, expected 3".
The Sql statements syntax and the field names are correct.
I could run this query from query wizard with out any
errors.
I suspect, that I am missing something with openrecordset
property, but couldn't figure out.
Could some one please help me with this?
TIA,
Sheela

Private Sub cmdSaveClose_Click()
Dim k As Integer
Dim dbs As Database
Dim rstSQLpositionexists As Recordset


On Error GoTo Err_cmdSaveClose_Click
Set dbs = CurrentDb
Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=[Forms]![formname]!
[cmbFreezerName]) AND
...
and tablename.Position between [Forms]![formname]!
[txtStartPosition.Value And [Forms]![formname]!
[.txtEndPosition.Value")

If rstSQLpositionexists.RecordCount > 0 Then
MsgBox ("mesage..")

DoCmd.Close

End If

Statements.
.....

Exit_cmdSaveClose_Click:
Exit Sub
Err_cmdSaveClose_Click:

MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description

Resume Exit_cmdSaveClose_Click

DoCmd.Close

End Sub

 

[Dirk Goldgar]

Hi:

I am trying to run the following code under a command
button click event:
But this code is always giving an error with "error number
3061: Too few parameters, expected 3".
The Sql statements syntax and the field names are correct.
I could run this query from query wizard with out any
errors.
I suspect, that I am missing something with openrecordset
property, but couldn't figure out.
Could some one please help me with this?
TIA,
Sheela

Private Sub cmdSaveClose_Click()
Dim k As Integer
Dim dbs As Database
Dim rstSQLpositionexists As Recordset


On Error GoTo Err_cmdSaveClose_Click
Set dbs = CurrentDb
Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=[Forms]![formname]!
[cmbFreezerName]) AND
..
and tablename.Position between [Forms]![formname]!
[txtStartPosition.Value And [Forms]![formname]!
[.txtEndPosition.Value")

If rstSQLpositionexists.RecordCount > 0 Then
MsgBox ("mesage..")

DoCmd.Close

End If

Statements.
....

Exit_cmdSaveClose_Click:
Exit Sub
Err_cmdSaveClose_Click:

MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description

Resume Exit_cmdSaveClose_Click

DoCmd.Close

End Sub

Unlike opening a query in the Access user interface, DAO doesn't know
what those Forms!FormName!ControlName references are, so it interprets
them as parameters, which must have their values filled in before the
query can be run. Since you're specifying the SQL statement in code,
though, you can resolve them yourself and just build the values right
into the SQL string:

'---- start of example code -----
Dim strSQL As String

strSQL = _
"SELECT tablename.RecordID FROM tablename WHERE " & _
"(((tablename.FreezerName)=" & _
Chr(34) & [Forms]![formname]![cmbFreezerName] & Chr(34) & _
") AND " & _
...
"and tablename.Position between " & _
Chr(34) & [Forms]![formname]![txtStartPosition] & Chr(34) & _
" And " & _
Chr(34) & [Forms]![formname]![txtEndPosition] & Chr(34)

Set rstSQLpositionexists = dbs.OpenRecordset(strSQL)

'---- end of example code -----

Note that I've assumed for my example that the fields in question are
all text fields, and so I've enclosed the values in Chr(34)'s -- that's
the double-quote character ("). This would be different if the fields
were numbers or dates.

 

[Gerald Stanley]

You have included the names of your controls within the SQL
string with the result that they are being taken literally.

You need to compose your SQL along these lines

"SELECT tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)= '" & [Forms]![formname]!
[cmbFreezerName]) & "'AND etc

Note that I have enclosed the freezername in '' as I have
assumed that it is a text.

Hope This Helps
Gerald Stanley MCSD

 

[Marshall Barton]

I am trying to run the following code under a command
button click event:
But this code is always giving an error with "error number
3061: Too few parameters, expected 3".
The Sql statements syntax and the field names are correct.
I could run this query from query wizard with out any
errors.
I suspect, that I am missing something with openrecordset
property, but couldn't figure out. []
Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=[Forms]![formname]!
[cmbFreezerName]) AND

[]

In VBA you need to use the value of the combo box, not a
reference to it. If FreezerName is a Text field then the
value must be enclosed in quotes:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=""" & [Forms]![formname]!
[cmbFreezerName] &""") AND . . .

If FreezerName is a numeric type field, then use:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=" & [Forms]![formname]!
[cmbFreezerName] &") AND . . .

 

[sheela]

Thank you so much for your help. When i implement the
quotes, my program works. But now I am getting the
following error. Could you pls. help me with this error?

Error number 2467, the expression you entered refers to an
object that is closed or doesn't exist

TIA,
Sheela

-----Original Message-----

I am trying to run the following code under a command
button click event:
But this code is always giving an error with "error number
3061: Too few parameters, expected 3".
The Sql statements syntax and the field names are correct.
I could run this query from query wizard with out any
errors.
I suspect, that I am missing something with openrecordset
property, but couldn't figure out. []
Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=[Forms]![formname]!
[cmbFreezerName]) AND

[]

In VBA you need to use the value of the combo box, not a
reference to it. If FreezerName is a Text field then the
value must be enclosed in quotes:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=""" & [Forms]![formname]!
[cmbFreezerName] &""") AND . . .

If FreezerName is a numeric type field, then use:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=" & [Forms]![formname]!
[cmbFreezerName] &") AND . . .

 

[PC Datasheet]

Your form containing cmbFreezerName must be open or not visible when you open
the recordset!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thank you so much for your help. When i implement the
quotes, my program works. But now I am getting the
following error. Could you pls. help me with this error?

Error number 2467, the expression you entered refers to an
object that is closed or doesn't exist

TIA,
Sheela

-----Original Message-----

I am trying to run the following code under a command
button click event:
But this code is always giving an error with "error number
3061: Too few parameters, expected 3".
The Sql statements syntax and the field names are correct.
I could run this query from query wizard with out any
errors.
I suspect, that I am missing something with openrecordset
property, but couldn't figure out. []
Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=[Forms]![formname]!
[cmbFreezerName]) AND

[]

In VBA you need to use the value of the combo box, not a
reference to it. If FreezerName is a Text field then the
value must be enclosed in quotes:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=""" & [Forms]![formname]!
[cmbFreezerName] &""") AND . . .

If FreezerName is a numeric type field, then use:

Set rstSQLpositionexists = dbs.OpenRecordset("SELECT
tablename.RecordID FROM tablename WHERE
(((tablename.FreezerName)=" & [Forms]![formname]!
[cmbFreezerName] &") AND . . .