Error Mylti-User

[calvinle]

Hi,

I have built a database for multi-user having a front end and backend linked to a database.

When 1 user add items, it's fine. Now that I allow multiple user to add data, all the data are screwed, and it became #Error and has that "Not a valid bookmark" error.

I have a main form: frmParent
On that main form, i have a subform.

The code I have in the subform is:

 Private Sub Form_Current()
  Form_frmParent.subWork.Requery
End Sub

Is this code that cause that error?

 

[GinaWhipp]

Does everyone have their own Frontend? Is that Subform's Recordsource a Table or a Query? If a Query please post the SQL here...

 

[Uncle Gizmo]

Please could you explain in detail how your front end back end arrangement is set up?

 

[spikepl]

Form_frmParent.subWork.Requery

This format for referring to objects is for people who know what they are doing, because it has many unexpected sideeffects.

The proper format is

Forms!frmParent!subWork.Form.Requery

 

[calvinle]

Does everyone have their own Frontend? Is that Subform's Recordsource a Table or a Query? If a Query please post the SQL here...

Yes, everyone have their own frontend on their desktop.
The subform is recordsource to a table in continuous form.

Please could you explain in detail how your front end back end arrangement is set up?

My backend database only contain table.
My frontend link the backend database table (stored on the server).

This format for referring to objects is for people who know what they are doing, because it has many unexpected sideeffects.

The proper format is

Forms!frmParent!subWork.Form.Requery

Does this make any difference? as they all requery aren't they?

Thanks

 

[GinaWhipp]

If the Requery event is on the Main Form change to...

Me.subWork.Requery

...as it sounds like subWork is a control of the Main Form.

All that said, what are they changing? Because what looks to be happening is some PK is being changed but not being committed yet it is erroring out.

 

[calvinle]

I have removed that requery, because I judge it's useless.

However, I have made another test to enter a data at the same or almost same time with another user here, and here is the error I get:


Is there a way to prevent this to happen? or replace this error with another message or so?

Thanks.

 

[GinaWhipp]

Apparently, you have set up a field in the Table to be unique and allow no duplicates. You need to fix that in order to remove that error.

 

[calvinle]

Apparently, you have set up a field in the Table to be unique and allow no duplicates. You need to fix that in order to remove that error.

Yes, that unique field is the Primary Key..

 

[gemma-the-husky]

are you working ok, other than that?

the thing is, random errors of the sort you describe can often indicate corruption

 

[calvinle]

Everything else is ok. That only happen when user add new record at the same time.

 

[GinaWhipp]

I thought so, however, this suggests you are allowing Users to enter data in that PK field which should never happen. It also suggests you are allowing them to enter data on the MANY side without the data first being on the ONE side.

I am assuming this is a Form/Subform with the Subform in Datasheet view. Well, you need to control how the Users get data in the Subform by forcing them to enter on the one side first.

 

[calvinle]

The subform is on the main form in continuous view.
They then simply click on the next record to enter a new record.
Whenever they enter anything in a field then the pk id will create 1 record.
How else can I manipulate the user for entering data in continous form?

 

[GinaWhipp]

Hmm, then perhaps there is some corruption. If you open the Table directly and try to enter a new record, what happens.

 

[calvinle]

you mean while the user are adding new data to it or when they all off?

 

[GinaWhipp]

No difference, just open the Table and go to a new record and see what happens when you enter it. Do you get an error then?

 

[calvinle]

I left for lunch then I came back, and the error appears again.
I had to compact my database, so I lost total 2 line of data.
I have tried to add new line via the table and no problem.

After I compact the database and reopen, i see duplicate PK.. how is that possible??

 

[GinaWhipp]

Okay, it's either corruption or that is not a Table bout a Query that that Subform is based on. Please post the Recordsource of the Subform.

 

[calvinle]

I have a main form: frmParent
I have a subform on that main form: subWork
I have a combobox on that main form to load all user id on parent form opening from tblUser.
FormParent on load:

   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

Combobox rowsource:

 SELECT [tblUser].[UserID], [tblUser].[Full Name] FROM tblUser ORDER BY [Full Name];

In that combobox after update: becauser user role 5 can choose, and under cannot.

  If IsNull(Form_frmParent.cboUser) Then
    Form_frmParent.subWork.LinkMasterFields = ""
    Form_frmParent.subWork.LinkChildFields = ""
  Else
    Form_frmParent.subWork.LinkMasterFields = "cboUser"
    Form_frmParent.subWork.LinkChildFields = "UserID"
  End If

subWork source: subWork (subform)
subWork recordsource: tblWork
subWork on main form link master: will change depending on VBA above
subWork on main form link child: will change depending on VBA above
In all this, I have added an audit for delete, add, edit record found on Allen Browne web site. But I have disable them for the testing thinking that maybe that is the problem. So, for now, no more tracking for delete or add record.

 

[GinaWhipp]

Sorry but non of the above answers what I asked. Please post the Recordsource of the Subform.