Batch Processing in MS Access

I am having a difficult time trying to simulate batch processing from a data enterer perspective like that of mainframe days. I've created a batch header form (Form1) that the user populates with a dollar amount and number of items. The user enter each item in another form (Form2) with thier corresponding dollar amount. As the user enters data into Form2, Form1 is being updated with the summed calculations in another field next to the set dollar amount.

I'm trying to put together a function that will

a. Alert the user when the batch is completed upon the dollar amounts matching in both fields in Form1
b. Alert the user if the batch does not match according to the dollar or number of units provided.

A signficant number of my headaches come from the fact that the function does not alway calculate or pass the calculated variables up to the batch header (Form1) as it should. Maybe someone can help determine where I went wrong by perusing the attached module

Public Function CalculateBatch()

On Error GoTo TheMismatchedError
'Values from the data entry form is calculated via query and is passed to the batch header form.
[Form_VIS_Main_Batch_Processing]![bt_total_verified] = [Form_VIS_Main_Batch_Processing]![VIS_Btc_Contributions_Totals]![bt_contributions_subtotals]
'Batch header form is refreshed so that user can see how much they have processed
'Filter for typo's that may send the batch over
If [Form_VIS_Main_Batch_Processing]![bt_total_verified] > [Form_VIS_Main_Batch_Processing]![bt_total] Then
'Abort safely
MsgBox "The Batch is not balanced! Please revue and correct.", vbOKOnly, "Batch Processing"
stRunCalculationReviewMacro = "mco_btc_review": DoCmd.RunMacro stRunCalculationReviewMacro
GoTo The_Match_Up
End If

stOpenSearchForm = "VIS_Src_Partner_Services": DoCmd.OpenForm stOpenSearchForm, acNormal, , , acFormAdd, acWindowNormal
stClosePartnerServices = "VIS_Main_Partner_Services": DoCmd.Close acForm, stClosePartnerServices, acSaveNo

'Determine if we are at the end of the batch process
If [Form_VIS_Main_Batch_Processing]![bt_total] = [Form_VIS_Main_Batch_Processing]![bt_total_verified] Then
'Let the processor know its complete
MsgBox "Batch Completed and Balanced", vbOKOnly, "Batch Processing"
'Call in the clean up crew
[Form_VIS_Main_Batch_Processing].bt_close_date = Now()
[Form_VIS_Main_Batch_Processing].bt_batch_balanced = 1
[Form_VIS_Main_Batch_Processing]![cmd_new_batch].Enabled = True
[Form_VIS_Main_Batch_Processing].cmd_reset_batch.Enabled = True

'Close the other forms
stRunCleanUpMacro = "mco_close_batch": DoCmd.RunMacro stRunCleanUpMacro

End If

Exit Function

'Error caused by a null or zero value being passed in the Batch Form on the first go around
[Form_VIS_Main_Batch_Processing]![bt_total_verified] = [Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions]![pt_contribution_amount]
Resume The_Match_Up
End Function

Anyhelp would be greatly appreciated.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
if you are referring to controls on a main form and a subform the format should be like this

       Forms![Form_VIS_Main_Batch_Processing]![bt_total_verified]      ' this part is referring to a control on the mainform

[Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions].FORM![pt_contribution_amount]  ' this line is referring to a control on the sub form.

Forms![Form_VIS_Main_Batch_Processing]![bt_total_verified] = [Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions].FORM![pt_contribution_amount]
I think part of the problem may be caused by the way you are calling your foms. I've included one line below from yourcode above to show you what I mean:

[Form_VIS_Main_Batch_Processing]![bt_total_verified] = [Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions]![pt_contribution_amount]

In the first for you reference the control on your form like
However here you are missing the "Forms" description at the beginning which I think is confusing access. In the second reference to a form in that line I think you did it better when you referenced the control on the form like this
Rey Obrero (Capricorn1)Commented:
also to correct the null value error if ever there will be a case of [pt_contribution_amount] being null

