Filtering On Combo Text

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 !

---

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 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 !

---

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 !

---

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 !

---

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 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 !

---

Text Box Filtering A List Box //?
Already have a listbox that allows you to click on it- and the record is displayed!

What I am trying to do is create a textbox above this listbox that will sort as I type in it.



Example: LISTBOX Contains (4) records
--------------
Ghostbusters
Gone Wild
Gone with the Wind
Oceans Eleven

I type ""Go"" in the textbox above

My LISTBOX now contains (2) records
---------
Gone Wild
Gone with the Wind

How can this be done!
<do I have to make my listbox run off a query??>

Apreciate the help! Thanks!
Love this Forum! Thank YOu all for all the Help! Hope I can contribut to the ASP form soon!

View Replies !

---

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

I hope someone can help me.

I am working on a Marketing Database to centralise information about our customers so that field sales can search customers that would fit a similar sales situation, by vertical market, by product sold, by competitive products etc.

I have created many tables and linked them together:
The main table is Customer Accounts (tblAccounts) and contains information such as:
AcntID (AutoNumber)
AcntName (text, Primary Key)
NAMID (number), relates to Account Manager table (tblNAMs)
SalesRegionID (number), relates to Sales region table (tblRegion)
PartnerID (number) relates to Partner table (tblPartners)
AcntCustomerSince (number)

tblNAMs contains:
NAMID (AutoNumber)
NAMName (Text, Primary Key)

tblPartners contains:
PartnerID (AutoNumber)
PartnerName (text, Primary Key)

tblRegion contains:
RegionID (AutoNumber)
RegionName (text, Primary Key)

Those last 3 table relate to the tblAcnt by intermediate relationships

I have also the following tables:

tblVendor contains:
VendorID (AutoNumber)
VendorName (Text)

tblProducts contains:
ProductID (autoNumber)
ProductName (text)
VendorID (Number)

tblToolsBfr contains:
ToolsBfrID (AutoNumber)
AcntID (Number, Primary Key)
VendorID (Number, Primary Key)
ProductID (Number, Primary Key)

tblToolsSold contains:
ToolsSoldID (AutoNumber)
AcntID (Number, Primary Key)
VendorID (Number, Primary Key)

tblToolsAftr contains:
ToolsAftrID (AutoNumber)
AcntID (Number, Primary Key)
VendorID (Number, Primary Key)

tblVendors and tblProducts are linked to tblToolsSold, tblToolsBfr, tblToolsAftr. Those last 3 tables are linked to the tblAcnt via the AcntID link

I have created a report that can show all this information on one page, per account.

I have also created a filter using combo boxes to allow me to drill down to Sales Region and/or Account Manager and/or Partner.

I am struggling with the rest of the filter as I would like to also filter what can be found in the tblToolsBfr, tblToolsSold and tblToolsAftr but because Vendors and Products are located in two different tables, I do not know what I should put in Row Source or as an event procedure or query.

Please can you help me, perhaps there is an example I could use?

I really hope someone can help as I am completely stuck on this for the past 3 days.

Valibal

View Replies !

---

Design Problem - Filtering Combo Box Items Based On Related Table Field
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:

Categories Table:
CategoryID (PK)
Description


Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)


Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)


JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)

Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:

SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));

All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?

Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).

Thanks in advance,

Chris

View Replies !

---

Combo Box And Text Box Help.
Hi,

I have a combo box on a form with three values, when I select one of the values I want a text box to show a corresponding value.

The combo box has three values for different types of memberships; under18, over60, and standard, these have different prices. So what I want is for when one of the values is selected the text box will show the price.

Any help on how to do this? The database is below.

View Replies !

---

Amend Top Value Via Text Or Combo Box
Is there a way whereby I, or another user, can amend the ‘TOP’ value of a query via a Combo or Text box entry on a form? I have searched this Forum for a solution to this very problem but have had no success. I understand how to achieve this via the queries ‘SQL’ view but cant figure out a way to do it by the way that I have already described!!

Your advice or a ‘pointer’ in the right direction would be extremely well received.

Thanks for your time.

Have a nice day...

Kind Regards

CarolW

View Replies !

---

Text Box Bound To A Combo Box
Hi,

This one is really confusing me!

I have a text box on a form that is bound to a field in a table that is populated by a combo box coded into the table whose data source is a select query.

Everything looks fine in the table, but when I display the data on the form it is displaying a different field of the source select query than the one displayed on the table.

Why is this, and how can I fix it?

View Replies !

---

