IIF statement doesn't update immediately on form 3

[Trogg]

Hello all,

I have an IIF statement on a form that inserts different fees based on the type of account chosen in another field on the form. The IIf statement looks like this... =IIf([6th Week]="C",175,IIf([6th Week]="D",90,IIf([6th Week]="W",45,IIf([6th Week]="WD",45,IIf([6th Week]="W*",90,IIf([6th Week]="C*",220,IIf([6th Week]="D*",135,0))))))). In Access 2003 and back this works and updates immediately and perfectly. However in Access 2007 I have to navigate at least 2 records in either direction of the current record before the field will update. Any ideas why it works fine in the earlier versions of Access and then behaves this way in 2007. This is the only issue I have encountered moving this DB to 2007. Thanks in advance for your help.

 

[Gammachaser]

I can't tell you what may have changed, but a statement like that is asking for problems no matter where you use it. Data belongs in a table, not in code. I highly recommend you put your data in a simple table (or add it to an existing one if appropriate) and use a recordset (or at least dlookup) to get it. It will work better, the code will be easier to read, and it is much easier to update if it changes (and it likely will.)

 

[Trogg]

Sorry if I was not clear or if something was misunderstood but the form is used to enter data into the associated tables so the data does reside in tables.

 

[dhookom]

A control source set to an expression beginning with "=" doesn't insert anything into a table. It only displays data. Something else must occur for the data to "enter into the associated tables".

I believe what Gammachaser is getting at is values shouldn't typically be typed into expressions and IMO nested IIF()s are horrible.

We would both (Gammachaser and I) expect a table that stores C, D, W, etc with the related numeric values 90, 45, 45, etc. WHEN letters and numeric values change, you could update the data rather than the nested IIF() statement.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Hi Duane,
Thanks for the reply.. I won't argue the fact that nested IIfs are bad and I'll certainly entertain a better way if you have a suggestion. Maybe I am still not making it clear. The control source in the form is a drop down box that stores the choices (ie. W, C, D, etc.). When one of those choices are made the corresponding fee is entered into it's control via the IFF statement. Both values go into the record in the table in the DB. My original question is why this doesn't update immediately on the form as it did in earlier versions of access. I have to navigate at least two records in either direction, then come back to the record to see it updated. Please share any better way to accomplish this if you have one.. I assume it might be with select case maybe?? Thanks for helping.

 

[MajP]

As previously stated:

create tblAcctFees

acctType Fee
C 175
D 90
W 45
WD 45
W* 90
C* 220
D* 135

combobox -
rowsource: Select acctType, Fee from tblAcctFees order by ...
columns: 2
widths: 1",0"
boundColumn: 1

Both values go into the record in the table in the DB

private sub cmboFee_afterupdate()
'I assume the first value (acctType) can be bound to a field.
'If not then
'me.acctTypeField = me.cmboFee
'The fee can then be pushed into another field
me.someFeeField = me.cmboFee.column(1)
'combo fields are 0 indexed
end sub

 

[dhookom]

As per my first sentence in my previous reply, I don't understand how "the corresponding fee is entered into it's control via the IFF statement". Is there code involved?

I would recommend creating a small table like:
[tt]
tblWeekFee
========================
WeekCode Fee
C 175
D 90
W 45
WD 45
W* 90
C* 220
D* 135
[/tt]
Your combo box could include the Fee column (could be hidden). Then use code in the after update of the combo box to set the value of the Fee text box:

Me.txtFee = Me.cboWeekfee.Column(1)

This is much simpler and easier to maintain.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya New Trogg . . .

I'm curious ... add a button to the form and copy/paste the following line to the [blue]On Click[/blue] event:

[blue]   Me.ReCalc[/blue]

After the form opens ... hit the button and let me know what happens?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trogg]

Apologies to all... as I look back over my original message I see that I failed to mention that there is a table containing the codes and associated charges. I thought I had mentioned this so sorry for the confusion. There are four fields in the code table.. they are Code, description, index, and cost. The combo box on the form (ID is field121)displays the code,description, and cost when it is dropped down. After a selection is made only the code appears in field121. A seperate text box (ID is Text250)contains the nested IIF statements that supply the cost based on the code chosen. Duane... I tried your solution by removing the IIf statement from Text250, then went to the after update property for field121, clicked on the elipse and built the code as you described.... MajP, your solution was similar. When I go back to the form I get a syntax error for the code... here is the code:

Private Sub Field121_AfterUpdate()
Me.Text250 = Me.[5th Week}.Column(1)
End Sub

I hope we can get this to work... sems like a great solution and better than nested IIfs obviously... thanks for your help as we proceed here.

 

[PHV]

I'd try this:

Private Sub Field121_AfterUpdate()
Me!Text250 = Me!Field121.Column(2)
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Trogg]

Thanks PVH.. that does work but... couple of issues. It changes the value for every record in Text250, not just the current one and if you close the form and open it back up the value is gone. Would love to get this working and do away with the nested IFFs. AceMan if you are reading... the recalc button works... updates the form and IIf statements. I'll use it if I keep the IFFs. Thanks for everyones help so far.

 

[dhookom]

Trogg,
Did you set the control source of Text250 to the field you want to use for storing the value? Your results suggest not.

BTW: Text250 and Field121 adn [6th Week] suggest issues. The control names should be a bit more descriptive than the default names. [6th Week] suggests (I could be wrong) an un-normalized structure.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

HI Duane... yes unfortunately it is not normalized. I inherited this creation and just trying to make it better without completely rebuilding it. To answer your question, Text250 does not store it's value. It displays and holds it's value along with a couple of other text fields on the form. The values all get totaled up in another field on the form to show a grand total. Payments are entered manually on the form in fields that are bound to a table in the DB. Since Text250 does not have a bound source do I stick with the nested IIfs and use Acemans recalc button or can I do something better? Thanks for all your input and assistance.

 

[dhookom]

You stated "It changes the value for every record in Text250, not just the current one and if you close the form and open it back up the value is gone."

That is what I would expect with a control that has no control source. If you don't want to store the value then simply change the control source to:

=Field121.Column(2)

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Wow.. thanks Duane... sometimes we just can't see the forrest for the trees. Works perfectly... just had to add =Val(Field121.Column(2)) so that it would display as formatted (currency) in the textbox. Thanks to all for a simple and great solution!!