[mlmorris]

I have a problem creating an Access form for data entry into multiple tables.

i am running Win XP/Home SP 2 (the entire version description is Version 5.1 (Build 2600.xpsp_sp_2_gdr.070227-2254 : Service Pack 2)).

The Access version is 2000 (9.0.2720)

I am trying to create a form for data entry into fields for 3 different tables. I searched this forum and found nothing relevant. I have not found the sample databases helpful. Although reasonably skilled on applications such as Word, I am a novice with databases.

I am trying to learn how to accomplish this task without using the wizards. I first tried to create the form without using a wizard. Here is the sequence using Create from Design View:

Click on New/Design View

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 1 from the drop down box.

The Table 1 field names show up in the Field List window.

Drag desired field names to form

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 2 from the drop down box.

Drag desired field names to form

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 3 from the drop down box.

Drag desired field names to form

The form looks acceptable in Design View. In Form View, all the fields are displayed, but the last Record Source selected (in this case, Table 3) has been applied to the entire form (not just the fields from Table 3), so the fields in the form from the previous two tables are not properly linked to the appropriate tables.

Then I tried using the wizard. Here is the sequence using the Form Wizard:

From the Database window, click on Forms in the Objects panel.

Click on: Create form by using wizard/New/Form Wizard/OK.

In the drop down box for Tables/Queries, select table 1.

Move fields from “Available Fields” to “Selected Fields” using > button.

In the drop down box for Tables/Queries, select table 2.

Move fields from “Available Fields” to “Selected Fields” using > button (Selected Fields now has fields from two tables).

At this point, if I finish creating the form, I have no problems. The form is created in Design View, and I can switch to Form View without any problems. I can enter data into the form and the data is entered into the correct tables.

When I examine the Form Record Source, it looks like this:

SELECT (long series of entries of field names from the two tables) FROM Table 1 INNER JOIN [Table 2] ON [Table 1].[Table 1 field name]=[Table 2].[Table 2 field name];

However, if I try to add fields from a third table by selecting it in the drop down box for Tables/Queries, and

Move fields from “Available Fields” to “Selected Fields” using > button (Selected Fields now has fields from three tables), then

The wizard will create a form in Design View only if no more than 4 additional fields from the third table are added to the Selected Fields.

When I try to add five fields from the third table, the form will be created in Design View, but when I try to switch to Form view, I get this message:

“System error in FROM Clause”

When I examine the Form Record Source (from Design View), there is a long series of entries after the SELECT statement from all three tables, then it looks like this:

FROM (Contacts INNER JOIN [Table 3] ON [Table 1].[Table 1 field]=[Table 3].[Table 3 field]) INNER JOIN [Table 2] ON

Note that everything after the ON statement is missing

If I try to add more fields beyond 5, at some point (I haven’t determined the exact number), the error message changes to:

“Wizard cannot create the form”

There is no other info supplied.

I must be missing something basic. Can anyone help?

Thanks

[OBP]

Hello mlmorris, your problem probably stems from the way that your Tables and data are structured, can I outline some basics for you.
The tables should have a common "Link", usually using the Main Key Field of the Master table.
If the data requires one record in one table for one record in another table (one to one) then they should probably be in just one table.
However if the tables require more than one record for each record in the master table (One to many) then they should always be in a seperate table.
They should be set up in Relationships with a "One to Many" Relationship with "Enforced Integrity".
This means that the "Slave" tables cannot contain records for a Non existent record in the Main table.
Each slave table should have a Field to hold the Main Tables Key Field Information, they MUST not be Autonumber fields.
Once this has been created, you should use the Wizard to create queries for each table.
From the Queries use the Form Wizard to create Forms for each table.
When you have created all the forms return to the form for the Main Table.
Use the Sub Form Wizard to add your "Slave Table's" forms to the Main form as Sub Forms, Linked to the main form via the Main Form's Key field in each Table.

If the Tables hold data of a Repetitive nature for selection purposes in the Main Table (like Town Names etc) then the One to Many relationship should be set up going from the Repetitive Table to the Main Table and the Main Form should be set up with "Combo" or "List" Boxes to select the data, only the Repetitive data's Id should be stored in the Main table.

I hope this makes sense to you.

