How to Save Records only if no records exist in another table?

G

Guest

I have searched through the forum for examples on saving records only if a
certain condition exists but can't seem to figure out where to add the "if"
statement in my code below. I have created a query to test if a contract#
exists or not. If it does NOT exist in the Billing table, then go ahead and
SAVE any field changes to Effective_contract_detail table and to the audit
trail, Effective_contract_detail_log table. If the contract# exists in
Billing table, then DO NOT SAVE to Effective_Contract_detail table (because
no changes are allowed) but do SAVE to the Effective_contract_detail_log
table. Help, please. Thank you!!

gg

Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

Dim rst As New ADODB.Recordset, SQLStmt As String
Me!Creation_date = Now()

SQLStmt = "select product_no from product where prod_no = " & Me!Prod_no & "
and prodgrp_no = " & Me!Prodgrp_no & " and pfee_no = " & Me!Pfee_no & " and
prodtype_no = " & Me!Prodtype_no
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rst
If Not rst.EOF Then
Me!product_no = rst!product_no
End If
End With
rst.Close
Set rst = Nothing

If (IsNull(Me!product_no)) Then
MsgBox ("Error, invalid product combination")
End If

Dim contract_detail_log As New ADODB.Recordset
' Log changes to the contract_detail_log table.

If Me!product_no <> preProduct_no Or _
Me!doc_type <> preDoc_type Or _
Me!Orig_amount <> preOrig_amount Or _
Me!Amount_discount <> preAmount_discount Or _
Me!PO_no <> prePO_no Or _
Me!Billing_comments <> preBilling_comments Or _
Me!Comments <> preComments Or _
Me!Billing_freq <> preBilling_freq Or _
Me!Billing_type <> preBilling_type Or _
Me!Effective_start_date <> preEffective_start_date Or _
Me!Acceptance_date <> preAcceptance_date Or _
Me!Contract_end_date <> preEffective_end_date Or _
Me!SOP_no <> preSOP_no Or _
Me!Revenue_freq <> preRevenue_freq Then

contract_detail_log.Open "Effective_contract_detail_log",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Saves any field changes to the below fields in the
Effective_contractc_detail_log.

With contract_detail_log
.AddNew
![Contract_no] = Me!Contract_no
![Line_item] = Me!Line_item
![product_no] = preProduct_no
![doc_type] = preDoc_type
![Orig_amount] = preOrig_amount
![Amount_discount] = preAmount_discount
![PO_no] = prePO_no
![Billing_comments] = preBilling_comments
![Comments] = preComments
![Billing_freq] = preBilling_freq
![Billing_type] = preBilling_type
![Effective_start_date] = preEffective_start_date
![Acceptance_date] = preAcceptance_date
![Contract_end_date] = preEffective_end_date
![Revenue_freq] = preRevenue_freq
![SOP_no] = preSOP_no
![log_date] = Now()
![log_user] = Null
.Update
End With
contract_detail_log.Close
Set contract_detail_log = Nothing
End If
'This saves any updated fields to both the Effective_contract_detail_log
and the Effective_contract_detail tables.
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If
If [Orig_amount] = 0 Then
MsgBox ("Be sure to enter an amount.")
Me!Orig_amount.SetFocus
Exit Sub
End If

Exit_Command223_Click:
MsgBox ("Record saved!")
Exit Sub
 
G

George Nicholson

As currently written, your code should *add* a record (not save changes) to
Effective_contract_detail_log if any changes have been made to the specified
fields. If you also want to add a record when no changes have been made to
those fields, then simply remove the very,very long "If Me!product_no <>
preProduct_no Or ..." statement and you will always add a record whenever
the piece of code runs.

If there are conditions under which you do *NOT* want to add a record to the
Log, then I don't think we have enough info to answer the question.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


