Solved

Multi Select List Box

Posted on 2004-09-22
22
276 Views
Last Modified: 2008-03-17
Hi,

I am using this website first time and need desperate help.  I am kind of novice to VBA utilization.  So if someone can extend help I will appreciate for the patience and kindness.

I have a master table called 'tblProjects' - it has 2 fields - 'ProjectBUScopeID' & 'ProjectBUScopeName'.  These two fields data needed to be stored from a form 'frmProjects' where I have a multi select listbox that is created from a query out of a table 'tblBUScope' that has 15 items listed.

I need to assign or select via a command button   1 or more selected items from the 'tblBUScope' to 'ProjectBUScopeID' & 'ProjectBUScopeName' that applies to the each individual project and stores them to the individual table; also I need to show the assigned or selected items on a different window of the form 'frmProjects.

What I did so for is I created a list box using a query from the 'tblBUScope'  and I am able to select multiple choices but when I click on the command button next to it, it gives me error.  I created a click event on the button to update/store the selected item calling a function but I don't think I did everything quite right.
 
The following codes that I tried which is not working - anotherwords I got the error - '3265 item not found in this collection.

Private Sub Command553_Click()

On Error GoTo Err_Command553_Click

    Me.TextNotice = CreateProjectBURecords(Me.ProjectBUScope) & " Records Created"
    Me.ProjectBUScope.Requery

Exit_Command553_Click:
    Exit Sub

Err_Command553_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_Command553_Click
   
End Sub
----

Public Function CreateProjectBURecords(ctlRef As ListBox)
Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim RecCount As Integer
   
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qBUScopeAppend
    Set rst = qd.OpenRecordset
    RecCount = 0
    For Each i In ctlRef.ItemsSelected
        rst.AddNew
        rst!BUScopeID = ctlRef.ItemData(i)
        rst!BUScopeName = Me.ProjectBUScopeName
        rst.Update
        RecCount = RecCount + 1
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateProjectBURecords = RecCount
   
Exit_CreateProjectBURecords:
    Exit Function

Err_CreateProjectBURecords:
    Select Case Err.Number
        Case 3022     'ignore duplicate keys
            RecCount = RecCount - 1
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_CreateProjectBURecords
    End Select
   
End Function



Please if someone can help me with the code.

Thanks in advance.
Dewan   -  
0
Comment
Question by:dewanchoudhury
  • 12
  • 6
  • 2
22 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Which line gives you the error?
0
 

Author Comment

by:dewanchoudhury
Comment Utility
I didn't get the compile error but I am getting the error when I click on the form button after selecting multiple choices and click the button to see if they get stored.
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Hi, thanks for checking in my questions.

I think my code is not completely right.  I got the error when click the button on the form and not at the time of compiling.

Dewan
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Does it give you an option to debug? If it does, when you go into debug, which line is highlighted in yellow?
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
Comment Utility
I think you sould reference the control a little differently as it is likely pulling the default property of the control instead of the control itself, the default property is Value and in the case of a multi-select list box that will return Null ...

Me.TextNotice = CreateProjectBURecords(Me.Controls("ProjectBUScope")) & " Records Created"

Steve
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Steve,

I appreciate your reply but it still is not working.

This time I am getting '3219-Invalid Operation'

My two tables 'tblProjects' (Master table)  

ProjectID
ProjectName
ProjectDescription
ProjectBUID
ProjectBUScope
ProjectManager
ProjectStatus
Comments


and

'tblBUScope' (small table) - where I have only

BUScopeID - 14ID - Primary Key
BUScopeName - 14 Corresponding Names

My Multi List Box looks through all these 14 BU Scope ID's and only assigns the one I am selecting to ProjectBUScopeID and ProjectBUScopeName.

What I did I created the append query - where I have both the tables and I joined them by the code below which may not be good which seems alright but may be something I am not doing right.

INSERT INTO tblProjects ( ProjectBUScopeID, ProjectBUScopeName )
SELECT tblProjects.ProjectBUScopeID, tblProjects.ProjectBUScopeName
FROM tblBUScope INNER JOIN tblProjects ON tblBUScope.ScopeID = tblProjects.ProjectBUScopeID;

