Random Incorrect Data

P

psyc0tic1

Access Moron
Local time
Yesterday, 18:21
Joined
Jul 10, 2017
Messages
360
I just noticed a strange thing happening in my database.

I have 2 data input forms that do not create new records but only update information in existing records.

The random thing happening is the code behind the forms is set to log the UserID in each record of the persons making the edits. There are 2 fields in the data table (tbl_auditdata) called LabInspectorUserID and VisualInspectorUserID where the userid values are stored for each record.

What I noticed is that randomly it will log the userid as a zero rather than their actual userid. It doesn't happen all the time either. A user could update 7 records and 2 of them would have a zero for their userid.

This database has been in use since mid 2015 and I noticed the first occurance of this happened at the end of April 2018 and I had not made any changes to the database for months before that.

This is the relevant code behind each form to update the above fields in the data table:

frm_labtestinput:
Code: 
Private Sub UpdateRecord_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Complete"
        Me.Recordset.Fields("LabInspectorUserID").Value = Credentials.UserId
        Me.Recordset.Fields("LabUpdate").Value = Date
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If

    blnGood = False
End Sub

frm_visualinspectioninput:
Code: 
Private Sub Vis_Input_Submit_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Waiting On Lab"
        Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
    blnGood = False
End Sub

I don't see how this is happening and maybe some code from the credentials module is necessary to show... anybody have any suggestions?
 
sounds like '=Credentials.UserId' is not working,
try:
=Environ("Username")
 
Ranman256 said:
sounds like '=Credentials.UserId' is not working,
try:
=Environ("Username")
Click to expand...

Doesn't Environ grab the windows username rather than the database userid? Their windows username is not necessarily the same as the username they used in the database.

In retrospect I should have set the db up to use Environ from the start but too late now.
 
I take it you've checked Credentials to make sure there are no bad records first?

How are you making sure there is a valid user logged in? Do you have some place you can check Credentials.UserId to verify that they are really getting in to the program properly?
 
Mark_ said:
I take it you've checked Credentials to make sure there are no bad records first?

How are you making sure there is a valid user logged in? Do you have some place you can check Credentials.UserId to verify that they are really getting in to the program properly?
Click to expand...

I will post the code for the credentials module and the complete form codes showing how the credentials are checked.

Credentials module:
Code: 
Option Compare Database

Public UserName As String
Public UserId As Integer
Public AccessLvlID As Integer
Public Function GetCurrentUser() As Integer
    GetCurrentUser = UserId
End Function

frm_visualinspectioninput:
Code: 
Option Compare Database

Private blnGood As Boolean

Private Sub Form_Open(Cancel As Integer)
    If Credentials.AccessLvlID <> 1 And Credentials.AccessLvlID <> 2 And Credentials.AccessLvlID <> 4 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
End Sub

Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    
    If Not blnGood Then
        Cancel = True
        strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
        Else
         Me![VisUpdate] = Date
    End If
   
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

Private Sub Vis_Input_Submit_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Waiting On Lab"
        Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.VisInspectDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.QCLine.Value)) Then
        validate = False
    End If
    If (IsNull(Me.BlackGreenDot.Value)) Then
        validate = False
    End If
    If (IsNull(Me.PartProdDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisInspected.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisGood.Value)) Then
        validate = False
    End If
    If (IsNull(Me.MfgPONum)) Then
        validate = False
    End If
End Function

frm_labtestinput:
Code: 
Option Compare Database
Option Explicit
Private blnGood As Boolean

Private Sub Form_Open(Cancel As Integer)
    If Credentials.AccessLvlID <> 1 And Credentials.AccessLvlID <> 3 And Credentials.AccessLvlID <> 4 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
    
End Sub

Private Sub Form_Load()
    On Error Resume Next
    Dim HasUsb As Long
    HasUsb = DLookup("HasUSB", "tbl_parts", "ID = " & Me.Part_Number)
    If HasUsb = 2 Then
      ShowStretch
    ElseIf HasUsb = 1 Then
      ShowStretch2
    ElseIf HasUsb = 11 Then
      ShowStretch3
    Else
      HideShrink
    End If
