How to report records in a box

[NewFruit]

Kindly help with the following report:

I have a table with field “FRUITNAMES” with the following text records, the fruit names keep changing based on vendor requests (for example):

APPLES
MANGO
STRAWBERRY
PAPAYA
ORANGE
KIWI
REDAPPLE
CALIFORANGE

I would like to print the above in a report, in a box size width=3” and height=2”, in the following format:

APPLES, MANGO, STRAWBERRY,
PAPAYA, ORANGE, KIWI, REDAPPLE,
CALIFORANG[FONT=&quot]E

Any suggestions or vba code? Thankyou.[/FONT]

 

[NewFruit]

Hello all:

Just bumping up the thread. 54 Views & no replies! I hope I asked the right question.

Making the 3"x2" box on the report was easy but I am unable to populate it with the data from multiple records and same field. Note, I am not trying to concatenate two fields but merely trying to display data from the same field and many records, and separated by commas.

Any pointers experts. Thanks in anticipation.

 

[NewFruit]

Thread bump. 107 views and 0 suggestions, hmm...did not realize it is such a tough question!:banghead:

I am attaching the following snippet of how it needs to print in a portion of our report.

If it can't be done in Access, experts please tell me so or any pointers will be helpful. Thanks in anticipation.

 

[Khalid_Afridi]

Hmmmmmm, Its possible, you need some VBA coding which iterate through the record in the same field and concatenate each record to a string variable separated by comma then call that variable on report unbound control.

 

[Khalid_Afridi]

The following code produce exactly what you need;

Private Sub Command2_Click()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim conField As String
Dim conField1 As String

strSQL = "Select fruitName FROM fruit"
Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
    conField = rs!fruitName
    rs.MoveNext
    conField1 = conField1 + conField & ", "
 
 Loop
Debug.Print conField1
End Sub

Result:
APPLES, MANGO, STRAWBERRY, PAPAYA, ORANGE, KIWI, REDAPPLE, CALIFORANGE,

 

[NewFruit]

Thanks for the code and your replies.

A few more questions:

1) Where do I insert this code: should it be in an "unbound box" of a report?

2) If it is in an unbound box of the report, how should I assign the value of the variable "conField1" which is being generated from your code?

3) Should I be assigning the value in some "event", if so, which one? and how to trigger the report to display the variable?

Once again, thank you for your help.

 

[Khalid_Afridi]

Thanks for the code and your replies.

A few more questions:

1) Where do I insert this code: should it be in an "unbound box" of a report?

2) If it is in an unbound box of the report, how should I assign the value of the variable "conField1" which is being generated from your code?

3) Should I be assigning the value in some "event", if so, which one? and how to trigger the report to display the variable?

Once again, thank you for your help.

Ok!

I have written the code on command button click event just for testing to get the desire result. The same can be used on open REPORT event with just minor changes.

First of all understand the code:

'Declare Varialbles to get the open recordset
Dim rs As DAO.Recordset ' declare Recordset
Dim db As DAO.Database ' declare Database
Dim strSQL As String 'declare variable to hold SQL statement
Dim conField As String ' declare variable to hold record data
Dim conField1 As String ' declare variable to hold record data and concatenate them together

strSQL = "Select fruitName FROM fruit" ' Write SQL Query to open recordset. in this example select all the records from fruit table, you may need different query as per your requirement

Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL) ' open the recordset

' now iterate through the recordset and store the values in conField1 variable
concatinating each value.

Do While Not rs.EOF ' using of Do While Loop for iteration
conField = rs!fruitName ' hold First Record value
rs.MoveNext ' Mover to next record

conField1 = conField1 + conField & ", " ' hold the first value and concatenate the next value to conField1 variabl

Loop ' loop for next record
Debug.Print conField1 ' Just to print the confield value in the immediate window for testing
End Sub
=====================

Now the Report Part:

1 - Create a 3"x2" textbox unbound control on the report and name it txtItems
2 - On Report open event copy and paste the following code with little minor changes.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim conField As String
Dim conField1 As String

strSQL = "Select fruitName FROM fruit" ' [COLOR="Blue"]you may need to change your query accordingly[/COLOR]
Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
    conField = rs!fruitName
    rs.MoveNext
    conField1 = conField1 + conField & ", " 
Loop
[COLOR="blue"]me.txtItems.value = conField1 [/COLOR] 'Add this line of code which will pass the value to the control (txtItems) on your report as per your request.

Give a try and let me know if you get the required result.

Good Luck!

 

[NewFruit]

Thank you so much Afridi bhai (brother). That works just perfect.

Issue [SOLVED].

 

[Khalid_Afridi]

Thank you so much Afridi bhai (brother). That works just perfect.

Issue [SOLVED].

You are welcome Fruit, (whatever your name - look like Pakistani ) my pleasure your problem solved. Sorry for late reply, I was bit busy in home as I on my annual vacation to my homeland

Best Regards,