assigning fields collection to variables

M

Mark Kubicki

I'm trying to create a set of variable of all the fields in a table
[tbeFixtureSchedulePrintoptions].
This information will later be used in formatting the detail section of the
report without having to look-up the values in the
[tbeFixtureSchedulePrintoptions] at each record


I'm getting an "object not supported" error; so... this will be a new lesson
for me; could someone please direct me on what that would be and how to
achieve what I am trying to do (please)

many thanks in advance,
mark

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vField As Field
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("tbeProjectInfo", dbOpenSnapshot)
Set rst2 = dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)

For Each vField In rst2
vName = "v" & vField.name
Dim vName As Variant
vName = vField.Value
Next vField

End Sub
 
M

Marshall Barton

Mark said:
I'm trying to create a set of variable of all the fields in a table
[tbeFixtureSchedulePrintoptions].
This information will later be used in formatting the detail section of the
report without having to look-up the values in the
[tbeFixtureSchedulePrintoptions] at each record


I'm getting an "object not supported" error; so... this will be a new lesson
for me; could someone please direct me on what that would be and how to
achieve what I am trying to do (please)

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vField As Field
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("tbeProjectInfo", dbOpenSnapshot)
Set rst2 = dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)

For Each vField In rst2
vName = "v" & vField.name
Dim vName As Variant
vName = vField.Value
Next vField

End Sub
Click to expand...
You should use
Dim vField As DAO.Field

And the For loop needs to use a different variable for the
field's value.

Why do you open 2 recordsets and then only use one of them?

Beyond that, I'm not clear what you are trying to accomplish
with this code. It kind of looks like you should be using a
collection (or maybe an array) to keep the list of
names/values, but I have to wonder why you don't use the
table in the report's record source query???
 
M

Mark Kubicki

thanks for the reply

to your questions:
- I actually do use both of the recordsets.... but edited the code for
clarity, and forgot to remove the reference in the DIM and SET statements

- what I am trying to accomplish (in greater detail)?
....the table [tbeFixtureSchedulePrintOptions] is populated with report
options that the user picks (print this, not that... include this, not
that... add this line of text only if something is true...) When the form
opens, my intention was to load all of those choices into variables (rather
than having to look them up at each record), then include the variables in
code which generates text that will be assigned the detail fields of the
report. (the concatenations do get complicated and tricky, so a simple SQL
became more than cumbersome; I elected to write code...)

- why don't I use a collection (or maybe an array), or include the table in
the report's record source query???
...because I'm a totally self-taught newbie and I either don't know that I
should, or know how to, or am just plain intimidated by that level of
knowledge (I admit to having learned a lot, but also learning in little tiny
baby steps)

mark



Marshall Barton said:
Mark said:
I'm trying to create a set of variable of all the fields in a table
[tbeFixtureSchedulePrintoptions].
This information will later be used in formatting the detail section of
the
report without having to look-up the values in the
[tbeFixtureSchedulePrintoptions] at each record


I'm getting an "object not supported" error; so... this will be a new
lesson
for me; could someone please direct me on what that would be and how to
achieve what I am trying to do (please)

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vField As Field
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("tbeProjectInfo", dbOpenSnapshot)
Set rst2 = dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)

For Each vField In rst2
vName = "v" & vField.name
Dim vName As Variant
vName = vField.Value
Next vField

End Sub
Click to expand...
You should use
Dim vField As DAO.Field

And the For loop needs to use a different variable for the
field's value.

Why do you open 2 recordsets and then only use one of them?

Beyond that, I'm not clear what you are trying to accomplish
with this code. It kind of looks like you should be using a
collection (or maybe an array) to keep the list of
names/values, but I have to wonder why you don't use the
table in the report's record source query???
Click to expand...
 
M

Marshall Barton

Mark said:
to your questions:
- I actually do use both of the recordsets.... but edited the code for
clarity, and forgot to remove the reference in the DIM and SET statements

- what I am trying to accomplish (in greater detail)?
...the table [tbeFixtureSchedulePrintOptions] is populated with report
options that the user picks (print this, not that... include this, not
that... add this line of text only if something is true...) When the form
opens, my intention was to load all of those choices into variables (rather
than having to look them up at each record), then include the variables in
code which generates text that will be assigned the detail fields of the
report. (the concatenations do get complicated and tricky, so a simple SQL
became more than cumbersome; I elected to write code...)

- why don't I use a collection (or maybe an array), or include the table in
the report's record source query???
...because I'm a totally self-taught newbie and I either don't know that I
should, or know how to, or am just plain intimidated by that level of
knowledge (I admit to having learned a lot, but also learning in little tiny
baby steps)
Click to expand...


