Please Help. Question About Filtering On Reports With Combo Boxes, Many Linked Tables

Filtering On Combo Boxes
I have a question about filtering on combo boxes. I was able to set up a form with 2 sub-forms, but it's more than what I need.

What I want it to do, is when the user makes the selection on the first combo box, I want it to filter on that selection and then drop that into the 2nd combo box. When the user makes the selection on teh 2nd combo box, then it should filter the results to a sub-form.

Any help? I have the relationships, and can get it to work when I have 1 form, and 2 sub-forms, but I really want just 1 form, with 1 subform.

View Replies !

---

Filtering Out Items In Combo Boxes
Need some help here....

I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.

Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.

(I have attached a word doc. that shows the table relationships.)


Thanks a lot ahead of time!!
ScrmingWhisprs

View Replies !

---

Filtering Combo Boxes Within A Form
I have a form that we are creating to issue project numbers. We have about 80 different clients. I have set up a combo box for the client name, where we can choose the client from the drop down menu. We want the form to filter according to the client name that you choose. For example, if you pick Lawrence as the client name, the next combo box is the project name and I want it to only show the project names that have been assigned to Lawrence in the past. Can someone please help me on how to do this and if it is even possible at all? Thank you so much for your help!! I have been wrestling with this for about a week and a half now.

View Replies !

---

Combo Boxes Filtering Or Populting Same Records
Anyone help
I have 2 combo boxes cd group and cd name when I select CD group the records only associated yo that cd name display with that group.

Anyway, also the name of songs form works too,. So then now when I go from record to record the records display right with the songs and not with the cd group and name when I select Mormon Tabernacle Chior and name God Bless Ammerica it disaplys for all the records any help.

Why is it the relationships.

Thanks,

Nike Van der stad
mikevds@optonline.net

View Replies !

---

Help Needed Filtering A Form Using Combo Boxes
Please help. My inability to achieve this is very frustrating.

I have a tabular form based on a query, which returns a large number of records. I need users to be able to filter the records to display data relating to what they have entered in a number of combo boxes.

For example I have fields showing Product ID, Manufacturer ID, Purchase Order number and due date. Each of these fields has a corresponding unbound combo box where users can select values that appear in the table. I need to be able to filter the form based on what is in these combo boxes. I.e. Filler for a product ID to show all purchase orders numbers and due dates relating to that product ID or filter for a manufacturer ID and due date to show all Product IDs and purchase orders relating to that a manufacturer and date.

Ideally this would be achieved by pressing a command button.

The query, table and combo boxes are all built and work fine but I am unable to get the filter to work, I am using Access 97 and am not very familiar with visual basic. Any help would be appreciated.

Thanks.

View Replies !

---

Filtering With 2 Combo Boxes And Then Populating Text/memo Fields
I have two questions.
I have a form with 2 combo boxes. The first pulls from a simple list. Once you make a choice, the second combo box is then filtered by the first. This works correct, except that once a choice is made in the first, the filter is locked. If you change the first combo box's value a second time it does not effect the second box. The query that I am using for the second combo box has two columns that pull the first 50 characters of a pair of memo fields. Quote: SELECT MSSS.SS_ID, Left([Application_Name],50) AS Expr1, Left([Description],50) AS Expr2, MSSS.Site_Code
FROM MSSS
WHERE (((MSSS.Site_Code)=[Forms]![Edit or Delete Requests]![Combo6])); These fields are then used to populate 2 text fields using an event procedure. Quote: Private Sub Combo10_AfterUpdate()
' Display Partial Application Name and Description based on choice
Me!txtApplication_Name = Me!Combo10.Column(1)
Me!txtDescription = Me!Combo10.Column(2)
End Sub This is working fine, but it starts the next problem.

I need to change the form to include the entire memo field into a text box. I was using the columns of the second combo box to fill them in. Since the combo box is limited to only 50 characters, i could only grab part of it. I am guessing there is a much better way to accomplish what I need, but I am too inexperienced with forms and vb to come up with it. Below are the fields I need from table MSSS
Business_Name
Application_Name
Description
Acronym
Level_1_Support
Level_2_Support
Escalation_process
Troubleshooting
Priority
Links
Modified
Disabled

