Tables :: How Safe Using Combo Box To Input Foreign Key
Aug 12, 2013
In a Related Tables, you need to enter a FK to link to the other table.You either key in the FG from memory..The standard procedure is to design the field as combo box AND use SQL to select the field that will input the ID of the other table. So, if you want to input foreign key 3 , the combo box will display what 3 is then when you selected Access will insert 3 for you.
My question is since I can later on, edit the table and change the the value to another value, and mess the whole thing up!!! HOW can I lock my first choice so it will stay unedited ? My second question is: Is this the only way to input the FK if you do not remember the exact ID number or there are thousands of records in the related table.?
I want to be able to invoke a form from a main form that allows me to add, delete or amend records in a table related to the one updated by the main form.
As many details records are being updated, I want to retain the value of a couple of foreign keys (but be able to amend them if required) based on the value of the previous record.
I have looked at the Dlookup function and wonder if this is what I should be using. If it is, I am having trouble trying to understand how you could differentiate between the first invocation of the pop up form and any subsequent table entries. The way I understand this function is that to get the value of the previous record in the table you use "ID-1", but this would almost certainly lead to an error for records being updated for a new master record, or if the table happended to be empty then there would be no ID-1 record.
hi i need to create a table, i've figured out to make LastName the primary key. But im not so sure about the foreign key, and whether it is necessary to put it into the table in order for all my tables to relate? Sorry if this doesn't make much sense. Also, to clarify.. 3NF primary keys in one table cannot be used in another table?? thanks
I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.
I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.
I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).
Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?
Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK Task Name Team ID - FK
tblTeam TeamID - PK Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
Can anyone give me some ammo to tell me boss what a safe number of users of an access database is. It is a well built database that runs quickly and effiecently but I need to know at what point this will be put at risk. 100 users? 200+? I am running the database a FE/BE with the client sitting on each users pc and the BE sitting on a server. The network is 10/100.
I am trying to find out how to integrate ms access with source safe and also the cost of source safe. The microsoft website does not give me information on the cost or even how to buy source safe. Can any one help me??
I am trying to model my database to create relationships between different tables in my database. During the process of doing so I ended up having one primary key in table A linked with to foreign keys in two different tables ( table B & table C) and both of those two table are junction tables to break out many to many between many other tables
Now for some reason!!! I feel that there is something wrong with my logic mapping and modeling of the relationships between tables due the fact of having one primary key linked to two foreign keys in two different tables :(
Is my intuition is right? should such case be considered as indicative of wrong modeling of relationships between tables in a single database And if so what is the disadvantages of that link (talking down the road) when the database if fully populated? :confused:
By the way I am new member and new to database, so please take it easy on me :o
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.
Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.
In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.
how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?
Trying to get a record entered into a field on table (a) to automatically enter into same field on table (b).Example: Plant database table, input record in plant name field. Have same record appear in propagation table in the plant name field.Played around with relationships a bit, don't know if there is where u do this.Plant name is primary in both tables.
Anyone know if an input box can have an autocomplete like Comboboxes? So say I type the string "Blue House, Red Carpet, Green Grass" and enter it. the next time I go to enter in a value and start typing "Green Grass", the form, auto inserts it, and as I start typing "Blue House", it is also auto inserted. anyone know if this is doable?
I have a customers list that i am always ading to .I need to run a query to see what individual customers have ordered .
I have the query that works great when i manually insert the customers Surname .but i want the combo to do this job .Is it possible ,or should i be doing something different .
I have a AUCTION form, it has a combo box and the label for this combo box is called seller. This combo box is a look up to the USER table which has the following fields:
uid, uname, city, state
The combo box is pulling and displaying a drop down list of uname from the USER table, with this SQL state in the "row source".
Code: SELECT user.uname FROM [user] ORDER BY user.uname;
In the control source of this combo box I have seller, and the form's record source is AUCTION, tagging on the AUCTION table.
The problem is the form is putting the uname into the AUCTION table's seller field, but I want it to input the uid field, however that it should still show the uname list when the user click on the combo box to make a selection.
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
I want to add a combo box to a form, where depending on the values entered in the box another combo box would show more values. In other words, if the first combobox had choices of 1, 2, 3 and 4, if 1 was selected the next combo box would have values a, b, c and d. If 2 was selected, it would have values of a, d, e, f and g.
I am using Access 2007 and have reviewed many blogs and looked at the Northwind db to try to solve this problem. I would like to create a form that functions in the same manner as the "Order Details" form in the Northwind db. Specifically, I want to create a form that will allow me to input a value and have the form get data from another table and populate the table I need populated.
I would like to populate a table "Outside", field names are Key, Name, term, Value, Date.
The table that contains the data is named "CV" and the filed names are Key, Name.
In the form, I would like to enter a value for Key into a combo box, and have it put the value for Key and for Name into the form, and allow me to enter whatever I want to in the Term, Value and Date field. When I tab out, I would like all this data to be in the table "Outside.
Hi there, i have a database i am setting up with 4 tables and am trying to think of the best way to input new data into it. All the tables are linked and its all based on printers. When a new printer is brought if it exists in the database then thats cool it just needs a Printer number assigned to it and the database will do the rest looking up printer model and what cartridges it requires. But if u add a model for a HP printer for example you need to add the model number to the tblink and create new cartridges and use the cartid to link the modelID i hope u get the picture!? lol there is a pdf i have created using onenote with screen dumps and comments about what i need it to do. If anyone has some advice or help it would be very greatful! Thanx mike
i am very new to access. Logic and method needed in order to create a data base that records weekly orders.
What types of tables do i need? How can i input more than one product per order? How do o group the information in a organized way?
Ideally i would like to Input the clients name the week of the order and then all the products etc that are ordered.
Then be able to see in an orders table each client that made an order and a subdatasheet (the small plus in the corner) that once clicked i see all the products that are in that order?
I have two tables that have a list of user info. One table has a status field. I want to update the status of users that is in table 1 but does not match any user in table 2 with "text". How would I do that? I do have an unmatch query of both tables.
Hi I'm just doing an access form to link to a mysql daabase to update a websites products, using a odbc connection.
When i input a new record and go back to it i see only #Deleted in all fields i have to close the database and open again to see the data correctly. Is there a way to cure this problem?
