Form and Subform help needed

[Davepacey]

I have a form with a subform, the subform contains several fields that are summed in a textbox [Text14]. The form has a second subform that the user types amounts to reduce stocks by, these fields are summed in a textbox [Text20]
If the totals in the two boxes are not the same, I need a message saying so, and stop the form from closing till they are equal, only when equal can the form be closed.

I have the following code, without the messagebox, where does it go ? Forgive me if this is simple, I am new to VB and a steep learning curve is envisioned.

If [frm_repel_produced_sub].[Form]![Text14] <> [frm_reduce_stocks_sub].[Form]![Text20] Then DoCmd.CancelEvent Else If [frm_repel_produced_sub].[Form]![Text14] = [frm_reduce_stocks_sub].[Form]![Text20] Then DoCmd.Close , "frm_reduce_stocks"


TIA.

Dave

 

[Jack Cowley]

Referring to controls on subforms requires specific syntax. You will find the syntax you need here.

hth,
Jack

 

[Davepacey]

Hi Jack,
I do not have a problem with referencing subforms, I have done that bit and it works ok, The problem I am having is with displaying the message "The Totals Do Not Match"
The rest of the code works just fine. If the totals do not match, the form will not close, if they do, it does. I just need to know how to make it display a message to let the user know that he needs to make the totals match.

Thanks anyway

dave

 

[Jack Cowley]

Oops... Is this what you are after:

If [frm_repel_produced_sub].[Form]![Text14] <> [frm_reduce_stocks_sub].[Form]![Text20] Then
MsgBox "Totals must Match"
Me.FieldOfYourChoice.SetFocus
Else
DoCmd.Close , "frm_reduce_stocks"
End if

If this is in the On Click event of your close button it should work.

hth,
Jack

 

[Davepacey]

Thanks Jack, the following code does exactly what I need, after tormenting my fried brain for an hour, I came up with the goods.

If [frm_repel_produced_sub].[Form]![Text14] <> [frm_reduce_stocks_sub].[Form]![Text20] Then
MsgBox "The Totals Do Not Match"
DoCmd.CancelEvent
Else
DoCmd.Close , "frm_reduce_stocks"
End If

Thanks a lot.

I now have another problem, how to get a field in a subform to fill with the value of the same field in previous record......easy with Alpha Five !! and then to recognise when the value has changed.
I have to do this without using linked fields to the mainform, as the data can change from record to record. (some fields are linked, and do fill as required) Not sure if this is possible.
i.e. the subform is linked on a batch number field between child and parent. This batch number can remain the same over several shifts, which means the date can change, as can the shift. I would like to not have to type in the date and shift for every record entered in the subform, bearing in mind that the records may be typed in several days after the event.

Will post this elswhere also

Dave.

 

[Jack Cowley]

Not quite sure what you have or what you are after, but you can link a subform on more than one control. You can link on the current controls as well as date and shift if that will help.

To fill a control from a previous record you will find information here and here.

I hope the scattergun approach is of some assistance....

Jack

 

[Davepacey]

Thanks Jack,
I managed to get it by adding combo boxes on the main form, and referencing them in the default value properties of the field in the subform, and just as long as the combo boxes are changed before moving to next record, it works just fine.

Dave.

 

[Jack Cowley]

You are welcome. And you can use code to check when moving to a new record to be sure that the combo boxes have changed. This way the user cannot move on unless they have done as you want them to.

Jack