My Form won't Hide - long explaination

[dcurtis]

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

 

[intuitas]

Hi,

What version of Access are you using?
I don't quite follow the nature of the query you describe. Is this a parameter query requiring user to input, say, (pay)week and (user)initials?

What happens when you run the query on its own - what records are returned if the (pay)week/(user)initials pair is not found ie. there is no record in the underlying table?

I would use code to set a flag (a global variable if necessary) depending on the outcome of the query. This would be independent of the (form control)/(table field) values you are testing.

Is it necessary to open the frmPayroll_Entry form before closing the frmModifyPaySheet form?

Could you alternatively ask for the two pieces of information (I am assuming payweek and user initials)in a preliminary input form, test the response against the underlying table and open either frmModifyPaySheet of frmPayrollEntry accordingly?

Intuitas

 

[dcurtis]

Ok, I am using Access XP. The query is actually an input query (the user has to enter data, initials and week ending).

If I run the query on it's own and there is no match, it returns empty.

Since I am fairly new to VBA, I don't really understand how to set the global variable based on the query results.

It is not necessary to close or open either form, a message box would suffice, but it's a user-friendly feature that was requested by the powers that be.

If you could give me an example of what the code would look like to test gather the information and test it, then open the proper form I would appreciate it. I am going to try to figure it out also, but am afraid I may not have the skills/knowledge yet.

Thanks for you help.

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