Sounds like a very complex report to me so you can't be that
much of a newby ;-)

As long as you only have an identifier and a value, I think
a custom collection may be the way to go. The code could
look something like this air code:

Private ReportParms As New Collection

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vField As DAO.Field
Set dbs = CurrentDb
Set rst2 =
dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)

For Each vField In rst2
ReportParms.Add vField.Value, "v" & vField.name
Next vField

Then you can get the value by referring to the collection
entry with this kind of syntax:
ReportParms!vsomefieldname
or
ReportParms("v" & something)
 
M

Mark Kubicki

So, I've entered this code (which ought tobe the same as you indicated
below... and am getting this error...
'3251'
Operation is not supported by this type of object

Any hints as to where i shouldbe looking?
Again, many thanks,
Mark

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst2 As DAO.Recordset
Dim vField As DAO.Field
Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)
For Each vField In rst2 ' <---- ERROR OCCURS AT THIS LINE
ReportParms.Add vField.Value, "v" & vField.name
Next vField
End Sub

Marshall Barton said:
Mark said:
to your questions:
- I actually do use both of the recordsets.... but edited the code for
clarity, and forgot to remove the reference in the DIM and SET statements

- what I am trying to accomplish (in greater detail)?
...the table [tbeFixtureSchedulePrintOptions] is populated with report
options that the user picks (print this, not that... include this, not
that... add this line of text only if something is true...) When the form
opens, my intention was to load all of those choices into variables
(rather
than having to look them up at each record), then include the variables in
code which generates text that will be assigned the detail fields of the
report. (the concatenations do get complicated and tricky, so a simple
SQL
became more than cumbersome; I elected to write code...)

- why don't I use a collection (or maybe an array), or include the table
in
the report's record source query???
...because I'm a totally self-taught newbie and I either don't know that
I
should, or know how to, or am just plain intimidated by that level of
knowledge (I admit to having learned a lot, but also learning in little
tiny
baby steps)
Click to expand...


Sounds like a very complex report to me so you can't be that
much of a newby ;-)

As long as you only have an identifier and a value, I think
a custom collection may be the way to go. The code could
look something like this air code:

Private ReportParms As New Collection

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vField As DAO.Field
Set dbs = CurrentDb
Set rst2 =
dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)

For Each vField In rst2
ReportParms.Add vField.Value, "v" & vField.name
Next vField

Then you can get the value by referring to the collection
entry with this kind of syntax:
ReportParms!vsomefieldname
or
ReportParms("v" & something)
Click to expand...
 
M

Marshall Barton

Mark said:
So, I've entered this code (which ought tobe the same as you indicated
below... and am getting this error...
'3251'
Operation is not supported by this type of object

Any hints as to where i shouldbe looking?
Again, many thanks,
Mark

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database
Dim rst2 As DAO.Recordset
Dim vField As DAO.Field
Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("tbeFixtureSchedulePrintoptions",
dbOpenSnapshot)
For Each vField In rst2 ' <---- ERROR OCCURS AT THIS LINE
ReportParms.Add vField.Value, "v" & vField.name
Next vField
End Sub
Click to expand...


My bad, that was supposed to be:

For Each vField In rst2.Fields
 
M

Mark Kubicki

I was about to say: "
THANKS ...perfect (and now I'm on my way to learning about "custom
collections" (one step at at time!)

but unfortunately, when I adjust the code...
at the next line (ReportParms.Add vField.Value, "v" & vField.name), I get
the error: 424 "object required"

hovering over vField indicatesa value of 6; and over vField.name a value of
"PresetOption" -both correct

-mark
 
M

Marshall Barton

Mark said:
I was about to say: "
THANKS ...perfect (and now I'm on my way to learning about "custom
collections" (one step at at time!)

but unfortunately, when I adjust the code...
at the next line (ReportParms.Add vField.Value, "v" & vField.name), I get
the error: 424 "object required"

hovering over vField indicatesa value of 6; and over vField.name a value of
"PresetOption" -both correct
Click to expand...


It sounds like you forgot the line:

Private ReportParms As New Collection

at the top of the module, before any procedures.
 

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

VBA for report detail data record source 4
qty in table1 minus qty in table2 equal to result column in table2 2
simple question: to update the record in a table that is not the current datasource... 1
Printing Individual PDF Reports 2
Canceling an OnClick Event 3
Second Recordset? 13
Access records from table 2
Referencing Properties of Nested Subreports 1

Top