Run Code OnClose

[esskaykay]

Hello,

I’m having a problem when attempting to run code upon closing a form. I have an IF statement on the OnClose property of a form. However, it’s always running the quesry against record #1. I want it to use data from the current record.

CODE:
If Me.Request_No > 0 And Me.Grades = -1 Then
Do this
Else
Otherwise do this
End If

Any suggestions,
Thanks,
SKK

 

[HiTechCoach]

You will probably need to put the code in the form's Before Update or After Update event.

Without seeing the actual code for what you are doing, I am not sure which event will be best.

 

[esskaykay]

I'll try that.

Thanks,
SKK

 

[missinglinq]

Boyd's correct; by the time the form's OnClose event fires, there is no current record! And what happens if the user works with more than one record? From looking at the code, it really looks like something that needs to be in the Form_BeforeUpdate event.

 

[esskaykay]

I've been playing with this a bit but still no luck. I wrote some basic test code with messages to tell me what record I'm on. First I assigned the code to a test command button's OnClick. It worked fine. I then assigned the code to the form's OnClose and it always grabbed the data for record #1 (Request_No). Next, I tried the BeforeUpdate and AfterUpdate but still no luck.

Here's my code...

OnClose CODE:

If Me.Grades = -1 Then
If Me.Request_No > 0 Then
GoTo GoodBye
Else
DoCmd.RepaintObject
DoCmd.RunCommand acCmdSaveRecord

DoCmd.RunSQL "INSERT INTO tblGrades ( Permit_No, RequestDate, Location, Contractor, RequestBy, Phone, RequestTime )SELECT tblPermits.Permit_No, tblPermits.PermitDate, tblPermits.Par_Addr, tblPermits.Contractor, tblPermits.RequestBy, tblPermits.Phone, txtTime FROM (tblPermits INNER JOIN tblLegals ON tblPermits.Notice_ID = tblLegals.Notice_ID) LEFT JOIN tblContractor ON tblPermits.Contractor = tblContractor.Contractor WHERE ((([tblPermits.Permit_No])=[forms]![frmPermits]![Permit_No]))"

DoCmd.OpenForm "frmGrades"
DoCmd.RunCommand acCmdRecordsGoToLast
End If
Else
End If
GoodBye:


Command Button OnClick TEST CODE:
If Me.Grades = -1 Then
If Me.Request_No > 0 Then
MsgBox "NOPE" & " - " & Me.Request_No
GoTo GoodBye
Else
MsgBox "YUP" & " - " & Me.Request_No
End If
Else
MsgBox "NOPE" & " - " & Me.Request_No
End If
GoodBye:






I'm probably missing some little thing, but I'm no programmer so please bear with me.

Thanks again,
SKK

 

[HiTechCoach]

The On Close event will probably never work. It should not fire until the data in the form has been unloaded. So I can not see how it will ever work in the On Close event.

Are you getting error messages?

What is not working?

There is a typo in your SQL statement:

... [B])S[/B]ELECT ...

needs a space before the Select like this:

... [B]) S[/B]ELECT ...

 

[esskaykay]

Thank you. I'll will adjust.

SKK

 

[HiTechCoach]

I would also change this:

Code:

... ([tblPermits.Permit_No])=[forms]![frmPermits]![Permit_No]) ...

to

Code:

... ([tblPermits.Permit_No])=" & [forms]![frmPermits]![Permit_No] & ") ...

This assumes that Permit_No is a numeric data type.


You might try the Unload event of the form is you do not want to use the Before or After Update events.