gg said:
I have searched through the forum for examples on saving records only if a
certain condition exists but can't seem to figure out where to add the
"if"
statement in my code below. I have created a query to test if a
contract#
exists or not. If it does NOT exist in the Billing table, then go ahead
and
SAVE any field changes to Effective_contract_detail table and to the audit
trail, Effective_contract_detail_log table. If the contract# exists in
Billing table, then DO NOT SAVE to Effective_Contract_detail table
(because
no changes are allowed) but do SAVE to the Effective_contract_detail_log
table. Help, please. Thank you!!

gg

Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

Dim rst As New ADODB.Recordset, SQLStmt As String
Me!Creation_date = Now()

SQLStmt = "select product_no from product where prod_no = " & Me!Prod_no &
"
and prodgrp_no = " & Me!Prodgrp_no & " and pfee_no = " & Me!Pfee_no & "
and
prodtype_no = " & Me!Prodtype_no
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rst
If Not rst.EOF Then
Me!product_no = rst!product_no
End If
End With
rst.Close
Set rst = Nothing

If (IsNull(Me!product_no)) Then
MsgBox ("Error, invalid product combination")
End If

Dim contract_detail_log As New ADODB.Recordset
' Log changes to the contract_detail_log table.

If Me!product_no <> preProduct_no Or _
Me!doc_type <> preDoc_type Or _
Me!Orig_amount <> preOrig_amount Or _
Me!Amount_discount <> preAmount_discount Or _
Me!PO_no <> prePO_no Or _
Me!Billing_comments <> preBilling_comments Or _
Me!Comments <> preComments Or _
Me!Billing_freq <> preBilling_freq Or _
Me!Billing_type <> preBilling_type Or _
Me!Effective_start_date <> preEffective_start_date Or _
Me!Acceptance_date <> preAcceptance_date Or _
Me!Contract_end_date <> preEffective_end_date Or _
Me!SOP_no <> preSOP_no Or _
Me!Revenue_freq <> preRevenue_freq Then

contract_detail_log.Open "Effective_contract_detail_log",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Saves any field changes to the below fields in the
Effective_contractc_detail_log.

With contract_detail_log
.AddNew
![Contract_no] = Me!Contract_no
![Line_item] = Me!Line_item
![product_no] = preProduct_no
![doc_type] = preDoc_type
![Orig_amount] = preOrig_amount
![Amount_discount] = preAmount_discount
![PO_no] = prePO_no
![Billing_comments] = preBilling_comments
![Comments] = preComments
![Billing_freq] = preBilling_freq
![Billing_type] = preBilling_type
![Effective_start_date] = preEffective_start_date
![Acceptance_date] = preAcceptance_date
![Contract_end_date] = preEffective_end_date
![Revenue_freq] = preRevenue_freq
![SOP_no] = preSOP_no
![log_date] = Now()
![log_user] = Null
.Update
End With
contract_detail_log.Close
Set contract_detail_log = Nothing
End If
'This saves any updated fields to both the
Effective_contract_detail_log
and the Effective_contract_detail tables.
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If
If [Orig_amount] = 0 Then
MsgBox ("Be sure to enter an amount.")
Me!Orig_amount.SetFocus
Exit Sub
End If

Exit_Command223_Click:
MsgBox ("Record saved!")
Exit Sub
Click to expand...
 
G

Guest

Thanks, but how do I save the contract record (not the log record) only if
the contract# does NOT reside in another table (I have the fields in the
query)? I have added the "if" statement:
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If

Thanks, George!



George Nicholson said:
As currently written, your code should *add* a record (not save changes) to
Effective_contract_detail_log if any changes have been made to the specified
fields. If you also want to add a record when no changes have been made to
those fields, then simply remove the very,very long "If Me!product_no <>
preProduct_no Or ..." statement and you will always add a record whenever
the piece of code runs.

If there are conditions under which you do *NOT* want to add a record to the
Log, then I don't think we have enough info to answer the question.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


