# Problem with Access forms using multiple tables



## mlmorris (Jan 27, 2005)

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 (Mar 8, 2005)

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 (Jan 27, 2005)

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 (Aug 5, 2005)

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 (Mar 8, 2005)

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 (Jan 27, 2005)

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 (Mar 8, 2005)

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 (Aug 30, 2007)

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 (Mar 8, 2005)

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 (Oct 20, 2004)

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 (Aug 30, 2007)

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 (Mar 8, 2005)

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


----------



## slurpee55 (Oct 20, 2004)

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 (Aug 30, 2007)

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 (Aug 30, 2007)

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


----------



## t_g2 (Aug 30, 2007)

Sorry guys, I didn't see the attachment part of it, now I know how to do it. Again, thanks for any help.
GT


----------



## slurpee55 (Oct 20, 2004)

t_g2, can you repost but as a .zip file rather than a .rar file? Thanks,


----------



## t_g2 (Aug 30, 2007)

Here it is...


----------



## t_g2 (Aug 30, 2007)

The problem is that there is duplication of Route# and Trailer# (in case it is a single truck, the Route# and Vehicle #). When it arrives and makes the movements inside the yard, they are the same for every movement, except for the last internal movement, because then the Route# changes, gets a different Route#, even if it goes back to the same location where it came from (Route# is different when it goes from A to B and when it goes from B to A).
The Vehicle# can be different, one trcuk can pull it when it arrives and another when it makes a internal movement. Hope it helps.

Waiting for your positive reaction,
GT


----------



## slurpee55 (Oct 20, 2004)

One minor error - when I joined the tables in a query using the vehicle #, there are 3 vehicles in the movement data that do not exist in the vehicles table - they are 45001, 530214, and 45031 (although the last may be a typo of one that is listed, 4503102).
However, it appears that you have designed your tables so that you have drop-down lists of the vehicles, etc. so that shouldn't happen generally.


----------



## OBP (Mar 8, 2005)

slurpee, are you happy to work on this one?
If so just give us a call if you need any assistance.
I would suggest that the trailers might warrant their own table and also a Table of routes would be very useful.


----------



## t_g2 (Aug 30, 2007)

slurpee,
Of course, the Vehicles table is a lookup table for the Movements, only those registered in the Vehicles should be the choices.

OBP,
There is a separate table for the Routes as well, I just removed it to make the whole thing simpler and easier to understand.

The question is if it is a problem that I want to select two vehicles in the Movmenets table, one for the trailer and one for the tractor fields. Is it OK to make two connections from Vehicles to Movements? Or do I have to make a new table for the trailers?

Thanks,
GT


----------



## OBP (Mar 8, 2005)

Personally I would have a separate table for the Trailers as they cannot move on their own whereas the Trucks can.


----------



## slurpee55 (Oct 20, 2004)

I was tinkering with it at home last night and the first thing I did was to make a separate table for trailers that was the same form as the vehicles one, but with only the items marked as trailers in it. I then made the trailer table the source for the look-up field for the trailer column of the vehicles. Then I made a query to combine the movements and vehicles tables and made that the base for a form and a table. I will see about duplicating that here at work.


----------



## slurpee55 (Oct 20, 2004)

OBP, I will be glad to work on this. However, there is one item (from post 10 or so) that reads: "- 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?"
I will probably need your expertise with that. Thanks.


----------



## OBP (Mar 8, 2005)

slurpee, you should be able to do that using just one or more Queries as they can combine fields if required.
When you have got as far as you can post it and I will help with anything outstanding.


----------



## slurpee55 (Oct 20, 2004)

Here are some first thoughts. Look it over and let me know whatever....


----------



## OBP (Mar 8, 2005)

sulrpee, one thing that I do not see in the Movement Table is a Destination.
GT does the Route Table specify a Departure and Destination?
Without a Destination you can't say what is currently where, so you could not do the "Yard Inventory".
I also do not like Drop down "Look up" fields in tables as they make Querying much harder, they also provide very limited description in the current set up, so the Users will have to Know what type of vehicle is represented by the vehicle number, is that OK?
If the vehicle selected was a Tractor it would be nice to make the Trailer Field Visible for selection, but not visible normally.


----------



## slurpee55 (Oct 20, 2004)

Hi OBP, it was just that t_g2 had set up the tables with look-up fields, which I didn't think was worth the bother of changing (I'm lazy?) although I would personally put them in the form. As for where the items are - the internal destination at least - I think that can be derived from the movement type code - arrival, internal or departure.
But it would be a lot nicer (and easier to do sorts) if vehicles had a # that made it obvious what they were, e.g. c123, v234, t1232, r4321.


----------



## t_g2 (Aug 30, 2007)

