Error Mylti-User

[calvinle]

In case, you will ask me more later, I decided to post them all, but I also did post the recordsource:

subWork recordsource: tblWork

 

[GinaWhipp]

Oh, okay, missed that... sorry. Hmm, what are the PK's in that table and what are their Data Types.

 

[calvinle]

The code for all my open table are: openTable instead of opendynaset, do you think that might be the cause?

The PK is autonumber, and the data are field are just text, date, and number. nothing special.

 

[GinaWhipp]

No, I don't think that is the cause. That error message is suggesting that the PK is getting duplicated. If everything else is ruled out then the only thing left is corruption.

Hmm, why are you doing this...

  Set db = DBEngine.OpenDatabase("[URL="file://server/file.accdb"]\\server\file.accdb[/URL]")
  
  Set rs = db.OpenRecordset("tblUser", dbOpenTable)
  
  rs.MoveFirst
  Do Until rs.EOF
    If rs.Fields("Username") = NetworkUserName Then
      If rs.Fields("User Role") = 5 Then 'AUDIT USER
        Form_frmParent.cboUser.Enabled = True
        Form_frmParent.subWork.LinkMasterFields = ""
        Form_frmParent.subWork.LinkChildFields = ""
      Else 'NORMAL USER
        Form_frmParent.cboUser = rs.Fields("UserID")
        Form_frmParent.cboUser.Enabled = False
        Form_frmParent.subWork.LinkMasterFields = "cboUser"
        Form_frmParent.subWork.LinkChildFields = "UserID"
      End If
    End If
    rs.MoveNext
  Loop

To open the Form? When all you have to do it Link the Form/Subform it will show only that Users records. However, that still does not explain the message. Hmm, do you have Name Autocorrect checked? If yes, uncheck Compact & Repair and see what happens.

 

[calvinle]

I set that code to verify the user role of the user that used it.

If the user fall into another role than 5, then the listing will only display the record that has their user id.
If the user fall into role 5, then they will see all data.
Because I don't want normal user to see other user's data.

For the autocorrection, you mean on my subform? I just set all the field to autocorrect to no now.

Another question:
Since the subform is continuous view, whenever they add a detail in a field, it will create an PK id. Does it also save that record?

Is this code useless? Because I thought this might be the error as the record is not saved after they add detail to the record:

 Private Sub Form_AfterDelConfirm(Status As Integer)
  DoCmd.Save
End Sub
 Private Sub Form_AfterUpdate()
  DoCmd.Save
End Sub

 

[GinaWhipp]

No, Autocorrect is under Access Options...

A record is saved once you go to a new record unless you select Undo. That code is useless, remove it. And really serves no purpose for what you are doing.

 

[GinaWhipp]

I am stumped... Without seeing the File I have no idea why you are getting that message.

 

[calvinle]

Will you mind checking the file? I will remove all unecessary data then i wil attach it here.

Thanks for your support.

 

[GinaWhipp]

I can check the file and yes, please remove all private data but DO leave something in it for me to see the issue.

 

[calvinle]

I have removed all the unrelated information to the file. After I left work, there was an error again. I realized it when I open the database from home, so I left you with the error in the backend file. You will just need to compact it to remove the error (im sure you know how to, but just in case).

Here is the file (password sent to your PM)
View attachment Quality Audit Control.zip

 

[arnelgp]

i think the problem lies in here:

 Do Until rs.EOF     If rs.Fields("Username") = NetworkUserName Then       If rs.Fields("User Role") = 5 Then 'AUDIT USER         Form_frmParent.cboUser.Enabled = True         Form_frmParent.subWork.LinkMasterFields = ""         Form_frmParent.subWork.LinkChildFields = ""

when new record is added userid will be blank.

 

[GinaWhipp]

Okay, got it and found first problem...

The not a valid Bookmark error is because you have corruption in tblWork. Look at the records for User ID 14 and you will see them. You need to remove those. Now, you could try Compact & Repair but that might not work so...

Export the Table out to Excel, remove the bad records and reimport.

Next suggestion...
Remove ALL the Form_frmParent and replace with Me because that will cause other issues. Note, that is not something you use regularly, as a matter of fact I use so sparingly I can count on my hands the amount of times I needed it.

Doing the above should fix your issues.

 

[calvinle]

i think the problem lies in here:

 Do Until rs.EOF     If rs.Fields("Username") = NetworkUserName Then       If rs.Fields("User Role") = 5 Then 'AUDIT USER         Form_frmParent.cboUser.Enabled = True         Form_frmParent.subWork.LinkMasterFields = ""         Form_frmParent.subWork.LinkChildFields = ""

when new record is added userid will be blank.

That cannot be. The userid is query thru the tblUser, and it's not a required either the PK.

 

[GinaWhipp]

@arnelgp

That is not the issue. There is corruption in a Table, that is the problem.

 

[calvinle]

Okay, got it and found first problem...

The not a valid Bookmark error is because you have corruption in tblWork. Look at the records for User ID 14 and you will see them. You need to remove those. Now, you could try Compact & Repair but that might not work so...

Export the Table out to Excel, remove the bad records and reimport.

Next suggestion...
Remove ALL the Form_frmParent and replace with Me because that will cause other issues. Note, that is not something you use regularly, as a matter of fact I use so sparingly I can count on my hands the amount of times I needed it.

Doing the above should fix your issues.

How is that corruption cause since the beginning? The data was clean when they use it the first time, so what cause that corruption?

I dont see any corruption with userid 14. You mean I have to delete that user off the record? or the corrupted row only? When I compacted and repair the database, it will remove all the #Error. Which will created duplicate record, I then delete those record to make sure there is only 1 standing unique PK. But the problem keep coming back.

See:

 

[GinaWhipp]

Learn about symptoms and causes here...
http://www.granite.ab.ca/access/corruption/symptoms.htm

Don't compact, export to Excel and get rid of the bad records. You only need to remove the bad rows not the User.

 

[calvinle]

Okai, i will remove via excel, however i have a question. In the sample database, I have removed the related table to that tblWork. By imprting back the data from excel, will it impact the related data?
There is not much data ln the related table now, so I can delete them, but later on, there will be more data.

You said to not compact? Can you explain why? Is there hiding corrupted data even after compacting?

Thanks. It's good to learn new stuff.

 

[GinaWhipp]

It will affect it as the PK will no longer be Autonumber but we can deal with that on this small set of data. We need to figure out why that happened so it can be prevented.

The compacting is *hiding* the bad rows, at least I think that is what is happening. However, if you bring it back and it happens again then we will then know it is something within the table itself.

 

[calvinle]

If the error is due to the corrupted table, then the user should not be able to add data right at all right? Why after I compact, the user is able to add data still until it bug again?

 

[GinaWhipp]

Exactly, that's why we want to get rid of the bad records and see if we can figure out what is causing the corruption or it is just going to keep happening.