Forms![Form_VIS_Main_Batch_Processing]![bt_total_verified] = Nz(([Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions].FORM![pt_contribution_amount]),"")
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

nanohurtzAuthor Commented:
but the use of "Form_formname" is supposed to replace the use of "Forms![formname]" I tried your approach and now the values are not being transfered from field to field. You may need to see the database app to fully understand what Im trying to achieve. Let me see if I can explain in further detail.

Form 1 is called "VIS_Main_Batch_Processing". Within this form is a sub form called "VIS_Btc_Contributions_Totals". Within "VIS_Btc_Contributions_Totals" is a control (text field, currency) called "bt_contributions_subtotals" (a field within a query that sums the amounts entered by the data enterer). Still in Form 1 there is a control (text field, currency) called "bt_total". This field is a simple text control that the data enterer uses to populate with the amount to be batched. Note: that once they start entering data this control is disabled and its value cannot be changed. There is a third control (text field, currency) "called bt_total_verified". This field is automatically populated every time the form is refreshed from a new value available in "bt_contributions_subtotals".

Hence the statement [Form_VIS_Main_Batch_Processing]![bt_total_verified] = [Form_VIS_Main_Batch_Processing]![VIS_Btc_Contributions_Totals]![bt_contributions_subtotals]

Form 2 is called "VIS_Main_Partner_Services" within this form is a subform called "VIS_Sub_Partner_Contributions" and within this subform is a control (text, currency)  called "pt_contribution_amount". Once the data enterer provides an amount and presses the control (button) submit. the function called "CalculateBatch" is called and that when the magic is supposed to happen. The problem im still having is the following:

1. If the query results in a zero or null amount I get an error (which usually occurs when the data enterer starts the batch)
2. The focus between forms acts wierd, often results in values not being passed or updated.

What do you recommend I do to ensure that every time the data enterer presses the submit button values are updated without fail.

As far as the null values is concerned, capricon1 posted a suggestion above that should help you fix that (the Nz function converts a null value to something else), although I don't know where the null value you're referring to appears in.

For the focus, I would suggest doing this - in your code, you have several lines that do a Refresh, presumably to save the form's data. However, I'd suggest this instead:

That acts to save the data without any potential requery/focus problems. You also have this:
DoCmd.Close acForm, stClosePartnerServices, acSaveNo

I'm assuming that is there to close the form without saving data changes. However, it doesn't do that - the acSaveNo parameter just means the form *design* changes aren't saved - form data is commited to the table. You undo any form edits by using something like:
nanohurtzAuthor Commented:
I really appreciate the help you guys are providing me so far..unfortunately I'm still having issues with this null value that seems to stop the program when the query executes and no results appear. Everytime [Form_VIS_Main_Batch_Processing].bt_total_verified = Nz(([Form_VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions].[pt_contribution_amount]), "") or [Form_VIS_Main_Batch_Processing].bt_total_verified = Nz(([Form_VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions].[pt_contribution_amount]), 0.00) <-- (tried a numberical value) I get the following error.

Run-time error '438':
Object doesn't support this property or method

I suspect this is a result of the statement trying to pass a "nothing" (instead of $0.00) from one field to another. Maybe I should consider scrapping the current batch window design and trying something else, maybe a $0.0001 cheat may circumvent these nulls and blanks
Hmm - could you try changing the references to your forms? Instead of using this:


Change it to:


I rather suspect that there is a mis-reference there, and it might be this:

Is that the name of the subform or the subform *control*?
nanohurtzAuthor Commented:
I've decided to minimize the amount of forms passing variables around and decided to embed a subform in the very form the data enterers are batching thier amounts. It resulted in less forms and less code. Most importantly it eliminated the need to scan for nulls during the creation of the first recordset to batch. Thanks everyone, especially Capricorn1 for the help :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.