Question: Why can't I add new records from form view after I enforced my relationships?

[goldwhale89]

Hi guys,


I am working on a systems database project and prior to enforcing my relationships, I could add records from the form view. After enforcing my relationships, I can't. I've been banging my head for two days trying to figure this out and I just can't. I am also having trouble with my print invoice button because it is asking for a parameter value as opposed to printing the current invoice number on the form. If anyone has any suggestions or would be willing to take a look I would really appreciate it.

[davegri]

Sure, we'll take a look. Post the db here.

[goldwhale89]

Hey guys so I tried to upload the file and the forum wouldn't let me, so I am posting a google drive shareable link hopefully it works. Thank you!!

https://drive.google.com/open?id=1jU...IXw8MQvU6fQIVf

[davegri]

Lead us through your procedures that produce the error.
I noticed one thing immediately in tblInv-Request. Neither of the keys should be prime keys and should allow duplicates. Create a new prime key (autonumber) and name it invRequest_PK. You may or may not need it later.

[isladogs]

Hi GW89
Looks like you're in good hands with davegri already.
For future reference not everyone will open external files.
In future, try reducing file size below the limit by removing most records, compacting then zipping your database.

[goldwhale89]

From what I thought was when I created the relationships and enforced the relationships that is when the error occurred. When I go into form view and try to add new records I get a notification saying that a related record must be in the table first. For tblInv-Request, my professor had me name those 2 as my primary keys because she said it was my linking table. I'm sorry I don't know too much about databases this is my first time taking an Access based class and it's been kind of tough for me. Feel free to change anything because I have an original copy.

And got it, ridders52. Thanks for the heads up.

[davegri]

This will fix the invoice report. Macros are just sneaky tricky.

[davegri]

When I go into form view and try to add new records

What form? How do you get to the form? What needs to be entered into the form to produce the error? No mind readers here...

[June7]

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. I am surprised instructor did not inform of this.

As for tblInv-Request, you have compound key. This should actually be okay but could just set them as compound index to prevent duplicate pairs. I avoid compound keys if at all possible (only resorted to once). Agree with Dave that if you do ever need to associate a dependent table then use an autonumber PK.

Many would advise not to use text fields as PK/FK because text indexes slower. Many also advocate not to use data that has meaning to users as PK/FK. I am not in either camp.

I never use macros, only VBA.

Form of issue probably sfrmInv-Request which has query as RecordSource that includes related tables with INNER JOIN. Don't include the related tables. Options for data entry for many-to-many relationship:

1. single form bound to junction table (sfrmInv-Request) with comboboxes to select from tblInventory and tblSaleRequest

2. main form bound to tblInventory and subform bound to sfrmInv-Request with combobox to select from tblSaleRequest

3. main form bound to tblSaleRequest and subform bound to sfrmInv-Request with combobox to select from tblInventory

Form sfrmItemsSold has an aggregate query as RecordSource which is not an editable dataset. Why use a form for this data display?

[goldwhale89]

Wow, thank you June 7! I have attached the zipped DB so hopefully it works. I'm still having an issue with adding records from form view, but davegri's suggestion for the print invoice issue worked perfectly! So thank you for that!

[June7]

I may have edited my previous post after you read it. Review again.

You still haven't advised which form has the issue. Did I find it?

[goldwhale89]

Sorry, davegri. FrmSale-Working, FrmDonation, and frmSaleRequest are the forms I am referring to. I am getting to the form either through the submenus (e.g. menu- donation with sales event, submenu- record new sale, then pops up frmSaleworking) or just by even clicking the forms themselves. When I open those forms and try to add new data , for example, if I were to open frmSale Working and add a new sale once I'm done adding data (Donor Invoice, Volunteer Operator, Acct. #, Date of Donation, etc.) I then try to save the form with the new record then I get the alert that I can't because a related record must be in the table first. Hopefully, that makes it a bit more clear.

[June7]

My description of many-to-many relationship data entry form options in post 9 applies here as well. tblDonationSale is a 'junction' table that establishes relationship between tblCash and tblDonor. With main form bound to tblDonationSale, those two related tables can be considered as 'lookup' sources and need comboboxes to select values from each table. With referential integrity enforced, relevant lookup records must already exist for record to be established in junction table. If you need to add new 'lookup' records 'on the fly' during data entry, that's what the combobox NotInList event is for.

If you want to add new donor via the subform, then that record must be committed to table before data entry on the main form so that DonorID is available. Form/subforms are not ordinarily set up this way (refer to my 3 options). There is no subform for tblCash. How do you assure record exists or even know what account numbers are available to use?

Record is committed when: 1) close table/query/form; or 2) move to another record of the same table/query/form; or 3) run code to save.

Again, strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[davegri]

COMPACT DB-davegri-v01.zip
Here's a simpler solution.
Got rid of a couple of tables, reworked/replaced all forms. Added a LOT of comboboxes to lookup/display fields.
Added a bit of VBA with forms. Don't know how many of the mcr procedures (macros) still apply. Very few probably.
Any new reports you define will automatically appear on frmReports.

[goldwhale89]

Wow! Thank you davegri This helps so much. it's interesting because the tables you removed are the tables my professor made me add. This would be the second time her suggestions made it harder on me. Thank you again!