If you need an Example I have Hundreds that you can choose from, or you can post what you have and I or one of the other "helpers" will arrange the data for you.

[mlmorris]

Thank you for your response. I will try some experiments to see if I can better understand the table relationships.

In the database I am working on, I have used combo boxes successfully for the 2 digit State codes and several other fields--although the individual for which I am developing (if you can call what I am doing development) the database prefers not use combo boxes.

One of the problems I have with most of the examples using forms with multiple tables is that the examples generally use the forms to display data from a query, rather than as a primary data entry tool.

Beyond that, I am not clear on how to use key fields. For example, say I have a table of vendors that participate in multiple events over the years.

Say I have one table of Vendor Info (name, address, phone, etc.), and I call the primary key in this table VendorID.

Assume also I have a table of events where one vendor can be associated with more than one event, and more than one vendor can be associated with one event.

So, I assume I should place the events info (name of event, location, date, etc.) into a separate table called Events Info. The primary key for this table I assume should be something like EventsID. Then, should the linking field in the Events Info table be the VendorID field from the Vendor Info table? If so, does the field name have to match exactly in both tables? And, I assume that the VendorID field in the Vendor Info table must be an autonumber field, but a number field in the Events Info table.

Am I on the right track here? Or have you some suggestions on how I get on the right track?

Again, I thank you for your help.

[cristobal03]

The "linking field" is more properly known as a foreign key and, yes, if I understand your example correctly, you are describing an appropriate use of a foreign key to relate two tables.

The Autonumber restriction is logical not technical. In other words, again, your example of VendorID being auto-incrementing as a primary key but a long integer ("number field") as a foreign key is correct, but not because of data type limitations within Access's Jet database engine. You could make primary and foreign keys using just about any data type; it's just very unwise to do so. Since you would want VendorID to be unique as a primary key, you would use an Autonumber; since you would want the ability to manage and manipulate VendorID as a foreign key, it's just a number. But in most cases, you will not want users to make the association themselves by manually imputing the foreign key VendorID.

Now, as far as I know it is impossible to bind a single form to three different record sources using only the form property sheet. Instead, you must use an unbound form and handle the data binding programmatically. The subforms OBP mentioned are a good workaround in these scenarios: they're easy to develop, relatively simple to maintain, and can be integrated virtually seamlessly into a parent form. The only difference is that you will be developing 3 forms not 1, but doing so will give you some practice and more perspective regarding the correctness and robustness of your database's design.

From what it sounds like, you might want to revisit your implementation. In most cases it helps usability to create visual contrast between components of data entry; users will have a more successful experience with the program if they have a more intuitive understanding of what happens to the data once it's input. Here I'll reiterate the subform approach. If the data is conceptually different enough to merit its own table, it's usually distinct enough to warrant its own form as well.

I dunno, just some thoughts I had.

HTH

chris.

[OBP]

mlmorris, you have got the principle just right, it is a pity the "individual" does not like Combos as in Access they are one of the most efficient ways of working with data.
If you need any more assistance let us know.

[mlmorris]

Once again, thanks for your help.

I examined two Access 2000 sample databases (Contact Management and Event Management) to try and better understand table relationships. Despite all the books I have on access, none provide enough detail—for me. As an example, it appears, from the examples, that linking two tables requires more than just a foreign key in one of the tables. That foreign key, or linking field must include some method of selecting data from the first table. Part of the basics, I suppose. Thanks for your patience with my very basic questions.

One of those sample databases had a form with this statement (or something similar): “You will save the data when you close the form.” That started me on another, so far unsuccessful, quest for information. If I use a simple form to enter data into a table, the data transfers immediately; the form is just the “user interface.” However, some forms--for example, online employment applications and most online financial transactions--require the data entry person to click on a button that often says “Submit.” Presumably, that allows the data entry person to review the info before the data is entered into the database and nothing actually enters the database until that button is clicked.

Here are my questions:

Until the button is clicked, is the data held in some sort of a “temporary,” or “holding” table?

Does this approach use Command Buttons? If so, are there associated field/section/form event properties that must be enabled? Or is additional (VBA?) code required?

Thanks

[OBP]