gg said:
I have searched through the forum for examples on saving records only if a
certain condition exists but can't seem to figure out where to add the
"if"
statement in my code below. I have created a query to test if a
contract#
exists or not. If it does NOT exist in the Billing table, then go ahead
and
SAVE any field changes to Effective_contract_detail table and to the audit
trail, Effective_contract_detail_log table. If the contract# exists in
Billing table, then DO NOT SAVE to Effective_Contract_detail table
(because
no changes are allowed) but do SAVE to the Effective_contract_detail_log
table. Help, please. Thank you!!

gg

Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

Dim rst As New ADODB.Recordset, SQLStmt As String
Me!Creation_date = Now()

SQLStmt = "select product_no from product where prod_no = " & Me!Prod_no &
"
and prodgrp_no = " & Me!Prodgrp_no & " and pfee_no = " & Me!Pfee_no & "
and
prodtype_no = " & Me!Prodtype_no
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rst
If Not rst.EOF Then
Me!product_no = rst!product_no
End If
End With
rst.Close
Set rst = Nothing

If (IsNull(Me!product_no)) Then
MsgBox ("Error, invalid product combination")
End If

Dim contract_detail_log As New ADODB.Recordset
' Log changes to the contract_detail_log table.

If Me!product_no <> preProduct_no Or _
Me!doc_type <> preDoc_type Or _
Me!Orig_amount <> preOrig_amount Or _
Me!Amount_discount <> preAmount_discount Or _
Me!PO_no <> prePO_no Or _
Me!Billing_comments <> preBilling_comments Or _
Me!Comments <> preComments Or _
Me!Billing_freq <> preBilling_freq Or _
Me!Billing_type <> preBilling_type Or _
Me!Effective_start_date <> preEffective_start_date Or _
Me!Acceptance_date <> preAcceptance_date Or _
Me!Contract_end_date <> preEffective_end_date Or _
Me!SOP_no <> preSOP_no Or _
Me!Revenue_freq <> preRevenue_freq Then

contract_detail_log.Open "Effective_contract_detail_log",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Saves any field changes to the below fields in the
Effective_contractc_detail_log.

With contract_detail_log
.AddNew
![Contract_no] = Me!Contract_no
![Line_item] = Me!Line_item
![product_no] = preProduct_no
![doc_type] = preDoc_type
![Orig_amount] = preOrig_amount
![Amount_discount] = preAmount_discount
![PO_no] = prePO_no
![Billing_comments] = preBilling_comments
![Comments] = preComments
![Billing_freq] = preBilling_freq
![Billing_type] = preBilling_type
![Effective_start_date] = preEffective_start_date
![Acceptance_date] = preAcceptance_date
![Contract_end_date] = preEffective_end_date
![Revenue_freq] = preRevenue_freq
![SOP_no] = preSOP_no
![log_date] = Now()
![log_user] = Null
.Update
End With
contract_detail_log.Close
Set contract_detail_log = Nothing
End If
'This saves any updated fields to both the
Effective_contract_detail_log
and the Effective_contract_detail tables.
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If
If [Orig_amount] = 0 Then
MsgBox ("Be sure to enter an amount.")
Me!Orig_amount.SetFocus
Exit Sub
End If

Exit_Command223_Click:
MsgBox ("Record saved!")
Exit Sub
Click to expand...
Click to expand...
 
G

George Nicholson

gg said:
Thanks, but how do I save the contract record (not the log record) only if
the contract# does NOT reside in another table (I have the fields in the
query)? I have added the "if" statement:
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If

Thanks, George!
Click to expand...

That doesn't work for you? Oh, maybe it's the missing "Then" at the end?

If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no)) Then

HTH,
George Nicholson said:
As currently written, your code should *add* a record (not save changes)
to
Effective_contract_detail_log if any changes have been made to the
specified
fields. If you also want to add a record when no changes have been made
to
those fields, then simply remove the very,very long "If Me!product_no <>
preProduct_no Or ..." statement and you will always add a record whenever
the piece of code runs.

