When Using Cascading Combos - Requerying Other Fields
Oct 6, 2005
OK ive got my 2 vaguely working combos which select the surgery, and the staff.
On the same form I want a couple of tickboxes which show for example, whether a person attended the course, if they had to pay etc (all yes/no answers).
How do I get the tickbox to update? i have tried to requery it but nothing happens, it just stays as what the last thing done to it was.
I am trying to find a solution for this many to many relation and combos! I ve managed to do the one-to-many as the Mile-o example but i have trouble getting the same result right on many-to-many relations
as you know access needs us to create an extra table for many-to-many relations! but the example talks about the current an the previous table! how can i have 3 cascading combos with many to many relations to each other???
i also have a fourth but this one is related as one to many with the 3rd!
for more information i have:
Many Cars-to-many Categories of brands of parts Many Categories-to-Many Brands of parts One Brand-to-Many parts
the many parts combo is also a record selector!
In the beginning I had one table of parts with 4 foreign keys then i changed it to the current one with the previous chain of relation ending to parts with one foreign key! is this better???
Thanx! I hope I made my proble as clear as i could :)
Can anyone kindly help me with the attached database. I need to synchronize 3 combo boxes. based on the choice made in the previous box. I have seen it done before using text fields but not using numeric fields as in my case here and without using queries.
I have set the RowSource for the 3 boxes on the OpenForm event and have removed all my code attempts.
I am new at programming in Access and I am writing a database for our church. I have 2 tables, one called “Donators” and the other “Donations”. In the Donators table I have their Last Name and First Name along with their address etc. In the Donations form I use a combo for the Last Name and a separate combo for the First Name. I did this so the person entering data doesn’t have to use a mouse but simply tab and type. Both combos are bound to the ID number. Without using a cascading combo, all First Names appear not just the ones relating to the Last Name. I checked the examples on cascading combos and tried them but without success. I could really use someone’s help. Thanks.
Right i have a project form which has a sub form of location
One project can have many locations
therefore it is nescarry to enter more than one location
Problem is with the cascading combo boxes i have for country to region to sub region, is that you can enter one fine, but if you enter another is deletes the information from the first entry
I have a query about cascading and combo boxes, which I was kindly, advised about previously. However, I’m stuck as to how to cascade using the selections from more than one combo box. Here are the tables involved
What I’m trying to do is create a form to enter downtime incidences and would like the downtime reason to be selected following a series of combo boxes. The first combo box selects the Line on which the downtime has occurred, using this, the next combo box selects the machine. I would like the category selected independently but then need the Downtime Reason selected based on the selections in the machine combo box and the category combo box. This is where I am having the problem.
I can get the machine combo box to display machines dependent on the Line selected and I can get the downtime reasons combo box to display machines dependent on either the machine selected or the category selected, but I don’t know how to get it to do it based on both.
e.g. the category may be mechanical, but there are reasons that are mechanical and specific to each machine.
I have used cascading combos for Make and Model. They work fine. I have set up tblMake as follows: ID Make I have make tblModel as follows: ID Make Model I use these cascading combos on a form. The data from this form goes into the "Cars" table. I have set the "Column Bound", "Width" etc. to show the name of the car, not the number. However, I have created a search form. This uses a SELECT...... case in the Row Source. The details of each car are shown in a unbound listbox. This select case is supposed to show "Make" and "Model", which it does, however - they are numbers!!! Please can anyone let me know how to change it so it shows the actual names of the car. Thank you
and I am creating a form to view the items currently on consignment with the customer. I have the main form 'Consignment' that holds the CustomerID, ConsignID, DateStarted, Employee (SalesPerson), Inventory Interval, and Closed (Y/N)... The subform lists in datasheet view the records in form 'ConsignDetails' that have the same ConsignID. The controls on the subform are :
ProductID UnitPrice Quantity Color Size
The ProductID is selected in a combobox from all products. The UnitPrice defaults to a DLookup() from the products table based on the ProductID, but can be changed and stored. The Quantity is a manual entry field. Color and Size are both supplier-dependent fields, in other words, the supplier of the product has unique Size and/or color codes or descriptions. I have no problem creating a combobox either in the AfterUpdate event of the ProductID or in the MouseDown event of the Color or Size field to limit the drop-down list to only those Colors/Sizes from the CompanyColors / CompanySizes tables with the same SupplierID as the Product in the first control.
So far so good, but here is where I am beating my head against the wall. Every time I choose a product from a different supplier, the Color and Size controls display ONLY the items from the current Supplier, not the items from the Supplier of the Product on their individual records. I have seen ways to make the combo popluate a locked textbox, hide the combo, and display only the text, but in a data entry application the user will be primarily on the keyboard, and the solutions seem to make it almost impossible to do without using the mouse over a continuous form.
I am using Access 2002 (ver 10.6501.6735) SP3 to create an ADP using MSDE to link to SQL7 tables....
Any help will be appreciated, I have spent almost two weeks trying different approaches and may be too close to the forest to see the trees.
Ive got my combos working fine now (thanks to all you kind folk on this forum!) but now that I have opened the main form (in which the combo box form is nested), once I have selected an item in the first one, and then click on the 2nd combo, a parameter box comes up saying "Forms!Training Record!Course_title_combo" and wants a value.
I cant see why this doesnt work when the form is nested, any ideas?
Hello to all ! I have this db where i wantto include 4 and if possible 5 cascading combos where the user can select records from a table and view them in a subform which is in datasheet view. All is going well until i try to add the 4th combo.Then when i select the 4th combos selection the subform displays nothing. If someone could point me to the right direction i would be most thankful. I have attached the db. Thx for all replies :)
I have two unbound cascading combo boxes on a form that limit the data for a stored field.
I am designing this as a subform to show software installed on a PC (hence the need to have a continuous form). As the manufacturer, software package and edition are already stored in my database, I am storing only the ID of the edition to limit duplicating data.
I would like to show the values in the cascading combos that relate to the edition ID stored as well as filtering the edition ID using the combo boxes (i guess a filter and a reverse lookup)
Please see attached pic. First record was entered as Microsoft Autoroute. New record for Manufacturer Frontier, change the previous records combos.
Combo A pulls from a query which gets surgery address1, address2, and surgeryID. It displays only the first 2 fields.
Combo B gets info from a query getting staff firstname, surname, title, staffID and surgeryID, and shows the first 3 fields.
I have the following code in the afterupdate of box A (surgery_combo)
Private Sub surgery_combo_AfterUpdate() staff_combo = 0 staff_combo.Requery End Sub
HERE IS THE PROBLEM:
The queries seem to work fine, as box B displays the staff that work at each surgery. However on selecting a staff member, you can only SELECT the ones who have a salutation (mr/mrs etc) in the first column. And when you DO select this - the combo shows a most unhelpful "Mr" whereas I want the name there.
How do I change the column that the combo displays once selected?
This is my first attempt with cascading combo boxes (2) and with the help of the forum I've made it work BUT, I feel like an idiot because I'm drawing a complete blank on how to link them back to a "main" data table.
The example I used as a reference was to create them as unbound boxes while using queries to pull for each of the combo boxes. I have 4 tables. One will serve as the main table while 3 others are each feeding one of the combo boxes. I went back and set the main table as the record source and added some of the other fields but those unbound fields have me stumped.
If anyone recognizes an earlier post that clarifies this, please let me know. I've looked through numerous posts but haven't found one yet.
Just like everyone else, any help would be greatly appreciated.
Greetings. I have a database that displays forecasts of our faculty needs. I now have 2 tables, Managers and classes. The Managers are bound to the form, the Classes to the subform. The subform changes every time I choose a new manager, as planned. But the Subform keeps requerying itsself, and I can't enter/modify data as a result.
I have a query based on a table which gives a breakdown of sales by month, sale type and by cost centre (department).
I also have another query which summarises the sales by sale type and cost centre so that and is based on the first query.
the second query is a subform of the first. I tried the second query as an update query but couldn't figure out how to execute it in order to refresh the subform. I've also tried to requery the second query having recreated it as a select query but I'm not able to refresh the data.
The aim is to refresh the data in the subform as any of the underlying data is changed in the original form.
I'm ok with VBA and with macros so if anyone has any advise or ideas I'd appreciate it.
I am having trouble requerying a combo box.I have created a Drawing Register database which allows users to create a new job as well as set up a drawing register for that job if the use checks a box in the job set-up form which indicates that particular job will have a register.Using the Job-Set up form I created a query which brings pulls the job number, job name and register check box and filtered it to show only those jobs that the box is checked. To this query I also added the relevant fields from the drawing register table. The Job Set Up table is linked to the Drawing Register table by the Contract No. field. Unfortunately I cannot set this field in the Job Set Up table.
Using this query I created a Drawing Register form with the sub-form Drawing Register Details. I added a combo box which shows the lists of all the drawings and allows me to view a specific drawing when selected. The problem I am getting is that the list is showing ALL drawings for EVERY job with a register and not just those drawings for the job I am currently updating. I tried using the following code with no luck
Private Sub Form_Load() Forms![DwgRegFRM].Form![Combo15].Requery End Sub
Private Sub Combo15_GotFocus() [Combo15].Requery End Sub
I want that when I choose to open the register for a particular job the combo box only lists the drawings for that particular job.
I am stumped as to how to get a form to update/refresh. Heres what I have! My main form:[Daily Log From] is opened, with a subform:[DailyDSQry subform] populated by a Query. This form has a command button that opens another form: [NewDSLog] in "acAdd","acDialog". What I need is: when the [NewDSLog] form closes, the [DailyDSQry Subform] needs to requery/update so that it displays the new entry.
I have tried the following in a command "CLOSE" button in form [NewDSLog]: docmd.Runcommand AcCmdRecordSave Me!Forms![Daily Log]![DailyDSQry subform].Refesh Me!Forms![DailyDSQry subform].[Query].!Requery doCmd.close
but no LUCK.....Keep getting errors Any Ideas Thanks all, I do appreciate the help Enviva
I have a main form populated by Dlookups.When I click on a textbox I open another form which is used to change data in my main table. Once this is closed the main form is still displayed but the amended data isnt reflected in this form. How do i get the data to change, I have tried requerying the form and the query that is used for the dlookups but I cant get it to work.
I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry form.
After this form is closed, I return to the subform to choose the company name that I just added, only it is not there because I can't figure out where to automatically requery this combobox. My duct tape solution that's working, is a command button that runs this code when clicked:
I have tried putting this code in the OnCurrent, OnFocus, Onclick, OnEnter....etc on the subform and on the combobox itself. So far the only way it requeries is if I use the command button.if you want to see the NotInList code, and the code on my company entry form.
I have a list box (List25) that is generated from a query (qryThisBox). One of the fields in that query uses the criteria [Forms]![MyForm]![SelectedField] to filter.I also have combo boxes in the form that List25 displays in. Unfortunately, I want to requery the list box in the form, using a combo box that calls the same underlying table field, SelectedField.
How can I do this? Can I somehow dynamically change the criteria in qryThisBox to change when it gets requeried?How do I stack combo box requeries, eg: Combo Box 1 gets selected - does a requery
and/or
Combo Box 2 gets selected - and filters the results of the requery for Combo Box 1.
Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."
Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"
This is the code that I'm using:
Private Sub cmbWorkgroup_AfterUpdate() Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery End Sub
I have a form with a tabbed control on it. The tabbed control has 4 tabs that each have a report on them. When I select a company name on the main form, I requery these 4 reports to show data related to that company.
For some strange reason, 2 of my reports requery with no problem, and 2 of them give me a message that the report can't be found. Here is the code:
Code: Private Sub cboCompanyName_AfterUpdate() On Error GoTo cboCompanyName_AfterUpdate_Err ' DoCmd.SearchForRecord , "", acFirst, "[CompanyID] = " & str(Nz(Screen.ActiveControl, 0))
' After selecting a company, requery the subreports to display the appropriate data
As a side note, if I chose the company, say "ok" to the error, and then go run the queries that these reports are based on, I get the correct records. Also, if I chose the company and run the 2 failing reports standalone, they work fine, so I know the problem isn't the reports or the queries themselves.
I have also checked and rechecked for any spelling mistakes and have found none. I should mention that the error message is "Programs cannot find the field 'SubrptCompany_Payback' referred to in your expression".
Hello! I got a database with two tables in it. Site table has following fields: ID,SiteID Customer table got following fields: DropID, Dropname, SiteID.
These two tables are connected in a One-to-Many relationship.
I got a form with two combo's . I want to select "SiteId" from the first combo and based on the selection i do in the first combo i want the second combo to just show the relevant data (dropid and dropname) for that "Site". I got one table named "Site" and one table called "Cutomers".
The database contains alot of other stuff and much of it is in norwegian so im not sure how usefull it would be if i posted the mdb here but if someone think its usefull then i will post it.
I can choose from the combo boxes and it works fine but the connection between the two are not working. Not matter what i choose in combo1 i get all recrods in combo2.
I am making a Invoice DB. I have made a combo for Suppliers which takes data from SuppliersTable and data is recorded in InvoiceTable. I have another Combo for Services which takes data from SuppliersTable and records up in InvoiceTable. And I have another field (PaymentPeriod) dependent on ServiceCombo
I want to make Services Combo dependent on Supliers.
How can I do that?
Combo 1 = SuppliersCombo SuppliersTABLE: suppliers data origin Field: SupplierMaster InvoiceTable: supplier data destination Field: Supplier
Combo 2= ServiceCombo dependent on Suppliers Combo SuppliersTABLE: suppliers data origin Field: ServiceMaster InvoiceTable: service data destination Field: Service
Form Field: PaymentPeriod dependent on ServiceCombo SuppliersTABLE: suppliers data origin Field: PaymentPeriodMaster InvoiceTable: paymentperiod data destination Field: paymentperiod
How can I do these dependent?
I hope I explainned myself clearly, I am access first timer.
Hi There, I know this subject has been beaten to death, but I have searched the forum and cannot find a post relating to my particular problem. I have three cascading combos on a subform that work fine and dandy when I open the subform on it's own, but when I open the main form that the subform is embedded into, I get the following message [IMG]cascade[/IMG] I know I am missing something simple. Any advice would be most welcome.
I have created 2 tables TBL_ObjectList contains data for objects in the night sky e.g. IDMessier_IDNGC_IDCommon_Name Type DistanceConstellation Apparent_Mag 1M1NGC 1952Crab NebulaSupernova remnant6.3Taurus9
TBL_Observations contains observing data where the Object field looks up the data in TBL_ObjectList e.g. ObservationIDObjectDateNotes 1M122/11/2007blah blah
I created a form FRM_Observations with a combo box to select the object e.g. M1 and it fills in the rest of the data for the object in the form.
My question is: How does this then relate to TBL_Observations? When I switch to a new observation record the object selection stays at the one for the previous record.
I know this is probably basic stuff but I just cant see it.
Is there a simple online "how to" for this stuff? as I just cant seem to get to grips with it.