Thanks in advance to any help!

View Replies !

---

Combo Boxes And Looking Up Tables
Hello All,

I am a new Access user, and I am trying to create a database that will store and track print music.

I want to be able to track data by a text box called "Item Number". The item number is obtained automatically when the user inputs data from two different Combo Boxes.

"cmboInstrument" and "cmboMusicType"

Both these combo boxes look up separate tables which have in them two columns each
1. Instrument (Eg. Bass Guitar)
2. Code Number (Eg.0012)

The combo boxes look up the tables fine.

My problem is, that I want the txt box to display the data from both combo boxes (Eg: an eight digit number 01002111). I then also want the text box to generate a Item number starting from 0000.
So all in all the Item Number after selections have been made will display 010110000000 (an ID number example).

The numbers generated need to be displayed in the table under Item Number so that they can be associated with the product, therefore I assume they need to be bound and I am currently using the code

"Private Sub cmboMusicType_AfterUpdate()
Me.txtItemNumber.Value = Me.cmboMusicType.Column(1)
End Sub"

Though this only works for one combo box, if I put it in another combo box - the numbers will not join together.

Please Help!!!!

View Replies !

---

Combo Boxes To Filter N Show Tables
Im kinda new to Access and only been using the "Access 2003 for Dummies" for learning and making small databases. So far I can find out how to do the things I want, but recently got stuck with a combo box feature. What im trying to do is select entries in my combo box so that it will filter the table and show all its contents based on that filter. Im not sure how to link the combo boxes the right way and it seems some VB coding is needed which I dont normally use. If anyone has any idea how to do it, tell me how! Thanks. BTW im using Access 97.

View Replies !

---

Filtering - By Option Boxes Multiple
I am trying to design a form which will provide my users multiple reporting options using the same query.

my query contains the following fields:

Daycode
WeekNumber
PeriodNumber
Line
Machine
Eventcode
TotalTime
Occur
Avg

i want the user to choose in this order :

a time period to run the report.....either Daycode, WeekNumber,PeriodNumber

then select:

a Line,

