Simple Question: Is there a way to connect two fields in different tables, so that when I enter information in one table, it automatically fills in the appropriate field in the new table?
I would be using a form to enter the data. When I enter information in the form, I want it to automatically update the corresponding fields in the sub-forms, even though they're not displayed in the form. This is especially important when I'm adding new records--the linked (parent/child) fields MUST be updated, of course, or the records won't be linked when I open the record again.
Thanks,
Siena
ETA: Okay, I can see that it does automatically fill in the primary key field, but I need it to fill in at least one other field as well.
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
I have a database for the clients at my work place.
The main table with PK ie FRED001 has main client details (address DOB etc) then i have numerous other tables ' relationship one to many' with their investment details (lots of investments per client), appointment details, pension details, health details etc. The data base works fine in table form all the links master to child work fine but now i need to let the rest of the office loose on the system i am trying to create more user friendly form system with command buttons etc
I have created a form for each table some of which have sub forms in etc on their own they appear to work fine you can view all the records and add a new one
I am having problems linking the numerous forms together and when they are linked together (i also need the child form to auto insert the code from the master form? since it comes out blank at the moment!). At the moment i have the main form with command buttons on it to open up the child forms. this works i can see the current investments already entered on the database but it will not let me add a new investment or a new appointment etc it says that by adding a new invesment under the client code i am breaking the code no duplication rules?? but the child tables are set up with the clientcode allowed to be duplicated ??
I need to have each child table in a new form really since there is far too much information for it to all be on sub forms within the main table.
I have tried to also put the forms on tabs but i get the same result.
basically i need to be able to open up the main form at client X then using command buttons or page tabs move to both view their existing investments and add any new investments. Im sure it is something very simple that i have done wrong but i have been trying to solve this for ages now and its getting me more and more wound up ahhhhhhhhhhhhhhhhhhh!!
I have also tried inserting under tabs a small subform list for each table/formie policy number, surname etc to fill in new policies and the name with a command button to the form ( which should then have the details in?!?!) since i figured if these details were filled in then when i went to the form they should be there but tthat doesn't work either
I want to update my date field in my database everytime when the record is changed. I do not want to do this by using a macro and I cannot make it word by using the before update function. In the before update I have tried the following without success (nothing happens):
Private Sub Modified_Date_BeforeUpdate(Cancel As Integer) Me![Modified Date] = Now() End Sub
I am currently updating an old Db which rosters staff. One of the enhancements will allow the user to automatically allocate staff details to a Roster after a given date. i.e. Roster date 20th Feb 15, repeat for 3 days. 21st 22nd 23rd Feb 15. I have created a form with a medium date field and then a Combo box with drop down values of 1 thru 7 days. I want to link the combo box value to the Date box.
i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.
rivate Sub Command10_Click() Dim query As String
query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then cn.Open "dsn=ABC", "", "" End If rs.Open query, cn, adOpenKeyset, adLockOptimistic RESOURCEINFO.SetFocus rs.Fields(0) = RESOURCEINFO rs.Update
rs.Close Set rs = Nothing cn.Close Set cn = Nothing On Error GoTo Err_Command10_Click
Test findings showed that when I open a form the data does not update. I added a refresh action after the openform action. If I want to open three forms via the macro, do I need three refresh actions, one after each openform action, or simply a single refresh action that would apply to all three forms.
I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form? Thanks for your help.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
can someone tell me how to make a form's data change when related data is changed in another form. E.g. If I change the price of a product in 'products' to £3.00, how do I make it automatically show that it is £3.00 in 'orders' just by typing in the product code?
I have a form with three subforms. I want to disable the automatic record updates in Access so that when I press a save button it updates the Form and the subforms and if I press a cancel button the forms are all reset to as they were before any changes, either as a blank record or with data retrieved from the database. Anyone have any ideas how I can do this?
Is there any way to update the same record from two forms? The requirement is that in a form I have a checkbox. If I select the checkbox, it will open another form, in which I can update some fields which are of the same record. When I tried, it is updating on another record. Is that possible to do so?
i am attaching a sample db . If anyone knows how to do it, please update the sample db or give me advice.
I have created an app that looks a lot like a wizard...start with one form and then must choose NEXT to move to the next form or BACK to the previous form...there are 11 forms in all - each is based on a different table but all have a single common ID between them.
On the last form there is a FINISH button that will close all the forms and store the information.
My question is this: can I put a CANCEL button on any/all of the forms that will close ALL of the open forms without saving any of the data? If so, what is the simplest way to accomplish this?
There will 5 additional sections of the database that will work the same way but with different forms and I would like to be able reuse as much of the code as possible without having to explicitly define the open/or possibly open form names in the code.
I'm fairly new to access and using access 2003. I have a main table and 3 other lookup tables. i have created the form using the main table. there are about ten fields. what i need is, when i select one field, another four fields should automatically update. is this possible? if it is possible how do i go about doing it?
any help would be greatly appreciated. thanks dem125
I have a form where there are four fields that the user selects via a drop down combo box: account number, Group, Name and Date. The entire form is linked to a table named 'Questionaire', but all of the drop down selections are sourced throw other tables and queries. At the end of the day, all of the information flows to the 'Questionaire' table, which is the DB's main table. Subsequently, there is also a subform as part of this form, but that is beyond the question here.
What I am trying to do is change my Group field to be something that automatically populated by the database and not something that the user needs to identify. For instance, when the user begins to enter his or her information into the DB, they input their name, date, and account number....once the account number is entered, i would like to have the group automatically generate and NOT be something they must choose.
The possible account number selections are driven from a different table named 'Accounts', and in this table are the fields: Decription, Group, and so forth.
What do I need to code or build in order to have the 'Group' ID automatically update when the 'Account' number is entered?
I have a database I created. its has Units, then it has Tracts and under tracts it has Leases. the main form is for the units. then I have a sub form which is tracts. in the tracts there is a field for Leases Vol/Page. everything works great. now I have a client that wants to send me Title opinions which are for several of the leases in the tract sub form. I need to input the info in a form and have it saved in fields in each Lease at one time.
Right now I have to goto each lease and insert it every time one by one. the field for each lease is the Vol/Page field. is their a way I can do this without having to goto each lease one at a time.??????? in other words one title opinion might be the same for ten of the leases I need to update these all at once.
I have two forms linked by a field "JUVIS". My problem is that I can't get the second form to show data newly entered on the first form without moving to another record on the first form. I have a command button on the first form that opens the second form. All works well, meaning the forms are synchronized,based on the JUVIS field, except in the case of entering new data. In this case the second form opens to a blank screen instead of showing the JUVIS field. The forms are both linked to tables not to queries. Any help would be appreciated
I have this database I'm working on and have a couple of issues I can't work out.
The first is this, I want to have my sub forms go to the last record when moving through the main form, currently I can only get this to work when I open the form?
If I move through the records in my main form (forward and backwards), the two sub forms go to the first record only, so how can I get them to go to the last recor?
I've attached the DB to show you what I mean. Thanks in advance Tez
Hello all. Hope I describe my problem correctly. :D
I have a near complete database with forms linking to tables. Tables have relationships with keys set to ref. integrity/cascade records.
Here is the problem -
My forms are meant for people to input data into these tables. However, I do not want them to change those keys.
Problem 1) If I set primary/foreign keys to "autonumber" I cannot get the relationships to recognize ref. integrity and the records will not cascade, and will not update.
Problem 2) If I set primary/foreign keys to "double integer" then if someone enters a new record into the forms, it is recorded as "zero" when it should be updating in numerical order (I have 479 records in the database, so the next new record should be 480).
Problem 3) If I change it any other way - from double integer or auto number, the tables do not update at all, or do not update correctly.
What is going on here? I thought creating forms was a fairly simple task - create the form, it is related to the table, update the form, it updates the table. I mean, duh. :confused:
Thanks anyone for your help, because Microsoft's site sux.
I have similar text boxes on two different forms. On the first form when I add a value and click the add button it automatically submits that value to the second form. However, on the second form when I select a value and click add, it deletes the previous value given by the first form. I have written code that allows the user to input more than one value on the second form, which works fine.(The previous values do not get deleted) I want to write some code that allows the user to submit a value on the first form, and that value remains on the second form's text box even when the user adds more values on the second form. Also the values are inputted to a table.
One previous suggestions was to put field2=field1 & field 2. However this does not work.
Any suggestions would be a of great help. I am pretty new at Microsoft Access. Thanks for the help
i have created a form and its working correctly. however i have just been informed some of the fields needed deleting and different ones put in their place. so im just wondering if there is anyway to updated the field list so i can just add the new fields without having to recreate the whole form.
I have created a form and subform using form wizard where users could enter the data of a new order.In the form, there are some expressions where it does a computation of the fees that the company earned for each order. This is a percentage of the gross income.The subform expression formula updates the fee amount and net income automatically when the gross income is entered and fee percentage entered.
Is there a way to update the fee amount and net income which the expressions derived into a data field in a certain table?I actually need these information to be in the table too, as data as they are required by other users.Or is there a better way to do it?I am mainly using wizards and don't know how to use any SQL or VBA.
Updating one field on the result of another, again this I can do BUT how do i get it to do this for all records? I have tried the on load property but it only updates the first record?
How would I update all SQL stored within all forms to change its syntax?I am returning all the row-sources for all the comboboxes on all forms in an Access db. Most work fine but some have syntax errors, for example double quotes instead of single. Is there any way of finding these stored SQL statements and updating them on mass, as I am doing it manually at the moment!
I created a new form with two subforms. They each reference their own table but are linked by a field called Case_Name. I created a unbound combo box in the main form to allow filtering of the records based on Case_name and I do a requery when the record is found. I am importing data from another application into the three tables ( main with other fields, as well as other tables ..Case_Name only).
The form/subforms are then used to modify or add new data to the existing records in each table/subform. The problem I have is when I go to update/add data in the subforms, it comes back and tells me that it cannot add data due to data integrity. It thinks I am trying to add a new record with the same Case_Name. I have the forms and subforms set up as Data Entry = No so that no new records can be added. I only want to modify existing data as the records are created from the import process.
I have a form (frmHourEnter) with a textbox (Week_No_Calc) which has a calculation, in Control Source, based on another textbox. After exiting from Week_No_Calc how can I get a field (Week_No) in table (tblHourEnter) to be updated with the calculated value?
