Referring to bound column in multi select list box

[oatsybob]

So I have a list box that lists organizations. I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization. When the list box was not multi select, the expression [forms]![media]!


[List30] made the default value of one of the fields in my contact form the bound column from the selection in the list box. However, now that the list box is multi select, the contact input form does not seem to be able to get the value from the bound column in the list box. When multi select is turned on, is the bound column stored differently.

To even get the contact input button to work, I had to change the code from:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click


    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "Contactsqry"
    stLinkCriteria = "[Organization ID]=" & Me.List30
    DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command40_Click:
    Exit Sub


Err_Command40_Click:
    MsgBox Err.Description
    Resume Exit_Command40_Click
    
End Sub

To this:

 
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click


    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim var As Variant


    stDocName = "Contactsqry"
    stLinkCriteria = "[Organization ID]=" & Me.List30.ItemsSelected(var)
    DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command40_Click:
    Exit Sub


Err_Command40_Click:
    MsgBox Err.Description
    Resume Exit_Command40_Click
    
End Sub

Could this change have something to do with it? Any thoughts otherwise?

Thanks in advance

[pbaldy]

You can't refer to it that way when it's Multiselect. This is one way:

http://www.baldyweb.com/multiselect.htm

[oatsybob]

Thanks. I was able to modify your code to open the contact input form filtered by a specific organization. When I open the contact input form, there is a text box the control source for which is a field that is linked to the primary key in my organizations table (contacts and organizations are in two separate tables). When I'm viewing contacts I have already entered, that text box is filled with the correct value, but when I go to enter a new contact it's blank. When my list box was single select, I could set the default value to its bound column ([forms]![media]!
[list30]), but this no longer works. It's important that I the default value for that field draws from the list box and that its control source is a linked table field because a lot of non-technical users at my non-profit will be using this database and may not know about table linkages. Do you know a way of getting the default value set?

[ssanfu]

I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization.

I'm trying to understand why you changed to a multi-select list box.
Are you trying to add one contact to multiple organizations?

[oatsybob]

I changed because I also use the form to send mass emails. The form has two list boxes. One of the list boxes displays a specific type of organization and the other gets populated with all the contacts from the organizations that are selected in the first. I changed them both to multi select because I wanted to be able to populate the second list box with the contacts from multiple organizations and either send an email to all of those contacts or select specific contacts to send a mass email to. The form also tracks through a third table the histories of contact my non-profit has with all organizational partners and contacts.

[ssanfu]

Would you post your current code?

[oatsybob]

Sure!

I modified this from pbaldy's link earlier in this thread. It does what I need it to, which is to open a form delimited by the user's selection in a list.

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click


 Dim stDocName As String
 Dim stLinkCriteria As String
 Dim varItem As Variant
 Dim s As String
 Dim ctl As Control
 
    If Me.List30.ItemsSelected.Count <> 1 Then
            MsgBox "Must select 1 organization"
        Exit Sub
    End If
    
    Set ctl = Me.List30
    
    For Each varItem In Me.List30.ItemsSelected
    s = s & ctl.ItemData(varItem)
    Next varItem


    stDocName = "childrencontactsqry"
    
    stLinkCriteria = "[Organization ID]=" & s
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_Command40_Click:
    Exit Sub


Err_Command40_Click:
    MsgBox Err.Description
    Resume Exit_Command40_Click
    
End Sub

However, what I still need to happen is for the default value of one of the text boxes in the new form that is opened to be both associated with a field in a table AND drawn from a selection in the list. The following picture shows what I mean. I need the default value of "Organization ID" to be gotten from the list box.



When the list box was set to single select, this command was able to achieve what I need:



Now that I have it set to extended multi select, no cigar. Any thoughts?

[ssanfu]

What is the SQL for the list box "List30"?

If the "Organization ID" is the bound column, then you could have a hidden text box ("OrgID_PK") on the form. In your code, change the code to:

Private Sub Command40_Click()
   On Error GoTo Err_Command40_Click


   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim varItem As Variant
   Dim s As String
   Dim Ctl As Control

   Me.OrgID_PK = Null    '<<Add this line
   

   Set Ctl = Me.List30

   If Ctl.ItemsSelected.Count <> 1 Then
      MsgBox "Must select 1 organization"
      Exit Sub
   End If



   For Each varItem In Ctl.ItemsSelected
      s = s & Ctl.ItemData(varItem)
   Next varItem

   Me.OrgID_PK = s    '<<Add this line

   stDocName = "childrencontactsqry"

   stLinkCriteria = "[Organization ID]=" & s

   DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command40_Click:
   Exit Sub


