Solved

Batch Processing in MS Access

Posted on 2004-08-31
8
1,000 Views
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
[Form_VIS_Main_Batch_Processing].Refresh
'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

The_Match_Up:
Exit Function

TheMismatchedError:
'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]
[Form_VIS_Main_Batch_Processing].Refresh
Resume The_Match_Up
End Function

Anyhelp would be greatly appreciated.
0
Comment
Question by:nanohurtz
[X]
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
8 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
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
     "[Form_VIS_Main_Batch_Processing]![bt_total_verified]".
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
     "[Forms]![VIS_Main_Partner_Services]![VIS_Sub_Partner_Contributions]![pt_contribution_amount]"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 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]
0
 
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]),"")
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:nanohurtz
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.

2.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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:
[Form_VIS_Main_Batch_Processing].Dirty=False

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:
Forms(stClosePartnerServices).Undo
0
 

Author Comment

by:nanohurtz
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11990794
Hmm - could you try changing the references to your forms? Instead of using this:

[Form_VIS_Main_Batch_Processing].bt_total_verified

Change it to:

[Forms]![VIS_Main_Batch_Processing]![bt_total_verified]

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

Is that the name of the subform or the subform *control*?
0
 

Author Comment

by:nanohurtz
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 :)
0

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

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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