Hi,
I'm fairly new to database design. I'm designing a database that has 5 tables, the first table stores a persons details and a unique trace number. The other tables contain this trace number and some other details.
Is it possible that when i enter a persons details to the details table, name, Surname, Trace Number, that this trace number will cascade or replicate in the other 4 tables? How would i do this?
thanks,
Joe
As part of my data structure I have a Fault table, Transaction table & Resolver table. The Resolver's are dependant on the Transaction that are selected in the fault table. What I am after (and it's not happening) is when selecting a Transaction in the Fault table the only resolver that is available to the user is the one related to the choosen Transaction.
At the moment after selecting a Transaction all Resolver's from the Resolver table are available. When creating my Fault table I selecting the Resolver field as a Lookup and choose the Transaction table and the Resolver field.
Hi All
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients
Client ID
Company Name
Contact
Title
2. Commercial Projects
Project ID
Nature of Project (Graphic Design, Multimedia, Media)
Project Name
Project Information
Staff Member Responsible
Start Date
End Date
3. Students/Graduates
Student ID
Student Name
Area of Expertise
Contact Details
Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects
Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients
V
V
Projects
V
V
Students/Graduates
But it wont show if its:
Commercial Clients
^
^
Projects
^
^
Students/Graduates
Anyone have any ideas how I get get them to cascade upwards?
Thanks
Pete
Hi all, complete Access n00b here requiring some assistance if possible with the dreaded cascading combos. Would appreciate any help I can get. I've tried searching but can't find this particular problem mentioned (am probably searching for the wrong thing, but anyway...) Here is my situation:
I have a form [Component] with a simple cascading combo box scenario [cboCategory] and [cboSubcategory].
[cboCategory] has as its row source -
SELECT tblCategory.categoryid, tblCategory.Category FROM tblCategory ORDER BY tblCategory.Category;
Control source is unbound.
I have Me.cboSubcategory.Requery as an afterupdate event.
[cboSubcategory]'s row source is -
SELECT tblSubcategory.subcategoryid, tblSubcategory.subcategory FROM tblSubcategory WHERE tblSubcategory.Categoryid=Forms!Component!cboCateg ory ORDER BY tblSubcategory.Subcategory;
Control source is bound to SubcategoryID in tblComponent.
I have set up [tblCategory] as the one side of a one to many relationship with [tblSubcategory] and [tblSubcategory] as the one side of a one to many relationship with [tblComponent]. [SubcategoryID] (foreign key) is the only piece of 'category' information I am storing in [tblComponent]. I presume that's the correct thing to do since you can calculate the category from the stored subcategoryID.
The problem is that the form only seems to remember the most recent combo choice so if i fill in one record, navigate to another use the combos again, then navigate back, the original combo choice has been overwritten in the form record (although the underlying tblComponent seems to have the correct data written to it). In other words ALL records on my form display the last values that were entered using the combo boxes and not the correct values. Is this normal? I hope not, and if not then what am I doing wrong?
Thanks in advance :)
Some background: I am making a form at work for a coworker with cascading combo boxes where she can select a Customer, then Platform Description, then Period, then Year. I used VBA code for these and they all seem to be working, except the Year.
Code:
Private Sub Form_Load()
On Error Resume Next
CustomerCB.SetFocus
[Code].....
I am pulling a table from Excel into Access (SD0039DA_T2), then I have used a delete and append query to populate a 2nd table (SD0039DA_T). I did this 2nd table because the first table was slowing down all of Access because it's such a large file directly linked to Excel.
The only real difference between the two tables is I added another column to SD0039DA_T called BillingYear. This is the Year in the cascading comboboxes/listboxes I am having trouble with. In the append query, I used ...
Code:
BillingYear: Right([SD0039DA_T2]![Billing Date],4)
...appended to BillingYear. I want just the year (YYYY), not the whole billing date from the original table (MM/DD/YYYY).
So I'm thinking the problem is with the YearCB section of the VBA code or something to do with my tables?
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables?
2- how to cascade the updates to the related fields?
3- Is there any way to force the data update to other tables?
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate()
Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;"
Combo2.DefaultValue = [Combo2].[ItemData](0)
Command4.SetFocus
End Sub
[code]....
Hi
I have 4 Comboxes all Cascading from one an other and it's all working fine.
My question is about My fourth combo boxe, It as two fields that the user can see when he selects the fourth comboboxe; pretty straightforward...
I want the user to see the two fields on the form after using the combo boxe. For this I have one of the record to populate the comboboxe and the other one to populate an other text box using the dlookup function where the criteria is = to the field selected in the combo boxe, I hope you're following...
Now, some of my records for each fields are not all different from each other so when the user select a set of value the "dlookup text box" may have several option: the index is not unique if you see what I mean...
To have the unique index, I would need to add to the 4th combobox the primary key which is an autoNo that the user doesn't really need to see...
So... I am thinking of hidding the primary key column in the comboBoxe and make it the main field THEN write a code on the after Update of the Comboxe to hide the comboxe and show a text box placed over the comboxe with a dlookup function with a criteria = primary key and an other text box set on a different field with the same criteria.
I hope this is making sense, I was wondering what you were thinking of this method, it seems to involve a lot of fiddling which I don't mind but I'd rather make sure that i making it right.
Dear members,
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?
Thanks in advance.
Irshad
I don´t get this effect: I am struggling with a cascading combobox problem, could you help me, please?
form is frm_Principal. Combobox are Categoria, 2ª Categoria and 3ª Categoria. Where´s the error?
http://www.drupload.com/uploads/211DB_MM08.rar
thanx so much
Hi!
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 :)
Please Heeeeeeeeeeeeeeeeelp :) :)
I'm looking for if possible a simple solution for my cascading combo boxes that do not work in a subform.
I have provided a link to my DB and the form i'm having issues with is frm_Projects any suggestions woul dbe great i know that this subeject has been looked at several times but i still seem to not figure it out.
Database (http://www.cyruspostings.com/hold/db.zip)
I have three cascading combo boxes that, when a selection is made, will auto-fill 3 following text fields. So I had it working just beautifully, with the data pulling from the correct columns. All of a sudden, they stopped working, and the text fields are displaying #Name? instead of the appropriate data. Nothing has changed on my form, and I have really been struggling to determine what happened and to fix it. Any help or suggestions would be welcome. Thanks.
View 2 Replies View RelatedCan 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.
Thanks for your help
This ought to be an easy one, and I had it figured out in the past, but I can't remember what I did before. I've tried so use the forum search, so I wouldn't have to bother anyone with this, but I can't seem to word my serch criteria properly.
I have a cascading combo box for doctor's names that will auto fill 7 other fields (contact info), based on its selection. It works very well, until I have a doctor with the same last name as another doctor, say, Dr. Mary Jones, and Dr. Henry Jones. The list is sorted alphabetically, so Henry Jones is first. His information populates exactly as it should, when selected from the list. However, when I select Mary Jones, only HENRY's information displays in the 7 corresponding fields. What's wrong here?
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.
View 3 Replies View Relatedi have a subform called Tbl_Trainingsubform
on here i have two combo boxes which i am trying to setup as casacading...i believe this is how they are referred to anyway.
combo box 1 is called cbosection the other is called cboactivity
i have a table called tbl_training which has the following
trainingID
Activity
Section
and here is my sql that i have put on the afterupdate of Section...
Private Sub cbosection_AfterUpdate()
On Error Resume Next
cboActivity.RowSource = "Select Tbl_Training.activity " & _
"FROM Tbl_Training " & _
"WHERE tbl_Training.section = '" & cboSection.Value & "' " & _
"ORDER BY Tbl_Training.activity;"
End Sub
what i am trying to achieve is that when the user selects from the section box, the combo box activity will then be limited to the relevant list
what am i doing wrong
Andy
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 few cascading combo boxes that restricts the value of one combo box depending on what the the value of other one is.
The problem I have is that some of the data is not displayed in the combo boxes when scrolling through the records after data entry.
I have tried to re query them on the form on open event but it does not seem to help much.
Any Ideas?
thanks :)
Hello
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
TblLocation
LocationID- Auto# (PK)
Location - Text (Clean Room, Chemo Room, Anteroom)
TblCleaning
CleanID-Auto# (PK)
LocationID-(FK)
DutyID- (FK)
Date/Time
TechID
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
Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).
In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).
The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.
Any ideas would be appreciated. Thanks!
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?
View 3 Replies View RelatedRight 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,.
Many 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
can anyone fix this please
i have uploaded a sample