Need help with new record being added and subform data

[SGrohola]

Good day! I have a form to track owner/operators, based on tbl_Owner Operators. I have another table, tbl_Addresses, which houses the addresses for the owner/operators. tbl_addresses has it's own primary key, and there is a one-to-many link between each table. (tbl_Owner Operators (1) to tbl_Addresses (many)).



My problem is, when I want to add a new owner operator to my form, it lets me. Then, if I click on the button to open sub-form to add address, it lets me add the address, but when I try to save and close, I get an error stating "You cannot add or change a record because a related record is required in table "tbl_Owner Operators". It does this for new records, and if I try to add an address to an existing record. I created the separate table for the address, as my boss wants us to keep all addresses for each owner operator.

When I check the relationship, it's a one-to-many relationship. And the sub-form is linked via the same relationship to the main form.

Can anyone help? I seem to be stuck here.

[ranman256]

Why a separate table for address?

doesnt an owner live in 1 address? Why would they have more?
all mine are in the person name table. 1to1.

[pbaldy]

If you're opening a separate form, you might need to explicitly save the operator record first. A form/subform I'd expect to keep up.

Ranman: physical address, mailing address, shipping address, legal service address, etc. I've never needed a separate table, but it's not inconceivable either. Heck, I've got a home address and a mailing address (PO Box).

[SGrohola]

My Director wants to keep a running list of addresses. Doesn't make sense to me either, but I'm not the boss. So, if there are address changes, I need to be able to add a new address to that driver. The Employee ID under the address table is the same as the ID (Primary key) under tbl_Owner Operator. When I add to the actual table, there is no problem adding the address, or an additional address. The problem is at the form level. I have a parent/child relationship from the Owner/Operator form to the subform-addresses. I created a button on the main form that opens the Address subform, and i tried adding a "refresh" command and a "save" command when the button is clicked, before it opens the address subform. Still not getting anywhere.

[Bulzie]

Sounds like since you have a relationship created between the tables, the OwnerID FK is not getting saved to the Addresses table. So if I am wrong ignore the rest But if not, on the Subform, inthe LinkMaster and LinkChild properties, put the ID for LinkMaster and EmployeeID for LinkChild. If that does not add the value of ID when either before you save the record Before Insert or when the form is open OnOpen or OnLoad. Something like:

Me.EmployeeID = Forms![frmOwner]![ID]

Add the EmployeeID field to the addresses but make invisible if you don't want to show it.

[SGrohola]

I'm still getting an error. I added the code "Me.EmployeeID=Forms![frm_Main1]![ID] to OnOpen for the Address subform. I get a run-time error, stating "You can't assign a value to this object".

I have no problems when I do this using just opening the main table (tbl_Owner Operators). The subdatasheet (tbl_Addresses) opens, and I can add all the addresses that I want, using the ID assigned to each owner/operator. But, I can not get the connection in the form and subform. Any help?

[Bulzie]

Again not sure if that is your issue, when you start to enter data in the subform, look at your EmployeeID field, does it have a value when you start to enter data?

Did you try this:

But if not, on the Subform, inthe LinkMaster and LinkChild properties, put the ID for LinkMaster and EmployeeID for LinkChild.

Also try that code Me.EmployeeID=Forms![frm_Main1]![ID] in the OnLoad instead of OnOpen.
You can also maybe move Me.EmployeeID=Forms![frm_Main1]![ID] in the BeforeInsert event.

[SGrohola]

The Address subform is set up as a button. When the button is clicked on the main form (Owner Operator), it brings up the Address form (as a popup).

The OnClick command shows:


So, the relationship is there between the Main form and the address form. Did I not make the connection correctly?

And you are right, unless the address was already associated to the owner/operator, it does not add the employee ID in the subform.

[SGrohola]

Let me add.... there are already owner/operators in the main form that have addresses assigned to them. Where I'm having problems, is trying to add an additional address to an existing owner/operator, or when adding a new owner/operator, adding any addresses.

[Bulzie]

Try what I wrote in post #7. Need to make sure that ID is getting into EmployeeID field when you add a new record.

Or do this. Remove the Relationship between the 2 tables in the Relationship window. Then go add a new address for someone, and then go look in the Address table to see if that EmployeeID is populated. If it is not then that is your issue as I stated above.

[SGrohola]

It worked!!! Thank you!! I removed the button at first, and just created a subform, and had no problems, so I knew the connection was there.

I recreated the button with the frm_Addresses, and made the parent-child relationship, but was still having the same problem, getting an error, stating "You cannot add or change a record because a related record is required in table "tbl_Owner Operators".

I went back to your last reply, and moved the code from the OnOpen to the OnLoad and also added it to BeforeInsert (just to play it safe.)

THANK YOU SO MUCH!!! I've been pulling my hair out trying to figure this out!!

[Bulzie]

Awesome!!!