Forcing a Form Update

[cpsqlrwn]

I have a subform with an editable field which has a default value of zero. I want to force a non zero entry in this field with message box dialog generated by the form's Before Update event. How do I force a form update when no data has changed in the form. I have experimented with the dirty method in various form events but without success. If my editable field is left unchanged my cursor just loops around all the fields without stopping.

 

[Remou]

I am not quite sure what you wish to do. Is it relevant to update this control in the Current Event? Is something like this suitable?

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Hi"
End Sub

Private Sub Form_Current()
Me.txtText.SetFocus
Me.Dirty = True
End Sub

 

[cpsqlrwn]

In the main form the user selects an item number which is being manufactured. After selecting the manufactured item, the cursor drops to a subform where lot number, date produced, and quantity produced are entered. Lot number is not mandatory, date produced appears with the current date showing which probably would not be modified, and quantity produced shows zero. After inputing this data the cursor moves to a series of tabbed page controls where raw materials are entered, edited, or deleted. My goal is to require a non zero entry in the quantity produced field before the tab control forms are accessed. If the user does not enter a number in the quantity produced field, the subform will most often be in a non dirty status as a lot number may not entered and the current date may be accepted (both likely). This prevents me from creating a message box dialog linked to the form's before update event requiring a positive number in the quantity produced field. Does that make any sense? That is why I am wanting to show the form as dirty and requiring an update if no data is changed.

 

[Remou]

I think the outline code above would work, alternatively, have you considered setting the focus to the quantity and requiring an entry before the cursor can move from the control?

 

[TheAceMan1]

cpsqlrwn . . .

[blue]My goal is to require a non zero entry in the quantity produced field before the tab control forms are accessed.[/blue]

Your problem is trying to pin down the message to the subforms [blue]BeforeUpdate[/blue] event, but you have no idea until you tab into the first tabs subform! This is where your validation and MsgBox should occur. If the validation fails you simply display your message and set focus back to [blue]Quantity Produced[/blue].

The best place for this is the [blue]GotFocus[/blue] event of the field in the first subform of the tab that receives the focus.
Here you would have something like:

[blue]   Dim sfrm As Form
   
   Set sfrm = Forms![purple][b][i]MainFormName[/i][/b][/purple]![purple][b][i]subFormName[/i][/b][/purple].Form
   
   If Trim(sfrm![Quantity Required] & "") = "" Then
      [green]'Your message here[/green]
      sfrm![Quantity Required].SetFocus
   End If
   
   Set sfrm = Nothing
