Select Case

[Rayner]

Hello

I have a Database that is being used to calculate work on a Project

Each user completes a Form on the Database with his or her hours, but as each user has a set rate per hour, I would like this recorded for the job (this amount changes each year, so it can't be calculated on the fly with an expression. The value needs to be stored for the date it was done ).

There are about a dozen users, and each user has a different rate per hour.

I thought that the best way to capture this would be with an On Load Event.

I was going to use a Select Case statement, like this:

Code: Select all

Private Sub Form_Load()
Select Case StaffName
Case Joe.Bloggs
Me.StaffRate = 80
Case John.Smith
Me.StaffRate = 90
End Select
End Sub

But that didn't work, so I tried it like this:

Code: Select all

Private Sub Form_Load()
Select Case StaffName
Case Me.StaffName = Joe.Bloggs
Me.StaffRate = 80
Case Me.StaffName = John.Smith
Me.StaffRate = 90
End Select
End Sub

But when the form loads, I get an 424 Message: "Object Required"

I'm not really familiar with building Case Statements (obviously), so any advice regarding the (wrong) way I'm structuring it would be greatly appreciated.

P.S. This is the way it seemed to be structured when I Googled it, but I guess I'm missing something. :(

Many thanks for any help in fixing my code.

[HansV]

Hi Rayner,

Welcome to Eileen's Lounge!

Literal text values must be enclosed in double quotes, so the proper syntax would be

Code: Select all

Private Sub Form_Load()
  Select Case StaffName
    Case "Joe.Bloggs"
      Me.StaffRate = 80
    Case "John.Smith"
      Me.StaffRate = 90
  End Select
End Sub

However, I wouldn't do it this way.

In the first place, I'd prefer to store the staff rates in a table. Then you'd only have to update the table once a year, instead of updating the VBA code, possibly in multiple places. Even better, you could have a separate record for each staff member and each year, so that you can look up both the current rate and that for previous years.
In the second place, depending on how the users enter data, it'd probably be better to look up the staff in the On Current event of the form and in the After Update event of the text box or combo box in which the staff member is selected.

[Rayner]

Thank you Hans

I originally stored the values in a Table with a different Field for each Year, ie, 2009 Rate, 2010 Rate, 2011 Rate, etc, but because there will be so many Reports doing calculations on the Table that's storing the Project Hours, it was decided that it would be easier to store the Value in the Field for the Date and Hours, and then only update the VB Code once, rather than update each Query for each Report, if that makes sense? I think there may have been other reasons, but it was discussed a few weeks ago, and I may have forgotten. I just remember that they wanted the Default Value of the Project Hours Table to be populated based on whichever user was entering the data.

Many thanks

By the way, if I add this code to the On Load event, would this overwrite the Field each time. What I really want is for it to populate the Field, ie, the Default Value should be set based on who's adding the Hours to the Form. I guess I can't add the above code to an Event. Should I create a Module like:

Code: Select all

Option Compare Database

Select Case StaffName
Case "John.Smith"
Me.StaffRate = 80
Case "Joe.Bloggs"
Me.StaffRate = 90
End Select

And then add Rate() to the Default Value Property?

Thanks again

[HansV]

Where do you get the StaffName from? Do users have to enter it, or is it the Windows login name, or ...?

[Rayner]

Yes, that's right. The Username is the user's Windows Login Name, which is entered via a Module retrieval.

Code: Select all

Option Compare Database
Private Declare Function WNetGetUserA Lib "mpr.dll" _
    (ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long
Public Function GetNetUser() As String
    Dim lpUserName As String, lpnLength As Long, lResult As Long
    'Create a buffer
    lpUserName = String(256, Chr$(0))
    'Get the network user
    lResult = WNetGetUserA(vbNullString, lpUserName, 256)
    If lResult = 0 Then
        GetNetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1)
    Else
        GetNetUser = "-unknown-"
    End If
End Function

HTH

[HansV]

If you don't want to store the rates for each staff member and each year in a table, I'd still store the current rate for each staff member in a table, and use that. If you get a new staff member, or if someone's rate changes during the year, you only need to edit the data in the table. This can be done while others are using the database. In order to edit code, you need exclusive access to the database, and if you have a split frontend/backend design, you'd need to replace all frontend databases.

And yes, you should set the DefaultValue property of (the control bound to) StaffRate rather than its value.

[Rayner]

Thank you again Hans

I have a few concerns with your suggestion:

1) If the Value is stored in a a Staff Table, then the value of work could change in Reports.

For example

If Project 1 had User 1 work 12 hours @ 80.00, then a Report could say that billable Amount is 960.00.
If User 1's Rate-Per-Hour changed to 90.00, then when the Report ran again, wouldn't the value show that the amount that was billed was 1,080, whereas, if the value is stored in the Project-Hours Table, this rate would always remain at the amount that was billed at the time the work was carried out?

2) I also think the MD wants to be able to change the rate on-the-fly, e.g., even though User 1's rate is normally 80 per hour, for a job that involved more travel or was more complex, the rate per hour might be 90 per hour, but it would only be for this one job, so having it stored in the Staff Table wouldn't work. It needs to be stored per job, per user.

Does that make sense?

I'm sorry if it seems I'm being really awkward--I'm just trying to implement the solution the way I've been asked to.

