dewanchoudhury
asked on
Multi Select List Box
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.
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(ctl
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!qBUScopeAppe
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_CreateProjectBURecord
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_CreateProjectBURecord
End Select
End Function
Please if someone can help me with the code.
Thanks in advance.
Dewan -
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.
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(ctl
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!qBUScopeAppe
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_CreateProjectBURecord
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_CreateProjectBURecord
End Select
End Function
Please if someone can help me with the code.
Thanks in advance.
Dewan -
shanesuebsahakarn
Which line gives you the error?
ASKER
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.
ASKER
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
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
Does it give you an option to debug? If it does, when you go into debug, which line is highlighted in yellow?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.ProjectBUScope
FROM tblBUScope INNER JOIN tblProjects ON tblBUScope.ScopeID = tblProjects.ProjectBUScope
Appreciate your suggestion again.
Dewan
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.ProjectBUScope
FROM tblBUScope INNER JOIN tblProjects ON tblBUScope.ScopeID = tblProjects.ProjectBUScope
Appreciate your suggestion again.
Dewan
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("t
For Each varItem In Me.lstProjectBUScope.Items
rst.AddNew
rst.Fields("BUScopeID").Va
rst.Fields("BUScopeName").
rst.Update
intCount = intCount + 1
Next varItem
rst.Close
Set rst = Nothing
CreateProjectBURecords = intCount
End Function
... 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("t
For Each varItem In Me.lstProjectBUScope.Items
rst.AddNew
rst.Fields("BUScopeID").Va
rst.Fields("BUScopeName").
rst.Update
intCount = intCount + 1
Next varItem
rst.Close
Set rst = Nothing
CreateProjectBURecords = intCount
End Function
ASKER
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
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
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
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
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
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
ASKER
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
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
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
"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
ASKER
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
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
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
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
ASKER
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
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
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("t
For Each varItem In Me.lstProjectBUScope.Items
rst.AddNew
rst.Fields("BUScopeID").Va
rst.Fields("BUScopeName").
rst.Update
intCount = intCount + 1
Next varItem
rst.Close
Set rst = Nothing
CreateProjectBURecords = intCount
End Function
Public Function CreateProjectBURecords() As Integer
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim intCount As Integer
Set rst = CurrentDB.OpenRecordset("t
For Each varItem In Me.lstProjectBUScope.Items
rst.AddNew
rst.Fields("BUScopeID").Va
rst.Fields("BUScopeName").
rst.Update
intCount = intCount + 1
Next varItem
rst.Close
Set rst = Nothing
CreateProjectBURecords = intCount
End Function
ASKER
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
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
ASKER
Hi Steve,
I haven't heard from you since I placed the above post.
Please help me.
Thanks,
Dewan
I haven't heard from you since I placed the above post.
Please help me.
Thanks,
Dewan
ASKER
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
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
ASKER
The Database Engine could not lock table 'switchboard Items' because it is already in use by another person or process
ASKER
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.
The Database Engine could not lock table 'switchboard Items' because it is already in use by another person or process.