Access fields

Question

I'm currently working on setting up a new database and stuck on an idea. I want to be able to create a form where I can input multiple records but have 1 field as a job # that stays the same until the job is over. So it would be something like Job #44340101 and then something like a log book rather than inputting the job # for each record. I thought about doing something where it would create a new table for each job but over the course of years it would get over loaded with tables. I'm open to suggestions or ideas as well. I'm relatively new to this.  

Answer 1

No need to create multiple tables, nor would it be a good design approach.

A single table is the way to go.  All this would be controlled within a form.  You can easily setup such functionality within a continuous form.  You can set the default value for the control for instance.

Don't forget you should never be working directly within tables, but always using forms.

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Seems to me you need 2 tables: one is a list of Jobs, the other of LogBook entries for each job:
tblJobs
JobID autonumber PK
JobName text(50) required uniqueindex
JobStartDate datetime required
etc.

tblJobLogBook
LogID autonumber PK
JobID long int FK
LogDate datetime required
LogMessage text(255) required

In Relationships window, put both tables on the design surface, drag and drop the JobID field and IMPORTANTLY check the box to enforce referential integrity.

Then create a form for the Jobs, with a subform for the LogBook entries. The subform control's LinkMasterFields and LinkChildFields are both set to JobID.

---

-Tom. Microsoft Access MVP

Answer 3

As Tom says, you need two related tables.  The alternative interfaces would be a form/subform as Tom describes or a single form based on tblJobLogBook, with a combo box bound to the JobID foreign key column.  With the latter you can carry the value of the JobID forward from record to record.  You'll find an example in Defaults.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

This little demo file includes examples of how to carry values forward, either in the current session of the form only, or over multiple sessions of opening the form.

---

Ken Sheridan, Stafford, England

Answer 4

Ok. I was hoping i could post an image of what I had in mind to make sure we're on the same page so I guess i'll do it the hard way.

Something like:

Job #   43242532

                      Time                Date               Speed         Range

                     11:00AM           4/4/14               65             45-57

                     12:00PM          4/4/14                56             95-123

Job # 54366345

                      Time                Date               Speed         Range

                     11:00AM           4/4/14               65             45-57

                     12:00PM          4/4/14                56             95-123

Rather than having to keep typing in the Job number on the form for each new record.

Answer 5

That is exactly how it would work if you create a Parent Child form.

Look at the Northwind sample application, Order form.

The parent is the Order. It gets entered only once.
The child records are OrderDetails. They all belong to that one Order.

So also with your Jobs and LogBook entries.

---

-Tom. Microsoft Access MVP

Answer 6

Ok. I added the tables, relationships, and forms. However, I'm getting 2 different errors now.

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship."

I'd get that when I tried to add a new record after one was already filled out and now after trying to get it figured out; I'm now stuck at "You cannot add or change a record because a related record is required in table ."

I didn't make any changes to the relationship or keys.

Answer 7

Can you post a screenshot of the Relationships window?
Also design each table, click the Indexes button in the Ribbon, and list all unique indexes and their fields.

The message means that you have a unique index and you're trying to save a second record with the same values, thus violating uniqueness.

---

-Tom. Microsoft Access MVP

Answer 8

Answer 9

I started over again with a new form and it works until I go to create a new record. I get the data input and hit new record but it just locks the form. Doesn't give me any error or even let me change any info in the parent form. It just stays locked to the data in the subform. Also won't let me go into design view or do anything until I close the form and re-open it.

Answer 10

See the "1" symbol on each end of the relationship line? That means this is a "one to one" relation. You need a "one to many" (1:M).

Delete the relation.
Change RunLog (no spaces or other funny characters - they may cause notational difficulties) field JobID to MasterID; it should be Number, Long Integer, and no indexes.
Draw the relation from Master.ID to RunLog.MasterID, and check the box to enforce.

Voila, you now have a 1:M relation.

Don't create any form yet. Enter a record in Master. Observe the autonumber value. Enter a record in RunLog with that value for the MasterID. That works. Enter another one with the same MasterID value. That works too. You proved the 1:M is working as expected.

What about an index on RunLog.MasterID to support the relation? Not needed. Access creates one behind the scenes (and does NOT show it in the Indexes form).

---

-Tom. Microsoft Access MVP