Solved

Multi Select List Box

Posted on 2004-09-22
22
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
22 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12126056
Which line gives you the error?
0
 

Author Comment

by:dewanchoudhury
ID: 12126138
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
ID: 12126156
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12126167
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
ID: 12126238
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
ID: 12126857
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
ID: 12128155
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
ID: 12134346
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
ID: 12141068
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
ID: 12144410
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12154173
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
ID: 12160640
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
ID: 12164293
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
ID: 12164478
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
ID: 12167481
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
ID: 12174757
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
ID: 12182347
Hi Steve,

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

Please help me.

Thanks,
Dewan
0
 

Author Comment

by:dewanchoudhury
ID: 12300876
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
ID: 12373153
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
ID: 12373165
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 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