Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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]),"")
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

609 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