Hello guys,

My thought is to make the lookups in the forms, but for now I think it's good enough in the tables then I will change them when the forms are done. It is not too difficult, my main concern is the right architecture. If it's not right, the info I get out of it will not be either.
I also thought about making the Trailer# not visible if the Vehicle# is not tractor. But I'm not there yet.

Yes, the Route table specifies the Origin or Destination. But how I think has nothing to do with the origin or destination. The way I think the inventory can be created is this:

Take a Vehicle# in the Vehicles table, then search for the latest (last; by Date&Time) Movement for it and if the Movement Type is not D (departure) then it is in the yard (if it just arrived or have made some internal movements, it all means that it is still in the yard). Just as slurpee wrote.

Then go to the next Vehicle# in the Veheicles table. When it's done, go to the Trailers table (if stored separately from the other vehicles) and do the same. Finally, create a list, sorted by whatever. Tell me if I'm not right, but I think this is the (or at least one) good way to do it.

The problem with numbering the vehicles like c123, t1232 it that we have rental vehicles as well and I've seen trailer# that begins with T (the vehicle/trailer # can be almost any combination of numbers and letters).


----------



## t_g2 (Aug 30, 2007)

slurpee,
I looked at the zip you posted. Looks good, but the main thing about the forms is that there are lots of movements to make, so they have to focus on that. Since the Vehicles table will be pre-populated, there will be very few new vehicles to record in the Vehicles table. So, I think the best is to put them on separate forms.

Thanks for all your ideas and efforts.
GT


----------



## t_g2 (Aug 30, 2007)

One more thing: if I put the trailers in a separate table, I don't need the Type ID, because in that table, all of them are T(=trailer). I need it only if I want to create an append query kinda table, which possibly can help creating the inventory, but I don't think it should be that complicated. But I need a VBA script to make the loop to go through from Record1 to EOF in the Vehicles table, also for the trailers.


----------



## slurpee55 (Oct 20, 2004)

So your list of vehicles and trailers will vary over the course of time? Will most of them stay the same? Even so, ones that are only rented briefly will be added to their appropriate table when you enter info about them.
It won't hurt to leave the Type ID in the table - I set the default to T, so all will be entered as such - in case you ever need to sort by that value or something....


----------



## OBP (Mar 8, 2005)

You do not need VBA to do the Inventory, just a Query, or maybe 2, I can't create it today, an it wait until tomorrow?


----------



## slurpee55 (Oct 20, 2004)

This has a new movements oriented form.


----------



## OBP (Mar 8, 2005)

GT, check out the Last Movement Query in this version.


----------



## t_g2 (Aug 30, 2007)

Guys,

Thanks for all the efforts you make for me.
I tried to do create the current inventory as a query myself, because I had an idea that suggested that I don't need VBA, but only a query. The fields involved are:
Vehicles.VehID (sorted by this)
Movements.VehID
Movements.TrailerID
and many others. 

It actually gave me a list, sorted by VehID. So, for whatever I had a movement, it was there. So the list is OK, but for every VehID I need to select only the last, by Date&Time.


----------



## t_g2 (Aug 30, 2007)

Oh, and OPB, the your query has a limitation in it, saying that Movement Type can only be departure, whereas in reality, the last movement can be anything. But the Expression idea is very good. I'll try it.


----------



## t_g2 (Aug 30, 2007)

I probably found the solution to see the last record for each vehicle:
http://office.microsoft.com/en-ca/access/HA010345631033.aspx?pid=CL100570041033#3
I'll try it next week and let you know how it went. Or you can try yourself and share the experience. But any other ideas are welcome.
GT


----------



## OBP (Mar 8, 2005)

GT, the query that I supplied gives the last record (by date and Time).


----------



## t_g2 (Aug 30, 2007)

OBP,

I looked at it again and yes, it gives a list of all the movements, sorted by vehicle#, then date&time. But I need to see only the last movement about every vehicle, not all.

Thx,
GT


----------



## OBP (Mar 8, 2005)

GT, you must be looking at the wrong Query.
I have just downloaded the database that I posted and the query called
qry_movement_and_vehicles
lists all the vehicle movements and the query called
Last Movement Query
only shows the last Movement for the "D" type movement, which can be changed to any other classification or removed all together.


----------



## t_g2 (Aug 30, 2007)

Hi,

I looked at the query you posted and you are right, it shows the last movement for every departure and yes, it can be changed to show every last movement. My apologies...

GT


----------



## t_g2 (Aug 30, 2007)

Hello OBP,

I am facing a new problem, I could use your expertise. You helped me a lot last time, please do it again. The new thread is here:

http://forums.techguy.org/business-applications/633960-lookup-form-results-saved-new.html

Thanks


----------

