Solved Subreport based on listbox items chosen, but some with user input (1 Viewer)

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
Terrible title, I don't know how to describe it! I have a form that the user completes to create a report. This report is a letter that is sent to the customer asking them to provide a document. There is a multi select listbox with 10 options for the user to choose what they need. The ID of whatever they pick is added to a table and shows in the middle of the report in a subreport. The listbox is based on a table with 3 fields: ID, listbox text, and the longer text that actually appears on the report. Example: Listbox = Name change / Report text = "Please send us a completed name change form." This is all working fine. But now they want to add a couple options to the listbox that require user input, like the following:

Listbox = Licensing Agency
Report text = Please contact the (agency name) Licensing Agency to pay the required taxes.

I have a field for agency name, but I don't know how to put that info into the report with the way it's currently set up. Any ideas on how best to achieve that?
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
Might help to see how you add the selections to the table. You could use the Replace() function in that process to replace "(agency name)" with the value from the field.
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
I got it from your website multi-select listbox example!

Code:
Public Sub List()
Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

 If Me.Dirty Then
  Me.Dirty = False
End If
    
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterCustomer", dbOpenDynaset, dbAppendOnly)

    'add selected value(s) to table
  Set ctl = Me.lstCust
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem
 
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterDMV", dbOpenDynaset, dbAppendOnly)

    'add selected value(s) to table
  Set ctl = Me.lstDMV
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem
 
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterDealer", dbOpenDynaset, dbAppendOnly)

    'add selected value(s) to table
  Set ctl = Me.lstDealer
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
 
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
Not sure where you're getting the agency name, but along the lines of:

rs!DocumentNeeded = Replace(ctl.ItemData(varItem), "(agency name)", YourValueHere)
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
Thank you! Is it an issue that my table (tblLetters) is only saving the ID number of the item needed? The subreport query pulls the ID from tblLetters and the description from tblDropdowns. The description is where the (agency name) appears. Do I need to change tblLetters to hold the longer description in addition to the ID, and then this should work?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
Is the table storing data or just a temporary source for the report (emptied and repopulated each time the report is run)? Normally a related table would just hold the ID and you'd get the description via query as you are doing. If the table is just holding temporary data for the report I'd dump the modified description into it. I suppose even if the table is permanently holding what when out as a letter, you could make a case for storing the description since it varies by record.
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
The table is storing data and pulling the description via query. I'm fine with adding the description to the table and I added a field (DocNeededDesc), but having trouble getting it to add. The description is in the 3rd column of the listbox. The ID is the bound column. This is what I have at the moment that isn't working to add the description:

Code:
Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterCustomer", dbOpenDynaset, dbAppendOnly)

    'add selected value(s) to table
  Set ctl = Me.lstCust
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    rs!DocNeededDesc = Me.lstCust.Column(2)
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterDMV", dbOpenDynaset, dbAppendOnly)

Also - in this particular listbox, there are 10 items. It is the 3rd item on the listbox that is the one with (agency name). Could an If statement go in that says if the 3rd item is selected, replace agency name with whatever is in the LicensingAgency textbox?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
For a multiselect listbox:

ctl.Column(2, varItem)
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
Thanks! I've gotten it to add the description to the table, and now am trying to get it to replace the (agency name). It will add the other items to the table, but when I select the 3rd item with agency name, I get "Invalid use of Null."

Code:
Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterCustomer", dbOpenDynaset, dbAppendOnly)

    'add selected value(s) to table
  Set ctl = Me.lstCust
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    
    If Me.lstCust.Selected(2) = True Then
        rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)
    Else: rs!DocNeededDesc = ctl.Column(2, varItem)
    End If
    
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblLetterDMV", dbOpenDynaset, dbAppendOnly)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
What line errors? What is this supposed to do?

If Me.lstCust.Selected(2) = True Then
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
It is this line that causes the error:
Code:
rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)

I thought maybe I needed something to say "If the 3rd item in the list is selected, take the text from [LicensingAgent] and replace "(agency code)" in the description. If it's any of the other options, just put the description in as is."

The description is currently going into the table, but I haven't gotten the LicensingAgent text to change for that one option. Hope that makes sense.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
Can you attach the db here?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
I'm not sure about the insert event, but this code appeared to work:

Code:
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)

    Debug.Print ctl.Column(2, varItem), Me.LicensingAgent
    rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)
   
    rs!State2StateID = Me.ID
    rs.Update
  Next varItem

I put paul in the licensing agent field and this got inserted into the table:

Please contact the paul Licensing Agency to pay the required taxes.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:12
Joined
Aug 30, 2003
Messages
36,118
By the way, not knowing how you were using it, I triggered it by entering a couple of things and then clicking on the navigation buttons to move to a different record (to force the save).
 

Jupie23

Registered User.
Local time
Today, 14:12
Joined
Nov 9, 2017
Messages
84
That's perfect! Thank you so much for taking the time to help!!
 

Users who are viewing this thread

Top Bottom