I have a database which is used to search for products by its specifications, i have set up cascading combo boxes to work in the following order.
Profile
Thickness
Width
etc.
So the current row source of one of the Combo boxes is SELECT DISTINCT [tbltestv2].[Thickness] FROM tbltestv2 WHERE ((([tbltestv2].[profile])=(Forms]![Run ratev2]!cboprofile]))
This basically means that it will search for whatever thicknesses are available in the selected profile.This is the one that works fine although it is not sorted numerically. I.e 1, 1.2, 12, 2, 2.5 where I want it as 1, 1.2, 2, 2.5, 12
From here I amended the order by so that it will sort it numerically, In the row source I had the following expression.SELECT [tbltestv2].[Thickness] FROM tbltestv2 WHERE ((([tbltestv2].[Profile])=[Forms]![run ratev2]![cboprofile])) ORDER BY Val([Thickness]), IIf(Val(Right$([Thickness],1)) =0,Right$ ([Thickness ],1),""); The issue with this one is that it shows duplicate data for instance 1, 1, 1, 1.2, 1.2, 2.5, 2.5, 12, 12, 12
I know that if the beginning of the expression is SELECT DISTINCT it will eliminate this however, the SELECT DISTINCT is conflicting with the ORDER BY and it will not let me do it.
I have a table [Employees] that has the field [FirstName] and [LastName]. On my form [Jobs], I have a combobox [ComboWho]. The combo box has all of the possible first names. When a first name is picked, it makes the last name fill in a textbox.
In the real version, it is a [LastName] is a lot of information, so it is kept in a memo field. I tried to use cascading combo boxes, but it cuts off my text.
I keep getting it to almost work... I've tried about 5 different methods... DLookUp, subform, etc.
Right now, I'm using this code:
Private Sub ComboWho_Change() Dim location As String Me.ComboWho.SetFocus location = DLookup("LastName", "Employee", "FirstName = '" & Me.ComboWho.SelText & "'") Me.MyTextBox.SetFocus Me.MyTextBox.Text = location End Sub
It has properly filled in the textbox. I thought it was working perfectly, but ran into one snag... We have been using a split form... and it won't let me filter that column (Column ComboWho). I think because it is saving everything as numbers instead of text.
ComboWhoDatabase.accdbI've attached a copy of the simplified database.
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
i have a simple datsbase with 2 fields - drawing number and description. the drawing numbers are in the formatt STD123 e.t.c. When I try to sort numerically the order ends up as STD1 STD10 STD11 STD100 STD101.......... How do I get it to list in true numeric order in report and table view i.e. STD1 STD2 STD3.......... Please bear in mind that my knowledge of Access is VERY limited. I've tried a search and don't understand about queries and such like. Even so I'd have thought that even the most basic database programmes would be able to handle something as simple as an alpha/numeric sort list....seems not!!!!! ????????????????
I have a form with 4 Combo boxes bound to fields in 4 tables;
1) Oblast Name 2) District Name 3) Jamoat Name 4) Village Name
The selections work fine. Cascade works from Oblast to Village Name. My question is ;
Currently the ID's of the bound columns are stored in the tables instead of the corresponding data. For example for the Oblast name; I have the value "3"=" rather than "Khatlon" for District_Name, I have "223", rather than "Muminobad".
Is there way to capture the names of the respective places rather than storing ID's in the table?
I am trying to figure out how to sort a column in access the same way it comes out in excel. I have a text list of "buildings" ie: 89, 33, 270C, 270B, 270A etc. In excel this simply sorts alphanumerically ie 33, 89, 270A, 270B, 270C.
I made the mistake of changing the format from text to number, lost all the data and successfully scared myself into not wanting to experiment anymore so any input I could get would be very appreciated
This is driving me a little mad, and its probably something quite simple!
I am creating a form which will allow users to narrow down their search based on criteria entered into three combo boxes.
The first combo box pop's up a calendar and is populated when the date is chosen on the calendar. The second combo box will provide a list of systems that have a record entered on that date The third combo box will then show a list of case id's based on the system and date selected.
I can get it working when ignoring the date, but as soon as I bring the date combo into the equation I get a data mismatch error. Now I presume that this is to do with holding a date in a text field and comparing with a date/time field in the table, so I have set both to 'short date', but still get the error.
Can anyone provide any pearls of wisdom to help stop me from going insane? (that might be a bit late tho!)
I have a database (Access 2007) that has two tables, reports, queries and so far all seems to be working and going well. Quite impressed with it to be honest for my lack of skills.
However, I've run into a bit of trouble and need assistance getting a combo box to work with another combo box to produce list kind of thing.
The database is a vehicle database and I need the 'Vehicle' combo box to give me a list of vehicles. I then need the 'Model' combo box to produce the models of the car selected in the 'Vehicle' combo box.
Example - If I select 'Ford' from the 'Vehicle' combo box list I want all the models to be displayed in a list form thing in the 'Model' combo box so the user can easily select make and model of the car.
I have two tables: CarMM VD
The 'CarMM' table contains a list of around 1,500 records of car makes and models. First column has the 'Vehicle' make and the second field coloumn has the 'Model'.
The 'VD' table contains all other information like the charges, name, the make and model of car once selected in combo box.
I then have the form called 'VD'. In this form contains the combo boxes I want to be cascading.
Any advice on how to do this? I'm quite new to this so any help would be nice. Thanks you very much.
I need some help setting up my tables to allow cascading combo boxes. I need to document when a Pharmacy IV room and chemotherapy room are cleaned. Each Room requires different cleaning procedures.
Here is my table structure: TblCleaningDuties DutyID-Auto# (PK) Duty - text (Example- mop floor, clean hood, wash ceiling, wash sink) Frequency-text
If the user selects Clean Room I don't want them to see wash sink and anything else not pertinent to the Clean Room. How do I set up the tables? Thanks, Jason
Just wondering if anyone can help Im creating two combo boxes where the first one determines the values in the second one.
The tables are made up of the following;
Line
Line number (PK) ..... ....
Main Setting
Part Number (PK) Press Line (FK)
Now ive took the advice of previous threads but im having the following error message when im creating this;
The record source 'SELECT [Main Settings].[Part Number] FROM Main AS Settings WHERE [Main Settings].[Press Line]=[Forms]![Example]![Line]' specified on this form does not exist.
I know that this doesnt exist but im typing it correctly. Access seems to be changing my statement i.e. inserting the AS command. Any help will be much appreciated
I have an unbound Form with a Subform bound to a query. The query parameters are selected by the users via Unbound cboboxes. The form is working but I would like to add a feature on each cbobox that would allow to show "All" the data (ie:Show the data like if there was no parameter on that field). I can't see how to add this feature, is anybody got any idea?
Right I have followed one of the sample databases and modified it to create cascading combo boxes like i needed.
Now all i need to do is use them in the context i need.
Basically I have country, region and sub region. These cascade to each other in the form which has been created. If you select a country, then in region you in only see the regions in that country, etc.
What i need is to be able to use the cascading combos boxes in conjunction with the "Location table". So someone can put in a project number and then use the cascading combo boxes to to chose the country, region and sub region boxes saving all of the data into the location table .
Please can someone help, as it took me allmost three hours to create the cascading combo boxes but now i cant even use them for what i wanted them for,.
I have trawled this forum searching for help with cascading combo boxes, it was very helpful but alas I need that bit more.
I have 2 combo boxes and the result is displayed in a subform. The problem is that it will only display one selection made from the Combo boxes and I need multiple selections on display in the record.
It is a parts job worksheet in a garage where multiple work is required on a vehicle. It is based on Work Type and Work Codes which then bring up the detailed description.
The first Combo Box is Work Type and this allows you to select letters. The second Combo Box is Work Code and this allows you to select numbers.
I used the example I found on here but haven't changed the field names as I daren't alter the coding until I know it will display multiple selections in the subform of the record.
I attach the database to see of anyone can help me, ppllleeeaaassssee. I woul dbe most grateful if you could make the changes and zip it back up as I ain't no VB programmer.
Hi folks, this may seem like a silly question... but I'm rather new at this...
I have set up 4 Cascading combo boxes on a form, as per the fantastic little tutorial by SJ McAbney... They work great...
What I want them to do is output the combo box selections back to a table. I have been able to get it to do this, but all it's putting back in the table is the ID number of the combo box selection. I need to get the text there instead. I experimented with switching the column order on the form, so the text is before the ID... and that worked with the output, but messed up a number of other things.
i currently have 4 combo boxes that each has its own table, and setup as cascading. Now what i want to add is an <All> in each combo to box to give any results in that table.
i have
combo 1 SELECT [tblProductManufacturer].[ProductManufacturerID], [tblProductManufacturer].[ProductManufacturer] FROM tblProductManufacturer UNION Select "<All>", "<All>" as Bogus From tblProductManufacturer ORDER BY [tblProductManufacturer].[ProductManufacturer];
combo 2 SELECT tblProductModel.ProductModel, tblProductModel.ProductModelID FROM tblProductModel WHERE (((tblProductModel.ProductManufacturerID)=[Forms]![MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "<All>", "<All>" as Bogus From tblProductModel ORDER BY tblProductModel.ProductModelID;
combo 3 SELECT [tblProductVersion].[ProductVersion], [tblProductVersion].[ProductVersionID] FROM tblProductVersion WHERE ((([tblProductVersion].[ProductModelID])=[Forms]![MainScreen]![MainWindow].Form.CboProductModel)) UNION Select "<All>", "<All>" as Bogus From tblProductVersion ORDER BY [tblProductVersion].[ProductVersionID];
combo 4 SELECT [tblProductSize].[ProductSize], [tblProductSize].[ProductSizeID] FROM tblProductSize WHERE ((([tblProductSize].[ProductVersionID])=[Forms]![MainScreen]![MainWindow].Form.CboProductVersion)) UNION Select "<All>", "<All>" as Bogus From tblProductSize ORDER BY [tblProductSize].[ProductSizeID];
in place of UNION Select "<All>", "<All>" i have also tried
UNION Select Null as AllChoice, "(All)" as Bogus
And in place of UNION Select "<All>", "<All>" as Bogus i have also tried
UNION select distinct null, null (And added distinct after the first Select of the query)
I'm having a problem with a form used to display table records as well as create a new record entry.
The form has 2 combo boxes and the rest are fields.
Combo1: Artist Combo2: Gallery
I would like it so that when the form opens and displays the first record it will also populate the two combo's with the correct details for that record. Secondly when I click to create a new record it will populate the first combo with all artists but then the second combo will populate with multiple galleries (I think because that artist might have done work with multiple galleries).
I'm a little stuck so any help would be great at this point!
Im using the code from an 'older' example posted here ages ago and it works fine on other databases when there are only 3 combo boxes. This time I'm using on a total of 5.
The problem is the 3rd, 4th and 5th boxes will not requery to a null value, I've tried using DoCmd.Requery "cbo_whatever" instead of Me.cbo_whatever.Requery in case that makes a difference (as you guessed I'm not much of a code jockey :p)
So am I being a bit thick, why doesn't this work ? I've had a search on the forum and have found many examples (obviously) of cascading box examples etc. but I'm comfortable using this example and can figure out what's wrong.
Private Sub cbo_region_AfterUpdate()
If IsNull(Me.cbo_region) Then Me.cbo_city = Null Me.cbo_city.Enabled = False Me.cbo_city.Locked = True Else Me.cbo_city.Enabled = True Me.cbo_city.Locked = False Me.cbo_city.Requery End If
End Sub
Private Sub cbo_city_Afterupdate()
If IsNull(Me.cbo_city) Then Me.cbo_building = Null Me.cbo_building.Enabled = False Me.cbo_building.Locked = True Else Me.cbo_building.Enabled = True Me.cbo_building.Locked = False Me.cbo_building.Requery End If
End Sub
Private Sub cbo_building_AfterUpdate()
If IsNull(Me.cbo_building) Then Me.cbo_floor = Null Me.cbo_floor.Enabled = False Me.cbo_floor.Locked = True Else Me.cbo_floor.Enabled = True Me.cbo_floor.Locked = False Me.cbo_floor.Requery End If
End Sub
Private Sub cbo_floor_AfterUpdate()
If IsNull(Me.cbo_floor) Then Me.cbo_room = Null Me.cbo_room.Enabled = False Me.cbo_room.Locked = True Else Me.cbo_room.Enabled = True Me.cbo_room.Locked = False Me.cbo_room.Requery End If
I am using a form with 3 combo boxes name, weight and grade. The combo boxes are cascading:
A name is selected in the first combo box, since each name has different weights attached to it only the weights associated with that name are selected when the weight combo box is clicked. In a similar vein, there are only certian grades associated with a certain weight.
The effect is to filter the results so that an invalid entry cannot occur.
My problem is that once an entry is made and I select a new name for the next entry, the weight and grade boxes remain with the same values instead of updating to new values. Updating of the boxes does not appear to be working.
Does anyone have any ideas how to solve this problem?
I have 4 Combo Boxes on my form (cmbo1, cmbo2, cmbo3, cmbo4). I have successfully managed to get the cascading down as long as the user starts with cmbo1.
What I would like to be able to do is have the user select any combo box on the form and have the rest of the boxes filter automatically, e.g., a user selects a value in cmbo2, the afterupdate would then filter/lookup the values of cmbo1, cmbo3, cmbo4 based on the value selected in cmbo2.
Is this possible and if so, does anyone know how to do this?
Hello to everybody! I need a big help to solve a problem with my DB Access. I created a form in a DB where I have to fill some information and store them in a table. I created the cascading combo box. It works, infact when I select the value in the first Combo box named NOME_PROVIDER the second combo named NUM_MAM comes populated with the value got from a query. Anyhow it works well.
THE PROBLEM right now is that I can only select the first value of NOME_PROVIDER and not all the other. I'm sure that the selection query works, but I'm not able to select the other values. Maybe could be a focus problem or something like that.
PLEASE CAn you give me an help on this matter? Thanks in advance to everybody!!! Cheers, Antonio.
I have 2 Cascading Combo boxes setup and working correctly.cbo1 lists all agents and cbo2 lists the branches that relate to each agent.tbl_agents and tbl_branches are setup with a 1 to many relationship.I have used the code (explained here (http://www.fontstuff.com/access/acctut20.htm)) that allows the user to add a new agent if they are not in the list. This works correctly. Now I want to let the user add a new branch for the agent they have just added. The code appears to work correctly from the front end, but when you check tbl_branch, there is a new entry but it is missing the appropriate agentID foreign key (ie, it's not related to the new agent).Is there something that I'm missing? I assume this is possible, but I must be doing something wrong.Maybe I need to create a little pop-up form for data entry?any help appreciated.
I'm really stuck on this and have tried loads of different ways, but I can't seem to get it to work. I have 3 tables: tbl_Workshop, tbl_WorkshopArea and tbl_Instructor.
I want to have a combo box that will display the field WkshopArea from the workshoparea table. When an area is selected the next combo box should only display the names of the workshops available in that area. (The second combo box should be based on the WkshopTitle field in the workshop table.
Once a workshop is chosen from the second combo box a text box should be updated with the corresponding code of the workshop.
I've attached an example of my database for you to look at - both the forms on there don't work, but show examples of what I've been trying.
I have a form with a combo box in it. When the user selects either the US or Canada, a second combo box will open with States or Provinces listed. For background, this will record the location of applicable governing law for the contract we are recording. Anyway, here is the code. My issue is I need to add some code that says, if the law is another country (not US or Canada), have the second combo box indicate an n/a or gray out the second combo box. I would appreciate some help. Thanks
Private Sub cbotlCountry_AfterUpdate() On Error Resume Next Select Case cbotlCountry.Value Case "United States" cboState.RowSource = "tblStates" Case "Canada" cboState.RowSource = "tblProvince" End Select End Sub
hi, I have a db which uses the data taken from cboInstructors which is used in a query and a produces a list in cboExpertise. This procedure is repeated from cboInstructors for cboAssessors and then cboIVName.
This subform then shows all the modules each student have taken, who taught it, who assessed and who IV'd it. The problem is when one selection is made all the subsequent choices are the same as the cbo above it, i.e. the queries do not run again when a diferent selection is made in cboInstuctors.
Checked the FAQ on this but doesn't apply to what I need.
Basically, I have a Product Class, and Products that are in the class. My database is for a computer component business, so the clsses are Processors, Mainboard etc.. and the products that fit into that class. The Class and products are listed in the Products table, and I need a way to have it on my subform, so I can choose the product class from one combo box, and then have another combo box to view the products in that class.
Right now I have it :
SELECT DISTINCT Products.[Product Number], Products.[Product Description] FROM Products
and that lets me select ALL the items in my products list, but I also have a Product Class Combo in the form that does nothing yet - So I thought adding : WHERE (((Products.Product Class)=(!FORMS![Orders Subform1]![Product Class])) ORDER BY Products.Product Class
To the end would sort it, but it doesn't and gives me the error : "Syntax Error (Missing Operator) in query expression"
