Select Case fails to fire with Data Users

[johnlute]

Win2000 and Access 2003

I recently fixed the clutter of my form designs by going to a single
subform control and a tab control. The subform has a null source
object value which then gets loaded via the tab control's OnChange
Event:

Private Sub TabCtl_Change()
If Me.Dirty Then Me.Dirty = False
Select Case Me.TabCtl
Case 0
Me.sfrmCtl.Visible = False
Case 1
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmLocationsLocationIDs"
Me.sfrmCtl.LinkMasterFields = "txtLocationID"
Me.sfrmCtl.LinkChildFields = "txtLocationID"
Case 2
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmFacilitiesLocsContactIDs"
Me.sfrmCtl.LinkMasterFields = "txtLocationID"
Me.sfrmCtl.LinkChildFields = "txtLocationID"
Case 3
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmFacilitiesProfiles"
Me.sfrmCtl.LinkMasterFields = "numLocationAddressID"
Me.sfrmCtl.LinkChildFields = "numLocationAddressID"
Case 4
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmLocationsWebsites"
Me.sfrmCtl.LinkMasterFields = "txtLocationID"
Me.sfrmCtl.LinkChildFields = "txtLocationID"
Case 5
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmLocationsAttachments"
Me.sfrmCtl.LinkMasterFields = "txtLocationID"
Me.sfrmCtl.LinkChildFields = "txtLocationID"
End Select

End Sub

This works fine as long as the user has write permissions to the
database design. For all other users the code fails to fire.

Does anyone have any ideas how to resolve this?

Thanks!

 

[Stefan Hoffmann]

hi John,

This works fine as long as the user has write permissions to the
database design. For all other users the code fails to fire.

Can you define "fails to fire"?

Does anyone have any ideas how to resolve this?

Imho you need to set the LinkChildFields before LinkMasterFields.


mfG
--> stefan <--

 

[johnlute]

Thanks for the reply, Stefan.

Can you define "fails to fire"?

The subform displays as a blank, white, rectangle. The proper subform
isn't loaded.

Imho you need to set the LinkChildFields before LinkMasterFields.

I'll check into that - it may be part of the problem however it's
*got* to be primarily because the user hasn't any write permissions to
the design. When I log into the database then it behaves. When a data
user logs in then it decides to do otherwise.

 

[Stefan Hoffmann]

hi John,

I'll check into that - it may be part of the problem however it's
*got* to be primarily because the user hasn't any write permissions to
the design. When I log into the database then it behaves. When a data
user logs in then it decides to do otherwise.

No, this should not be the problem.

I've used this function in *.mde's without problem:

Public Sub FormSetSubForm(ASubForm As Access.SubForm, _
ASourceObject As String, _
ALinkChildFields As String, _
ALinkMasterFields As String, _
Optional ALinkMasterChild As Boolean = True)

On Local Error GoTo LocalError

If ASubForm.SourceObject <> ASourceObject Then _
ASubForm.SourceObject = ASourceObject

If ALinkMasterChild Then
If ASubForm.LinkChildFields <> ALinkChildFields Then _
ASubForm.LinkChildFields = ALinkChildFields
If ASubForm.LinkMasterFields <> ALinkMasterFields Then _
ASubForm.LinkMasterFields = ALinkMasterFields
Else
If ASubForm.LinkChildFields <> "" Then _
ASubForm.LinkChildFields = ""
If ASubForm.LinkMasterFields <> "" Then _
ASubForm.LinkMasterFields = ""
End If

Exit Sub

LocalError:
FormErrMsgBox "Fehler beim Zuweisen des Unterformulars '" & _
ASubForm.Name & "'."

End Sub

mfG
--> stefan <--

 

[johnlute]

Stefan,

Imho you need to set the LinkChildFields before LinkMasterFields.

I gave that a quick check and set the Child first - yikes. It
completely frustrated things and the subform wouldn't load properly at
all.

I have to take this out of the equation.

 

[johnlute]

Stefan,

No, this should not be the problem.

Maybe not but all things seem to point to it! When I log into the
database as Admin then the OnChange Event fires properly. When a data
user (who has no permissions to design) logs into the database then
the OnChange Event doesn't fire properly. This is what's happening and
if it's not a permissions issue then I'm completely lost as to why it
behaves like this.

I've used this function in *.mde's without problem:

My database is a .mdb and I'm not sure that what you've posted will
address the problem. I'll give it a try and let you know.

Thanks!

 

[Stefan Hoffmann]

hi John,

Maybe not but all things seem to point to it! When I log into the
database as Admin then the OnChange Event fires properly. When a data
user (who has no permissions to design) logs into the database then
the OnChange Event doesn't fire properly. This is what's happening and
if it's not a permissions issue then I'm completely lost as to why it
behaves like this.

Check the permissions of your data user...


mfG
--> stefan <--

 

[johnlute]

Stefan,

Check the permissions of your data user...

The user is a full data permissions user with no permissions to design.

 

[Stefan Hoffmann]

hi John,

The user is a full data permissions user with no permissions to design.

Okay, it's weird. Maybe your mdb file is corrupted. Try importing all
objects into a new database.


mfG
--> stefan <--

 

[johnlute]

Stefan,

Okay, it's weird.
Agreed!

Maybe your mdb file is corrupted. Try importing all
objects into a new database.

Actually, I just did that prior to this problem! I also just logged
into the database as a data user and on my PC and I'm having the EXACT
problems that they're having.

I don't think this is corruption, either. It's too consistent and
repeatable of a problem. From PC to PC the problem persists EXCEPT for
me as I'm an Admin with full permissions to everything.

I'm ready to throw in the towel...

 

[Stefan Hoffmann]

hi John,

I'm ready to throw in the towel...

Just an idea: Is there any form timer running which may interrupt the
event handling?

Another one: Search for CancelEvent...


mfG
--> stefan <--

 

[johnlute]

Hi, Stefan.

Just an idea: Is there any form timer running which may interrupt the
event handling?

That didn't cross my mind as I rarely use those and didn't recall
using any on these forms. I double checked and my memory was true.
None.

Another one: Search for CancelEvent...

I don't have any of those in the forms, either.

Here goes the towel...