Multiple users in db causing debug error.

[beacon]

I'm having some trouble with my database (what's new, right?) and an error message that seems to appear somewhat randomly for the users that are accessing it.

I'm using Access '03 and know it fairly well, but I may be lacking in some of the fundamental areas.

When I created the database I placed it on a shared folder on our network for roughly 15 users to access when they needed to enter data. The problem I'm having is that sometimes, when the users are entering data, a debug dialog box appears, opens the VBA code, and highlights a line of code I've written for a submit (and save) command I created.

My question is, do you have to do something special to allow more than one user to use a database simultaneously with another user or is my code preventing one user from saving if another has accessed the database prior.

Here's the line of code that highlights when the error appears:

Private Sub cmdSubmit_Click()
    Dim subVal As Variant
    subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion)
    If subVal = vbYes Then
        DoCmd.Save acForm, Me.Name
        DoCmd.Close acForm, Me.Name
        MsgBox "Your form was saved", vbOKOnly
        DoCmd.OpenForm "Switchboard", acNormal
    ElseIf subVal = vbNo Then
        MsgBox "You will be returned to the form without saving", vbOKOnly
        DoCmd.CancelEvent
    End If
End Sub
 

The only thing extra I've done with this database is remove some of the options on the menu bar for the user to see and I hid the database navigation window. Other than that, I haven't done anything else, so if I was supposed to create an MDE (which I know nothing about) please let me know.

Thanks,
beacon

P.S. Thank heavens for a place like the scripts where people like me can come to find out how little they know, but actually get answers to the questions they ask to help them learn.

[Jim Doherty]

I'm having some trouble with my database (what's new, right?) and an error message that seems to appear somewhat randomly for the users that are accessing it.

I'm using Access '03 and know it fairly well, but I may be lacking in some of the fundamental areas.

When I created the database I placed it on a shared folder on our network for roughly 15 users to access when they needed to enter data. The problem I'm having is that sometimes, when the users are entering data, a debug dialog box appears, opens the VBA code, and highlights a line of code I've written for a submit (and save) command I created.

My question is, do you have to do something special to allow more than one user to use a database simultaneously with another user or is my code preventing one user from saving if another has accessed the database prior.

Here's the line of code that highlights when the error appears:

Private Sub cmdSubmit_Click()
Dim subVal As Variant
subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion)
If subVal = vbYes Then
 DoCmd.Save acForm, Me.Name
DoCmd.Close acForm, Me.Name
MsgBox "Your form was saved", vbOKOnly
DoCmd.OpenForm "Switchboard", acNormal
ElseIf subVal = vbNo Then
MsgBox "You will be returned to the form without saving", vbOKOnly
DoCmd.CancelEvent
End If
End Sub
 

The only thing extra I've done with this database is remove some of the options on the menu bar for the user to see and I hid the database navigation window. Other than that, I haven't done anything else, so if I was supposed to create an MDE (which I know nothing about) please let me know.

Thanks,
beacon

P.S. Thank heavens for a place like the scripts where people like me can come to find out how little they know, but actually get answers to the questions they ask to help them learn.

Hi Beacon,

Do you have a handle on what error handling means? you have nothing in there not even the lazy mans line (On Error Resume Next)

Your program is going to bomb out with perplexing regularity if you don't have something in place to at least capture the basic errors of which one it seems to me is the following:

Why are you trying to save the form itself? my guess is you mean to save a record Yes? if correct (and by reading the lines I can see it must be) you have issued the wrong command when you should be considering

DoCmd.RunCommand acCmdSaveRecord

Do you know what 'splitting' an access database means?

How big roughly is your database? are you aware that placing a database on a network share means the mdb file has to be dragged over the network before a user can do anything?
It is more efficient to have each user with their own copy of a frontend database residing on the client maybe in their profile if they logon to a domain or at least in a folder having a common name on each client pc on a common drive assignment. You then link tables from the server to each frontend file and ensure they are linked on opening of the database.

By the way I like your PS it is kind of endearing

Regards

Jim :)

[beacon]

Hi Jim,

I haven't gotten much into error handling because my coding in VBA isn't all that great, although it does seem to get better every time I'm tasked with a new project.

I really enjoy coding in VBA, but the book I have isn't very descript in the philosphy of how to code or the components that make up the data members for a particular function. I'm sure this is my problem with the line of code I wrote that was giving me the error message, but I used it anyway since it appeared to be the best example in my book.

I could be wrong on this, but I think our shared drive is a standalone server that is the ideal place for something like a database. All I know is that our IT department put there instead of on a public drive.

My intention was, in fact, just to save the record so I'll use the docmd.runcommand you suggested instead. Do you think I should create an error handle for this?

