Batch Processing in MS Access

Posted on 2004-08-31
Medium Priority
Last Modified: 2008-03-04
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.
Question by:nanohurtz
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 15

Expert Comment

ID: 11949999
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
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1500 total points
ID: 11951402
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]
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11951431
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]),"")
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 11986784
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.

LVL 41

Expert Comment

ID: 11986807
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:

Author Comment

ID: 11990490
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
LVL 41

Expert Comment

ID: 11990794
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*?

Author Comment

ID: 11993782
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 :)

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question