Populating a Field and Saving Record

[athyeh]

Hello,

I have a field: Goal on my form that I want to be auto-populated based on a long-string of IIf statements that I am going to try and write. I also want this information recorded with the submission of each record.

I have read pbaldy's: http://www.baldyweb.com/Autofill.htm

But cannot figure out how to incorporate VBA code.

Sample:



Goal = [Process] - ("Yes" in [Break])

[June7]

Populating fields with calculated results requires code (macro or VBA) in some event. The trick is figuring out what event. It could be a control AfterUpdate or button Click or form BeforeUpdate.

[athyeh]

I have identified an event to start with, would you be able to point me to a VBA resource that explains field and arithemtic statements?

[June7]

Not sure what you mean by 'field and arithemtic statements'.

Access Help has some guidelines on building expressions in query and textbox. Basically the same operators are used in VBA (+, -, /, *).

[athyeh]

Sorry, what I was trying to say is something that talks about how to write vba code, specifically, how to combine if statements with math.

[June7]

"If statements with math"? You will have to be more specific on what you want. Possibly what you want is an IIf() expression in query or textbox. Again, search Access Help.

Start with a review of http://office.microsoft.com/en-us/ac...010341717.aspx

[athyeh]

I originally started with this IIF statement in the textbox:

=IIf([Combo73]="Break",([Text33]*0.75),IIf([Combo73]="Lunch",([Text33]*0.5),IIf([Combo73]="Huddle",([Text33]*0.75),IIf([Combo73]="Full Shift",[Text33],"0"))))

It works great. But I want the info in that textbox to be recorded with each record that I submit. After reading Baldy's post, I am under the impression that the only way to accomplish this is to: Translate the above statement into VBA coding and then attach it to the textbox under an "After Update" event.

What I am looking for is how to translate the expression to VBA coding?

[athyeh]

This thread is solved.

Wrote VBA Code in After Update Event as follows:

If Me![Combo68] = "Break" Then Me![Goal] = ([Text72] * 0.75)

Thank you for your replies.