Taking out certain value from Table (1 Viewer)

[rehanemis]

Hi,

I am using ms access for entry of data of AC technicians. All Technicians can gain bonus amount in % if they work well and bonus % is dynamic for each technician. Suppose, Technician Mr.Jhon work for $300 or above so his bonus % is 5 and if he done work of $800 or above then bonus % is 9 and son on. But this % rule is not same for each Technician(based on experience company decided the %).

So How can i get % figure while entering data of specific Technician suppose I am entering Data for Mr.Jhon and i enter work done $600 (which is greater than $300) so the value 5 should be appear in next of my form column.


I hope this make sense, please let me know if you have any question. I have also attached an snap shot of For % bonus.

Looking forward to hearing from you very soon.

Thanks in advance.

 

[Vonik]

It seems like you give bonuses for sales in certain tiers, regardless of experience. Everyone gets a bonus for $250 - $349, $350 - $449 ... even though the bonus percentages change based on experience.

So, if you have a control, textSalesAmount on a form and a control, textBonusPercent.

You should be able to use an afterupdate subroutine such as;

Private Sub textSalesAmount_AfterUpdate()
Dim TechName As String
TechName = Me.textTechName

If Me.textSalesAmount >= 250 And Me.textSalesAmount < 350 Then
    Me.textBonusPercent = DLookup("250", "techs", "tech =" & TechName)
End If

If Me.textSalesAmount >= 350 And Me.textSalesAmount < 450 Then
    Me.textBonusPercent = DLookup("350", "techs", "tech =" & TechName)
End If

'Add If functions for all the bonus brackets

End Sub

Like i said in one of your other post, i still have no idea what im doing so dont trust me.

 

[rehanemis]

Thanks for you suggestion and you really understands what i want.
I will implement try to implement it but can we not achieve this with Switch statement so that our code part reduced.

Actually there might be chance to increase in columns for work done(as you see in my earlier post snap shot).

 

[Vonik]

I'm not familiar with the Switch Statement. Can you upload your database file?

 

[rehanemis]

Please see the attached database.

Using the form when Enter door Sale i would like to populate bonus% according to value in Door Sale.

Techs table contains the criteria for bonus%.

Thanks

Note: Database size is more than 2 mb but less than 3 (after zip) .. how can i send to you.

 

[Vonik]

I'm sorry but i don't see an attached file.

 

[rehanemis]

Please see the attachement

View attachment test.accdb

 

[Vonik]

Rehanemis,

I remember when i was where you are with access, it wasn't that long ago. This is what i have learned:

1. Wait awhile until you start using bound forms, try to make forms with the controls populated by vba code. (Nothing was more frustrating than figuring out bound forms)
2. I tried something similar to what you're doing and ended up with corrupted data everywhere. I was trying to stay away from vba but that was what was hurting me.
3. When you start using bound forms disable all the controls on that form and use a command button that takes data from your bound form (such as a primary key or something unique that can be used to dlookup the primary key) to populate another form (via a recordset or dlookups) where you can edit the data and use a SQL update query to modify the data in your tables.

The solution i suggested and solutions i just tried with your database require a major overhaul of your forms and how you populate their controls.

Sorry dude maybe someone else can offer some better advice.

 

[Vonik]

Rehanemis,

I modified your database, take a look at the new form i created.

 

[Vonik]

and thanks for introducing me to the switch function, i used it in that new form i created, to view the vba press Alt F11

 

[rehanemis]

Thanks for your suggestion. Your suggestion of dlookup works fine.

Thanks again.