Err_Command40_Click:
   MsgBox Err.Description
   Resume Exit_Command40_Click

End Sub

Instead of referring to "Forms!Media!List30", use "Forms!Media!OrgID_PK"

[oatsybob]

I'm not sure what you mean by the code for List30. This is the sql for the query that populates the list?

SELECT Mediaorgs.Name, Mediaorgs.[Organization ID], Mediaorgs.Phone, Mediaorgs.Fax, Mediaorgs.Email, Mediaorgs.Address, Mediaorgs.City, Mediaorgs.PostalCode, Mediaorgs.[Sub-Type], Mediaorgs.[Non-Profit], Mediaorgs.Website
FROM Mediaorgs
ORDER BY Mediaorgs.[Sub-Type], Mediaorgs.Name;

Organization ID is the PK, however when I pasted in the code and changed the default value I got a #Error

edit: now when I attempt to compile the code with your changes, it gives me "method or data member not found". Do I need to declare OrgID_PK before I can refer to it?

[ssanfu]

I'll post an example......

[ssanfu]

Ahhh, I think I understand now. I've attached an example (A2K format) to demonstrate what I tried (unsuccessfully ) to explain. It was easier to throw together a demo.


I do see things that you should be aware of.....

Object names in Access should only contain letters, numbers and the underscore. (And some don't even use the underscore). You have used spaces and dashes in some field names. If you ever want to convert the dB to SQL Server, SQL Server will choke - horribly.

Object names like "Date", "Time" & "Name" are reserved words and shouldn't be used as object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html
Also, "Name" is not very desciptive. "Name" of what?? In two years, if this dB has to be modified by someone else, it will be harder to figure out what "Name" is referring to. And two (or more) tables have the field name of "Name" in the same query, you have to include tables names or Access complains.


In this SQL for List30, I try to have the bound field as the first field in the query. It makes it easier to get the PK for the record. If it is not the first field, you have to use the "Columns" property to get the PK.

This looks like the SQL for List 30:

SELECT Mediaorgs.Name, Mediaorgs.[Organization ID], Mediaorgs.Phone, Mediaorgs.Fax, Mediaorgs.Email, Mediaorgs.Address, Mediaorgs.City, Mediaorgs.PostalCode, Mediaorgs.[Sub-Type], Mediaorgs.[Non-Profit], Mediaorgs.Website
FROM Mediaorgs
ORDER BY Mediaorgs.[Sub-Type], Mediaorgs.Name;

I would have had the query fields in this order:

SELECT Mediaorgs.[Organization ID], Mediaorgs.Name, Mediaorgs.Phone, Mediaorgs.Fax, Mediaorgs.Email, Mediaorgs.Address, Mediaorgs.City, Mediaorgs.PostalCode, Mediaorgs.[Sub-Type], Mediaorgs.[Non-Profit], Mediaorgs.Website
FROM Mediaorgs
ORDER BY Mediaorgs.[Sub-Type], Mediaorgs.Name;

In the "Column widths" property of the list box, the first column ("Organization ID") would be zero to hide the column. If it is an autonumber, the users shouldn't see it. See: http://access.mvps.org/access/general/gen0025.htm

[oatsybob]

Hey alright!! Thanks so much for your help with this, I wish I could repay you somehow. I implemented your suggestions and it's working like a charm. I didn't realize that I had to physically make a hidden text box on the form.



Also thanks for the naming tips. I'm still pretty new to access have been learning mostly by trial and error. That'll save me a big headache when I convert this thing to SQL server.

[ssanfu]

Great.

Be sure and read about the autonumber usage.
"If it is an autonumber, the users shouldn't see it. "

See: http://access.mvps.org/access/general/gen0025.htm

[oatsybob]

Oh that makes a lot of sense. That way there's no chance things will get messed up (in that way at least). This is much better (minus the gaps in the add contacts form)

[ssanfu]

Autonumbers are not guaranteed to be sequential, consecutive or even positive. Only unique. Nothing yo can do about gaps in autonumbers.

You can use autonumbers to provide a unique identifier, but "roll your own" number for patient ID if you need a Patient number. There are many ways to create your own numbering system. If you are interested, Paul has a write up at http://baldyweb.com/CustomAutonumber.htm

And there is a discussion (debate?) about autonumbers at:
http://www.utteraccess.com/forum/Cus...-t1953743.html

Get your , it is a long debate