! Using DAO to Populate the Details Section !

  • Thread starter Thread starter Wembly
  • Start date Start date
W

Wembly

Hi,

I want to use DAO to populate the Details section of a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded SQL) at
the OnOpen event of the Report.

I would then assign the textboxes with rs!fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate = [StartDate]

However, this does not work either. The error message that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.
 
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource, e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound text box, but it is
too late to try to bind the control to a field.
 
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and the
output will simply display the string.



-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource, e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wembly said:
Hi,

I want to use DAO to populate the Details section of a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded SQL) at
the OnOpen event of the Report.

I would then assign the textboxes with rs!fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate = [StartDate]

However, this does not work either. The error message that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.
Click to expand...


.
Click to expand...
 
Wembly, please read the reply again.

There is a world of difference between assigning a value to an unbound
control in Detail_Format, and attempting to re-assign the ControlSource of a
control in that event.

One of the other 3 options will probably be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and the
output will simply display the string.



-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource, e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wembly said:
Hi,

I want to use DAO to populate the Details section of a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded SQL) at
the OnOpen event of the Report.

I would then assign the textboxes with rs!fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate = [StartDate]

However, this does not work either. The error message that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Ok, I now understand that you can only assign a value to a
control, but to assign the field to it, it will not work.

However, my recordset will return a table with multiple
records. And as such, I need to use the Details section so
that each row is displayed on the details section of the
report.


-----Original Message-----
Wembly, please read the reply again.

There is a world of difference between assigning a value to an unbound
control in Detail_Format, and attempting to re-assign the ControlSource of a
control in that event.

One of the other 3 options will probably be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and the
output will simply display the string.



-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource, e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I want to use DAO to populate the Details section of a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded SQL) at
the OnOpen event of the Report.

I would then assign the textboxes with rs!fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate = [StartDate]

However, this does not work either. The error message that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.
Click to expand...
Click to expand...


.
Click to expand...
 
That is the default behavour for reports.
They do repeat the detail section for each record in the report's
Recordsource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Ok, I now understand that you can only assign a value to a
control, but to assign the field to it, it will not work.

However, my recordset will return a table with multiple
records. And as such, I need to use the Details section so
that each row is displayed on the details section of the
report.


-----Original Message-----
Wembly, please read the reply again.

There is a world of difference between assigning a value to an unbound
control in Detail_Format, and attempting to re-assign the ControlSource of a
control in that event.

One of the other 3 options will probably be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and the
output will simply display the string.




-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource,
e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound
text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I want to use DAO to populate the Details section of a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded SQL) at
the OnOpen event of the Report.

I would then assign the textboxes with rs!fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate =
[StartDate]

However, this does not work either. The error message
that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
Ok, forget about the RecordSource for a moment.

Take this one for example,

Private Sub Report_Open(Cancel As Integer)

dim db as database
dim rs as recordset
set db = currentdb
Set rs = db.OpenRecordset("Select ....")

StartDate = [field1]
EndDate = [field2]
AmountPayable= [field3]
SuperAmount = [field4]
Premium = [field5]
TotalAmount = [field3] + [field4] + [field5]

End Sub

If the rs returns multiple records, how can I write the
code so that the rows are repeated in the Report_Format or
the Details_Format for that matter? The design of the
report should only show one set of control fields, and
when the report is loaded and where there are multiple
records, I want this set of controls to reappear just
below the first set, with the next record from the
recordset, etc (much like what the Details Section can do).



-----Original Message-----
That is the default behavour for reports.
They do repeat the detail section for each record in the report's
Recordsource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Ok, I now understand that you can only assign a value to a
control, but to assign the field to it, it will not work.

However, my recordset will return a table with multiple
records. And as such, I need to use the Details section so
that each row is displayed on the details section of the
report.


-----Original Message-----
Wembly, please read the reply again.

There is a world of difference between assigning a
Click to expand...
value
to an unbound
control in Detail_Format, and attempting to re-assign
Click to expand...
the
ControlSource of a
control in that event.

One of the other 3 options will probably be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and the
output will simply display the string.




-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource,
e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound
text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I want to use DAO to populate the Details section
Click to expand...
of
a
Report, and was wondering if this can be done. The way I
have it now is to create the recordset (embeded
Click to expand...
SQL)
at
the OnOpen event of the Report.

I would then assign the textboxes with rs! fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate =
[StartDate]