If there are conditions under which you do *NOT* want to add a record to
the
Log, then I don't think we have enough info to answer the question.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


gg said:
I have searched through the forum for examples on saving records only if
a
certain condition exists but can't seem to figure out where to add the
"if"
statement in my code below. I have created a query to test if a
contract#
exists or not. If it does NOT exist in the Billing table, then go
ahead
and
SAVE any field changes to Effective_contract_detail table and to the
audit
trail, Effective_contract_detail_log table. If the contract# exists in
Billing table, then DO NOT SAVE to Effective_Contract_detail table
(because
no changes are allowed) but do SAVE to the
Effective_contract_detail_log
table. Help, please. Thank you!!

gg

Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

Dim rst As New ADODB.Recordset, SQLStmt As String
Me!Creation_date = Now()

SQLStmt = "select product_no from product where prod_no = " &
Me!Prod_no &
"
and prodgrp_no = " & Me!Prodgrp_no & " and pfee_no = " & Me!Pfee_no & "
and
prodtype_no = " & Me!Prodtype_no
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rst
If Not rst.EOF Then
Me!product_no = rst!product_no
End If
End With
rst.Close
Set rst = Nothing

If (IsNull(Me!product_no)) Then
MsgBox ("Error, invalid product combination")
End If

Dim contract_detail_log As New ADODB.Recordset
' Log changes to the contract_detail_log table.

If Me!product_no <> preProduct_no Or _
Me!doc_type <> preDoc_type Or _
Me!Orig_amount <> preOrig_amount Or _
Me!Amount_discount <> preAmount_discount Or _
Me!PO_no <> prePO_no Or _
Me!Billing_comments <> preBilling_comments Or _
Me!Comments <> preComments Or _
Me!Billing_freq <> preBilling_freq Or _
Me!Billing_type <> preBilling_type Or _
Me!Effective_start_date <> preEffective_start_date Or _
Me!Acceptance_date <> preAcceptance_date Or _
Me!Contract_end_date <> preEffective_end_date Or _
Me!SOP_no <> preSOP_no Or _
Me!Revenue_freq <> preRevenue_freq Then

contract_detail_log.Open "Effective_contract_detail_log",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Saves any field changes to the below fields in the
Effective_contractc_detail_log.

With contract_detail_log
.AddNew
![Contract_no] = Me!Contract_no
![Line_item] = Me!Line_item
![product_no] = preProduct_no
![doc_type] = preDoc_type
![Orig_amount] = preOrig_amount
![Amount_discount] = preAmount_discount
![PO_no] = prePO_no
![Billing_comments] = preBilling_comments
![Comments] = preComments
![Billing_freq] = preBilling_freq
![Billing_type] = preBilling_type
![Effective_start_date] = preEffective_start_date
![Acceptance_date] = preAcceptance_date
![Contract_end_date] = preEffective_end_date
![Revenue_freq] = preRevenue_freq
![SOP_no] = preSOP_no
![log_date] = Now()
![log_user] = Null
.Update
End With
contract_detail_log.Close
Set contract_detail_log = Nothing
End If
'This saves any updated fields to both the
Effective_contract_detail_log
and the Effective_contract_detail tables.
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If
If [Orig_amount] = 0 Then
MsgBox ("Be sure to enter an amount.")
Me!Orig_amount.SetFocus
Exit Sub
End If

Exit_Command223_Click:
MsgBox ("Record saved!")
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It was a typo in my post. Thanks!

I still am missing some line of code to get this logic to work. It is still
either saving to both tables or not at all.

George Nicholson said:
gg said:
Thanks, but how do I save the contract record (not the log record) only if
the contract# does NOT reside in another table (I have the fields in the
query)? I have added the "if" statement:
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If

Thanks, George!
Click to expand...

That doesn't work for you? Oh, maybe it's the missing "Then" at the end?