My main concern is just that I'm hoping to be able to have more than one user enter and save data into the database without it causing any problems. My users will enter the same information a dozen or more times if they get an error message like that and then they will call me and tell me every time...it's actually pretty funny after about the 5th time.

My db is 120 mb, by the way.

Anyway thanks for the help...

[Jim Doherty]

Hi Jim,

I haven't gotten much into error handling because my coding in VBA isn't all that great, although it does seem to get better every time I'm tasked with a new project.

I really enjoy coding in VBA, but the book I have isn't very descript in the philosphy of how to code or the components that make up the data members for a particular function. I'm sure this is my problem with the line of code I wrote that was giving me the error message, but I used it anyway since it appeared to be the best example in my book.

I could be wrong on this, but I think our shared drive is a standalone server that is the ideal place for something like a database. All I know is that our IT department put there instead of on a public drive.

My intention was, in fact, just to save the record so I'll use the docmd.runcommand you suggested instead. Do you think I should create an error handle for this?

My main concern is just that I'm hoping to be able to have more than one user enter and save data into the database without it causing any problems. My users will enter the same information a dozen or more times if they get an error message like that and then they will call me and tell me every time...it's actually pretty funny after about the 5th time.

My db is 120 mb, by the way.

Anyway thanks for the help...

Hi Beacon,

We all learn as we experience stuff :) no question. It always pays to implement error handling though because you have to anticipate where your program might go wrong for your own long term sanity more than anything else LOL.

For instance whilst you might issue a save command the command itself might well be valid but it might be not able to actually perform it for instance where your disk might be full.

Even in your own program you might have allowed them to save by virtue of a command button being present and not disabled it and they click it but haven't completed mandatory fields. In each of these cases errors are generated and if there is no error handling certainly in the case of an MDE file they will get thown straight out the system or with an MDB file they see the VBA code window which is the last thing you want.

As for your server the fact its on a server is better than nothing but the 'split' database is the way to go for various reasons ie if one users copy on their machine was not performing you could work on their copy and maintain and not have to kick everyone of the system in order to do that.

Regards

Jim :)

[ADezii]

I'm having some trouble with my database (what's new, right?) and an error message that seems to appear somewhat randomly for the users that are accessing it.

I'm using Access '03 and know it fairly well, but I may be lacking in some of the fundamental areas.

When I created the database I placed it on a shared folder on our network for roughly 15 users to access when they needed to enter data. The problem I'm having is that sometimes, when the users are entering data, a debug dialog box appears, opens the VBA code, and highlights a line of code I've written for a submit (and save) command I created.

My question is, do you have to do something special to allow more than one user to use a database simultaneously with another user or is my code preventing one user from saving if another has accessed the database prior.

Here's the line of code that highlights when the error appears:

Private Sub cmdSubmit_Click()
    Dim subVal As Variant
    subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion)
    If subVal = vbYes Then
        DoCmd.Save acForm, Me.Name
        DoCmd.Close acForm, Me.Name
        MsgBox "Your form was saved", vbOKOnly
        DoCmd.OpenForm "Switchboard", acNormal
    ElseIf subVal = vbNo Then
        MsgBox "You will be returned to the form without saving", vbOKOnly
        DoCmd.CancelEvent
    End If
End Sub
 

The only thing extra I've done with this database is remove some of the options on the menu bar for the user to see and I hid the database navigation window. Other than that, I haven't done anything else, so if I was supposed to create an MDE (which I know nothing about) please let me know.

Thanks,
beacon

P.S. Thank heavens for a place like the scripts where people like me can come to find out how little they know, but actually get answers to the questions they ask to help them learn.

I've made modifications to your code and also included basic Error Handling Functionality for you to see. Also, do not declare the Return Value from Msgbox() as a Variant, it should be Integer as indicated.

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
 
Dim subVal As Integer      'Not Variant
 
subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion)
 
  If subVal = vbYes Then
    'I'll leave the questionable line in place
    DoCmd.Save acForm, Me.Name
    DoCmd.Close acForm, Me.Name
    MsgBox "Your form was saved", vbOKOnly
    DoCmd.OpenForm "Switchboard", acNormal
  Else   'only 2 Options exist (if not Yes, must be No)
      MsgBox "You will be returned to the form without saving", vbOKOnly
      DoCmd.CancelEvent
  End If
 
Exit_cmdSubmit_Click:
    Exit Sub
 
Err_cmdSubmit_Click:
    MsgBox Err.Description, vbExclamation, "Error in cmdSubmit_Click()"
    Resume Exit_cmdSubmit_Click
End Sub