OpenRecordset

J

Jason

I'm trying to use the OpenRecordset method to autofill
some fields in a form. Below is the code I have. When I
open the form, I get the error

"Run-time error '3061':
Too few parameters. Expected 1."

Any idea what I'm doing wrong?

Thanks in advance for your help!

Jason

Private Sub Form_Current()

'If there is no entry in the e-mail field then fill it in
from previous entry
If IsNull(Me.E_mail) And IsNull(Me.Phone) And IsNull
(Me.Contact) Then
Dim rst As Recordset, dbs As Database, strSQL As
String
Set dbs = CurrentDb()
strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=[Forms]!
[1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.Contact
rstOutput.Field(1) = Me.Phone
rstOutput.Field(2) = Me.E_mail
'If there is no previous e-mail entered then look in
tblVendorsToReport
rst.MoveLast
Dim intCount As Integer
intCount = rst.RecordCount
If intCount < 1 Then
strSQL = "SELECT
tblVendorsToReport.SupplierEmail " & _
"FROM tblVendorsToReport INNER JOIN
tblNCMT ON tblVendorsToReport.VendNum =
tblNCMT.intVendorNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]!
[intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.E_mail
End If
rst.Close
Set dbs = Nothing
End If
End Sub
 
D

Douglas J. Steele

Try changing your SQL to:

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber] & "));"

assuming invVendorNumber is numeric, or

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & Chr$(34) & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber] & _
Chr$(34) & "));"

if it's text. (In other words, put the value of the control on the form into
your SQL, rather than a reference to the control)
 
J

Jason

Doug,

Thanks for your help. I'm getting close now. I changed
the SQL string as you indicated (for text) but now I'm
getting a type mismatch error when it gets to the
dbs.OpenRecordset (strSQL) line. I pasted the SQL to a
stand alone query and didn't have any probelms. Any more
ideas?

Thanks again,

Jason

strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= " & _
Chr$(34) & [Forms]![1-1frmCAR]![frmNCMTsub].
[Form]![intVendorNumber] & Chr$(34) & _
"));"
Set rst = dbs.OpenRecordset(strSQL)

-----Original Message-----
Try changing your SQL to:

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & "));"

assuming invVendorNumber is numeric, or

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & Chr$(34) & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & _
Chr$(34) & "));"

if it's text. (In other words, put the value of the control on the form into
your SQL, rather than a reference to the control)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I'm trying to use the OpenRecordset method to autofill
some fields in a form. Below is the code I have. When I
open the form, I get the error

"Run-time error '3061':
Too few parameters. Expected 1."

Any idea what I'm doing wrong?

Thanks in advance for your help!

Jason

Private Sub Form_Current()

