Multiselect Listbox

[Brandi Mills]

I have a table named
tbl_Questions
with a column called Region
I have a total of 8 regions

I am attempting to set an Unbound multiselect listbox on a form called frm_DH

I need the user to be able to select and deselect any combination of 8 regions and the record to show in the splitform datasheet and update the questions table.

So far I can select the data but it does not populate the qry_DH nor the tbl_Questions and fails to save once the form is closed.

here is my code:

[Code/]
Private Sub cmd_lst_Region_Click()
On Error GoTo PROC_ERR

'Declare items
Dim iItem As Integer
Dim Region As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

' Save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If

' Open a Recordset based on the table
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Questions", dbOpenDynaset)
With Me!lst_Region


' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
Region = .Column(0, iItem)

' Determine whether this combination is currently
' in the table
rs.FindFirst "[ID] = " & Forms!frm_DH!lst_Region & " ; " _
& "[Region] = " & lst_Region.AddItem

If rs.NoMatch Then

' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!ID = Me.txt_ID
rs!lst_Region = lst_Region
rs.Update
End If

' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then

' delete this record if it's been deselected
rs.Delete
End If

' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing

PROC_EXIT:

Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub
[End_Code/]

I am sure I have messed this up royally in addition I am sure I am attempting to do the impossible.

thank you for any assistance and I hope I have offered enough information. I am using access 2007 at this time.

[ADezii]

Unless I am misreading this Thread, you basic Logic for creating a String of Semi-Colon Delimited, Selected, Region(s) is way off. The following Code will generate the desired String from the Region(s) chosen:

Dim ctl As ListBox
Dim varItm As Variant
Dim strBuild As String      'To build Region(s) Selected List
 
Set ctl = Me![lst_Regions]
 
If ctl.ItemsSelected.Count < 1 Then Exit Sub
 
For Each varItm In ctl.ItemsSelected
  strBuild = strBuild & ctl.ItemData(varItm) & ";"
Next varItm
 
strBuild = Left$(strBuild, Len(strBuild) - 1)       'Strip Trailing ';'
 
Debug.Print strBuild

Sample OUTPUT:

Region1;Region5;Region8
Region4
Region2;Region4;Region6;Region7
Region7;Region8
etc...

[Brandi Mills]

ADezii

I am sure you are reading it correctly. I appreciate your assistance and will get back to it in the AM. I will post the outcome.

Thank you,

[Brandi Mills]

ADezii,

I appreciate your response and code snip, but this did not work for me.

I need the form to update the table and the information is not being updated or saved once I leave the form. As a novice to this I am unsure how else to explain it.

I will try again.

Table Name: tbl_questions
Query Name: qry_DH (Select Query)
Split-Form Name: frm_DH being fed by qry_DH
MultiSelect Listbox Name: lst_Regions (there are 8 Regions)

I need the listbox to populate the column region on the tbl_Questions using the frm_DH. If it could do it dynamically that would be a plus, but I would settle for it updating after I leave the form or after I press the save button or something. Right now my code nor the above snip is working. I can choose the items they just do not save or populate the table.

Any ideas?

[ADezii]

Dim ctl As ListBox
Dim varItm As Variant
Dim strBuild As String      'To build Region(s) Selected List
 
Set ctl = Me![lst_Regions]
 
If ctl.ItemsSelected.Count < 1 Then Exit Sub
 
For Each varItm In ctl.ItemsSelected
  strBuild = strBuild & ctl.ItemData(varItm) & ";"
Next varItm
 
strBuild = Left$(strBuild, Len(strBuild) - 1)       'Strip Trailing ';'
 
'Does the String exist in tbl_Questions
If DCount("*", "tbl_Questions", "[Region] = '" & strBuild & "'") > 0 Then       'Yep, exists
  MsgBox strBuild & " alread exists in the [Region] Field of tbl_Questions", vbExclamation, "Duplicate Entry"
Else
  If IsNull(Me![txtID]) Then        'Must have an ID
    MsgBox "You must enter an ID before the Record can be saved", vbExclamation, "Missing ID"
      Me![txtID].SetFocus
  Else      'Good to go!
    CurrentDb.Execute "INSERT INTO tbl_Questions ([ID], [Region]) VALUES (" & Me![txtID] & _
                      ", '" & strBuild & "')", dbFailOnError
      MsgBox "Record Saved", vbInformation, "Save Record"
  End If
End If

P.S. - If this does not adequately explain things, I'll send you an Attachment to illustrate the concepts more clearly.

[Brandi Mills]

oh boy, okay - I am getting an error on the SQL INSERT INTO statement. I guess because the [ID] on tbl_questions is the primary key on that table. It is calling duplicate record and stating unable to update the table based on no duplicates allowed. Which makes perfect sense except now I royally confused on how to get the information into the table.

Maybe I am going about this all wrong. I was Attempting to keep only one table. Maybe a second table making the database one-to-many or something would be more appropriate?

[ADezii]

Brandi, why don't you Upload your Database, so that we can get a picture of exactly what is going on? If you are able to do this, just provide the bare essentials, and strip it of any confidential information.

[Brandi Mills]

ADezii,

I was thinking about that, but was unsure how involved you wanted to get. I am being blocked by my companies security rules. I have sent home the file and will upload it this evening for review. I have stripped down to example data and removed all company logos.

thank you and will upload this evening.

[ADezii]

Kindly provide detailed info on anything that is relevant.

[Brandi Mills]

okay,

I need the frm_DH and multiselect lst_region to populate the qry_DH and subsequently the tbl_Questions column regions table.

The user may select to remove a region from the record or add the region to the record at any time. They should have to have at minimum one region and they could have a possible choice of "ALL". If "ALL" is selected then they should not be able to select additional items from the list.

Anything you can do to assist would be great - thank you

[ADezii]

Cannot Open the File, Brandi - Unrecognized database format

[Brandi Mills]

:(

Of course! I think I saved it in 2010 instead of older version. I am ridiculous. I will reload it again this evening. thank you

[Brandi Mills]

I am so sorry for the lateness of this reply. I just started back on the project and would be very greateful if you could look at this for me again.

I hope you can open this one, Let me know.

thank you,

[ADezii]

Unrecognized Database Format again, need the DB in Access 2003.