Appreciate your suggestion again.

Dewan

0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
make sure the make of the list box you are multi-selecting from is not the same name as the field you are selecting.
...   lstProjectBUScope

You never told the code o use the error handler so I removed it ... you can find exactly what line you are getting an error on by clicking Debug when the error message pops up. PLease let us know which line it is.

Public Function CreateProjectBURecords() As Integer
    Dim varItem As Variant
    Dim rst As DAO.Recordset
    Dim intCount As Integer
   
    Set rst = CurrentDB.OpenRecordset("tblProjects", dbOpenDynaset, dbAppendOnly)
    For Each varItem In Me.lstProjectBUScope.ItemsSelected
        rst.AddNew
        rst.Fields("BUScopeID").Value = Me.lstProjectBUScope.ItemData(varItem)
        rst.Fields("BUScopeName").Value = Me.ProjectBUScopeName.Value
        rst.Update
        intCount = intCount + 1
    Next varItem

    rst.Close
    Set rst = Nothing

    CreateProjectBURecords = intCount
   
End Function
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Steve,

For some reason the whole setup that I did is now confusing me.

My two tables 'tblProjects' (Master table)  

ProjectID
ProjectName
ProjectDescription
ProjectBUScopeID**
ProjectBUScopeName ***
ProjectManager
ProjectStatus
Comments

Which is getting the data from the 2nd table

'tblBUScope' (small table) - where I have only

BUScopeID **- 14ID - Primary Key  - 1,2,3,4,5,6,7,8,9,10,11,12,13,14
BUScopeName ***- 14 Corresponding Names - ALL, COMPANY, COMPANY IT, EFR, EFN, FID, EFS, FIR,  MAB, NNA, SAP, UAS, XAB, ZAP

I created a list box using the wizard clicking on the list box.  On the wizard out of the 3 choices when it asked I checked 'I want the list box to look up the values in a table or query (ie. 1st choice), and selected 'tblBUScope's both the fileds and on the last step when it asked where to store the value I picked 'ProjectBUScopeID' which is 'Projects' table.
I changed the listbox to Extended for multi select.

I created a command button and on the button's I pasted your code on on click event.

I didn't get any compiling error but it is not storing the data to the table.

I am a little frustating.....but I appreciate your help......I wonder if I can talk to you over the phone....my number is 212 762 1043 (NY).

Thanks a million and regards,
Dewan








0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
here is a differen way to approach the issue from a different perspective.

Make a copy of your form. Remove the listbox.

Make a datasheet subform from 'tblBUScope', and use a combobox, not a list box for your 14 values. Now drag that subform onto your form and use ProjectBUScopeID  and BUScopeID  as the Master Linkning and Child Linking fields.

This way you can select as many scopes as necessary but do not have to write any code to populate records in an after update ot code int eh current event to make sure the correct sopes are selected for the record your are viewing.

Steve
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Hi Steve,

I am not getting it right.

Based on the above idea ------

What I did so far is,
- created a 2nd copy of my Project from,
- created a datasheet form of my 'tblBUScope'  ( I have now form 2 fields  BUScopeID, BUScopeName)
- but at this point I am confused where to make this combo box - is it on the design view of BUSCOPE form or on the Project form?....

I tried it but I got the idea about Master & Child Linking fields.....

Can you make it little step by step......

Thanks,
Dewan
 
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Let's back up a step and figure out what it is you need to do.'

"My Multi List Box looks through all these 14 BU Scope ID's and only assigns the one I am selecting to ProjectBUScopeID and ProjectBUScopeName"

If you only need to store 1 scope per Project then make the list box Multi-Select = No. Forget about the second /subform.


Steve
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Steve,

Sorry, about the misunderstanding.  I meant to say "My Multi List Box looks through all these 14 BU Scope ID's and only assigns the 'ones'  (i.e. more than one)  I am selecting.   So your answer prior to this one was right but I missing a step to follow.   All what I need a little step by step.

I am selecting the multiple fields from the MULTILIST BOX 'BUScope' and assigning the ones that are applicable to the 'ProjectBUScope' and they need to get stored to the 'ProjectBUScope' Field seperated by a comma.  I wanted to see these selected choices also on the form the ones that are selected to that very PROJECT too.

