New To Recordsets

D

darb

Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo box on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)=[Forms]!
[frmFdrAgreements]![FeederProducerName]));
 
G

Gary Miller

You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, " _ &
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
S

SteveS

Two errors...??

In Gary's code, a stray closing parentheses snuck (sneeked?) in that
should be deleted.
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
Click to expand...
^

In the original code posted, the line

was replaced by Gary's line
Set rst = dbs.OpenRecordset(strSQL)
Click to expand...

so the line
TheProducerID = [qryFilter1ProducerName]![FeederProducerID]
Click to expand...

needs to be changed to
TheProducerID = rst![FeederProducerID]
Click to expand...

or maybe
TheProducerID = rst.[FeederProducerID]
Click to expand...

(I get confused with the dot/bang usage..)

since [qryFilter1ProducerName] was replaced by strSQL.


There must be more to the code???? All the loop does is set a memory
variable to a value from a field in recordset 'rst'; recordset 'rst1'
isn't used at all.
It's easier to understand and help if the full sub is posted.


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Gary said:
You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, " _ &
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo box on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)=[Forms]!
[frmFdrAgreements]![FeederProducerName]));
Click to expand...
Click to expand...
 
M

Martin Seelhofer

Hey there
When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo box on
the form that this code runs from.
Click to expand...

You might want to access the QueryDef-object and its Parameters-
collection instead. E.g.:

Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryFilter1ProducerName")
qdf.Parameters(0).Value = ... ' add the vba statement to access the combobox
here
Set rst = qdf.OpenRecordset()

....

NB: You can even access the original link string of the parameter.
qdf.Parameters(0).Name will deliver that to you. This way, you
can easily work with more than just a single parameter :)


Cheers,

Martin
 
G

Gary Miller

Shucks, I stripped quite a few of them out. How did I miss
that ? Can I teach Outlook Express to compile VBA when I
type these things??? :))

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
SteveS said:
Two errors...??

In Gary's code, a stray closing parentheses snuck (sneeked?) in that
should be deleted.
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
Click to expand...
^

In the original code posted, the line

was replaced by Gary's line
Set rst = dbs.OpenRecordset(strSQL)
Click to expand...

so the line
TheProducerID =
Click to expand...
[qryFilter1ProducerName]![FeederProducerID]

needs to be changed to
TheProducerID = rst![FeederProducerID]
Click to expand...

or maybe
TheProducerID = rst.[FeederProducerID]
Click to expand...

(I get confused with the dot/bang usage..)

since [qryFilter1ProducerName] was replaced by strSQL.


There must be more to the code???? All the loop does is set a memory
variable to a value from a field in recordset 'rst'; recordset 'rst1'
isn't used at all.
It's easier to understand and help if the full sub is posted.


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Gary said:
You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, " _ &
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo box on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)=[Forms]!
[frmFdrAgreements]![FeederProducerName]));
Click to expand...
Click to expand...
Click to expand...
 
D

Darb

Thanks for the help guys.

Just a few more questions...

on the sql statement you
have "tblFeederProducer.FeederProducerName " _ &

shouldn't it be
"tblFeederProducer.FeederProducerName " & _ ?? The _ &
give me an invalid character error.

Sorry for not sending the complete code. I was just to
the point where I was trying to see if I could get a
value passed to a variable.

What I truly want to do is find all the records related
to the value in the combo box, get the record id's and
filter another table on all the record id's.

So if I look for FeederProducerName = "X" and producer X
has 3 records found in the query with record id's of 4, 5
and 6, I want to filter tblData for 4, 5 and 6. This
event is on the after update of the combo box.



Thanks again,

Darb
-----Original Message-----
You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, " _ &
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
darb said:
Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo box on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)=[Forms]!
[frmFdrAgreements]![FeederProducerName]));
Click to expand...


.
Click to expand...
 
G

Guest

I've just tried the code you sent. I still get the same
error... any ideas?

Darb

I really appreciate your time on this.
-----Original Message-----
Thanks for the help guys.

Just a few more questions...

on the sql statement you
have "tblFeederProducer.FeederProducerName " _ &