However, this does not work either. The error message
that
displays is: "...can't find the field '|' referred to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.


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


.
Click to expand...
 
Guess I would not try to design a report that creates text boxes on the fly.
It would be considerably easier to write the recordset to a temp table if
necessary, and use the report as it was designed to work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Ok, forget about the RecordSource for a moment.

Take this one for example,

Private Sub Report_Open(Cancel As Integer)

dim db as database
dim rs as recordset
set db = currentdb
Set rs = db.OpenRecordset("Select ....")

StartDate = [field1]
EndDate = [field2]
AmountPayable= [field3]
SuperAmount = [field4]
Premium = [field5]
TotalAmount = [field3] + [field4] + [field5]

End Sub

If the rs returns multiple records, how can I write the
code so that the rows are repeated in the Report_Format or
the Details_Format for that matter? The design of the
report should only show one set of control fields, and
when the report is loaded and where there are multiple
records, I want this set of controls to reappear just
below the first set, with the next record from the
recordset, etc (much like what the Details Section can do).



-----Original Message-----
That is the default behavour for reports.
They do repeat the detail section for each record in the report's
Recordsource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Wembly said:
Ok, I now understand that you can only assign a value to a
control, but to assign the field to it, it will not work.

However, my recordset will return a table with multiple
records. And as such, I need to use the Details section so
that each row is displayed on the details section of the
report.



-----Original Message-----
Wembly, please read the reply again.

There is a world of difference between assigning a value
to an unbound
control in Detail_Format, and attempting to re-assign the
ControlSource of a
control in that event.

One of the other 3 options will probably be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Do you mean to assign the data to the controls in the
Details_format sub routine? If so, my controlsource
assignment does not work.

It looks like this.

me.txtDate = [Start Date]

If I assign it with " " then it becomes a string, and
the
output will simply display the string.




-----Original Message-----
In Report_Open, you should be able to do any of these:
- assign a SQL statement to the report's RecordSource,
e.g.:
Me.RecordSource = "SELECT * FROM MyTable;"

- assign the ControlSource of the text boxes, e.g.:
Me.txtDate = "StartDate"

- assign a Recordset to the report's Recordset.

In Detail_Format, you can assign a value to an unbound
text box, but it is
too late to try to bind the control to a field.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

Hi,

I want to use DAO to populate the Details section of
a
Report, and was wondering if this can be done. The
way I
have it now is to create the recordset (embeded SQL)
at
the OnOpen event of the Report.

I would then assign the textboxes with rs! fieldname.

This however, does not work.

So, I tried binding the report's RecordSource with
the
same SQL at the OnOpen event.

At the DetailsFormat event, I would then assign the
textboxes with the fieldnames. E.g. txtDate =
[StartDate]

However, this does not work either. The error message
that
displays is: "...can't find the field '|' referred
to in
your expression. (2465)".

I'm stuck at the moment. Any help is appreciated.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Wembly said:
Ok, forget about the RecordSource for a moment.

Take this one for example,

Private Sub Report_Open(Cancel As Integer)

dim db as database
dim rs as recordset
set db = currentdb
Set rs = db.OpenRecordset("Select ....")

StartDate = [field1]
EndDate = [field2]
AmountPayable= [field3]
SuperAmount = [field4]
Premium = [field5]
TotalAmount = [field3] + [field4] + [field5]

End Sub

If the rs returns multiple records, how can I write the
code so that the rows are repeated in the Report_Format or
the Details_Format for that matter? The design of the
report should only show one set of control fields, and
when the report is loaded and where there are multiple
records, I want this set of controls to reappear just
below the first set, with the next record from the
recordset, etc (much like what the Details Section can do).
Click to expand...

I'm with Allen here. Why can't you just bind the report to
the query? Unbound reports are a pain (no sorting/grouping
or ??).

OTOH, even if we can't figure out why, you can usually do
what you're asking by coding the report's events along thses
lines:

Dim db As Database
Dim rs Ss Recordset

Private Sub Report_Open(
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select ....")
End Sub

Private Detail_Format(
txtStartDate = rs![field1]
. . .
txtTotalAmount = rs![field3] + rs![field4] + rs![field5]
rs.MoveNext
If Not rs.EOF Then Me.NextRecord = False
End Sub

Private Sub Report_Close(
rs.Close: Set rs = Nothing
Set db = Nothing
End Sub
 
Back
Top