Not sure how to best to describe this one, any help appreciated!

[JeffBarker]

Hi guys,

So I have a bit of an in-depth problem here, so I'll try to be brief but include as much information as I can!

Our CRM is an Access 2007 database and we use it for all sorts of things, including raising orders for our clients.

When our users open the database they are presented with a log-in screen that asks for a user name and password and logs them in. At this point the database writes an entry to tblLogIns with their User ID, Machine Name and Log In Date/Time.

We have an unbound text box on the main menu that contains this code to store their username:

Public Function fGetUserName()
    Dim vName As String, vUser As Integer
    
    vName = fOSMachineName

    If IsNull(DLookup("[UserID]", "tblLogIns", """" & vName & """" & " = [UserPC] and isnull([tblLogIns]![LogOutDate])")) Then
        fGetUserName = "unknown"
        Exit Function
    Else
        vUserID = DLookup("[UserID]", "tblLogIns", """" & vName & """" & " = [UserPC] and isnull([tblLogIns]![LogOutDate])")
    End If

    fGetUserName = DLookup("[UserName]", "tblUsers", vUserID & " = [UserID]")

The fOSMachineName code looks like this:

    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
    
    fOSMachineName = fStripNull(fOSMachineName)

The main menu stays open at all times, and we refer to the text box with the user name in at various stages in the database, making sure client orders are logged under the relevant user and emails generated from the database are sent from that person, that sort of thing.

I have two problems here, and I think they are related:

1. From time to time the user name seems to drop out of the test box using the fGetUserName code, and replaces it with 'unknown', so we have client orders logged under 'unknown' instead of Joe Bloggs etc.

2. We are currently trying to block our users from amending orders that are over 24 hours old, as historically some changes are not being filtered down to our Accounts department, invoices have been sent out incorrectly etc, and it's all a bit of a mess.

The current solution we have in place has only been live for a week, and already we have problems - we have some code that sits on the Order Entry form's On Current event that looks at the date the order was placed, and if that date is less than today, it will lock the form completely based on the current user's user type:

Dim UT As String
UT = fGetUserType

    Select Case UT
        Case "Admin"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
            
        Case "Director"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
            
            If Me.txtLogDate < Date Then
                Me.AllowEdits = False
                Me.[tblallocation subform].Enabled = False
                Me.TabCtl33.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblallocation subform].Enabled = True
                Me.TabCtl33.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If

        Case "User"
            If Me.txtLogDate < Date Then
                Me.AllowEdits = False
                Me.[tblallocation subform].Enabled = False
                Me.TabCtl33.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblallocation subform].Enabled = True
                Me.TabCtl33.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If

        Case "Manager+"
            If Me.txtLogDate < Date Then
                Me.AllowEdits = False
                Me.[tblallocation subform].Enabled = False
                Me.TabCtl33.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblallocation subform].Enabled = True
                Me.TabCtl33.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If
        
        Case "Finance"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
        
    End Select

fGetUserType looks like this:

    Dim vName As String
    Dim vUserID As Integer
    
    vName = fOSMachineName
    
    If IsNull(DLookup("[UserID]", "tblLogIns", """" & vName & """" & " = [UserPC] and isnull([tblLogIns]![LogOutDate])")) Then
        fGetUserType = "none"
        Exit Function
    Else
        vUserID = DLookup("[UserID]", "tblLogIns", """" & vName & """" & " = [UserPC] and isnull([tblLogIns]![LogOutDate])")
    End If
    
    fGetUserType = DLookup("[UserType]", "tblUsers", vUserID & " = [UserID]")

At some point, this stops working as users are using the system - and I can't work out why. I've stepped through the code after a user has informed me that they are able to amend an order, and it treats fGetUserType as a null value, thus completely bypassing my Select Case.

Does anyone have any ideas on why this might be happening, or suggestions on how I can improve this process please?

Many Thanks in advance!

Jeff.

 

[Cotty42]

Have you referenced the variables fgetusername etc. as Public, otherwise they will only be populated inthe function / sub-routine that references them.

Regards

Dave

 

[JeffBarker]

Have you referenced the variables fgetusername etc. as Public, otherwise they will only be populated inthe function / sub-routine that references them.

Regards

Dave

Hi Dave, thanks for the quick response here - I'm not sure, as those functions were already in the system when I took over the role!

Would you be able to advise how to check, please?

Thanks,

Jeff.

 

[Cotty42]

They will either be declared in a separate module in the database or in the Compare and Declarations area athe top of you code.

Look for something like:

Public fGetUserName As String

You can also add a watch on the variable and watch the stored value as you step through the program. If you get an <Out of context> message for the value then it is not stored as a Public variable.

 

[JeffBarker]

They will either be declared in a separate module in the database or in the Compare and Declarations area athe top of you code.

Look for something like:

Public fGetUserName As String

You can also add a watch on the variable and watch the stored value as you step through the program. If you get an <Out of context> message for the value then it is not stored as a Public variable.

Hi Dave,

Just as you posted this I realised what you were talking about, currently fOSMachineName is only marked as

Function fOSMachineName() As String

But I've now changed that to Public Function.

fGetUserName and fGetUserType are both public, but as they both use fOSMachineName I guess that could have been the weak link?

Thanks,

Jeff.