shouldn't it be
"tblFeederProducer.FeederProducerName " & _ ?? The _ &
give me an invalid character error.

Sorry for not sending the complete code. I was just to
the point where I was trying to see if I could get a
value passed to a variable.

What I truly want to do is find all the records related
to the value in the combo box, get the record id's and
filter another table on all the record id's.

So if I look for FeederProducerName = "X" and producer X
has 3 records found in the query with record id's of 4, 5
and 6, I want to filter tblData for 4, 5 and 6. This
event is on the after update of the combo box.



Thanks again,

Darb
-----Original Message-----
You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, "
Click to expand...
_
&
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
darb said:
Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo
Click to expand...
Click to expand...
box
on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)= [Forms]!
[frmFdrAgreements]![FeederProducerName]));
Click to expand...


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

Gary Miller

Post back and copy your latest exact code so that we can
eyeball it.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
I've just tried the code you sent. I still get the same
error... any ideas?

Darb

I really appreciate your time on this.
-----Original Message-----
Thanks for the help guys.

Just a few more questions...

on the sql statement you
have "tblFeederProducer.FeederProducerName " _ &

shouldn't it be
"tblFeederProducer.FeederProducerName " & _ ?? The _ &
give me an invalid character error.

Sorry for not sending the complete code. I was just to
the point where I was trying to see if I could get a
value passed to a variable.

What I truly want to do is find all the records related
to the value in the combo box, get the record id's and
filter another table on all the record id's.

So if I look for FeederProducerName = "X" and producer X
has 3 records found in the query with record id's of 4, 5
and 6, I want to filter tblData for 4, 5 and 6. This
event is on the after update of the combo box.



Thanks again,

Darb
-----Original Message-----
You may have a couple of problems, but you are just getting
stopped on the first one. If you have a parameter query that
references form variables it is best to bring the query SQL
directly into the code module and pass the parameters that
way. The version I wll give you concantenates the value from
the form directly into the SQL string.

The other thing I am concerned about is that after you
assign rst to the parameter query, your next line assigns it
to a table which I think that you meant to assign to 'rst1'.
Here is a revision which will allow you to open both
recordsets, but you will need to sort out what you want to
do between the two which isn't clear below. Also, Is your
ProducerID really a String or is it a Long Integer Number
which is more common for an ID?...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset
Dim strSQL as String

strSQL = "SELECT tblFeederProducer.FeederProducerID, "
Click to expand...
_
&
"tblFeederProducer.FeederProducerName " _ &
"FROM tblFeederProducer " _ &
"WHERE tblFeederProducer.FeederProducerName) = '" _ &
[Forms]![frmFdrAgreements]![FeederProducerName] & "';"



Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Set rst1 = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

' ** Beware, when you loop here you will lose the ProducerID
that you just picked up
' so something probably needs to be done with it before the
loop
Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set dbs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message Thanks for taking the time to read my question.

I am trying to create a recordset from a query. Here is
my code...

Dim dbs As Database, rst As Recordset
Dim TheProducerID As String
Dim rst1 As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryFilter1ProducerName")
Set rst = dbs.OpenRecordset("tblData")


rst.MoveFirst
Do Until rst.EOF
TheProducerID = [qryFilter1ProducerName]!
[FeederProducerID]

Loop
rst.Close
Set dbs = Nothing


When the OpenRecordset("qryFilter1ProducerName") line is
reached, I get an error that says "Too few parameters.
Expected 1." What does this mean, and how do I fix it?

In my query, I have the criteria linked to a combo
Click to expand...
box
on
the form that this code runs from.

thanks again for the help.

Darb

SQL of my query:

SELECT tblFeederProducer.FeederProducerID,
tblFeederProducer.FeederProducerName
FROM tblFeederProducer
WHERE (((tblFeederProducer.FeederProducerName)= [Forms]!
[frmFdrAgreements]![FeederProducerName]));




.
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

Message from Access 2007 on sub function 3
combine code for one button 7
qty in table1 minus qty in table2 equal to result column in table2 2
Function not working 3
Use a string to define a field 1
Run Query on Subform Using VBA Macro Button 1
Do I need a Nested Loop? 14
Array Compartments 3

Top