data entry in text box to control combo 1

[kpryan]

Hi,
I have a text box (Gross) which is (currency) showing totals. There is also a combo box which holds tax data in currency.
What I would like to do is when the tex box(Gross) has focus then the combo box will drop down and match the data in the text box
So far I have this;
On the 'textbox' (on focus event) combobox.setfocus
On the combo box (on the got focus) combobox.dropdown.
All I need it to do now is for the data in the 'textbox' to match the data in the combo box.
Hope this make sense
KP

 

[fneily]

So on the Onfocus event, you'd want:
Me![Combobox].Rowsource = "select [taxdata] from [tablewhatever] Where [textboxfield] = '" & Me![textboxname].value & "'"
combox.requery
combobox.dropdown
combobox.setfocus

If your combobox is bound to a numeric the Where is
[FieldYouWantInCombobox] = " & Me![textboxname].value

 

[TheAceMan1]

Howdy kpryan . . .

Hmmmmmm . . . a combobox that shows [blue]tax data based on a [purple]variable gross amount[/purple][/blue] [red]that could be anything![/red]

Whats the point of the combo and how is it populated . . . [red]considering you want to match Gross Values?[/red]

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi all,
Yes this is a difficult one. The ATO here only provides formuals for tax! No one produces a tax table such as one for MYOB. So I have created a script that enables the tax tables to be inserted into access.
So when the gross amount is calculated I wanted the tax amount to be entered via a combo box. Sounds difficult, yes, but I ant ot give it a try.
Some programmers I know here in Australia find creating something tanggable from what is avaiable from the ATO rather difficult.
So I'm hopeing to get something to work without having to enter the data manually.
I'll give the advice above from Fneily a go tomorrow and see if that will work.

KP

 

[TheAceMan1]

[blue] . . . So I have created a script that enables the tax tables to be inserted into access.[/blue]

Am I too assume these tax tables comprise [blue]Gross Ranges![/blue] . . . like the tax tables at tax time?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi Aceman1
Yes these are gross ranges. eg The min gross before tax = $380.00.
I have the gross ranges in a table with the appropriate tax range.
ie: Gross = $450.00
Tax = $ 20.00
I wanted to be able to select the gross range and then insert the Tax amount into the form so that is subtracts from the gross amount.

KP

 

[TheAceMan1]

kpryan . . .

When I mentioned ranges I meant two values describing a range, like:

[tt][blue] Range Tax
********** ***
300 to 349 $20
350 to 399 $22
400 to 449 $25
'
Others[/blue][/tt]

These ranges would be in the combo in seperate fields.

Am I seeing your combo setup correctly?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi TheAceman1
Yes that is correct.
The gross Amt is in one field and the amount to be allocated in another field.

The table has been constructed as follows: Plus a Primary Key

Gross(field) tax(field)
$400 $16
$450 $18
$500 $20

The Tables go to $8000.00

KP

 

[TheAceMan1]

kpryan . . .

Apologies for stretching this out. Can't be helped.

My vision of the combo was more like:

[tt][blue]Col0 Col1 Col2 Col2
ID Min Max Tax
**** **** **** ****
1 300 349 $20
22 350 399 $22
73 400 449 $25[/blue][/tt]

It certainly would make it easier to ping on gross! It can be done with what you've presented, it just gonna take more code. I'll work something up while I ask the next few questions.

1) If [blue]gross[/blue] is $425, is the tax $16 or $18?
2) Is the combo bound?

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi the Aceman1
According to tax tables it can be either. I have posted a part of the tax tables from the tax department.
When we use the PAYG system it does not matter as long as we are in the range. As most will get a refund at the end of the financial year.

450 12.00
452 12.00
454 12.00
456 12.00
458 12.00
460 12.00
462 14.00
464 14.00
466 14.00
468 14.00
470 14.00
472 14.00
474 14.00
476 16.00
478 16.00
480 16.00

Is it possible to ping on a range. ie If a person gross is $479 then it would fall between $478 and $479.

I do appreciate your help. Many thanks.

KP

 

[PHV]

A starting point, in the AfterUpdate event procedure of your TextBox:
Me![your combo] = DMax("[gross field]", "[your tax table]", "[gross field]<=" & Me![gross textbox])