End Sub

Private Sub ShowStretch()

    Me.InsideHeight = 10000
    
    Me.UsbInput1.Visible = True
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = False
    Me.UsbInput1.Height = 7600
    Me.Label46.Top = 9500
    Me.LabDefectFound.Top = 9300
    Me.Label48.Top = 9300
    Me.Label47.Top = 10200
    Me.LabAtt.Top = 10200
    Me.UpdateRecord.Top = 10500

End Sub

Private Sub ShowStretch2()

    Me.InsideHeight = 3000
    
    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = True
    Me.USBInput3.Visible = False
    Me.USBInput2.Height = 4700
    Me.Label46.Top = 6460
    Me.LabDefectFound.Top = 6460
    Me.Label48.Top = 6460
    Me.Label47.Top = 7460
    Me.LabAtt.Top = 7460
    Me.UpdateRecord.Top = 7760

End Sub

Private Sub ShowStretch3()

    Me.InsideHeight = 10000
    
    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = True
    Me.USBInput3.Height = 7600
    Me.Label46.Top = 9500
    Me.LabDefectFound.Top = 9300
    Me.Label48.Top = 9300
    Me.Label47.Top = 10200
    Me.LabAtt.Top = 10200
    Me.UpdateRecord.Top = 10500

End Sub

Private Sub HideShrink()

    Me.InsideHeight = 7560

    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = False
    Me.UsbInput1.Height = 0
    Me.USBInput2.Height = 0
    Me.Label46.Top = 1800
    Me.LabDefectFound.Top = 1800
    Me.Label48.Top = 1800
    Me.Label47.Top = 2880
    Me.LabAtt.Top = 2880
    Me.UpdateRecord.Top = 3050
    
End Sub

Private Sub cboGoToRecord_AfterUpdate()
    On Error Resume Next
    Dim rst As Object
    Dim HasUsb As Long
    
    Set rst = Me.RecordsetClone
    rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
    Me.Bookmark = rst.Bookmark
     
    HasUsb = DLookup("HasUSB", "tbl_parts", "ID = " & Me.Part_Number)
    If HasUsb = 2 Then
      ShowStretch
    ElseIf HasUsb = 1 Then
      ShowStretch2
    ElseIf HasUsb = 11 Then
      ShowStretch3
    Else
      HideShrink
    End If
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub



Private Sub UpdateRecord_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Complete"
        Me.Recordset.Fields("LabInspectorUserID").Value = Credentials.UserId
        Me.Recordset.Fields("LabUpdate").Value = Date
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If

    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.LabTestDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctTested.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctGood.Value)) Then
        validate = False
    End If
End Function
 
and the login form

frm_loginform:
Code: 
Option Compare Database

Private Sub Form_Load()

  CheckFrontEnd.CheckFrontEnd

End Sub

Private Sub Command1_Click()
    If IsNull(Me.txtLoginID) Then
        MsgBox "Please Enter Login", vbInformation, "Need ID"
        Me.txtLoginID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please Enter Password", vbInformation, "Need Password"
        Me.txtPassword.SetFocus
    Else
        Credentials.UserName = Me.txtLoginID.Value
        If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword Then
            Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            
        
            Select Case Credentials.AccessLvlID
                Case 1
                    If showProfile() Then
                        DoCmd.OpenForm "frm_home", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_home"
                    End If
                Case 2
                    If showProfile() Then
                        DoCmd.OpenForm "frm_visualinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_visualinspector"
                    End If
                Case 3
                    If showProfile() Then
                        DoCmd.OpenForm "frm_labinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_labinspector"
                    End If
                Case 4
                    If showProfile() Then
                        DoCmd.OpenForm "frm_multiinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_multiinspector"
                    End If
                Case 5
                    If showProfile() Then
                        DoCmd.OpenForm "frm_engineer", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_engineer"
                    End If
                Case 6
                    MsgBox "Your Account Has Been Deactivated. Please Contact the Administrator."
                Case Else
                    DoCmd.OpenForm "frm_loginform"
            End Select
            
            DoCmd.Close acForm, Me.Name
        
        Else
            MsgBox "Incorrect Login or Password"
        End If
    End If
    
