How To Seperate Offending Code...

[Sabotage1945]

Hi everyone,

I'm having problems figuring out what to do...

Background:

I've got a form called 'frmDirectory' which has a directory of students, I would like to link this with my 'frmStudents' where I can then display and edit the fields. FYI: The 'frmStudents' is using a query called 'qryStudentVisit' (a combination of 3 tables). I can add/edit the contents in my 'frmStudents' form normally.

So far 'frmDirectory' is working well, except I would like to press a button to "edit" the entry in the 'frmDirectory' and be taken to the 'frmStudents'. The code I have (and it seems to be failing me) is:

Private Sub cmdStudentForm_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmStudents"

stLinkCriteria = "[PersonID]=" & Me![PersonID]
[COLOR=Orange][B]DoCmd.OpenForm stDocName, , , stLinkCriteria[/B][/COLOR]

End Sub

This last 'orange' part is what VBA is complaining about. I don't understand why, and no one can seem to help me

The error I get in the "MS Visual Basic" dialog is:

Run-time error '3008':

The table 'tblStudents' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

[End], [Debug], [Help]

I'm assuming the [PersonID] I'm referrencing here is for each of the forms. I have a PersonID in frmDirectory and also a PersonID in the frmStudents. Could it be that I'm referencing something that I'm not supposed to in the 'frmStudents' form? FYI: I have the following in that form (its working fine inside that form)...

Control Name: txtPersonID
Control Source: PersonID​

In the VBA code I've tried:

stLinkCriteria = "[PersonID]=" & Me![PersonID]

as well as...

stLinkCriteria = "[txtPersonID]=" & Me![PersonID]

In my 'frmDirectory' I have [PersonID] as the item listed, and which I'm trying to link with in 'frmStudents'.
That didn't work either. I'm not sure which I should reference to get this to work.

The [PersonID] is a number (Long Integer) field stored in my 'tblStudents', and is also the Primary Key. I'm calling everything in the forms through a query vs the table.

Could the call I'm using to open the 'frmStudents' form to the appropriate [PersonID] be the wrong type of VBA code? I'm so new at this I'm not sure what I'm doing. All I want to do is have a command button open the coresponding student form based on his/her [PersonID].

When I Google'd this error I got this info, which says that this problem is by design:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;197952

"The key is to separate the offending code from the form, put it into it's own module, and call the module..."

I don't think I understand this last part - how do I go about doing that? Can anyone help me?

Cheers,
Sab.

 

[modest]

The table 'tblStudents' is already opened exclusively by another user

I think the error message pretty much says it all. It looks like you're opening a form that may already be opened. It'd be like having a Pringles container. You tell someone to pop the lid off, so the person does. Then you tell him to do it again, but the lid is already off. How can you pop the lid off something that doesn't have a lid? Can't do it! Error.

So you might want to put in a check to see if the form is open. Or you might want to just make sure it's closed before you open it.

 

[Sabotage1945]

I think the error message pretty much says it all. It looks like you're opening a form that may already be opened... Can't do it! Error.

So you might want to put in a check to see if the form is open. Or you might want to just make sure it's closed before you open it.

Thanks for the reply!

Yes, I see your point... but I have not opened anything up. I've tried to check this by closing the entire Access application, then open the .MDB file in question - and directly open up the 'frmDirectory' file. Not even the 'tblStudents'... I get the same error.

Now, the 'qryStudentVisit' file does link to the tblStudents file - could this be the reason? How can I achieve to access the query when it relates to the 'tblStudents'?

Thanks,
Sab.

 

[The_Doc_Man]

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmStudents"

stLinkCriteria = "[PersonID]=" & Me![PersonID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

By any chance is this event routine running from frmStudents? Which is also the form you are trying to open? You have to remember that Windows (therefore Access) gives you LOTS of ways to open things. Like click or double-click on an object.

 

[Sabotage1945]

By any chance is this event routine running from frmStudents? Which is also the form you are trying to open? You have to remember that Windows (therefore Access) gives you LOTS of ways to open things. Like click or double-click on an object.

It's a routine that is running from 'frmDirectory', which is supposed to open the 'frmStudents'.

FYI: The 'frmStudents' is using a query called 'qryStudentVisit' (a combination of 3 tables). I can add/edit the contents in my 'frmStudents' form normally.

I've also tried to create a new table, and new query to call on... this works, but the table is not the 'tblStudents' which I'd like to link to. So I don't think its the code - but a design / implementation issue.

Any ideas? Anyone can help me out?

Thanks a million,
Sab.

 

[Bat17]

The code looks ok and I have not been able to recreate the problem.

Have you checked the properties for frmDirectory to make sure that the Recordset Type is Dynaset and not Snapshot? You should also check in the queries behind frmDirectory to make sure that they have not been set to Snapshot

HTH

Peter