SQL select to display all records on a continuous report?

[AAAndy]

SQL select to display all records on a continuous report?

Below is what I currently have. It displays the same value in the continuous report instead all values. What do I need to fix?

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Temporary")


If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        
        Me.txtAddressee = rs!Addressee


        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If


MsgBox "Finished looping through records."


rs.Close 
Set rs = Nothing

[pbaldy]

Why not base the report on the table, or the same SQL?

[Bob Fitz]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Temporary")

Is "Temporary" a table or a query

In what event are you using the code shown.

[AAAndy]

I am trying to create a report that I can later print. I will have the following fields on the report.

First Name
Last Name
Address 1
Address 2
City, State ZipCode

If there is no Address 2 than I need the below to display, deletes a line Address 2

First Name
Last Name
Address 1
City, State ZipCode


Also the City field at time increases and at times decreases.

I can have a long city name like Winchester-on-the-Severn, Maryland
or a short one like Chicago, Illinois

so the city field needs to expand and contract

[pbaldy]

You can handle Address2 with the Can Shrink property of the textbox. You can handle the variable length by concatenating the 3 fields together in a textbox:

=City & ", " & State & " " & ZipCode

[AAAndy]

You can handle Address2 with the Can Shrink property of the textbox. You can handle the variable length by concatenating the 3 fields together in a textbox:

=City & ", " & State & " " & ZipCode

In the control source I put in

=City & ", " & State & " " & ZipCode

but now it says enter parameter value, city , state, and zip ?

[pbaldy]

Are those your field names?

[AAAndy]

Yes, I even tried the below

= [Temporary]![City] & ", " & [Temporary]![State] & " " & [Temporary]![Zip Code]

but it does not work

[pbaldy]

What is the record source of the report? It would have to be that table, or SQL that includes those fields. Can you attach the db here?

[AAAndy]

What is the record source of the report? It would have to be that table, or SQL that includes those fields. Can you attach the db here?

SELECT Temporary.Addressee, Temporary.Position, Temporary.[Organization Name], Temporary.[Address Line 1], Temporary.[Address Line 2], Temporary.[F Description], Temporary.[C Description] FROM [Temporary];

[pbaldy]

I don't see any of those 3 fields selected.

[AAAndy]

I don't see any of those 3 fields selected.

I figured it out, its working now, Thanks for your help!

[pbaldy]

No problem!

[AAAndy]

Spaces are being left even when I have Can Grow and Can Shrink options set to YES, What needs to be done?

I have this

First Name
Last Name
Address 1
Address 2
City, State ZipCode

I want this

First Name
Last Name
Address 1
City, State ZipCode

but is is doing this, it leaves spaces

First Name
Last Name
Address 1

City, State ZipCode

[pbaldy]

Are you sure Address2 is Null? Can you attach the db here?