Populate a report through VBA?

  • Thread starter Thread starter Snokke
  • Start date Start date
S

Snokke

Hi

Can anyone show me how to populate a textbox in my details section of
my report?

I'm creating a recordset in VBA.
While I'm looping trough this recordset I'd like to populate a textbox
everytime the loop makes a loop.
(I'm doing alot of control in the loop, thats way I'm using VBA.)

That is, I'd like to have this result in the report:
[example]
Text: FindingA
Text: FindingB
Text: FindingD
Text: FindingE
Text: FindingF
Text: FindingJ
[/example]

And where, in what event, do I place the code in the report?

If I have this loop:

Code: 
If Not rst.BOF And Not rst.EOF Then
Do Until rst.EOF
'What code should I place here to populate a textebox
namned "txtFirst"
'in the report namned "rptFirst" with a db filed named
"Firstname" (rst.Fields("Firstname"))
rst.MoveNext
Loop
End If

I have tried (in the Report_Open event):

Code: 
Me.txtFirst.Value = rst.Fields("Firstname")

Code: 
Me.Controls("txtFirst").Value = rst.Fields("Firstname")

Code: 
[Reports]![rptFirst]![txtFirst].Value = rst.Fields("Firstname")

All this have given me is:
Run-time error '2448':
You can't assign a value to this object.

I'm stuck and hope for some help :)

Regards
Andreas
 
Is there any field in the recordset that relates back to a field in the
report? This is a highly unusual method of placing values in text boxes. Is
the report bound to a record source?

--
Duane Hookom
MS Access MVP


Snokke said:
Thanks, worked like a charm. :)
But when I'm populating my textbox in my details section of my report
I'm only getting the last field in my recordset.

I have a recordset and I'm looping through this and in this loop I'm
trying to populate the textbox.

How do I move to the next textbox in the report in order to ge this
result?

[example]
Andreas
Pelle
Kalle
Karl
Nisse
Karin
[/example]

Now the loop only replaces the information in the textbox, so I only
get the last field in the record set:

[example]
Karin
[/example]

Hoping for more help. :)

Regards
Andreas

"Duane Hookom" <[email protected]> wrote in message
Use the On Format event of the section containing the text box.

--
Duane Hookom
Microsoft Access MVP


Snokke said:
Hi

Can anyone show me how to populate a textbox in my details section of
my report?

I'm creating a recordset in VBA.
While I'm looping trough this recordset I'd like to populate a textbox
everytime the loop makes a loop.
(I'm doing alot of control in the loop, thats way I'm using VBA.)

That is, I'd like to have this result in the report:
[example]
Text: FindingA
Text: FindingB
Text: FindingD
Text: FindingE
Text: FindingF
Text: FindingJ
[/example]

And where, in what event, do I place the code in the report?

If I have this loop:

Code: 
If Not rst.BOF And Not rst.EOF Then
Do Until rst.EOF
'What code should I place here to populate a textebox
namned "txtFirst"
'in the report namned "rptFirst" with a db filed named
"Firstname" (rst.Fields("Firstname"))
rst.MoveNext
Loop
End If

I have tried (in the Report_Open event):

Code: 
Me.txtFirst.Value = rst.Fields("Firstname")

Code: 
Me.Controls("txtFirst").Value = rst.Fields("Firstname")

Code: 
[Reports]![rptFirst]![txtFirst].Value = rst.Fields("Firstname")

All this have given me is:
Run-time error '2448':
You can't assign a value to this object.

I'm stuck and hope for some help :)

Regards
Andreas
Click to expand...
Click to expand...
Click to expand...
 
I have a textbox (txtFirst) on the details section of the report. In
the db I have a field (Firstname) that I'd like to populate the
textbox with so the report lists the names.

I thought that the details section of the report was kind of a
template of a report row?

I then thought that the easiest way to populate the textbox was to
create a recordset in VBA, do the necessary checks, an then loop
trough the recordset populating the textbox in every loop.

Giving the result like this:
Andreas
Sharon
Bill
Ted

But instead it gave this:
Ted

It seames that in every loop in the recordset the information in the
textbox is replaced not giving a new reportrow as I thought. :/

The report nor the textbox has a bind to any record source.

I need to use recordsets in VBA do to a lagre amount of checks.

Have I come about this in the wrong way?
If so, how should I do?
I'm sorry if I'm totally stupid. :)
Thanks for trying to help me.


Regards
Andreas
 
Unless Marshall Barton or others have more knowledge, I think it would be
very difficult to create a totaly un-bound report. Forms can bound to a
recordset but I don't think any version of Access allows this for reports.

You haven't provided any details regarding your "necessary checks". Are you
suggesting that it is near impossible to create a query or a query combined
with code that would arrive at the same results? If you can't do this, maybe
you would have to append records from you recordset to a temporary table and
then base your report on the temp table.
 
Hi again and thanks for your patience. :)



The database I'm about to build is an education database. The admin
wants a report on which classes to attend depending on the work
assigment.

The checks are:

1. Loops through users

2. Check if user has attended basic class, based on a table describing
class/work assigment relation.

2.A. If so, check time since attending. If to long.

2.A.A. If to long, check if there are a update class.

2.A.A.A. If there are an update class, has the user attended the class?

2.A.A.A.A. If so, how long time ago?

2.A.A.A.A.A. If to long -> user should attend class.

2.A.A.A.A.B. If Not to long -> user don't need to attend the class.

2.A.A.B. If no update class -> user must attend the base class.

2.B. If Not. -> user don't need to attend base class.



I don't know any way to fit all this into a SQL-question. Feel free
to try. ;)



The temporary table thing looks interesting. Can I do that?

The admin don't often use the report (once or twice a year) so the speed
isn't the first priority. :)



How do I create a temporary table?

In what event should I create it?





Regards

Andreas
 
Back
Top