I have seen some examples similar to it but not completely same.  I think I have to do it by creating & calling a Function.   But your earlier idea sounds simple although I couldn't do it.

Hope you won't get annoyed.

Thanks, Dewan
 
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
I am not annoyed ... I am just trying to make sure we to an answer that works for you :-)

First, I think you should consider storing multipple values in the same field, if you ever want to do something like report by BUScope it will be difficult, updating BUScope could also be an issue. Another thing to remember is that every time you load a record (Form_Current event) you will need to run code that parses the values back out of the field and selectes them on your listbnox.

We can set up a linking table (Many - Many) relationship between Scopes and Projects or we can write code to store multiple values in the same field. Which direction would you like to persue?

Steve
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Steve,

Thanks for your response,

I am trying to store multiple values in the same field, which is exactly I am looking to do.

So if you can provide me with the code and set up to store multiple values from  "Scopes to Projects" would of great help.  My table Scopes (which is 'tblBUScope' values, All, Company, Company IT.......etc. are total of 14 - they are static).  So I will wait to hear from you.

Once again thank you for the response.

Dewan
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
what part of this code breaks? do you have Option Explicit at the very top of your module? Make the name of your listbox be lstProjectBUScope.

Public Function CreateProjectBURecords() As Integer
    Dim varItem As Variant
    Dim rst As DAO.Recordset
    Dim intCount As Integer
   
    Set rst = CurrentDB.OpenRecordset("tblProjects", dbOpenDynaset, dbAppendOnly)
    For Each varItem In Me.lstProjectBUScope.ItemsSelected
        rst.AddNew
        rst.Fields("BUScopeID").Value = Me.lstProjectBUScope.ItemData(varItem)
        rst.Fields("BUScopeName").Value = Me.ProjectBUScopeName.Value
        rst.Update
        intCount = intCount + 1
    Next varItem

    rst.Close
    Set rst = Nothing

    CreateProjectBURecords = intCount
   
End Function
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Hi Steve,

Is there any way I can send my database so that you can look at it.  I do have some reference errors that I had before which was not causing any problem to store 1 selection only.  But when I adding your code, I am getting compile error.

My database was not made from scratch.  What I did is, I modified an existing database that has similar feature and gradually customizing it to fit our requirement.

Like I told you I am a novice to VBA, although I am able to create simple databases easily where VB is least required but in this case I must need to add the features that requires VBA to load some form and store data.   I am interested to learn but a lot of details is required to learn.

Thanks a lot
Dewan

0
 

Author Comment

by:dewanchoudhury
Comment Utility
Hi Steve,

I haven't heard from you since I placed the above post.

Please help me.

Thanks,
Dewan
0
 

Author Comment

by:dewanchoudhury
Comment Utility
I am trying to open forms using a click event procedure by using the following code but I ma getting following message.

Compile Error:  Invalid Use of Property

What I am trying to is - I have listbox on the form 'Form!Projects' and it has different projects that shows from the table 'Projects'.  I am trying to create a click event that I will use on the 'Form!Projects' and every time I click on each project text items on the list and it loads the respective each project that I click.

Can any one help on the following code review and get it working please.

Thanks in advance
Dewan


Private Sub List431_DblClick(Cancel As Integer)

Dim i As Variant
    Dim stDocName As Form
    Dim stLinkCriteria As String

    stDocName = "Projects"
   
    For Each i In Me.ProjectName
        stLinkCriteria = Form!Projects & Me.ProjectName(i)
        DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.ProjectName
    Next i

Exit_List431_Click:
    Exit Sub

Err_List431_DblClick:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_List431_Click

End Sub
0
 

Author Comment

by:dewanchoudhury
Comment Utility
The Database Engine could not lock table 'switchboard Items' because it is already in use by another person or process
0
 

Author Comment

by:dewanchoudhury
Comment Utility
Can anyone help me about database splitting.  I tried to split the database but I am getting this message.  New Access User....


The Database Engine could not lock table 'switchboard Items' because it is already in use by another person or process.

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now