What code to use for Access VBA to allow a form to calculate a text response?

0
votes

I have a generic access database and would like to get the most out of it.

I have created a form (formData_Header) that contains a sub-form (formData_Sub). The sub-form contains text boxes for items to be inventoried, along with data (numbers) from scans that correspond with these items. Each item will have 2 text boxes (count1 and count2) The header of the form contains text boxes that will be applied to all boxes in the sub-form, such as the values from instrument backgrounds. The background will have 2 text boxes (bkg1 and bkg2).

If I haven't lost you yet, I am wanting to take the background values from the header and the count values from the sub-form and calculate a text response in an other text box or Combo Box located in the sub-form.

i.e.: If bkg1 + bkg2 > count1 + count2then the output is "FAIL"
If bkg1 + bkg2 = count1 + count2then the output is "RE-evaluate" All else output is "OK"

I have tried to do this multiple ways and each time, I get a random error, or the code does nothing.

1
vbams-access
Create a custom function that takes the four textboxes as arguments and returns the desired result. Then use that function as ControlSource for the fifth textbox. – Gustav

1 Answers

0
votes

Create a custom function that is called from the after update event on each control.

It needs to first check if each control/param has a value in it. If not, return the default "FAIL" value, otherwise use the calcs you show above.

If you put the function in the main form code, use this format to put the result into a subform control:

Me.formData_Sub.Controls("txtResult") = strCalculatedResult

If your code is on the subform, write the result into the control with the usual:

Me.ControlName = strCalculatedResult