mlmorris, the type of Form that you are talking about is known as an "Unbound" form, i.e. it does not get it's data directly from a table or Query. They usually use Visual Basic and Recordsets to get, add or edit the data.
You can simulate this with a "Save Button", this Command button would allow the data in the table to be updated. if it is not pressed and say the Close Form Button instead then the "Changes" to the data can be "Cancelled".

[t_g2]

Hello,

My case is somewhat similar, but still, different. I tried to make a form to enter data into a table, then created a macro that would lookup one of the values I entered in the form in the other table and if it is found, then would automatically fill in some fields in the second table. I'm stuck halfway. When I wrote the macro, just to try, I used specific numbers (What to lookup for, what to write in the fields), it worked well. But when I wanted to control it by some kind of conditionally (again, based on what I entered the from before), I entered the name of the table to the "Change what?" kinda property (using the expression builder) and it just didn't do it. Is there any way to do it like that somehow? Is there a specific syntax I have to follow?

Thanks for any answers.
GT

[OBP]

GT, any thing is possible with VBA, but it might not be the best way of doing it.
Can you attach a Zipped copy of the database for us to look at?
It can be with or without data, but we need to know which fields you want to bring in to your form.
It may be possible just using a Subform.

[slurpee55]

It would be far easier to do one of two things: 1) create a query combining the three (or more) sources you are trying to link your fields to which holds all of those items in it and then make that the query the record source for your form or 2) create several subqueries each of which is tied to one of your record sources and then make a master, unbound form in which you insert the subqueries. The problem with the latter method is it limits the display of your form - you will see the first subquery, then the next and so on.

[t_g2]

Hi,

Sorry, I had to travel a bit. But now I'm back in business. So, the system says, I'm not allowed to send anything yet, so I can't send you anything. But anyway, I made some changes to the database and seemed I can solve the problem, so I don't need to make the saving to two tables (although if it's possible, than maybe it's still the easier way),but it created other problems. Now I have duplications in it. In a nutshell,the database will be used to record all arrivals, departures into and from a small terminal, also, all the movements inside the yard. I created a table for the Movements and another for the Vehicles (plus some others, but they're not important here).

The problems are:
- In the Movements table, I need to select sometimes one, sometimes two vehicles (single truck, van, car, ... or truck and trailer combination). Is it OK, if I make relationships from the Vehicles table to both fields (tractor&trailer ID) in the Movements table? Or is there a better way?
- In the Movements table I have timestamp function (Date and Time are set to the system date and time as a default value), but the problem is that when I start a new record, the current date and time get recorded in the record after, not the current one. I guess it's better to remove the default values, create variables to store the date, time and other data and write them in the table all at once (one by one).
- The application should be able to create a current inventory any time, meaning what's inside the yard. To do this, I think I need to create a VBA script, but I'm fully beginner in it. I think the way to do it is to create a loop that goes through all the records in the Vehicles table and lookup the last movement (based on date&time&route# as a combined primary key) for the Vehicle ID in the Movements table. Am I wright? Can you guys help me with that?
- The duplication I mentioned is in the Movements table, the route# and the tractor and trailer#s occur many times. I have a record for the arrival, and for all the movements inside the yard, which are all belong to the same route#, also, they are all made by the same Tractor and Trailer#s. Sometimes not the same tractor involved in the arrival and the internal movement, a trailer can arrive with one tractor that just drops it in the yard, then another picks it up to do the internal movements. Possibly, a third one takes it out of the yard.

If you could help me, I'd really appreciate it.
GT

[OBP]

Can you post the database as an Attachment using the "Go Advanced" and "Manage Attachments" buttons?

[slurpee55]

Does a trailer ever arrive without a truck? If not (which seems sensible) I would tie it as a subfield to the trucks. But as OBP says, without seeing your database, we can't do much.
Also, this looks as if it may be a long and involved bit of work - it would be better if you started a new thread for this, then post a link to your new thread in this one.

[t_g2]

Hi,

Here is the critical part of the database, I took out the rest, they are mostly lookup tables.
I made comments to the fields in the design view, see them, also, filled the tables with some samples.

Thanks,
GT

[t_g2]

And no, trailer cannot do anything without a tractor, but any tractor can arrive with no trailer on it.