Batch Processing in MS Access

Posted on 2004-08-31
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
  • 3
  • 2
  • 2
  • +1
LVL 15

Expert Comment

Comment Utility
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 119

Accepted Solution

Rey Obrero earned 500 total points
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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]),"")

Author Comment

Comment Utility
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.

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

LVL 41

Expert Comment

Comment Utility
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

Comment Utility
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

Comment Utility
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

Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now