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 ...
TheAceMan1 said:
[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:
Code:
[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]:
Code:
[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