End Sub

Private Function showProfile() As Boolean
    Dim Password As Variant
    Dim Question1 As Variant
    Dim Answer1 As Variant
    Dim Question2 As Variant
    Dim Answer2 As Variant
    Dim Question3 As Variant
    Dim Answer3 As Variant
    
    Password = DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question1 = DLookup("Question1", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer1 = DLookup("Answer1", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question2 = DLookup("Question2", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer2 = DLookup("Answer2", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question3 = DLookup("Question3", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer3 = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    
    showProfile = (Password = "password" Or IsNull(Question1) Or IsNull(Answer1) Or IsNull(Question2) Or IsNull(Answer2) Or IsNull(Question3) Or IsNull(Answer3))
End Function
 
What exactly is Credentials?
If it is a DAOrecordset, you may need Credentials!UserId
 
Rather than the repeated DLookups, I'd make a query that returns the single record you are trying to fetch and use that for all of your comparisons.

If for any reason one of them fails you'd not know as DLookup returns NULL and you are not checking for NULL.

I would also make sure you have OPTION EXPLICIT in there to make sure you have not misspelled something.

Your query would have all of the fields you need to check and you would simply set the filter and look at the single record that should be returned. This way if you have any issue regarding talking to your back end you will know immediately. As is, you could (for some reason) have ONE Dlookup fail but others work, thus getting the type of issue you have.
 
jdraw said:
What exactly is Credentials?
If it is a DAOrecordset, you may need Credentials!UserId
Click to expand...

I do not know the answer to that question but I can try changing the period to an exclamation mark and see what happens.
 
Mark_ said:
Rather than the repeated DLookups, I'd make a query that returns the single record you are trying to fetch and use that for all of your comparisons.

If for any reason one of them fails you'd not know as DLookup returns NULL and you are not checking for NULL.

I would also make sure you have OPTION EXPLICIT in there to make sure you have not misspelled something.

Your query would have all of the fields you need to check and you would simply set the filter and look at the single record that should be returned. This way if you have any issue regarding talking to your back end you will know immediately. As is, you could (for some reason) have ONE Dlookup fail but others work, thus getting the type of issue you have.
Click to expand...

If I understand what you are saying... you are saying that if this DLookup fails during login from the login form:
Code: 
Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")

then when this line in the appropriate data input form fires upon clicking the submit button:
Code: 
Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId

it would cause that value being input into the table to be a zero rather than their actual userid?

Wouldn't that cause all records edited by that user to have zero's for their userid since that DLookup failed during login before any records get edited on other forms? Remember I said they could edit 7 records but only 2 of them have a zero (or 1, or all of them, or none) for their userid and all others have the correct userid.

if there were any misspellings or any of that... wouldn't this have been an issue from inception rather than starting 2 years later? Or if there were misspellings, wouldn't it fail every time rather than random?
 
Can you take a screen capture of your relationship window and post the graphic so we can see your design?
 
jdraw said:
Can you take a screen capture of your relationship window and post the graphic so we can see your design?
Click to expand...

My relationships window only contains relations for the navigation but here it is

attachment.php
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    37.2 KB · Views: 213
Can you post a copy of your database --remove anything confidential first?
We do not need all of you data-- a few sample records --Porky Pig , Uncle Sam etc would suffice. We need to see some table design to get some context for your post.
 
In reverse order;

OPTION EXPLICIT means you would need to define all variables prior to using them and helps in development when you are most likely to accidentally misspell something. Just a very good coding habit to be in, but may or may not have an impact on your current issue.

For the DLookups, you have not indicated if the user does or does not log in/out often or if that can occur when they are having an issue. Likewise we do not have your application so we can't see if some code you have elsewhere is clearing or resetting the ID in question. For myself, I would be using a query that should be returning one and only one record when the user logs in. This way you cannot have an issue of one DLookup failing for any reason while another works. As is, this just "Appearing" says there may be a hardware issue / network issue. Either that or something was changed but you've not listed it.

Odd that only the ID is being affected though.
 
jdraw said:
Can you post a copy of your database --remove anything confidential first?
We do not need all of you data-- a few sample records --Porky Pig , Uncle Sam etc would suffice. We need to see some table design to get some context for your post.
Click to expand...

I have attached a copy of my database with most of the data stripped and converted all of the tables to local.

The first 7 records were the ones where I noticed the zero's for the userid in the LabInspectorUserID field of the tbl_auditdata. They do not reflect that now because I changed their status and deleted some data to coincide with that status.

You can login with Username: user1 Password:user1 and you will be an admin and see all forms in the navigation tabs.
 

Attachments

Mark_ said:
In reverse order;

OPTION EXPLICIT means you would need to define all variables prior to using them and helps in development when you are most likely to accidentally misspell something. Just a very good coding habit to be in, but may or may not have an impact on your current issue.

For the DLookups, you have not indicated if the user does or does not log in/out often or if that can occur when they are having an issue. Likewise we do not have your application so we can't see if some code you have elsewhere is clearing or resetting the ID in question. For myself, I would be using a query that should be returning one and only one record when the user logs in. This way you cannot have an issue of one DLookup failing for any reason while another works. As is, this just "Appearing" says there may be a hardware issue / network issue. Either that or something was changed but you've not listed it.

Odd that only the ID is being affected though.
Click to expand...

I understand a bit more about what you were saying now. I have noticed that if you sit idle in the front end for a while, the little locked icon in the folder where the back-end is located that shows someone is in the database will go away and will come back again when you start doing something (providing there is only one person doing work at that time).

Nobody has ever told me they were having any issues though... I just noticed this when I was poking around in the data to see what people were doing.

I talked to one of the persons that was the record updater where I noticed this and they said nothing happened out of the ordinary other than the computer they use is very slow and old and of course due to the great distances between front-ends and the back-end... slow network reaction time.
 
psyc0tic1 said:
I talked to one of the persons that was the record updater where I noticed this and they said nothing happened out of the ordinary other than the computer they use is very slow and old and of course due to the great distances between front-ends and the back-end... slow network reaction time.
Click to expand...

I'd ask if they ever have an issue logging in... Say they KNOW they typed the password correct but it didn't log in properly.
 
Mark_ said:
I'd ask if they ever have an issue logging in... Say they KNOW they typed the password correct but it didn't log in properly.
Click to expand...

Any time someone tells me they KNOW they typed their password correct but it didnt work... the db won't let them in because they always didn't type it in correctly or they forgot it. I know for sure because I used to be the one that had to go in and check if they remembered it correctly or not.

I have a password reset form in place as well so they can get in anyway.

all good information Mark... thank you for your input.

**EDIT**
I asked the users about issues logging in and everyone says the same thing... it is slow but logs in eventually.

Maybe again... the slowness of the back-end being many states away on our network causing something to get weird?
 
Last edited:
You must log in or register to reply here.

Similar threads

BlueSpruce
Solved Highlighting Next Record After Current Record Filtered Out of Datasheet
2
Replies
33
Views
1,755
BlueSpruce
BlueSpruce
MajP
Randomize Not Working
2
Replies
22
Views
1,971
The_Doc_Man
The_Doc_Man
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
1,003
Mike Krailo
Mike Krailo
M
Looking for Faster way to update Table - boolean field
2 3
Replies
44
Views
4,247
cheekybuddha
cheekybuddha
J
Refreshing List Box
Replies
8
Views
795
JamesJoey
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom