By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Enabling a combo box if it contains a value from a separate table.

P: 15
I've got a form [OfficerSNCONames] that adds the names of personnel of certain rank to a table. I'm trying to create a form for a certain occassion that has multiple selections from the values entered into that table[OfficerSNCONames] showing who from that table attended. I've got 15 combo boxes that show the names in the table but don't want all 15 to be enabled if only, say 3 people, show up. I can use the .enabled function with a true/false value but not if the value has to be one that is stored in that table. Unless there is a better way to show these individuals that I can select from a list and then make a report for each occassion, this seems like the best way to do it. Any suggestions or help?
Jan 22 '08 #1
Share this Question
Share on Google+
6 Replies


JKing
Expert 100+
P: 1,206
Why not just use a multi select list box? The list box can be populated with all the officer names and the report can be based off the selected items in the listbox.
Jan 22 '08 #2

P: 15
Why not just use a multi select list box? The list box can be populated with all the officer names and the report can be based off the selected items in the listbox.
And that would be accomplished how? Sorry, never heard of it. Is it possible in Access 2003?
Jan 23 '08 #3

JKing
Expert 100+
P: 1,206
This will take a little bit of SQL and VBA.

Let's assume for simplicity the form will only contain a listbox and a command button.

The listbox should be bound to a query that returns all possible attendees and their ID if applicable.

In the list box properties you want to set "Multi Select" to "Extended" this will allow users to select more than one row in the listbox by either shift+click or ctrl+click or a combination of both.

The command button will have an onclick event. This is where you will iterate through the selected items of the listbox to build a criteria string. This string will be used to "filter" your report. This button will also open the report only showing the selected attendees.

Of course you will need to have a query that returns all the information for all possible attendees and you will need to make a report based off that query as well.

Assuming our list box is called lstAttendees and our command button is cmdGenerateReport the VBA would be something like the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGenerateReport_Click()
  2.     'Declare a string variable
  3.     Dim strCriteria As String
  4.  
  5.     'Intialize string variable
  6.     strCriteria = "attendeeID IN ("
  7.  
  8.     'Iterate through selected items in the list box
  9.     For Each varItem In Me.lstAttendees.ItemsSelected
  10.         'Add selected item to the criteria string
  11.         strCriteria = strCriteria & Me.lstAttendees.ItemData(varItem) & ","
  12.     Next varItem
  13.  
  14.     'Trim trailing comma and add closing bracket
  15.     strCriteria = Left(strCriteria, Len(strCriteria) - 1) & ")"
  16.  
  17.     'Open the report
  18.     DoCmd.OpenReport yourReport, acViewPreview, , strCriteria, acWindowNormal
  19.  
  20. End Sub
  21.  
yourReport would be the name of the report you have created
attendeeID would be the field name for bound column of the list box

This is a basic outline of what you would need to do. You should be able to modify this to suit your needs.
Jan 23 '08 #4

P: 15
This will take a little bit of SQL and VBA.

Let's assume for simplicity the form will only contain a listbox and a command button.

The listbox should be bound to a query that returns all possible attendees and their ID if applicable.

In the list box properties you want to set "Multi Select" to "Extended" this will allow users to select more than one row in the listbox by either shift+click or ctrl+click or a combination of both.

The command button will have an onclick event. This is where you will iterate through the selected items of the listbox to build a criteria string. This string will be used to "filter" your report. This button will also open the report only showing the selected attendees.

Of course you will need to have a query that returns all the information for all possible attendees and you will need to make a report based off that query as well.

Assuming our list box is called lstAttendees and our command button is cmdGenerateReport the VBA would be something like the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGenerateReport_Click()
  2.     'Declare a string variable
  3.     Dim strCriteria As String
  4.  
  5.     'Intialize string variable
  6.     strCriteria = "attendeeID IN ("
  7.  
  8.     'Iterate through selected items in the list box
  9.     For Each varItem In Me.lstAttendees.ItemsSelected
  10.         'Add selected item to the criteria string
  11.         strCriteria = strCriteria & Me.lstAttendees.ItemData(varItem) & ","
  12.     Next varItem
  13.  
  14.     'Trim trailing comma and add closing bracket
  15.     strCriteria = Left(strCriteria, Len(strCriteria) - 1) & ")"
  16.  
  17.     'Open the report
  18.     DoCmd.OpenReport yourReport, acViewPreview, , strCriteria, acWindowNormal
  19.  
  20. End Sub
  21.  
yourReport would be the name of the report you have created
attendeeID would be the field name for bound column of the list box

This is a basic outline of what you would need to do. You should be able to modify this to suit your needs.

I've got this all done and it works great, except it doesn't store the names that I select in the list box in each record. I'd like it to be a historical file for each date to keep and be able to print the report if I need to. Hope I'm not being too difficult!
Jan 23 '08 #5

JKing
Expert 100+
P: 1,206
If you want to keep records of attendees for specific events / dates you will need to make a separate table(s) to hold this information.
Jan 23 '08 #6

P: 15
If you want to keep records of attendees for specific events / dates you will need to make a separate table(s) to hold this information.
I've got the table made already but when I select an individual in the list box and go to the next record, the selectees disappear and don't save in the table. I'd also like to see all of the selections show up in the report, not just the bound column. Any way to make that happen? The way it shows up now is rank, last name, first name. In the table the name field is a lookup list box but it only stores the rank since it's the first field, even though the bound column is the last name. How can I get it to show the rank, last name and first name?
Jan 23 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.