Writing a new record to a table but cross referencing a Primary Key ID before hand..

Good Morning:

I am using the following code to add new records to a table that is on my Sub Form. Below is the code that I placed in my Before Update event on my Sub Form:

What I am finding is that the control on my form (cboLocation) is sometimes blank and that causes me to error out on adding new records (because it is a null value) even though I have other controls/combo boxes that are displaying what the LocationsID should be.

The LocationsID can be found in my following table:

Table Name :tblLocationsMM
Field Name = DivisionIDFK = Number
Field Name = WrkRegIDFK = Number
Field Name = CreditRegIDFK = Number
Field Name = LocationsID = AutoNumber


Like I mentioned, I am displaying the DivisionIDFK, WrkRegIDFK, CreditRegIDFK via three other controls on my form:
DivisionIDFK = cboDivision
WrkRegIDFK = cboWrkReg
CreditRegIDFK = cboCreditReg

My question is, instead of me referencing the control (cboLocation) on my form to get the LocationsID, is there a way that I code write the code to lookup the LocationsID based on my other controls on my form (cboDivision, cboWrkReg, cboCreditReg) that never blank out?

Any direction would be greatly appreciated

Thanks,

Keith.

re: Writing a new record to a table but cross referencing a Primary Key ID before hand..

Will the combination of these 3 values return a 'Unique' LocationsID?

re: Writing a new record to a table but cross referencing a Primary Key ID before hand..

Yes and I have already established those relationships.

re: Writing a new record to a table but cross referencing a Primary Key ID before hand..

The logic would be something similar to this:

re: Writing a new record to a table but cross referencing a Primary Key ID before hand..

ADezii:

It is nice to know that there are souls out there like yourself. Your solution was perfect and I have been studying your approach and starting to see the light on how to apply Dlookup. With this project I am using Dlookup more than once and in different scenarios that are throwing me off.

Thanks a lot!

Keith.

re: Writing a new record to a table but cross referencing a Primary Key ID before hand..

You are quite welcome, Keith. Anytime we can help you, don't hesitate to give us a call. We have a great and talented community here, and I am happy to be a member of it.