Ok, I screwed up a long time ago, now what

[Autoeng]

A long time ago when I was first learning Access I named one of my fields "Part #". Now I need to refer to this field in a module but it doesn't like the # symbol a part of the name. This field is in numerous modules, reports and queries.

Do I have any option that is good versus the one I think is the only one (i.e. hunt down all references after changing the field name)?

 

[boblarson]

The modules are an easy fix as you can use the Replace feature. Going into the queries and reports are more problematic. There may be some tools out there to do that, but I don't know of any at the moment.

Good luck on this, I know it's not fun.


EDITED:
I found this http://www.acc-technology.com/rnwiz.htm ($34.95)

 

[pbaldy]

I use Speed Ferret, which costs, but I've heard very good things about this freeware:

http://www.skrol29.com/dev/en_vtools.htm

 

[Autoeng]

This field happens to be on a form which is tabbed to another and I don't want one user to have access to any of the fields on the form. Here is what I tried.

Private Sub Form_Open(Cancel As Integer)
Dim UserLoggedIn As String

UserLoggedIn = CurrentUser()

If UserLoggedIn = "username" Then
Part_#.Enabled = False
Rev_Lev.Enabled = False
Part_Description.Enabled = False
Replaces.Enabled = False
Resp.Enabled = False
Plnr.Enabled = False
Disp.Enabled = False
EARS.Enabled = False
Comments.Enabled = False

End If

End Sub

Is there a way to call disable for the form for current user? That would solve all of my problems!

 

[grnzbra]

You might want to find the last place that [Part #] works and change that query to

Select ... [Part #] as PartNo,...

and go from there. Changing everything with one of the utilities might ultimately be the proper way to go, I think this will work for a cheap&dirty gotta get it done now solution.

 

[Autoeng]

So no way to to call disable for the form for current user? That would solve all of my problems!

 

[ChrisO]

Not so easy…

Behind a Form: -

Option Explicit
Option Compare Text


Private Sub Form_Open(ByRef intCancel As Integer)

    [color=green]' Disable this Form for these users.[/color]
    If FormIsEnabled(Me, "Admin", "Mary Jo Snodgrass the III") Then
        MsgBox "Enabled"
    Else
        MsgBox "Disabled"
    End If

End Sub

In a standard Module: -

Option Explicit
Option Compare Text


Public Function FormIsEnabled(ByRef frmThisForm As Form, _
                         ParamArray vntUsers() As Variant) As Boolean
    
    Dim blnDisabled    As Boolean
    Dim lngElement     As Long
    Dim strCurrentUser As String
    Dim ctl            As Control
    
    FormIsEnabled = True
    strCurrentUser = CurrentUser()

    frmThisForm.cmdDummySetFocus.SetFocus
    frmThisForm.cmdDummySetFocus.Transparent = True
    
    On Error Resume Next
    
    For Each ctl In frmThisForm
        If ctl.Name <> "cmdDummySetFocus" Then
            For lngElement = LBound(vntUsers) To UBound(vntUsers)
                blnDisabled = strCurrentUser = vntUsers(lngElement)
                If (blnDisabled) Then
                    [color=green]' Stop the Form Timer[/color]
                    frmThisForm.TimerInterval = 0
                    [color=green]' Disable the Control[/color]
                    ctl.Enabled = False
                    [color=green]' Remove the Control Tip Text[/color]
                    ctl.ControlTipText = ""
                    [color=green]' Clear the Event Handlers[/color]
                    ctl.OnMouseMove = ""
                    [color=green]' -----------
                    ' Maybe
                    ' more
                    ' events
                    ' go
                    ' here
                    ' -----------
                    ' Flag the Form as Disabled[/color]
                    FormIsEnabled = False
                End If
            Next lngElement
        End If
    Next ctl
    
    Err.Clear
    
End Function

Limited testing only but hope that helps.

Regards,
Chris.

 

[gemma-the-husky]

AutoEng - at the top of your post you say you called a field

part # - with a space

later you are using

part_# - with a underscore

these are not the same. If you have embedded spaces in a field name you HAVE to surround the name with sq brackets to use it, so you now need to use

[part #] to address it properly. If # was a prohibited character, Access wouldn't have let you use it in the first place, so there should not be any problem.

 

[pbaldy]

"#" is on the list of reserved words and symbols that should not be used in object names. Like most others, you can often get away with it, but it should still be avoided.