Assumption: the BoundColumn of the combo is the amount.

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

 

[kpryan]

Hi PHV,
Many thanks for your reply. Yes the combo box is bound to a text box (currency) on the form. Yes the bound column is to insert the tax amount.
I have to go away for a few days, but will get to this on the week end.

I do appreciate all the help you guys give. I wish I was more experienced in using access

KP

 

[TheAceMan1]

kpryan . . .

For starters, the post by [blue]PHV[/blue] has been in the back of my mind since the start of this thread. I even asked the question ...

[blue][purple]Whats the point of the combo[/purple] and how is it populated . . .[/blue]

... as I've continually asked myself thru this thread: [blue]why lookup a combobox when it would be much easier to lookup a table!? In this case the underlying rowsource of the combo.[/blue]

Since you appeared to be adamant about looking-up the combo I proceeded to do so. However I ran into quite a few problems in my attempts:
[ol][li]A method in A2k was complete and worked well![/li]
[li]When I tried this method in 2003 and XP, problems arose. To cut it short were talking a [blue]version conflict[/blue] here, [purple]let alone the errors raised by setting focus to the combo from the Gross [blue]AfterUpdate[/blue] event (in anyway version)![/purple] At present I'm still checking on these errors for my own info in programming.[/li][/ol]

So ... what I present looks-up the table (not the combo), as confidence with access version compatibility has failed. The heart of the method is a function which you can call from anywhere. Here's the install:
[ol][li]Backup the form under another name so you can come back to square one if your not satisfied ... which I doubt.[/li]
[li]In a module in the modules window, copy/paste the following public function:

[blue]Public Function GrossTax(GrossVal As Currency) As Currency
   Dim db As DAO.Database, rstLo As DAO.Recordset, rstHi As DAO.Recordset
   Dim loVal As Currency, midVal As Currency, HiVal As Currency
   
      Set db = CurrentDb
      Set rstLo = db.OpenRecordset("[purple][B][I]TaxTableName[/I][/B][/purple]", dbOpenDynaset)
      Set rstHi = db.OpenRecordset("[purple][B][I]TaxTableName[/I][/B][/purple]", dbOpenDynaset)
      rstHi.MoveNext
   
   If Trim(GrossVal & "") <> "" Then
      Do Until rstHi.EOF
         loVal = rstLo!Gross
         HiVal = rstHi!Gross
         
         If Val(GrossVal) >= loVal And Val(GrossVal) <= HiVal Then
            midVal = loVal + ((HiVal - loVal) / 2)
            
            If GrossVal <= midVal Then
               GrossTax = rstLo!Tax
            Else
               GrossTax = rstHi!Tax
            End If

            Exit Do
         End If
         
         rstLo.MoveNext
         rstHi.MoveNext
      Loop
   End If
   
   Set rstLo = Nothing
   Set rstHi = Nothing
   Set db = Nothing
         
End Function[/blue]

[/li]
[li]Then in the [blue]AfterUpdate[/blue] event of [blue]Gross[/blue]:

[blue]   Me!Tax = GrossTax(Me!Gross)[/blue]

[/li][/ol]
The only difference between [blue]PHVs[/blue] code and mine is that I take accuracy down to the dollar! The choice is yours, which to use.

If you want further details on the version conflict looking-up the combo method, I'll be happy to oblige and post the info I have (its quite long).

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi the Aceman1
Many thanks for your reply. Ive been interstate here for the week, so only have manged to read your post. I will do as you have suggetsed on Monday and let you know how it goes. Going to the exact dollar is excellent. Many thanks for your help. Noew Ive gotsome work to do.
kp

 

[kpryan]

TheAceMan1,
AGain thanks for your post.
I have tried this code and it works fantastic. I do appreciate all your help.

Amazing! Thankyou.

KP

 

[paullem]

Hi,

Does anyone know how to get a Date Picker field in Access 2007 as you can in Access 2003. I know there is an option on the text field in Access 2007 but the field has to be bound which I don't want to do. I also would prefer not to use the ActiveX calendar control.

Thanks for any help