If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no)) Then

HTH,
George Nicholson said:
As currently written, your code should *add* a record (not save changes)
to
Effective_contract_detail_log if any changes have been made to the
specified
fields. If you also want to add a record when no changes have been made
to
those fields, then simply remove the very,very long "If Me!product_no <>
preProduct_no Or ..." statement and you will always add a record whenever
the piece of code runs.

If there are conditions under which you do *NOT* want to add a record to
the
Log, then I don't think we have enough info to answer the question.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


I have searched through the forum for examples on saving records only if
a
certain condition exists but can't seem to figure out where to add the
"if"
statement in my code below. I have created a query to test if a
contract#
exists or not. If it does NOT exist in the Billing table, then go
ahead
and
SAVE any field changes to Effective_contract_detail table and to the
audit
trail, Effective_contract_detail_log table. If the contract# exists in
Billing table, then DO NOT SAVE to Effective_Contract_detail table
(because
no changes are allowed) but do SAVE to the
Effective_contract_detail_log
table. Help, please. Thank you!!

gg

Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

Dim rst As New ADODB.Recordset, SQLStmt As String
Me!Creation_date = Now()

SQLStmt = "select product_no from product where prod_no = " &
Me!Prod_no &
"
and prodgrp_no = " & Me!Prodgrp_no & " and pfee_no = " & Me!Pfee_no & "
and
prodtype_no = " & Me!Prodtype_no
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rst
If Not rst.EOF Then
Me!product_no = rst!product_no
End If
End With
rst.Close
Set rst = Nothing

If (IsNull(Me!product_no)) Then
MsgBox ("Error, invalid product combination")
End If

Dim contract_detail_log As New ADODB.Recordset
' Log changes to the contract_detail_log table.

If Me!product_no <> preProduct_no Or _
Me!doc_type <> preDoc_type Or _
Me!Orig_amount <> preOrig_amount Or _
Me!Amount_discount <> preAmount_discount Or _
Me!PO_no <> prePO_no Or _
Me!Billing_comments <> preBilling_comments Or _
Me!Comments <> preComments Or _
Me!Billing_freq <> preBilling_freq Or _
Me!Billing_type <> preBilling_type Or _
Me!Effective_start_date <> preEffective_start_date Or _
Me!Acceptance_date <> preAcceptance_date Or _
Me!Contract_end_date <> preEffective_end_date Or _
Me!SOP_no <> preSOP_no Or _
Me!Revenue_freq <> preRevenue_freq Then

contract_detail_log.Open "Effective_contract_detail_log",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Saves any field changes to the below fields in the
Effective_contractc_detail_log.

With contract_detail_log
.AddNew
![Contract_no] = Me!Contract_no
![Line_item] = Me!Line_item
![product_no] = preProduct_no
![doc_type] = preDoc_type
![Orig_amount] = preOrig_amount
![Amount_discount] = preAmount_discount
![PO_no] = prePO_no
![Billing_comments] = preBilling_comments
![Comments] = preComments
![Billing_freq] = preBilling_freq
![Billing_type] = preBilling_type
![Effective_start_date] = preEffective_start_date
![Acceptance_date] = preAcceptance_date
![Contract_end_date] = preEffective_end_date
![Revenue_freq] = preRevenue_freq
![SOP_no] = preSOP_no
![log_date] = Now()
![log_user] = Null
.Update
End With
contract_detail_log.Close
Set contract_detail_log = Nothing
End If
'This saves any updated fields to both the
Effective_contract_detail_log
and the Effective_contract_detail tables.
If IsNull(Dlookup("contract_no", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!contract_no))
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Else
DoCmd.CancelEvent
Me.Requery
End If
If [Orig_amount] = 0 Then
MsgBox ("Be sure to enter an amount.")
Me!Orig_amount.SetFocus
Exit Sub
End If

Exit_Command223_Click:
MsgBox ("Record saved!")
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
Top