Auto fill text box based on selection from two combo boxes

[blaze777]

I have a form with two combo boxes "Housing" and "Plan". There is a text box called "Fee". There are six housing choices and four plan choices. There are four possible fees associated with all the combinations of "Housing" and "Plans".

How do I auto fill the "Fee" textbox after the two other choices are made from the comboboxes. I have created a Cross-Tab Query that organizes the data, but I can't figure out how to get it in the form

Any help would be appreciated.

 

[LittleSmudge]

In the AfterUpdate event for each combo box:-

Test that both combo's contain valid data ( Else do nothing )
- You need this because you can't guarantee that users will complete them in the correct order !

Do what even lookup function you nee to do ( Or nested Select Case ) to find the fee value

txtControl = CaluclatedFee



The detail of the 'calculation' will demend totally on how the data regarding which fee is charged for which house/plan, & you have not shared that with us.


'ope-that-'elps.

G LS

 

[blaze777]

Let me be more specific, because I'm still lost.

Combobox "Housing" list comes from Table "Housing" which has the following choices - House1, House2, House3, House4, and House5. Combobox "Plan" list comes from the Query "Housing" which has the following choices All, Fri, Sat.

If you choose House 1,2, or 3 and Fri or Sat plan the fee is $49. If you choose House 1,2, or 3 and All plan the fee is $79. If you choose House 4 or 5 and plan Fri or Sat the fee is $59. If you choose House 4 or 5 and Plan All the fee is $99.

Now, I want the user to select housing from the list and a plan from the list and the correct fee to auto-fill in the "Fee" textbox.

Thanks, for your help.

 

[LittleSmudge]

Do I understand you correctly - have you REALLY got a Query with the same name as a Table ? - It won't stop it working but it going to make maintenance and mods diffecult in the future.


As for your real question, simply replace
txtControl = CaluclatedFee

with

If ((Housing = 1) Or (Housing = 2) Or (Housing = 3)) Then
If Plan = "All" Then
txtControl = $79
Else
txtControl = $49
End If
Else
If Plan = "All" Then
txtControl = $99
Else
txtControl = $59
End If
End If

Caveats about checking that both combos contain valid data first still apply.


'ope-that-'elps.

G LS

 

[blaze777]

No I don't have a table and Query named the same. I was just a little lazy typing.

By the way thanks for your help!

 

[LittleSmudge]

You are forgiven .

 

[blaze777]

Well, I've been busy with other things for a while and just got back around to this, Below is the code I came up with, but it desn't work. I get $59 not matter what combination I choose.

Private Sub Fee_AfterUpdate()
If ((Housing = AF_Smith) Or (Housing = Alexander) Or (Housing = Windham)) Then
If Plan = "All" Then
Fee = 79
Else
Fee = 49
End If
Else
If ((Housing = Central) Or (Housing = Fair_Village)) Then
If Plan = "All" Then
Fee = 99
Else
Fee = 59
End If
End If
End If
End Sub

Thanks so much for your help,

 

[thornmastr]

I don’t think you want this on the after update event of your fee box. In fact, since the fee is based on conditions, you might want to put it on the before update event of your form and disallow the user to do anything at all with the fee field. You should include the following in that event



Select Case Me!housing
Case Is = "af_Smith"
Case Is = "Alexander"
Case Is = "windham"
If Me!plan = "All" Then
Me!Fee = 79
Else
Me!Fee = 49
End If
Case Is = "Central"
Case Is = "Fair_Village"
If Me!plan = "all" Then
Me!Fee = 99
Else
Me!Fee = 59
End If
End Select

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Rolliee]

I believe this would do it better. Try them.

Select Case Me.housing
Case "af_Smith", "windham"
If Me.plan = "All" Then
Me.fee = 49
Else
Me.fee = 99
End If

Case "Central", "Fair_Village"
If Me.plan = "All" Then
Me.fee = 249
Else
Me.fee = 299
End If
End Select

Rollie

 

[FoxProProgrammer]

Rollie,

Why would I want to pay you $249 for Friday and Saturday at Central or Fair Village when Blaze and Robert have the same house for $99? )

Just kidding!
dz

 

[blaze777]

Well I don't know where the $249 came from but if you want a room for that rate we can accomodate. The possible prices should be $49, $59, $79, and $99.

Thanks for your help!

 

[thornmastr]

Whoops.

Dyslexic keyboard. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blaze777]

Well guys, I've tried both your versions and it doesn't work. Here's what I used:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.Housing
Case "AF_Smith", "Windham", "Alexander"
If Me.Plan = "All" Then
Me.Fee = 79
Else
Me.Fee = 49
End If

Case "Central", "Fair_Village"
If Me.Plan = "All" Then
Me.Fee = 99
Else
Me.Fee = 59
End If
End Select
End Sub

Robert's version
Select Case Me!housing
Case Is = "AF_Smith"
Case Is = "Alexander"
Case Is = "Windham"
If Me!plan = "All" Then
Me!Fee = 79
Else
Me!Fee = 49
End If
Case Is = "Central"
Case Is = "Fair_Village"
If Me!plan = "all" Then
Me!Fee = 99
Else
Me!Fee = 59
End If
End Select

I tried both in the Form AfterUpdate and BeforeUpdate and "Fee" returned nothing.

 

[thornmastr]

Blaze,

Is fee on your form and how is it described, ie, name and name of underlying variable. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blaze777]

Yes "Fee" is an unbound textbox in the Form and it is labeled "Fee" Under the DATA Tab Enabled = Yes; Locked=No; Filter Lookup=Database Default.

Thanks for your help Robert.

 

[FoxProProgrammer]

Robert's, Rollie's, and G LS's code should work. If you always get $59, then the following must be true.

1. Housing has a value of "Central" or "Fair_Village"
2. Plan is not equal to "All". It is set to "Fri" or "Sat".

You might set a breakpoint and step through the code with the debugger to see what value Housing and Plan have when it gets to this code. How many columns are in the combo box, and which column is bound? The expression Me.Housing and Me.Plan will only work if the combo box has a single column, or if it has more than one column and the control is bound to the column that contains "Central", "Fair_Village", etc in the case of Housing, and "All", "Fri", "Sat" in the case of Plan.

Best,

dz

 

[thornmastr]

DZ,

Shucks, ya'll just stole my thunder and sparks. I think I'll go walk between the rain drops.

Mumble mumble. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[FoxProProgrammer]

Sorry, Robert. I didn't intend to intrude. I was only here to get a $299 room for $59! I know it was only a typo, but they do have truth in labeling laws. ) I'll scamper on to a different house now. My apologies.

dz

 

[blaze777]

Well I'm not currently getting $59 in Fee with the code provided by Robert and Thorn I get nothing. I was getting the $59 when I had the code in the AfterUpdate of the Fee txtbox. I since moved the new code to the Form AfterUpdate and now I'm getting nothing.

The two ComboBoxes are each one column with only the choices shown above along with a "" line. The Housing box has AF Smith, Alexander, Windham, Central, Fair Village, and "".
The Plan box has All, Fri, Sat.

Thanks for your input.

 

[thornmastr]

Blaze,

You mean you have not cranked up the debugger and found your problem. Set a break point and step the code line by line. If you really are stil having a problem with this, zip your database and email it over. Seriously, the reasons for its not working are not the code per se. I would check all the values of housing and Plans.

Of course, if you have bound your combo boxes to a numerical reprsentation of Housing and/or plan, you know that would mess up that logic real fast. For example, if housing AF_Smith is really a numeric 1, everything is going to fall apart in that code. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com