Updating 3 tables from 1 form

CEB

Registered User.
Local time
Today, 11:28
Joined
Jan 3, 2005
Messages
25
Hi Folks,
I am having a problem. I have 3 tables linked by User ID. I have made a form using the wizard (it only allowed two tables so I made the third one a sub). I can view all the details on this form using the nagivation control. My question is this: Can I add new records using this form?
Thanks in advance,
Colin
 
CEB said:
Hi Folks,
I am having a problem. I have 3 tables linked by User ID. I have made a form using the wizard (it only allowed two tables so I made the third one a sub). I can view all the details on this form using the nagivation control. My question is this: Can I add new records using this form?
Thanks in advance,
Colin

My answer is this - Try it!

On a real note, what tables are on the main form?
You could have the form Controlling the main table and the other 2 as Subforms.

You can add records, delete etc.
 
well

if you know which table you want to write to... you can add new record using dao coding easy enough...

Make your own add record button.

if you don't know how. explain what table and zip you db, I will have a look at it, and code it for you and zip it back.
 
Thanks Ian Mac & Brian, I'll play around with it.
My big problem is that this is the first time I've used Access on its own. I have written three projects in VB and C++ and used Access as storage. I think I'll create a button to clear the form so user can stay on the same form and then create another button to update them?? or something like that??
I'll play around with it tonight.
Thanks again,
Colin
 
Create a query to power the form. Get out of the habit of powering a form directly from tables. It is generally considered more effective using queries as you can narrow your records and select the relevant fields to show on the form.

Depending on your table joins, (1-to-many or 1-to-1) will depend on how you will need to structure the forms. Generally speaking, the data on the 1 side will occupy the main form whereas the data on the many side often resides on a subform.

Despite the generous offer from BLeslie88, stay away from record manipulation via code (unless absolutely necessary) as Forms can allow you to do almost anything. Check out the microsoft samples (Northwind for example) and look at how they do things. Alternativerly (an I would argue better) look at the sample database vault here for boatloads of useful demos.
 
Hi Fizzio,
I took your advice and used a query to load the form. I have now what I need. I also can update the 3 tables through this form. Thanks again. One more question if I may.
I have 28,000 records and will be adding another 12,000. The ID number (say user id) is in numerical order...this number was inserted after the data was entered. Once I click the add new record control I would like the number to increment by 1. Can you tell me where to place this code, I have tried to find it but I can't. Two approaches I've used are 1. I want to count the number of records in the main table, store it, increment it and place it in the ID-Number text box once the add new record control has been clicked. 2. I hold the last contents of the ID-Number text box in a var and place it in the blank form when the add new record control is clicked.
I have been trying to do this on form load / after update but have had no joy. Any suggestions are greatly appreciated!
Colin :)
 
Have a good rummage using the search facility as there have been a lot of posts (especially recently) on how to create a custom incremental ID. If you cannot find a useful solution, post back.
 
Colin,

Use the Search Facility here and look for "DMax". Normally DMax + 1 is used
to get the maximum value for your key field and add 1 to it.

Wayne
 
If your ID is numeric and there are no duplicates, why not use an autonumber so you don't need any code?

To convert your current values to an autonumber, you'll need to recreate the existing table. Copy the structure only and change the ID to the autonumber data type. Then run an append query that appends all the rows from the existing table to the new table. Then after you have determined that everything is ok, you can delete the original table and rename the new one. Access will take care of incrementing the autonumber as you add new records. There may occasionally be gaps in the sequence but don't worry about them they are natural.
 
Thanks Pat, but I don't really want to use Autonumber because of the increments that you get with errors. My ID number is equal to the number of records in the table.
Fizzo &Wayne I have searched for info and found some...however I still can't get it to work! I'm using the following code:
_______________________________________
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.[Personal Details_ID-Number] = DMax("[ID-Number]", "Personal Details") + 1
End Sub
________________________________________

I don't want to create a button to drive this...I would like it to happen when the user clicks on the "Add Rec" button on the nagivation control. Is this possible?
Thanks for your patience...feeling very stupid right now!!
Colin
 
Hello Again Folks,
Is there any way to control the nagivation controls on a form if you make the form using the wizard?
I'm still not having any luck with the increments!
Thanks,
Colin
 

Users who are viewing this thread

Back
Top Bottom