Need help pulling specific data out of a table

[kfuegoj]

Presently I have a form that is functioning from a specific table within my database. As the user enters individual sales information information into the form I need to be able to pull user data from another table. Essentially what I am trying to acoomplish is to be able to identify a user as they enter the data into the database, look up a sales commission rate and pull it back into the form for manipulation. If there were only 3 or 4 users it could be done with an If then or Case statement, however there are well over a hundred different people with varying percentage rates.

So to clarify my babble, I'm looking to take a field on a form, use it to identify specific information in a table not associated with the form, and save it in a variable for manipulation. Can anyone help me?

 

[ChrisO]

You will need to substitute your own names here but this might get you started.

Sub TestIt()
    Dim sngCommissionRate As Single
    
    sngCommissionRate = Nz(DLookup("SalesCommissionRate", "tblCommissions", "UserName = " & Chr$(34) & CurrentUser() & Chr$(34)), 0)
    
End Sub

Sorry, not reading too well today.

Assuming a Combo Box called cboUsers: -

Private Sub cboUsers_AfterUpdate()
    Dim sngCommissionRate As Single
    
    sngCommissionRate = Nz(DLookup("SalesCommissionRate", "tblCommissions", "UserName = " & Chr$(34) & Me.cboUsers & Chr$(34)), 0)
       
End Sub

Hope that helps.

Regards
Chris

 

[kfuegoj]

Thank you

Thank you for the reply. I see where you are going with most of this code, but could you explain to me what this part is doing--sorry i'm a little new at the coding !!


"UserName = " & Chr$(34) & CurrentUser() & Chr$(34)), 0)

 

[ChrisO]

We are looking up the value of SalesCommissionRate in table tblCommissions
where UserName = Some Name. (Either the CurrentUser() or a name in a Combo Box)

It is possible that the Some Name might have an apostrophe in it and surrounding it
with Chr$(34) ie double quotes will prevent problems. The whole line is enclosed in
a call to the Nz function with a default value of zero, in case the Some Name is not found.

 

[ChrisO]

By the way.

Since this is a double posting I was wondering if you might go back and delete the other post.

Thanks
Chris

 

[kfuegoj]

Thank you

Thank you chris that was very informative and exactly what I was looking for.

 

[kfuegoj]

I cannot delete the other post

Chris,

I didn't realize it posted twice. It must have posted once when I went to preview it, and then when I hit submit.

Any I went into the other post and it will not let me delete it because I don't have admin rights. Sorry.

 

[ChrisO]

OK, not a problem.

I think you should be able to edit your posts and delete them but maybe after someone has replied that's not on.

Anyway I'll post a message there to refer to this post.
At this stage bjackson is the only one to reply in the other thread.

Originally posted by bjackson in the other thread.

"cant you use a combo box so the user can select their identity
and then draw the information from that combo box"

Regards
Chris

 

[Mile-O]

Re: I cannot delete the other post

Any I went into the other post and it will not let me delete it because I don't have admin rights.

As you started the other post you can delete it - click the Edit button on the first post and then check the delete box at the top, then press Delete.