then select either Totaltime, Occur or Avg (this will require to be sorted Desecnding..

then finally to decide wheter to have all the data remaining, or sorted by machine.

DB is here--------> Click here

View Replies !

---

Clearing Text Boxes/combo Boxes?
Another quick request:

What would the code be for a button which clears the contents of a text box or a combo box on a form?

Many thanks,

Paul.

View Replies !

---

Filtering Reports
hi

I've made a form with companies. There's a linked sub-formed with orders for each company. These forms work perfectly using a combo box to determine each company. I've also made a report to print each company's orders. It's loaded by a command button , but the problem is that I'd like to open the report, filtered in such way to show only the orders from the company selected in the combobox of the main form. What is the code I have to use and where, in order to achieve this? In other words, how can I use the value of a combo box in a form in order to show specific records in a report? Thank you in advance

View Replies !

---

List Box Of Reports - Filtering Result
http://allenbrowne.com/ser-19.html

I use the call back function to generate my list of reports in a list box.

I chose to use this method as it seems like an easy way to do what I want, and to a certain extent understand how it works.

What I would really like to be able to do, is have it filter the list of reports.

For example, some reports are for me working behind the scenes, and also so many are subreports that go in to the main report so dont need listing.

I would like to have a report names 'Clients Full Report', 'Clients Medication Report' etc on the list (I dont mind changing report names as I think that will be the key to making it work) So many of my sub reports have client in its name, so if a report could be named '1Clients Main Report' and the code looks for the number 1 and removes the 1 from the list so it displays Clients Main Report on the list.

So its just a way to make the list box only include specific reports based on a number.

Any suggestions?

View Replies !

---

Combo Boxes / Text Boxes
Hi guys, I have a related databes with about 20 tables.

My main table stores the data linking with most of the other tables. This main table stores football match records with player line ups. Initially I had a problem linking the 11 player fields in the main table to the player table, Icould only do it with 1, so someone at work suggested to link it using the lookup function. This worked brilliantly. However, now I am designing a GUI with forms but the forms have combo boxes where the lookup function was used and when i change these to text boxes, the players names are replaced with their ID numbers.

Is there any way of creating the form without the unsightly combo boxes, as they won't be needed, the GUI will be read-only. I look forward to your help!!

View Replies !

---

Combo Boxes And Text Boxes
I have a table that has client names and addresses. I have designed a form to be able to invoice these clients and everything is fine however what I'd like to do is have the address of the client appear automatically.

I have set the client names in a combo box and would like their address to appear in either a text box or sunken label automatically from the table. Is this possible and how do I do it.

Please keep in mind that I've done basic programming so please be kind to this newbie :D

View Replies !

---

List Boxes And Combo Boxes
Hi everyone,
I have been making progress with customizing a
MS Access program, but one major problem is that
I have been trying to make a List Box or Combo Box
that I can use to enter data in the TABLE, but I
find that I get a pull-down list that has the list of
values from only the parameter that is primary key.
More specifically, the program is set up as follows:
I have two tables in this program: One that is called
"invoices" and one that is called "items". In both of
these tables, there is one common parameter, which
is "Item Number". Item number is the primary key,
and I used the "relationships" function to tie this
parameter to itself between the two tables.
I was successful in setting up a list-box for the
"Item Number", but when I try to set up a list-box
for another parameter that is supposed to display
a person's initials, the pull-down list displays the list
of item numbers instead of the list of people's initials.
In fact, I don't know if there is an extra step I need to
take so that the database stores a list of people's initials.
Instead, I just fill in the initials in the field for each
record for which the "Item Number" is the primary key.
How can I get the list-box to pull down a selection of
different people's initials, or in other words how can I get
all the people's initials to be stored so that the list of
initials can be looked up. Just so you know, I have tried
different choices of entries in the "Lookup" tab in the
Design mode of Tables, including Display Control,
Row Source Type, Row Source, and Bound Column,
but the outcome is that the only parameter that I
get get in the pull-down list is the primary key, which
is Item Number.
I appreciate any help you can offer in explaining how
to correct this.
On a separate note, one of the parameters is "Date", and
on the reports, I'm trying to figure out how to filter a
specific date range so that I can limit each report to a
specific month. Please advise me on this procedure as well.
Thanks.

View Replies !

---

Filtering On Combo Text
Aaaargh! It's a couple of years since I got my hands dirty in Access and I seem to have forgotten more than I thought! :(
I have a simple database which includes tables STOPS (stopID, stop name, routeID, Cost band), JOURNEY(CustID, routeID, stopID), and ROUTES (routeID, Route name, Bus co).
I have a main form with the customer number on linking to a sub-form on which the user will select the route from a combo box and then the stop from a combo box. All works fine but I want to filter the stop combo box to show only stops on the route already chosen.
I've done similar before but I have tried all sorts and failed to get anything to work. Any help appreciated.

View Replies !

---

Filtering Employee Name From Combo Box.
Hi all,

My database is like 1 Training course with many of the employee. My Training course are using the reference no. as the ID of my training.

Now I have a form that have a reference no. as my first combo box and all the employee name are in my second combo box to let the user select and add employee to the training.

So now what i need to do is how to filter out the employee that had already been added to the specify training? For example now user want to select a training 'A' from combo box with only 10 employees (out of 50) are involve in the training.

And now when user select a employee and click save then the employee name will be disappear from the combo box when the second time user want to select another name. So do someone know how to do the filtering of the existing employee in the training?

Thanks alot@@!!

Regard,
alex

View Replies !

---

Complex Combo Box Filtering
Ok, so I invoke your help once again.

Here's the situation: I have a list of Crew Leaders. Each crew leader has the option of being the leader of up to three different types of crews, which is determined by checkboxes. In a separate form, I would like to do the following:

The first combo box contains a list of the three types of crews. Once an option is selected, I would like only the Crew Leaders' names corresponding to that selection to show up in a second combo box. However, they are not ONLY part of this selection, necessarily. They may also be a part of a different selection, as they may be the leaders of one, two, or all three types of crews. The third combo box I think I can figure out (the first box filters different services that the crew can provide, but it is irrespective of the name of the Crew Leader, so the services are crew TYPE specific, not CREW specific).

I've done it where the first box filters the second box, but only when the selection in the first determines an exclusive set in the second. What I'm trying to do is have the first box create a non-exclusive set in the second box.

Ex: Crew Type: A
Crew Leader (Selection pool): Joe, Bob, Jim, Jerry, Phil, Mark

Crew Type: B
Crew Leader (Selection pool): John, Bob, Jim, Mike, Barry, Dan, Mark

Crew Type: C
Crew Leader (Selection pool): Rob, Jack, Jim, Joe, John, Mark

Anyone have any ideas? So far you guys have been amazing helps.

Thanks in advance!
-Jason

View Replies !

---

Need Help On Filtering Records Using Combo Or List Box
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.

I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.

First Approach:
I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria.
This is the code I used:
__________________________________________________ _______________
Private Sub Preview_Click()

DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]"

