473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
6 1679
JKing
1,206 Expert 1GB
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
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
1,206 Expert 1GB
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
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
1,206 Expert 1GB
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
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

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

Similar topics

5
by: Dalan | last post by:
I have been searching the archives in an effort to discover how to derive a dual use of a single combo box - so far no go. I found the piece below which pretty much represents the usage. To...
2
by: Jeff Barry | last post by:
Hi, I wonder if any one can help, I'm pretty new to Access and I can't figure out how to change the contents of a combo box based on a selection I make in another. Let me explain I have a...
3
by: Paul | last post by:
I have some option boxes and combo boxes that looks up values on a separate table (i.e. campus table with campusID & campus name fields) When I choose a selection from the combo box, it puts the...
2
by: Mike | last post by:
Hi I have developed an application for data enty for daily production in my factory. I have one combo box for shits and second combo box for the "machine names". the third field on the form...
3
by: Cindi Simonson | last post by:
Hi, I have a form with a combo box containing 4 columns of data. The form also contains 3 print buttons where the goal is to open 3 different reports according to the value in one of the...
4
by: Ziv Riezman | last post by:
Hi All I Have a datagrid on asp.net. i need to add a combo box column that chages it's data according to a spefiiec row in a datagrid. Please help me ?? Thnaks Ziv Riezman
6
by: fieldja | last post by:
I have a form called OwnerForm. It contains a combo box called Owner. The combo box looks up names from a table called OwnerName. It contains fields called OwnerID and Owner. I also have a main...
5
by: a Wellner | last post by:
I am trying to create a from that the user can select a street from a combo box (named street), then pick a second street from another combo box, containing only valid cross streets(named Cross). ...
2
by: ARC | last post by:
I'm testing a user's db that contains a very large number of records. I have an invoice screen, with an invoice select dropdown box that shows all invoices, and the customer's name, etc. With...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.