Karena Clay
asked on
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?
form-print-screen.docx
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?
form-print-screen.docx
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.
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.
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.
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.
At this stage in development you have no security.
In most cases, the CustomerID autonumber field should suffice.
FWIW, ...here 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.
Database115.mdb
(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.
Database115.mdb
screenshot
ASKER
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.
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.
You didn't attach your database (and Mr. Hartman is actually a Mrs).
Take a few days to review some of the Templates, and the download that Jeff provided. It will be well worth your time.
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.
ASKER
Here is the attached database
Database-09182015.accdb
Database-09182015.accdb
ASKER
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!!!
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!!!
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.
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.
Most of the databases don't have the two different auto number fields so it is not an apples to apples comparisonYou 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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott and Pat
I see that you guys probably have this covered, ...so I will leave it to you both.
I'm bogged down with other consulting stuff this weekend.
To All:
Enjoy the weekend.
Jeff
I see that you guys probably have this covered, ...so I will leave it to you both.
I'm bogged down with other consulting stuff this weekend.
To All:
Enjoy the weekend.
Jeff
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.
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:?
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.
;-)
JeffCoachman