End Sub
__________________________________________________ _______________
But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?

Second Approach:
On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.

This is the code I followed:
__________________________________________________ _______________
Private Sub btnClear_Click()
Dim intIndex As Integer
Me.cmbCountry = 0

End Sub

Private Sub btnsearch_Click()
Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter

Me.frmqrybyCountry1.Requery

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter


'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
__________________________________________________ _______________
After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.

Please help me, I would really want to figure this problem out. Thank you so much.

View Replies !

---

Combo Box Subform Filtering Problem
Hi all

I have been using some code from this site to filter my forms and subforms via a selection of combo boxes. However, when I try to filter by either date or callid, I get runtime error 2001 and the debugger drops in at the first forms!filter line. This seems to work perfectly well for the other 3 combos that I use. Is it to do with the type of fields I am trying to filter with or am I missing something integral to the code? Each combo is named Filter1, Filter2, etc and the name of the field to filter by is placed in the tag line for the corresponding combo box.

Private Sub Command13_Click()

Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.Filter = strSQL
forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.FilterOn = True
End If
forms!frmoutershell.frminnershell.Form.lblfilter.C aption = strSQL
End Sub

Any help would be appreciated as I am getting quite frustrated with it only working on certain parts of the form.

Regards

Jason

View Replies !

---

Drop Down Boxes In Form Linked?
I have a form ive created as part of my database used for data input.

Someone opens the form , enters their project number, and then enters what country it is in, what region it is in, and what sub region it is in. They are all dropdown menus with the information coming from a table via a lookup wizard.

What i want is when someone choses england in the country field, i want the region field to only displays the regions in england, and then in the sub region field i want only the sub regions that are in the regions displayed in the drop down list.

Anyone know how i can achieve this please?

I have attached a copy of the forms and tables

View Replies !

---

Text Boxes Linked To The Same Field
Is it possible to link multiple text boxes to the same field in a table

For example allowing three boxes for the entry of an address however it is all copied into the one field in the database?

Thanks for the help

View Replies !

---

Filtering On A Combo Box Based Off A Autopopulate Field
Hi, i've searched the forums but couldn't find an issue/resolution that fit my problem. I tried to put as much info as needed but please let me know if more info is needed.

I've set up one of my forms that require a signature with a combo box "CurrentUserName" to capture an electronic signature from a jpeg stored on the local server.

The CurrentUserNamecombo box has a one-to-many relationship between another table named "Electronic Signature" that holds the user's network ID and the Path on the server where the signature file is stored. The primary key in the second table is also named "CurrentUserName". This works perfectly, i select a username from the drop down list on the form and the picture atumomatically populates an unbound image field with the image.

The issue is, i only want the current user to be able to select their own username from the combo box on the form and hide the other usernames so i need the CurrentuserName combo box to be filtered on the form.

So what i've done is create another field named "CaptureUser" that will automatically capture the current user's network user ID using the =CurrentUser () as the defualt value of that field once the form is opened. What i need to do now is connect the two fields so for instance, if current username "jblow" is in the CaptureUser field, the CurrentUserName combo box will check the CaptureUser field and filter the choices so his name will only appear in the CurrentUserName combo box.

Hope that made sense, any help is greatly appreciated!

View Replies !

---