Relationship Issue.....I think?...Maybe the form?

OK. So I can only ask one question.....I hope I get this right.....
I took an access class 2 years ago and never touched it again. I was recently asked to create a database and I actually remembered a little more than I thought. I am creating a PRC database, basically keeping track of clients and services that we provide for the different divisions and the fiscal staff.
My Customer table fields are: CustomerID (auto#), SSN (no dubs), FN, LN, Address, Phone, Case#, Max Eligibility
My Vouchers table fields are: CustomerID (#), Voucher# (auto#), Date Issued, Appt Date, AmountAuthorized, Department, Case Manager, PRCcode, PRCservice, AddlInfo, VenderName, VenderAddress, FamilyStab (1250-amount used/authorized), Benefits (750-amount authorized), ExpiredVoucher (DateIssued+32), VoidedVoucher, WarrantDate, Warrant#, WarrantAmount, Fiscal Worker.
I have a One to Many relationship: CustomerID to Voucher#  because a customer can come in and receive several vouchers for services up to the max amount (1250 for FamilyStab, 750 for Benefits).
The 1st PRC form I created. I had one with all the fields listed but separated by using a rectangle. Customer info at top, services at bottom.
I can enter the first customer fine; then when entering the next customer, I would get a record dataset error. So, I messed around with the tables and the form and I think I messed up something else.  So I recreated the database again and the form. Now I don’t get the error (however, I have no clue what I did). This time I can enter the first person and a second person….but it will not save the second person.  I can enter data into both tables so I now think it is my form. I have attached a copy of my form.
I tried I big table and I can enter multiple people.  NOW, I can’t enter multiple services for same person. Example: Karena Carrington comes in to get auto assistance. She will need 3 vouchers because there are 3 different issues with her car. 1 is tires so that will need a voucher to Firestone for $200, 2 is muffler so that is Midas for $160 and 3 is air conditioning and brakes and needs parts from AutoZone for our garage to repair at $60.45. She now has a balance of $329.55 for future use within the calendar year.
My one question is: What do I need to do to make the database work?
Karena ClayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
FamilyStab (1250-amount used/authorized), Benefits (750-amount authorized), ExpiredVoucher (DateIssued+32)
Are theses fields "Calculated Fields" in the table itself:
If so then consider removing these fields and recreating them in a query.
Calculated table fields are an "Access Only" construct and may not be compatible when you upgrade to another RDMS.
They also present confusion when when you do start creating other queries based on these tables.

What does this mean:?
I had one with all the fields listed but separated by using a rectangle. Customer info at top, services at bottom.
are you referring to a Form/subform here?
Did you establish the Child and Parent link fields in the form(s)?

You may have to post a simple sample of this database for us to evaluate.
Post the exact instructions for its use, and tell us how to re-create the issue you are having.

Nothing in your overview points to anything over-complicated, so I am sure that any of us would be able to help.


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need to make use of Subforms for any data that can be related in a One-to-Many relationship.

For example, if a Customer can have more than one Voucher you should build a form based on the Voucher table, and form based on the Customer table, and "embed" the Voucher form into the Customer form. This will allow you to see all Vouchers for the current Customer (assuming your tables are created correctly, of course).

I'd encourage you to review some of the Templates you can download from Microsoft to better understand how to create Forms and such.
The green tags on the form fields indicate errors.

From your description, I would expect the vouchers to be entered on a subform.  Basic design is one table per form although we do use queries that join to other tables for lookup purposes.  But if you expect to add a row in tableA and a row in tableB, then use a main form with a subform.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Jeffrey CoachmanMIS LiasonCommented:
Also advise NOT storing SSN in an Access database.
At this stage in development you have no security.
In most cases, the CustomerID autonumber field should suffice.
Jeffrey CoachmanMIS LiasonCommented:
FWIW, is a very basic one to may setup (similar to what you posted)
(Screenshot and Sample database)

It all works fine when adding a customer.

Perhaps examining this will allow you to see what the difference is in your app.
Jeffrey CoachmanMIS LiasonCommented:
screenshotParen/Child Form
Karena ClayAuthor Commented:
Mr. Coachman,
Yes, those are calculated fields in the table itself. I was super excited when I figured out how to get them to work.
I am totally begginer when it comes to queries.....I have a difficult time with them.
I do not know what "Access Only" construct or RDMS is, however, I will take them out.

What does this mean?
I created 1 form and used the rectangle in Form Design to give the look of separate sections on the form.

Sorry but I do not recall what Child/Parent links on a form are or how to create them.

Mr. Hartman,
I tried doing the subform as suggested and I get error "You cannot add or change a record because a related record is required in table 'tblCustomers'.

Attached is my database.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You didn't attach your database (and Mr. Hartman is actually a Mrs).

Sorry but I do not recall what Child/Parent links on a form are or how to create them.
I would strongly suggest that you step back a bit and take some time to review the basics of creating databases, and of using Access. Jumping in feet first is not a bad thing, but you at least need to be sure you know what you're jumping into :)

Take a few days to review some of the Templates, and the download that Jeff provided. It will be well worth your time.
Karena ClayAuthor Commented:
Here is the attached database
Karena ClayAuthor Commented:
LOL! Jumping in......more like being thrown in. It has taken me 2 months to get this far and they were wanting it yesterday.

Mrs. Hartman,
I do apologize for the mistake...I just assumed.

I will definitly review the templates. However, I just thought it was something so easy.
Most of the databases don't have the two different auto number fields so it is not an apples to apples comparison.
I will continue to google and youtube. My supervisor told me to register and ask my question here.

Thank you for all of your help and time!!
Have a great day!!!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In your database -

Your relationships are wrong - the tables should be related on CustomerID.

On frmPRC (which is, I assume, the form you're working with), set the main form's Recordsource to this:

SELECT tblCustomers.* FROM tblCustomers;

You do NOT need tblVouchers in that equation. tblVouchers is handled by the subform.

Also, set the DataEntry property to NO on that form. DataEntry = Yes means you can ONLY add new records. That's most likely not what you're after.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Most of the databases don't have the two different auto number fields so it is not an apples to apples comparison
You can't have two different AutoNumber fields in the same table. AutoNumber fields are simply used as "pointers" to a specific record.

You should relate tables by storing the Primary Key of the parent table in the Child. That has absolutely nothing to do with the Primary Key of the CHILD table. That PK could be an AutoNumber, or it could be something else.

But the AutoNumber field of one table has nothing to do with the AutoNumber field of another table, except that one may store the contents of the other.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would also encourage you to remove the lookups at the table level, and instead create those on your forms. For example, in tblVouchers, you have the FiscalWorker field set to use a Combobox as the lookup. You should NOT be doing this, and should instead create a combo when you place that control on a form. Using table-level lookups hides your data, and you will end up with inaccurate reporting (among other things). The ONLY benefit of a table-level lookup is that is saves you a minute or so when adding that field to a form, since Access will create a combo for you. Your Display Control for all Text/Numeric fields should be Textbox and nothing else.

And take heed of Jeff's comment regarding calcualted fields. You should not store calculated values in your fields. Instead, you should provide those calculations through a query, or on the form level through code.
Bummer man.  All those cool features you discovered and tried to incorporate, the experts are telling you to remove.  It is sad but follow the advice if you are interesting in learning how to actually build applications for other people.  All those "features" are crutches and cause significant problems once you get to the point of using queries or code.

I made a couple of the changes we suggested so that the subform now works correctly.  I also changed the lookups for department and care manager so you could see how they work.  Have fun fixing the rest.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
@Scott and Pat
I see that you guys probably have this covered, I will leave it to you both.
I'm bogged down with other consulting stuff this weekend.

To All:
Enjoy the weekend.

I don't know that we were able to answer all the questions but I for one fixed Karena's application so it works.  That has to be worth something.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.