By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'When records are added it adds the coresponding LocationsID, YearID and Month IDs
  4. 'to the new record.
  5.  
  6. If Me.NewRecord Then
  7.  
  8.     JtnLocationsID = Forms!Forecastform!cboLocation
  9.     YearID = Forms!Forecastform!CboYear
  10.     MonthID = Forms!Forecastform!CboMonth
  11.  
  12. End If
  13. End Sub
  14.  
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.
Mar 19 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'When records are added it adds the coresponding LocationsID, YearID and Month IDs
  4. 'to the new record.
  5.  
  6. If Me.NewRecord Then
  7.  
  8.     JtnLocationsID = Forms!Forecastform!cboLocation
  9.     YearID = Forms!Forecastform!CboYear
  10.     MonthID = Forms!Forecastform!CboMonth
  11.  
  12. End If
  13. End Sub
  14.  
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?
Mar 19 '08 #2

kcdoell
100+
P: 230
Will the combination of these 3 values return a 'Unique' LocationsID?

Yes and I have already established those relationships.
Mar 19 '08 #3

ADezii
Expert 5K+
P: 8,597
Yes and I have already established those relationships.
The logic would be something similar to this:
Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. Set frm = Forms!Forecastform
  4.  
  5. If Me.NewRecord Then
  6.   'If cboLocation is Not Null, grab the value from there
  7.   If Not IsNull(frm![cboLocation]) Then
  8.     JtnLocationsID = frm!cboLocation
  9.     YearID = frm!cboYear
  10.     MonthID = frm!cboMonth
  11.   Else      'Forms!Forecastform![cboLocation] is Null
  12.     'Check and see if all 3 Controls have values in them
  13.     If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
  14.        Not IsNull(frm![cboCreditReg]) Then       'valuesd in all 3 Controls
  15.          JtnLocationsID = DLookup("[LocationsID]", "tblLocationsMM", "[DivisionIDFK] = " & frm![cboDivision] & _
  16.                           " And [WrkRegIDFK] = " & frm![cboWrkReg] & " And [CreditRegIDFK] = " & _
  17.                           frm![cboCreditReg])
  18.          YearID = frm!cboYear
  19.          MonthID = frm!cboMonth
  20.     Else
  21.       'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
  22.       '[cboWrkReg], or [cboCreditReg]
  23.     End If
  24.   End If
  25. End If
Mar 19 '08 #4

kcdoell
100+
P: 230
The logic would be something similar to this:
Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. Set frm = Forms!Forecastform
  4.  
  5. If Me.NewRecord..............................................
  6. End If

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.
Mar 20 '08 #5

ADezii
Expert 5K+
P: 8,597
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.
Mar 20 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.