Run update quary if...

[murlen]

I have my form set to run an appand quary on close.
how can I set this to run only If a record was added?

Best Regards,
Murlen

 

[Sergeant]

Hows about a little more info?
You see, if the form was attached to the intended recordset, you wouldn't have to faff with that.

 

[murlen]

I'm not sure what you mean by 'attached to the intended recordset'

all I have is;
Private Sub Form_Close()
DoCmd.OpenQuery "Junction1Query", acViewNormal
DoCmd.OpenQuery "Junction2Query", acViewNormal
End Sub

The quarys are;
Junction1Quary:
INSERT INTO Junction1 ( RegionID, TestID, TestNumber, TestDescription )
SELECT Region.RegionID, ServerTest.TestID, ServerTest.TestNumber, ServerTest.TestDescription
FROM Region, ServerTest
WHERE Region.RegionID not in (Select RegionID from Junction1);

Junction2Quary:
INSERT INTO Junction2 ( ChartID, TestID, TestNumber, TestDescription )
SELECT ChartInfo.ChartID, CellTest.TestID, CellTest.TestNumber, CellTest.TestDescription
FROM ChartInfo, CellTest
WHERE ChartInfo.ChartID not in (Select ChartID from Junction2);

The quarys only needed to be run when a new record is added to the chartinfo table, changes made to the current records do not affect the junction tables.


thanks,
Murlen

 

[Sergeant]

So what is the data source for form you speak of?

 

[murlen]

The RecordSource is ChartInfo

 

[Sergeant]

Given that things are designed as they are, I think that I'd use a form level variable to carry a boolean 'IsNewRecord as Boolean = False' (false = new record not added). On the ?before insert? event of your form, you could set that var to true. Then, on Form_Close, you can check the var and run the querries accordingly.

 

[murlen]

Sorry...I'm doing something wrong,
I'm getting an error on BeforeInsert "function call on left hand side of assignment must return variant or object"

heres what I have;

Function IsNewRecord() As Boolean

IsNewRecord = False

End Function

Private Sub Form_BeforeInsert(Cancel As Integer)

IsNewRecord = True

End Sub

Private Sub Form_Close()

If IsNewRecord = True Then
DoCmd.OpenQuery "Junction1Query", acViewNormal
DoCmd.OpenQuery "Junction2Query", acViewNormal
End If

End Sub

 

[Sergeant]

I wasn't thinking of a function, but a form level variable...
IsNewRecord as boolean

 

[murlen]

I'm unable to get pass the error "outside type block"

Option Compare Database
Option Explicit
IsNewRecord As Boolean

Private Sub Check53_AfterUpdate()
DoCmd.SetWarnings False
If Me.Check53 = True Then
DoCmd.OpenQuery "Junction2Int", acViewNormal
End If
DoCmd.SetWarnings True
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
IsNewRecord = True
End Sub

Private Sub Form_Close()
If IsNewRecord = True Then
DoCmd.OpenQuery "Junction1Query", acViewNormal
End If
End Sub

Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull([ChartID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub

Private Sub NewENC_Click()
On Error GoTo Err_NewENC_Click


DoCmd.GoToRecord , , acNewRec

Exit_NewENC_Click:
Exit Sub

Err_NewENC_Click:
MsgBox Err.Description
Resume Exit_NewENC_Click

End Sub