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

[kcdoell]

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:

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

'When records are added it adds the coresponding LocationsID, YearID and Month IDs
'to the new record.

If Me.NewRecord Then

JtnLocationsID = Forms!Forecastf orm!cboLocation
YearID = Forms!Forecastf orm!CboYear
MonthID = Forms!Forecastf orm!CboMonth

End If
End Sub
[/code]

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.

[ADezii]

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:

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

'When records are added it adds the coresponding LocationsID, YearID and Month IDs
'to the new record.

If Me.NewRecord Then

JtnLocationsID = Forms!Forecastf orm!cboLocation
YearID = Forms!Forecastf orm!CboYear
MonthID = Forms!Forecastf orm!CboMonth

End If
End Sub
[/code]

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.

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

[kcdoell]

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

Yes and I have already established those relationships.

[ADezii]

Yes and I have already established those relationships.

The logic would be something similar to this:
[CODE=vb]
Dim frm As Form

Set frm = Forms!Forecastf orm

If Me.NewRecord Then
'If cboLocation is Not Null, grab the value from there
If Not IsNull(frm![cboLocation]) Then
JtnLocationsID = frm!cboLocation
YearID = frm!cboYear
MonthID = frm!cboMonth
Else 'Forms!Forecast form![cboLocation] is Null
'Check and see if all 3 Controls have values in them
If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
Not IsNull(frm![cboCreditReg]) Then 'valuesd in all 3 Controls
JtnLocationsID = DLookup("[LocationsID]", "tblLocationsMM ", "[DivisionIDFK] = " & frm![cboDivision] & _
" And [WrkRegIDFK] = " & frm![cboWrkReg] & " And [CreditRegIDFK] = " & _
frm![cboCreditReg])
YearID = frm!cboYear
MonthID = frm!cboMonth
Else
'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
'[cboWrkReg], or [cboCreditReg]
End If
End If
End If[/CODE]

[kcdoell]

The logic would be something similar to this:
[CODE=vb]
Dim frm As Form

Set frm = Forms!Forecastf orm

If Me.NewRecord... ............... ............... .............
End If[/CODE]

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.

[ADezii]

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.

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.