Subform question (1 Viewer)

[magster06]

Hi Guys,

I have a main form with a suform. The information on the main form is "read only" and the subform is used for data entry.

I have a cbo that is used to find personnel and this is located on the main form. When I select personnel with the cbo, this populates the main form fields and only the ID in the subform.

I have the subform setup for data entry, so the only thing that is default is the ID number of the member. The ID is populated from the main form.

When I move through the personnel, the ID in the subform is the same as the main form. This worked fine, but now all the fields of the subform are being populated with a record. I want "data entry" only.

To work around this (I am not sure if this is the correct way) I placed the following code in the Current event of the subform:

Me.Form.DataEntry = True

and this seems to work.

My question is: what went wrong? Also, can I use the same code above in the click event of the "Save" button to clear the fields at one time?

Private Sub Form_Current()
     Me.Form.DataEntry = True
End Sub

Private Sub cmdSave_Click()
     MyAction = "Save"
     DoCmd.RunCommand acCmdSaveRecord
     MsgBox "Record Saved!", vbOKOnly + vbInformation
 
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MyAction
   Case "Cancel"
      If MsgBox("Do you want to cancel this update?", vbYesNo) = vbYes Then
        Cancel = True
        Exit Sub
      End If
   Case "Save"
   Case Else
       MsgBox "Please press the Save or Cancel button", vbOKOnly
       Cancel = True
       Exit Sub
End Select
End Sub

I think the above may be Pat's code from another post for the "Cancel".

Another question: why is it when I place the docmd under the "Save" (in Case Select), it will not work?

Sorry for the length and I hope I am making this somewhat understandable, lol.

Man, I have another question: Should the command buttons be on the main form?

Also, after I click the "Cancel" button and then try to select another member from the cbo on the main form I get the msgbox from the beforeupdate event of the subform, why?

 

[magster06]

Hello, anyone out there?

 

[JHB]

I have a cbo that is used to find personnel and this is located on the main form. When I select personnel with the cbo, this populates the main form fields and only the ID in the subform.

I have the subform setup for data entry, so the only thing that is default is the ID number of the member. The ID is populated from the main form.

Why do you not link the main and the subform together with the Master/Child fields then you don't need to set the ID number in the subform, or is it unbound forms?

... Also, can I use the same code above in the click event of the "Save" button to clear the fields at one time?

Why not try it, you have the database!

Private Sub cmdSave_Click()
     MyAction = "Save"
     DoCmd.RunCommand acCmdSaveRecord
     MsgBox "Record Saved!", vbOKOnly + vbInformation
 
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MyAction
   Case "Cancel"
      If MsgBox("Do you want to cancel this update?", vbYesNo) = vbYes Then
        Cancel = True
        Exit Sub
      End If
   Case "Save"
   Case Else
       MsgBox "Please press the Save or Cancel button", vbOKOnly
       Cancel = True
       Exit Sub
End Select
End Sub

Another question: why is it when I place the docmd under the "Save" (in Case Select), it will not work?

Because you are testing the variable MyAction and you are assign MyAction in the Sub cmdSave_Click.

Also, after I click the "Cancel" button and then try to select another member from the cbo on the main form I get the msgbox from the beforeupdate event of the subform, why?

Because you only cancel the before update event, you didn't reassign the variable MyAction, so MyAction is still = "Save" and the form is still "Dirty", you need to undo/clear the fields.

 

[magster06]

JHB,

Thanks for the response!

Why do you not link the main and the subform together with the Master/Child fields then you don't need to set the ID number in the subform, or is it unbound forms?

I do have the forms linked Master/Child. I am not sure why I thought that I needed the ID to show for the data entry.

Why not try it, you have the database!

I just didnt know if this would create problems in the long run. I get scared, lol.

Because you are testing the variable MyAction and you are assign MyAction in the Sub cmdSave_Click.

This one still confuses me. Then why does the "Cancel" code work within the Select Case? Doesnt the "BeforeUpdate" of the subform fire when I click on the Cancel button and then it checks for the Cancel, which in turn it runs the code under the Cancel? I thought this would be the same with the Save Case. I am sorry that I am not getting what you are saying.

Because you only cancel the before update event, you didn't reassign the variable MyAction, so MyAction is still = "Save" and the form is still "Dirty", you need to undo/clear the fields.

How do I reassign the MyAction? by placing Me.Undo under the Cancel Case?

Thanks again for your response JHB!

 

[JHB]

This one still confuses me. Then why does the "Cancel" code work within the Select Case? Doesnt the "BeforeUpdate" of the subform fire when I click on the Cancel button and then it checks for the Cancel, which in turn it runs the code under the Cancel? I thought this would be the same with the Save Case. I am sorry that I am not getting what you are saying.

You didn't show the code behind the "Cancel" button, so ... put in a break point in the code behind the "Cancel" button and step through the code to see what happen when you press the button.

How do I reassign the MyAction? by placing Me.Undo under the Cancel Case?

Yes, place the Me.Undo there, here you can also reassign the MyAction.

   Case "Cancel"
      If MsgBox("Do you want to cancel this update?", vbYesNo) = vbYes Then
        [B]Me.Undo[/B]
        [B]MyAction=""[/B]
        Cancel = True
        Exit Sub
      End If

 

[magster06]

You didn't show the code behind the "Cancel" button, so ... put in a break point in the code behind the "Cancel" button and step through the code to see what happen when you press the button.

I was using the code in the Select Case to handle this, but I am guessing that I am very wrong, lol. The only code behind my "Cancel" button is

MyAction = Cancel

 

[CJ_London]

there is a parallel thread covering the same subject on this link

http://www.access-programmers.co.uk/forums/showthread.php?p=1321557#post1321557