I know this has to be a pretty basic function, and I have a goofy error somewhere that may get me laughed at on such a forum as this . I am simply trying to create a report that loops through a recordset and displays the values contained in a field "TAG". Code is

Private Sub Print_Report_Click()
Dim db As DAO.Database
Dim rpt As Report
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim sSQL As String
Dim txtbx As Access.TextBox
Dim lblnew As Access.Label
Dim lngleft As Long
Dim lngtop As Long


sSQL = "SELECT TAG FROM ESGBKR"

Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
Set rpt = CreateReport
rpt.RecordSource = sSQL

'Set columns
Set lblnew = CreateReportControl(rpt.Name, acLabel, acDetail, , "TAG")

lngtop = lngtop + 200
If Not (rst.EOF And rst.BOF) Then
rst.MoveFirst
Do Until rst.EOF = True
For Each fld In rst.Fields
Set txtbx = CreateReportControl(rpt.Name, acTextBox, acDetail, fld.name, lngleft, lngtop)
txtbx.SizeToFit
lngtop = lngtop + 25 + txtbx.Height
Next fld

rst.MoveNext
Loop
Else
MsgBox "No Records Available"
End If

DoCmd.OpenReport rpt.Name, acViewPreview

rst.Close
Set rst = Nothing
Set db = Nothing
Set rpt = Nothing

End Sub


It seems to loop through my table, and give me the right amount of records (i.e., 5), but each record has the same value, the first one. So the table looks like this
Tag
X4-2A
X4-2B
X4-2C
X4-2D
X4-2E

I get a report that shows a column title "TAG" and then five values below that, each of which is "X4-2A". Any ideas what I have coded incorrectly to not get the correct values?

Thanks much

After cleansing with evandé Gentle Facial Cleanser & Make- up remover, spray the toner over face and neck from 10-12 inches distance, or saturate the cotton pad with toner and dab on skin. Leave to dry. For best results use day & night and follow with evandé Oil-free Face & Neck Moisturizer.



Best Facial Cleanser for Women

75 views and the only response is on facial cleanser? Is my question/request that stupid?

I figured that this has been done many times before and it is something basic I am missing, but maybe the whole direction I am taking is wrong?

As an update, I did replace the textbox with a label and replaced the fld.Name to fld.Value and everyting reported correctly. So,

Set txtbx = CreateReportControl(rpt.Name, acLabel, acDetail, , fld.Value, lngleft, lngtop)

works as intended, but

Set txtbx = CreateReportControl(rpt.Name, acTextbox, acDetail, , fld.Name, lngleft, lngtop)

gives me the same value over and over.

It seems like a textbox is the right thing to use in my application, but only a label is working for me.

What you are attempoting is not very common. In 10 yearsd reading over 500,000 posts and post ing over 150,000 replies I am not sure I have ever seen this done for a user app. It is usually done when crating a re0port wizard type utility.

I would recommend first creating the report manually. It would expect a single text box is used in the detail section to show the tag. The report's record source would contain all the required rows. When the report prints, it will automatically repeat the detail section for each row in the record source.

Once you know how the report design should look when done properly, you can modify your code to create the same report.

Curious, why would do you need to create the report with code when this is a standard report?

NOTE/WARNING: This code will NOT work if you compile the database into a MDE or ACCDE or if you use Access in Runtme mode.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

"Curious, why would do you need to create the report with code when this is a standard report?"

Probably because I don't know what I am doing and I like to make things complicated - the wheel needs to be redisgned, a circle is quite right The report below was merely the starting point for a larger query/report. I ultimately wanted to print all the tags from multiple tables where a certain date exceeded the required date, found in another table. (Trying to split the tables up and make everything as relational as I can, with as small of tables as I can). I also wanted to be able to report on only certain equipment based on a combo box selection. Can this all be done via standard reports?

Thank you very much for your reply. I was getting ready to give up since I had not heard anything back.

I suspect the reason why no one else has responded is that you are trying to do something no one else has ever had to do. I had to read your post several times and still can't really figure out what you are tying to do with your VBA code.

Access has a very powerful report write. I use it to create custom reports for other software applications that allow me to access there data. I have never had to do what you are attempting with your VBA code.


Glad to here you are wanting to get the table design correct.
Properly normalizing the table structure is critical. If that is done correctly then reporting get very easy.

Learning to create queries is the key to making reports easy. A query can take related data from multiple tables and Flatten or De-normalize the data to make it easy for a report writer to group as desired.

Why are they in multiple tables? I would try to keep them in the same table if at all possible.

I try to limit the number of tables. It is better to have a tall ( lot of rows/records) tale then lots of small tables with almost identical fields.

I never try to make my tables "small " in number of rows/records. Just the opposite.

I like the saying:
Columns (fields) are expensive and rows (records) are cheap.



Absolutely!

In a well designed database you should be able to do everything you need reporting wise without having to use code to modify the report design.

FWIW: I only use standard reports.

I urge you to check out this example on my site to see some of the possibilities:

Report Dialog Examples

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Normalizing! - That's the word I was looking for.

By small, I was trying to imply not putting too much data in one table, but rather spreading the data out over tables and then relating them - this is my understanding of normalizing. Sounds like this is not necessarily the approach you take.

I think I need to learn how to make some basic queries and then a standard report. I was thinking what I wanted to do was beyond the standard report wizards and that I would need VBA.

Out of curiousity, when you create a "standard" report, is that VBA code that generates the report or something else? Is there a way to see the code that is being used to generate the report?

Thanks for all your help coach!

I have edited my previous post. You may want to re=read it.

The only concern I have with to much data in a table is by having to many columns/fields in a row/record.

In a properly normalized database you will normally have more rows/records in your table not less.

I rarely have used the report wizard to create a new report. I have several saved reports that I use as templates. When I start a new report I copy one of the existing reports.

What I think you are wanting to do is a very basic report. The issue is that you have data in multiple table. You can use a Union query to combine tables together. Generally whenever I see the need to use a UNION query it is also an indication of a design issue.


Someting else.

Reports are an object similar to a Form object. You use the Report Designer to view the report object. You can then add VBA code to events to add more functionality.

Report and Form are so similar that they use the same controls. You can use a lot of the same design techniques for both. You can even save a form as a report. But not the other way around.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Also checkout:

Access Basics

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015