[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[cpsqlrwn]

Thanks TheAceMan1:

This subform with tabbed controls is a real pain. It had occurred to me that I might attempt what you suggested, and I will give it a shot. The event sequences as you are leaving the last field of the subform before entering the tab controls are very difficult to manage. I will try your method and report back.

 

[cpsqlrwn]

TheAceMan1....

I have been working with your approach and it is almost there. All the mechanics of the code are working properly except that I cannot get the cursor to return to the subform and the QuantityProduced control. It remains in the first field of the tab control form.

 

[cpsqlrwn]

TheAceMan1...

The problem is getting back to the subform from the first tab control form. I would like to solve that problem as it aids in the learning experience, but I guess another approach would be to move this QuantityProduced control so that it is not the last editable control in the subform and then use the gotfocus event of the next subform control as the holder of the code. I'd prefer to solve this focus problem, though. What do you think?

 

[TheAceMan1]

cpsqlrwn . . .

Hold your thoughts!

I have great disfavor under the premise with which where working, since its so easy for the user to [blue]Click around[/blue] what your trying to achieve . . .

I'm at cross roads with this and need to ask an additional question . . .

[blue]After selecting the manufactured item and the cursor drops to the subform where lot number, date produced, and quantity produced are entered. How is [blue]date produced [/blue] updated! . . . or more specifically . . . does a new new record go into edit mode as dipicted bt the pencil icon

in the record selector of a new record in the subform?[/blue]

[blue]This may seem academic[/blue] but its power has yet to present itself! . . .

See Ya! . . . . . .

 

[cpsqlrwn]

This is a very interesting question. The dirty cursor appears in the subform only if a field is changed. The cursor drops into the subform and at that point the form is NOT dirty. I experimented with trying to get this form to be dirty automatically and then use the BeforeUpdate event to handle my validation message but I could not get that to work either. The presence of the tab controls in the subform seems to have changed all the rules. I still want to try to resolve this.

In the meantime, however, I rearrranged the form so that Quantity Produced was not the last field and put my validation code on the GotFocus event of the next field. It is all working properly except for the DoCmd.Close line which produces a "This action cannot be performed while processing a form or report event" message. Can you help with this also? Here is the code. I still want to understand what is going on with the other scenario as well. Thanks for all your help.

Dim Response As Integer

If Me.QuantityProduced <= 0 Then
    Response = MsgBox("You Must Enter a Positive Number in Quantity Produced!", vbOKCancel _
    + vbCritical + vbDefaultButton2, "Positive Quantity Required!")
    If Response = vbOK Then
    Me.QuantityProduced.SetFocus
    Exit Sub
    Else
    Me.Undo
    DoCmd.Close
'    DoCmd.Close acForm, "ProductionForm", acSaveNo
  End If
End If

 

[TheAceMan1]

cpsqlrwn . . .

Thing are a little hetic here (moved to new apartment yesterday). I'll get back at noon today.

I'm thing of writing to the record and then erasing to force edit mode . . .

See Ya! . . . . . .

 

[TheAceMan1]

cpsqlrwn . . .

Apologies for the delay. Moving to my new home was quick but not so for my [blue]service provider![/blue] In any case AceMan1 is back up and kicking and [blue]didn't forget you![/blue] . . . So lets get on with it! . . .

[blue]My goal is to require a non zero entry in the quantity produced field before the tab control forms are accessed . . . This prevents me from creating a message box dialog linked to the form's [blue]before update[/blue] event requiring a positive number in the quantity produced field.[/blue]

Ideal for you would be putting the [blue]new record[/blue] (I assume its a new record) in [blue]edit mode[/blue]. The eaisest thing to do is write to a blank field (hello edit mode) and [blue]null it out![/blue]

My problem is I don't know how your [blue]moving/triggering[/blue] the focus to goto the subform. However your doing it you need to replace/intergrate the following (look at it as if you had a button on the mainform to do this) with your code:

[blue]   Dim sfrm As Form
   
   Set sfrm = [[purple][b][i]subFormName][/i][/b][/purple]].Form
   
   [[purple][b][i]subFormName][/i][/b][/purple]].SetFocus
   'sfrm![/b][/purple][i]1stFieldName[/i][/b][/purple].SetFocus
   If Not sfrm.NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew
   sfrm!Name = " "
   sfrm!Name = Null

   Set sfrm = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

cpsqlrwn . . .

Apologies for the delay. Moving to my new home was quick but not so for my [blue]service provider![/blue] In any case AceMan1 is back up and kicking and [blue]didn't forget you![/blue] . . . So lets get on with it! . . .

[blue]My goal is to require a non zero entry in the quantity produced field before the tab control forms are accessed . . . This prevents me from creating a message box dialog linked to the form's [purple]before update[/purple] event requiring a positive number in the quantity produced field.[/blue]

Ideal for you would be putting the [blue]new record[/blue] (I assume its a new record) in [blue]edit mode[/blue]. The eaisest thing to do is write to a blank field (hello edit mode or Dirty = True!) and [blue]null it out![/blue]

My problem is I don't know how your [blue]moving/triggering[/blue] the focus to goto the subform. However your doing it you need to [blue]replace or intergrate[/blue] the following code (look at it as if you had a button on the mainform to do this) with your code ([blue]you![/blue] substitute proper names/values in [purple]purple[/purple]):

[blue]   Dim sfrm As Form
   
   Set sfrm = [[purple][b][i]subFormName[/i][/b][/purple]].Form
   
   [[purple][b][i]subFormName[/i][/b][/purple]].SetFocus
   [green]'sfrm![purple][b][i]1stFieldName[/i][/b][/purple].SetFocus[/green]
   If Not sfrm.NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew
   sfrm!Name = " " [green]'Trigger edit mode![/green]
   sfrm!Name = Null [green]'Null the entry![/green]

   Set sfrm = Nothing[/blue]

. . . and your [blue]Before Update[/blue] event is on!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .