Recordset code problem

[PayeDoc]

Hello All

This is my first forray into this type of code! I have a pretty long
function in which I need to use certain values from table [tax tables] - but
only the values on the record that meets a particular criterion. I want to
do this by defining all the fields from [tax tables] as a recordset, and
this is my attempt:

Dim rsTax As Recordset
Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" & _
"FROM [tax tables] INNER JOIN months ON [tax tables].set = months.[default
tax set]" & _
"WHERE (((months.[month name])=[Forms]![frm x main]![month name]));")

I got the code by creating a corresponding query then copy/pasting the sql,
and adding the & _ line breaks.
But it's wrong: when I try to run this I get a "too few parameters" error.

Hope someone can help.
May thanks
Leslie Isaacs

 

[Stefan Hoffmann]

hi Leslie,

I got the code by creating a corresponding query then copy/pasting the sql,
and adding the& _ line breaks.
But it's wrong: when I try to run this I get a "too few parameters" error.

There were spaces missing. Use this idiom and use table aliases:

Dim rsTax As DAO.Recordset
Dim sql As String

sql = "SELECT T.*, M.[month name] " & _
"FROM [tax tables] T " & _
"INNER JOIN months M " & _
"ON T.set = M.[default tax set] " & _
"WHERE M.[month name] = [Forms]![frm x main]![month name];"

Set rsTax = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)

If you still get an error, set a break point in the Set rsTax line and
use type "? sql" into the immediate window and press enter.


mfG
--> stefan <--

 

[Daryl S]

PayeDoc -
First, add spaces before the FROM and WHERE, as they will otherwise be
concatenated right onto the prior text.
Then, to get the data from a form, it has to be evaluated outside of the
double-quotes. I assume [Month name] is a text field, so the value will need
single-quote delimeters also. Try this:

Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" & _
" FROM [tax tables] INNER JOIN months ON [tax tables].set = months.[default
tax set]" & _
" WHERE (((months.[month name])='" & [Forms]![frm x main]![month name] &
"'));")

 

[Leslie Isaacs]

Hello Stefan

Many thanks for your reply.
Adding the spaces, and the extra delimeters for the form reference (as
suggested by Daryl), did the trick!!
I didn't use the table aliases, mainly because I didn't really know why this
would be a good idea - just my ignorance.

Many thanks again for your help.
Les

hi Leslie,

I got the code by creating a corresponding query then copy/pasting the
sql,
and adding the& _ line breaks.
But it's wrong: when I try to run this I get a "too few parameters"
error.

There were spaces missing. Use this idiom and use table aliases:

Dim rsTax As DAO.Recordset
Dim sql As String

sql = "SELECT T.*, M.[month name] " & _
"FROM [tax tables] T " & _
"INNER JOIN months M " & _
"ON T.set = M.[default tax set] " & _
"WHERE M.[month name] = [Forms]![frm x main]![month name];"

Set rsTax = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)

If you still get an error, set a break point in the Set rsTax line and use
type "? sql" into the immediate window and press enter.


mfG
--> stefan <--

 

[Leslie Isaacs]

Daryl
Perfect - very many thanks - and I even understand it, so can (hopefully)
use this next time!!
Thanks again
Les

PayeDoc -
First, add spaces before the FROM and WHERE, as they will otherwise be
concatenated right onto the prior text.
Then, to get the data from a form, it has to be evaluated outside of the
double-quotes. I assume [Month name] is a text field, so the value will
need
single-quote delimeters also. Try this:

Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" & _
" FROM [tax tables] INNER JOIN months ON [tax tables].set =
months.[default
tax set]" & _
" WHERE (((months.[month name])='" & [Forms]![frm x main]![month name] &
"'));")

--
Daryl S

Hello All

This is my first forray into this type of code! I have a pretty long
function in which I need to use certain values from table [tax tables] -
but
only the values on the record that meets a particular criterion. I want
to
do this by defining all the fields from [tax tables] as a recordset, and
this is my attempt:

Dim rsTax As Recordset
Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" & _
"FROM [tax tables] INNER JOIN months ON [tax tables].set =
months.[default
tax set]" & _
"WHERE (((months.[month name])=[Forms]![frm x main]![month name]));")

I got the code by creating a corresponding query then copy/pasting the
sql,
and adding the & _ line breaks.
But it's wrong: when I try to run this I get a "too few parameters"
error.

Hope someone can help.
May thanks
Leslie Isaacs





.

 

[Leslie Isaacs]

Hello "NuBie"

Thanks for your reply.
The problem was to do with extra spaces, and delimeters, which Daryl and
Stefan have sorted out for me.
Thanks anyway
Les

see if this helps.

str = "this is a" _
& " continuation of the first line"



Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" _
& "FROM [tax tables] INNER JOIN months ON [tax tables].set =
months.[default
tax set]" _
& "WHERE (((months.[month name])=[Forms]![frm x main]![month name]));")

Hello All

This is my first forray into this type of code! I have a pretty long
function in which I need to use certain values from table [tax tables] -
but
only the values on the record that meets a particular criterion. I want to
do this by defining all the fields from [tax tables] as a recordset, and
this is my attempt:

Dim rsTax As Recordset
Set rsTax = CurrentDb.OpenRecordset("SELECT [tax tables].*, months.[month
name]" & _
"FROM [tax tables] INNER JOIN months ON [tax tables].set = months.[default
tax set]" & _
"WHERE (((months.[month name])=[Forms]![frm x main]![month name]));")

I got the code by creating a corresponding query then copy/pasting the
sql,
and adding the & _ line breaks.
But it's wrong: when I try to run this I get a "too few parameters" error.

Hope someone can help.
May thanks
Leslie Isaacs