'If there is no entry in the e-mail field then fill it in
from previous entry
If IsNull(Me.E_mail) And IsNull(Me.Phone) And IsNull
(Me.Contact) Then
Dim rst As Recordset, dbs As Database, strSQL As
String
Set dbs = CurrentDb()
strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= [Forms]!
[1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.Contact
rstOutput.Field(1) = Me.Phone
rstOutput.Field(2) = Me.E_mail
'If there is no previous e-mail entered then look in
tblVendorsToReport
rst.MoveLast
Dim intCount As Integer
intCount = rst.RecordCount
If intCount < 1 Then
strSQL = "SELECT
tblVendorsToReport.SupplierEmail " & _
"FROM tblVendorsToReport INNER JOIN
tblNCMT ON tblVendorsToReport.VendNum =
tblNCMT.intVendorNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]!
[intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.E_mail
End If
rst.Close
Set dbs = Nothing
End If
End Sub
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

Let me guess (since you don't say). You're using Access 2000 (or 2002), you
went into Tools | References and added a reference to DAO 3.6, but you
didn't remove the reference to ADO 2.x while you were there. The problem
you're running into is that both the ADO and DAO models have a recordset
object in them. Since ADO is higher in the list of references, rst is being
declared as an ADO recordset, but you need a DAO recordset.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations to avoid this problem with objects with
the same names existing in the 2 models. To ensure that you get a DAO
recordset, you'll need to use Dim rst as DAO.Recordset (to guarantee an ADO
recordset, you'd use Dim rst As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jason said:
Doug,

Thanks for your help. I'm getting close now. I changed
the SQL string as you indicated (for text) but now I'm
getting a type mismatch error when it gets to the
dbs.OpenRecordset (strSQL) line. I pasted the SQL to a
stand alone query and didn't have any probelms. Any more
ideas?

Thanks again,

Jason

strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= " & _
Chr$(34) & [Forms]![1-1frmCAR]![frmNCMTsub].
[Form]![intVendorNumber] & Chr$(34) & _
"));"
Set rst = dbs.OpenRecordset(strSQL)

-----Original Message-----
Try changing your SQL to:

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & "));"

assuming invVendorNumber is numeric, or

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & Chr$(34) & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & _
Chr$(34) & "));"

if it's text. (In other words, put the value of the control on the form into
your SQL, rather than a reference to the control)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I'm trying to use the OpenRecordset method to autofill
some fields in a form. Below is the code I have. When I
open the form, I get the error

"Run-time error '3061':
Too few parameters. Expected 1."

Any idea what I'm doing wrong?

Thanks in advance for your help!

Jason

Private Sub Form_Current()

'If there is no entry in the e-mail field then fill it in
from previous entry
If IsNull(Me.E_mail) And IsNull(Me.Phone) And IsNull
(Me.Contact) Then
Dim rst As Recordset, dbs As Database, strSQL As
String
Set dbs = CurrentDb()
strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= [Forms]!
[1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.Contact
rstOutput.Field(1) = Me.Phone
rstOutput.Field(2) = Me.E_mail
'If there is no previous e-mail entered then look in
tblVendorsToReport
rst.MoveLast
Dim intCount As Integer
intCount = rst.RecordCount
If intCount < 1 Then
strSQL = "SELECT
tblVendorsToReport.SupplierEmail " & _
"FROM tblVendorsToReport INNER JOIN
tblNCMT ON tblVendorsToReport.VendNum =
tblNCMT.intVendorNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]!
[intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.E_mail
End If
rst.Close
Set dbs = Nothing
End If
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
M

Max Smart

If tblNCMT.intVendorNumber is an integer field, you should not use
quotes around the variable in your WHERE string. If it's a text field,
you use single quotes ('this' not "this") and convert the variable to
string.

Max


Jason said:
Doug,

Thanks for your help. I'm getting close now. I changed
the SQL string as you indicated (for text) but now I'm
getting a type mismatch error when it gets to the
dbs.OpenRecordset (strSQL) line. I pasted the SQL to a
stand alone query and didn't have any probelms. Any more
ideas?

Thanks again,

Jason

strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= " & _
Chr$(34) & [Forms]![1-1frmCAR]![frmNCMTsub].
[Form]![intVendorNumber] & Chr$(34) & _
"));"
Set rst = dbs.OpenRecordset(strSQL)

-----Original Message-----
Try changing your SQL to:

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & "));"

assuming invVendorNumber is numeric, or

strSQL = "SELECT [1-1tblCAR].Contact, " & _
"[1- 1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON " & _
"[1- 1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=" & Chr$(34) & _
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]! [intVendorNumber] & _
Chr$(34) & "));"

if it's text. (In other words, put the value of the control on the form into
your SQL, rather than a reference to the control)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I'm trying to use the OpenRecordset method to autofill
some fields in a form. Below is the code I have. When I
open the form, I get the error

"Run-time error '3061':
Too few parameters. Expected 1."

Any idea what I'm doing wrong?

Thanks in advance for your help!

Jason

Private Sub Form_Current()

'If there is no entry in the e-mail field then fill it in
from previous entry
If IsNull(Me.E_mail) And IsNull(Me.Phone) And IsNull
(Me.Contact) Then
Dim rst As Recordset, dbs As Database, strSQL As
String
Set dbs = CurrentDb()
strSQL = "SELECT [1-1tblCAR].Contact, [1-
1tblCAR].Phone, [1-1tblCAR].[E-mail] " & _
"FROM [1-1tblCAR] INNER JOIN tblNCMT ON [1-
1tblCAR].NCMTNumber = tblNCMT.intNCMTNumber " & _
"WHERE (((tblNCMT.intVendorNumber)= [Forms]!
[1-1frmCAR]![frmNCMTsub].[Form]![intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.Contact
rstOutput.Field(1) = Me.Phone
rstOutput.Field(2) = Me.E_mail
'If there is no previous e-mail entered then look in
tblVendorsToReport
rst.MoveLast
Dim intCount As Integer
intCount = rst.RecordCount
If intCount < 1 Then
strSQL = "SELECT
tblVendorsToReport.SupplierEmail " & _
"FROM tblVendorsToReport INNER JOIN
tblNCMT ON tblVendorsToReport.VendNum =
tblNCMT.intVendorNumber " & _
"WHERE (((tblNCMT.intVendorNumber)=
[Forms]![1-1frmCAR]![frmNCMTsub].[Form]!
[intVendorNumber]));"
Set rst = dbs.OpenRecordset(strSQL)
rstOutput.Field(0) = Me.E_mail
End If
rst.Close
Set dbs = Nothing
End If
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DoCmd.OpenForm with a WHERE clause as SQL? 5
Too Few Parameters, 1 expected 3
Appending problem after splitting field 2
DAO Openrecordset Error 3
File in use error 7
Form Navigation 5
passing an array to another subroutine 2

Top