Can't close or hide form w/code 1

[dcurtis]

I posted this in the MSAccess Form thread and didn't get a response. Thanks in advance for any help

--

My form is based on an ask query (2 table, multi-field primary key). Once the user answers the prompts I have written code to check to see if one of the fields for the primary key is empty. The way it is set up, if one field is empty, there is no record in the underlying table. In the case of no records, the form is supposed to close and open another form where the use can enter data. I can get the other form to open, but no matter what I do, the original form stays in front or using the DoCmd to close it causes an error.

I have tried adding code to the OnLoad and the OnOpen to perform the check for an empty field and change forms, but can't seem to get it to run at all.

The SetFocus line under Case Is came about because if I don't change the focus, the MsgBox runs twice (I think once for each field in the ask query that the user needs to enter criteria for).

Also, from the ...Close ac form, "frmModifyPaySheet"....
line an error is produced:

"This action can't be carried out whlie processing a form or report event"

I realize this may be "long winded" and fragmented, but I don't know how else to explain it. Thanks for any help.

Here is my code:

Private Sub Initials_GotFocus()

Dim myValue As Variant
myValue = Me.Initials

myValue = IIf(IsNull(myValue), 0, myValue)

Select Case myValue
Case Is = 0
Me.butCloseModifyForm.SetFocus
MsgBox "You do not have a pay sheet started" & _
" for that week." & vbCrLf & "You will be taken to" & _
" the Enter Pay Sheet Form"

DoCmd.OpenForm "frmPayroll_Entry"
DoCmd.Close acForm, "frmModifyPaySheet", acSaveNo
Case Else

End Select

End Sub

------

A program is a spell cast over a computer, turning input into error messages.

 

[SBendBuckeye]

To hide the current form just before opening up the new done add this line of code and change the second one:

Me.Visible = False
DoCmd.OpenForm "frmPayroll_Entry", , , , , acDialog

The change to the second line will pause your first form until frmPayroll_Entry is closed or hidden. Then is should close properly without error.

Good Luck!

 

[dcurtis]

Thanks SBendBuckeye, it worked like a charm. Not to seem greedy, but is there a way to close the form (modify form), rather than hide it. I only ask because once the Payroll Entry form (the one that is opened w/the code) is closed (after adding data), the modify form is stil there. It's not a major problem, but would be nice for my users not to have to deal with it.

THANKS AGAIN!

----
A program is a spell cast over a computer, turning input into error messages.

 

[intuitas]

Elegant solution SBendBuckeye.
I posted some questions and thoughts in the Forms forum tonight - principally to suggest that you might want to test your user responses before deciding which form to open.

Is there a record in the underlying table?
YES No
Open the frmModifyPaySheet Open the frmPayroll_Entry
Form Form

Then you don't have the worry of closing a form which has not been used to update a record in the underlying table.

 

[SBendBuckeye]

Once the user gets back from frmPayroll_Entry, you should be able to do your original code without a problem:

DoCmd.Close acForm, "frmModifyPaySheet", acSaveNo

Good LucK!

 

[dcurtis]

Intuitas,

I posted in the Forms forum the answers to your questions.

Following up on this thread, I have created a preliminary input form (frmGetInfo) and used it to set Public (global?) values of myInitials and myWeek_Ending when the user clicks the "Next" button (butCloseGetInfo). That works fine, but I don't know how to test the values against the underlying table from the code.

Once I figure that out, or more accurately have it pointed out to me, I should be able to open the appropriate form.

Thanks for all your help so far.

----
A program is a spell cast over a computer, turning input into error messages.

 

[dcurtis]

SBendBuckeye,

After opening up the Payroll Entry form, adding data, then closing it, the application goes back to the Modify Pay Sheet form and tries to close it. It is still causing the run-time error 2585, "This action can't be carried out while processing a form or report event"

If we can solve that, I will have exactly what I need.

I am working on this from two different angles, and appreciate all the help. I just found this forum the other day, and it's already been a major help!!!

----
A program is a spell cast over a computer, turning input into error messages.

 

[SBendBuckeye]

Not sure, but it is possible the error is raised because you are in the GotFocus event for your control. Try moving the code into the OnCurrent event for the form. Also, a handy little function is Nz (Null Zero). It converts a numeric null to 0 and a string null to vbNullString.

Try something like this in the OnCurrent form code:

If Nz(Me.Initials.Value) = 0 Then
Me.butCloseModifyForm.SetFocus 'Not sure this needed
MsgBox "You do not have a pay sheet started" & _
" for that week." & vbCrLf & _
"You will be taken to the Enter Pay Sheet Form"
Me.Visible = False
DoCmd.OpenForm "frmPayroll_Entry", , , , , acDialog
DoCmd.Close acForm, "frmModifyPaySheet", acSaveNo
End If

Good LucK!

 

[dcurtis]

SBendBuckeye

I added your above code in the Current event. I still get the same error (can't process form......), and when I click end, it opens the modify pay sheet form. I may have to live with it, or work the preliminary info form, then open the appropriate form (enter or modify) from there. I am stuck........

Thanks for the help so far.

----
A program is a spell cast over a computer, turning input into error messages.