(FWIW, I did have the Database Tables normalized the way you're suggesting, i.e., the Rate-per-Hour in the Staff Table)

Anyway, if I do therefore want the Default Value of the Rate to be based on whichever user is logged on, should I create a Module (e.g., called Rates) and then put a Select Case Statement in it?

Many thanks for your help and patience.

[HansV]

I didn't mean that you should create a query based on the project-hours table and the staff table joined by staff ID.

1. You'd use the staff table to look up the rate once and store it in the project-hours table. The staff table just makes it easier to work with.

2. If you don't lock the rate field in the form(s) based on the project-hours table, you can still change the rate manually after it has been filled in by the lookup function.

[Rayner]

Sorry, Hans. I obviously misunderstood you.

I know how to use a Lookup for opening forms, eg, Open Form x where Form Y ID = Form X ID, and I know how to do a Lookup in Excel, but I'm not sure how to automatically populate the Default Value of a new Database Record via a Lookup, which is why I thought I had to use a CASE Statement.

Any help in guiding me in the right direction would be very much appreciated, and sorry for being so dumb! :(

Thanks again

[HansV]

Here's an example of the code you could use (you'll obviously have to modify the names used in the code):

Code: Select all

Private Sub Form_Load()
  Dim strUser As String
  Dim varStaffID As Variant
  Dim varRate As Variant
  strUser = Environ("Username")
  varStaffID = DLookup("StaffID", "tblStaff", _
    "StaffName = " & Chr(34) & strUser & Chr(34))
  Me.StaffID.DefaultValue = Nz(varStaffID)
  varRate = DLookup("Rate", "tblStaff", _
    "StaffName = " & Chr(34) & strUser & Chr(34))
  Me.Rate.DefaultValue = Nz(varRate)
End Sub

I have attached a small sample database.

StaffRates.zip

[Rayner]

Thank you again Hans.

I added the code above and just made some minor changes to the Field Names.

Code: Select all

Dim strUser As String
  Dim varStaffID As Variant
  Dim varRate As Variant
  strUser = Environ("Username")
  varStaffID = DLookup("StaffID", "tblStaff", _
    "StaffName = " & Chr(34) & strUser & Chr(34))
    Me.StaffID.DefaultValue = Nz(varStaffID)
  varRate = DLookup("2010Rate", "tblStaff", _
    "StaffName = " & Chr(34) & strUser & Chr(34))
  Me.StaffRate.DefaultValue = Nz(varRate)
End Sub

When I run the code, it gives a run-time error '3075'
Syntax Error (missing operator) in query expression '2010Rate'.

When I debug, it opens at the 7th line down:

Code: Select all

varRate = DLookup ("2010Rate", "tblStaff", _"

2010 Rate is the Name of Field in the tblStaff table, so I don't think I made a mistake in changing the names.

Can you see what I did wrong?

Many thanks for continuing to help me.

[HansV]

There shouldn't be a double quote " at the end of the line. If the Visual Basic Editor adds this quote automatically, it isn't recognizing one of the quotes earlier in the line. Make sure that you're using straight quotes, not "curly" or "smart" quotes.
Straight quotes look like this: " (good)
Curly quotes look like this: “ or ” (bad for use in VBA code)

[Rayner]

Those extra double-quotes are actually for the next line

Code: Select all

"StaffName = " & Chr(34) & strUser & Chr(34))

Obviously forgot to delete them when I pasted that one line in.

So, unfortunately, the error still occurs and stops at the same line.

Regarding the curly quotes: I copied and pasted the code into Notepad to eliminate any curly quotes and then pasted it back into VB in Access, so that should remove them, but (again), the error still appears. :(

Could it be something else?

Sorry for taking so much of your time.

[HansV]

Pasting into Notepad will not necessarily remove curly quotes. Try deleting the quotes, then typing them again in the Visual Basic Editor itself.

If that doesn't help, could you attach a stripped down, compacted and zipped copy of the database?
Remove items that aren't relevant to your question from the database, and make sure to delete or alter sensitive information.

[Rayner]

Thanks Hans

I manually typed the quotation marks into the DB, but unfortunately, it's the same error.

I've stripped everything unnecessary from the DB, so if you could have a look, that would be greatly appreciated.

Thank you again.

[HansV]

Apparently, Access is being picky about the field name beginning with digits instead of with a letter. You can get round it by enclosing the field name in square brackets:

Code: Select all

  varRate = DLookup("[2010Rate]", "tblStaff", _
    "StaffName = " & Chr(34) & strUser & Chr(34))

BTW, since you're using the GetNetUser function anyway, you might as well change the line

Code: Select all

  strUser = Environ("Username")

to

Code: Select all

  strUser = GetNetUser

[Rayner]

Thanks for fixing the Error message. I no longer get such when I open the Form, but the Field isn't populating with a figure. Was there something else I needed to add (eg, to the Default Value Property) to get it to populate when I open the Form or add another row?

Sorry for being SUCH a pain! :(

[HansV]

I think your form wasn't set up the way you intended - I get the impression that you want to allow only records belonging to the current user.
The attached version has been modified to do that. The record source of the form has been changed to a query that selects the records for the current user.

EL.zip

[Rayner]

Thank you Hans. Your help has been tremendous. I'm sure I couldn't have figured it out without you.

You and this Board are a fantastic resource.

I hope I don't have to bother you again (at least with this query, LOL).

Have a great week.

Take care

Kyle

[HansV]

You're welcome. Don't hesitate to ask more questions!