Synchronize A Combo-box And A Text Box
I've a form with a combo box "OrderNumber" (to allow the user to select the correct order) and a text box "Date" which displays the date of the order selected.
The problem is that I don't know how to synchronize the boxes.
I know the procedure to do it with two combo-boxes (cmboDate=Null & cboDate.Requery in the after update property of the cbo "Order Number"), but is it possible that when I change the order selected in the combo-box, the text box shows the correct date?
Thanks for your help!

View Replies !

---

Lookup Using Combo And Text Box
Hey guys

This is the problem,

I have 2 fields in a table. Cityand Country.

City Country

paris.......France
madrid....Spain
london....England
milan.....Italy
Rome.....Italy
ETC

Field "City" is connected to a combo box and field "country" to a textbox on the form.

I would like the user to select a city from the combo box. When this is done the textbox linked to the country field should automatically update to show the correct country. This should update without user intervention.

I would find it better to have the text box look up a value list rather than use a table for its source. the form is being used as data entry. The combobox linked to field "city" is also a value list.

Thanks in advance.
Nitesh

View Replies !

---

Remove Text From Combo Box
I have a combo box that I use a barcode scanner with. I scan the barcode and it finds the product using the code below:

Private Sub cmbBarCode_Click()
' ADD A TRANSACTION AUTOMATICALLY

Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
RS.Open "SELECT * from tblTransactions", Application.CurrentProject.Connection, adOpenStatic, adLockOptimistic
RS.AddNew
RS!OrderID = ID
RS!Barcode = cmbBarcode.Column(0)
RS!Manufacturer = cmbBarcode.Column(1)
RS!ProductName = cmbBarcode.Column(2)
RS!QuantitySold = -1
RS!Cost = cmbBarcode.Column(4)
RS.Update
RS.Close
Set RS = Nothing
lstTransactions.Requery

End Sub

What I need to happen is all the above but after it has finished or before it starts it needs to clear the contents of the box. So the barcode is entered it runs the above script and then removes the barcode from the combo box, so I am ready to scan the next barcode.

Thanks,
Danian

View Replies !

---

Default Value In Text Or Combo Box
I am trying to set the result of a query as the default value for a text or combo box. I have tried setting the query as the default value in the box's property. I have also tried doing it in VB. The code looks like this

Dim SQL AS String

SQL = "SELECT Address
FROM Table1
WHERE Name = Forms!Main_frm!name_lbx.Value;"

Forms!Address_frm!address_cbx.DefaultValue = SQL

I have also tried

Dim SQL AS String

SQL = DoCmd.OpenQuery([update address_qry])

Forms!Address_frm!address_cbx.Value = SQL

Where update address_qry is the same as the above.

I keep both forms. I know how to do it by setting the queries as values in a list box, then transfering the values to the text or combo boxes. But I was hoping there was an easier way.

Thanks
Alex

View Replies !

---

Default Value In Text Or Combo Box
I am trying to set the result of a query as the default value for a text or combo box. I have tried setting the query as the default value in the box's property. I have also tried doing it in VB. The code looks like this

Dim SQL AS String

SQL = "SELECT Address
FROM Table1
WHERE Name = Forms!Main_frm!name_lbx.Value;"

Forms!Address_frm!address_cbx.DefaultValue = SQL

I have also tried

Dim SQL AS String

SQL = DoCmd.OpenQuery([update address_qry])

Forms!Address_frm!address_cbx.Value = SQL
Where update address_qry is the same as the above.

I keep both forms open. I know how to do it by setting the queries as values in a list box, then transfering the values to the text or combo boxes. But I was hoping there was an easier way.

Thanks
Alex

View Replies !

---

Combo Box Dependant On Text Box
Hi

I would like to know how can i reference a combo box to the value of a text box on the form it is for a purchasing system.

if i select product one i only want the the order quantity for that item to show and the same with the price field as all the reorder levels and cost information is stored in a table. i have created queries only selecting the product code and reorder quantity and the product code and the cost price.

hope someone will be able to help

regards
melanie

View Replies !

---

Combo Box Fill In Text Box..BUT!
I know how to base the combo box on a query, and in the after update enter

Me.[txtboxName]= Me.[cboName].Column(1)

BUT...

I want to bound the combo box to a control source.

How can I achieve that?

Thanks~

View Replies !

---

Text Box Based On Combo
I have searched and looked for this throughout the forum but can't find it.
I have a simple text box which I want to populate based on a combo box. The combo needs to display the table's field name and the text box needs to display the record. Is this possible?
Cheers,
Rene

View Replies !

---