Show Multiple Columns from ComboBox on Report

[garymilam]

Ok, so I have a combo box that has addresses in it on my form. I have it so users can select the company name and show the full address on my report. Here is my code on the report =[Forms]![frmReceipt]![Combo91].[column](0). So 0 show the company name, but I want it to show column 1, 2, 3, 4 which reflects street address, city, state, zip. What do I put for columns 1-4. Help. Thanks.

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=87 29[/IMGnothumb]

[Seth Schrock]

You would just replace the 0 with a 1, 2, 3, or 4. If you want them all in one textbox, then you would just have to concatenate them together.

=[Forms]![frmReceipt]![Combo91].[column](0) & [Forms]![frmReceipt]![Combo91].[column](1) & [Forms]![frmReceipt]![Combo91].[column](2)

etc.

[jforbes]

I use this function to build addresses.

Public Function formatAddress(ByRef vCase As Variant, ByRef vTo As Variant, ByRef vCompany As Variant, ByRef vStreet1 As Variant, ByRef vStreet2 As Variant, ByRef vCity As Variant, ByRef vState As Variant, ByRef vZip As Variant) As String
    
    ' vCase, 0=No Change, 1=Uppercase, 2=Lowercase, 3=Camelcase

    Dim sAdddress As String
    Dim sTo As String
    Dim sCompany As String
    Dim sStreet1 As String
    Dim sStreet2 As String
    Dim sCity As String
    Dim sState As String
    Dim sZip As String
    
    sTo = Trim(vTo & "")
    sCompany = Trim(vCompany & "")
    sStreet1 = Trim(vStreet1 & "")
    sStreet2 = Trim(vStreet2 & "")
    sCity = Trim(vCity & "")
    sState = Trim(vState & "")
    sZip = Trim(vZip & "")
    
    If Len(sTo) > 0 Then sAdddress = sAdddress & sTo & " " & vbCrLf
    If Len(sCompany) > 0 Then sAdddress = sAdddress & sCompany & " " & vbCrLf
    If Len(sStreet1) > 0 Then sAdddress = sAdddress & sStreet1 & " " & vbCrLf
    If Len(sStreet2) > 0 Then sAdddress = sAdddress & sStreet2 & " " & vbCrLf
    If Len(sCity) > 0 Then sAdddress = sAdddress & sCity & ", "
    If vCase < 4 And vCase > 0 Then sAdddress = StrConv(sAdddress, vCase)
    If Len(sState) > 0 Then sAdddress = sAdddress & StrConv(sState, 1) & "  "
    If Len(sZip) > 0 Then sAdddress = sAdddress & sZip & "  "
    If Len(sAdddress) > 0 Then formatAddress = Left(sAdddress, Len(sAdddress) - 2)

End Function

Then your code would look something like this:

=formatAddress(0,[Forms]![frmReceipt]![Combo91].[column](2),Null,[Forms]![frmReceipt]![Combo91].[column](3),[Forms]![frmReceipt]![Combo91].[column](4),[Forms]![frmReceipt]![Combo91].[column](5),[Forms]![frmReceipt]![Combo91].[column](6),[Forms]![frmReceipt]![Combo91].[column](7))

[Narender Sagar]

1. Your combobox is based on a table/query.
2. Your report is also based on a table/query.
3. Create a new query (and add table of which combobox is based), choose whatever fields you want in this query and link your report to it.

[zmbd]

Narender Sagar: based upon the original post, one most likely would have to use a parameter query to accomplish what you suggest. Not a big deal; however something that should be pointed out.

garymilam: Reports are not forms and forms are not reports.
Why do I point this out you ask, because, normally reports do not have interactive features and I want to be sure that we're all on the "same page" with the nomenclature. It seems a small thing; however, what can be done on a form is quite often not available within a report or must be approached in significantly different ways to accomplish.

Seth and jforbes have given you two excellent options if you do not want to go with a query.