473,320 Members | 1,850 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

kcdoell
230 100+
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
5 1353
ADezii
8,834 Expert 8TB
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
230 100+
Will the combination of these 3 values return a 'Unique' LocationsID?

Yes and I have already established those relationships.
Mar 19 '08 #3
ADezii
8,834 Expert 8TB
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
230 100+
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
8,834 Expert 8TB
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

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

Similar topics

17
by: Rick | last post by:
Hi all, Is there a MySQL function to get the first record through a query? I would like to open a page of client records with the first one shown. The other records can be accessed from a...
1
by: Justin | last post by:
Hi, In the process of localizing the 'regions' table, we added three new tables. The localized data will be stored in the TokenKeys and TokenValues tables. It would be easier if we did away with...
0
by: Muhd | last post by:
Greetings all, please bare with me this will take a little bit to explain but I'm really hoping someone might have some ideas or can point in the right direction. I'm trying to create "snapshots"...
0
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A...
10
by: Mark | last post by:
I have a table about people containing 25 fields. The table contains the usual fields - first, last, address, city, state and zip. There is no primary key. These fields all have data with the...
8
by: shorti | last post by:
Here is an example of what I want to do (syntax might not be entirely correct as this is just an example): CREATE TABLE ParentA ( name CHAR (6) NOT NULL; address CHAR(64); ) IN CUSTOMER_TS...
7
by: Stephen Poley | last post by:
I have the following situation: - a table of employees, keyed on employee-id; - a table of training sessions, keyed on session-id; - a requirement to log who was present at which session, plus...
4
by: prosad | last post by:
hi; i have two tables in MySQL with: 1. TABLE(stat) column(c_id, FOREIGN KEY) referencing 2. TABLE (complaint) column (id_complaint, PRIMARY KEY). TABLE (complaint) also contain a second...
1
by: lionelm | last post by:
Hi there, I have the following tables: ProductionRun: {ProductionRunID(Primary Key), Product(foreign key referencing ProductRecipe table)} ProductRecipe: {Product(primary key), ....} ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.