Combobox AfterUpdate to existing record

  • Thread starter Thread starter jlute
  • Start date Start date
J

jlute

I have a combo box that's used to navigate records either by making a
selection or mouse button scrolling it. The problem is that read-only
users can't use it as the AfterUpdate code is disabled for them.

What's an "easy" AfterUpdate code for comboxes that will allow all
users the ability to make selections or mouse scroll? I've looked all
over the forums but can't find anything.

Thanks!!!
 
I have a combo box that's used to navigate records either by making a
selection or mouse button scrolling it. The problem is that read-only
users can't use it as the AfterUpdate code is disabled for them.

What's an "easy" AfterUpdate code for comboxes that will allow all
users the ability to make selections or mouse scroll? I've looked all
over the forums but can't find anything.
Click to expand...


If the form's AllowEdits property is set to True, then the users will not be
able to make any selections in the combo box, and you won't be able to use
it for this purpose. Therefore, you must not set the form's AllowEdits
property to True. Instead, either lock all the data-bound controls (by
setting their Locked properties to True), or else force the form's
Recordsource query to be non-updatable; for example, by using the DISTINCT
keyword in its SQL statement.

How are you distinguishing the read-only users from updating users? I'm
guessing you have code in the form's Open or Load event that is currently
setting the AllowEdits property. You have to change this to take one of the
approaches I mentioned above.
 
Dirk Goldgar said:
If the form's AllowEdits property is set to True, then the users will not
be able to make any selections in the combo box, and you won't be able to
use it for this purpose. Therefore, you must not set the form's
AllowEdits property to True.
Click to expand...

<CORRECTION>
If the form's AllowEdits property is set to FALSE, then the users will not
be able to make any selections in the combo box, and you won't be able to
use it for this purpose. Therefore, you must not set the form's AllowEdits
property to FALSE.
</CORRECTION>

Sorry, John, I don't know where my mind was when I wrote that.
 
Thanks, Dirk! I got your correction below. The form's Allow Edits
property is set to "Yes".
How are you distinguishing the read-only users from updating users?  I'm
guessing you have code in the form's Open or Load event that is currently
setting the AllowEdits property.  You have to change this to take one of the
approaches I mentioned above.
Click to expand...

Users login with either their personal username/password which has
permissions assigned to them or a "generic" read-only username/
password.

This is the current AfterUpdate:

Private Sub cbProfileID_AfterUpdate()
Dim strNewProfile As String

With Me.cbProfileID

' Capture the profile ID the user has selected or entered.
strNewProfile = .Value & vbNullString

If Len(strNewProfile) = 0 Then
' We've no idea what the user has in mind, so
' leave this alone.
Exit Sub
End If

' Is the user's entry an existing profile ID?
If .ListIndex = -1 Then
' This is not an existing profile ID.
' If we aren't on a new record, undo this entry,
' go to a new record, and re-enter it there.
If Not Me.NewRecord Then
.Undo
Me.Undo
RunCommand acCmdRecordsGoToNew
.Value = strNewProfile
' Me.cbProfileID = strNewProfile
End If
Else
' This is an existing profile ID.
' Undo the entry on this record and
' go to the record that was entered.
.Undo
Me.Undo
Me.Recordset.FindFirst "txtProfileID=""" & strNewProfile &
""""
End If

End With

End Sub

I've tried several edits to this but none have permitted navigation to
an existing record when logged in as a read-only user.
 
<QUOTE>
Thanks, Dirk! I got your correction below. The form's Allow Edits
property is set to "Yes".
How are you distinguishing the read-only users from updating users? I'm
guessing you have code in the form's Open or Load event that is currently
setting the AllowEdits property. You have to change this to take one of
the
approaches I mentioned above.
Click to expand...

Users login with either their personal username/password which has
permissions assigned to them or a "generic" read-only username/
password.

This is the current AfterUpdate:

Private Sub cbProfileID_AfterUpdate()
Dim strNewProfile As String

With Me.cbProfileID

' Capture the profile ID the user has selected or entered.
strNewProfile = .Value & vbNullString

If Len(strNewProfile) = 0 Then
' We've no idea what the user has in mind, so
' leave this alone.
Exit Sub
End If

' Is the user's entry an existing profile ID?
If .ListIndex = -1 Then
' This is not an existing profile ID.
' If we aren't on a new record, undo this entry,
' go to a new record, and re-enter it there.
If Not Me.NewRecord Then
.Undo
Me.Undo
RunCommand acCmdRecordsGoToNew
.Value = strNewProfile
' Me.cbProfileID = strNewProfile
End If
Else
' This is an existing profile ID.
' Undo the entry on this record and
' go to the record that was entered.
.Undo
Me.Undo
Me.Recordset.FindFirst "txtProfileID=""" & strNewProfile &
""""
End If

End With

End Sub

I've tried several edits to this but none have permitted navigation to
an existing record when logged in as a read-only user.

</QUOTE>

Ahah! Now I see what's up. I didn't realize that you were using workgroup
security, but it comes down to the same issue as if you'd just set
AllowEdits to No -- except that you can't override the ULS by changing the
AllowEdits property.

The problem is that this not an unbound control. You've overloaded a bound
combo box so that it can be used both for data entry (on a new record) and
for navigation. But on a read-only form, you can't edit any bound controls.
Period. The overloaded control will not work for read-only users.

So what would be a good workaround? You *could* use a separate control for
navigation, and use just a plain text box for actual data entry of this
field. But that does take extra space on the form, and presumably your
users are accustomed to working with a control that behaves the way your
combo box currently does. So <Blackadder> I have a cunning plan
</Blackadder> .

My idea is to detect, when the form is loaded, whether it is read-only or
not. If it's read-only, *unbind* the combo box from its controlsource, and
then in the form's Current event, have code to assign the value from the
controlsource field to the combo box. Am I right in thinking that the
ControlSource for you combo box "cbProfileId" is a field named
"txtProfileID"? If that's so, use code like this:

'----- start of code -----
Private Sub Form_Load()

If Me.Recordset.Updatable = False Then
' Unbind the cbProfileID control so that it can be used
' for navigation.
Me!cbProfileID.ControlSource = ""
End If

End Sub

Private Sub Form_Current()

' If this form is not updatable, manually set
' cbProfileID to the value of the txtProfileID field.
If Me.Recordset.Updatable = False Then
Me!cbProfileID = Me.txtProfileID
End If

End Sub
'----- end of code -----

In addition to that, you'll also need to modify your cbProfileID_AfterUpdate
procedure to allow for the possibility that the user *can't* add another
record, because the form is not updatable.
 
Ahah!  Now I see what's up... <Blackadder> I have a cunning plan.</Blackadder>

As always!
My idea is to detect, when the form is loaded, whether it is read-only or
not.  If it's read-only, *unbind* the combo box from its controlsource,and
then in the form's Current event, have code to assign the value from the
controlsource field to the combo box.  Am I right  in thinking that the
ControlSource for you combo box "cbProfileId" is a field named
"txtProfileID"?  If that's so, use code like this:

'----- start of code -----
Private Sub Form_Load()

    If Me.Recordset.Updatable = False Then
        ' Unbind the cbProfileID control so that it can be used
        ' for navigation.
        Me!cbProfileID.ControlSource = ""
    End If

End Sub

Private Sub Form_Current()

    ' If this form is not updatable, manually set
    ' cbProfileID to the value of the txtProfileID field.
    If Me.Recordset.Updatable = False Then
        Me!cbProfileID = Me.txtProfileID
    End If

End Sub
'----- end of code -----
Click to expand...

That is beyond cunning! So simple AND so effective! I've plugged it in
and ran it up and down. No problems! Thank you!!!

My form already has some requery stuff in the Current. Is this going
to cause any problems? I haven't seen any but my "vision" isn't so
good!

Private Sub Form_Current()
Forms![Marzetti Main Menu]![frmQueriesReports].Form!
[cbSelectReport].Requery
Forms![Marzetti Main Menu]![frmQueriesReports].Form!
[cbSelectQuery].Requery
Forms![Marzetti Main Menu]![frmQueriesReports].Form!
[cbSelectReportGen].Requery
Forms![Marzetti Main Menu]![frmQueriesReports].Form!
[cbSelectForm].Requery
Forms![Marzetti Main Menu]![frmQueriesReports].Form!
[cbSelectReportRQ].Requery

' If this form is not updatable, manually set
' cbProfileID to the value of the txtProfileID field.
If Me.Recordset.Updatable = False Then
Me!cbProfileID = Me.txtProfileID
End If

End Sub
In addition to that, you'll also need to modify your cbProfileID_AfterUpdate
procedure to allow for the possibility that the user *can't* add another
record, because the form is not updatable.
Click to expand...

Without changing anything I logged in as read-only and could NOT add a
record no matter what I tried. Was there something specific about the
event that you thought needed changed?
 
I've plugged it in and ran it up and down. No problems!
Excellent!

My form already has some requery stuff in the Current. Is this going to
cause any problems? I haven't seen any but my "vision" isn't so good!
Click to expand...

No, I don't see any reason it would cause a problem.
Without changing anything I logged in as read-only and could NOT add a
record no matter what I tried. Was there something specific about the
event that you thought needed changed?
Click to expand...

It's this part here:
If Not Me.NewRecord Then
.Undo
Me.Undo
RunCommand acCmdRecordsGoToNew
.Value = strNewProfile
' Me.cbProfileID = strNewProfile
End If
Click to expand...

Suppose the user enters a value in the combo box that isn't in the list.
Since he isn't on a new record (he couldn't be, if he's a read-only user),
the code will attempt to move to a new record so as to add the new item:
RunCommand acCmdRecordsGoToNew
Click to expand...

I would expect that statement to raise an error; maybe error 2046, "The
command or action 'RecordsGoToNew' isn't available now." Maybe I'm
overlooking something, or maybe you are trapping that error and ignoring it.
Did you try that scenario?
 
It's this part here:
Suppose the user enters a value in the combo box that isn't in the list.
Since he isn't on a new record (he couldn't be, if he's a read-only user),
the code will attempt to move to a new record so as to add the new item:


I would expect that statement to raise an error;  maybe error 2046, "The
command or action 'RecordsGoToNew' isn't available now."  Maybe I'm
overlooking something, or maybe you are trapping that error and ignoring it.
Did you try that scenario?
Click to expand...

Drat! I *THOUGHT* I tried every thing but when I went back to double
check by entering a new number then 2046 did indeed pop up. Makes
sense, too. It was this RunCommand that I was trying to edit
previously.

You're going to make a programmer out of me yet. I had no idea about
trapping (other than small, helpless animals) so I did a little
research. The best I can concoct is:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
HandleErr:
Select Case Err.Number
Case 2046 'The command or action 'RunCommand' isn't available now.
Resume ExitHere
Case Else
MsgBox "Unexpected Error " & Err.Number & ": " &
Err.Description, vbCritical, , "Error..."
Resume ExitHere
End Select

End Sub

This isn't working. The error still generates and the debugger points
to the AfterUpdate RunCommand.

Could yo ube so kind as to give me a hint about the On Error?

Thanks, Sir Dirkus Goldgarius.
 
You're going to make a programmer out of me yet.
Click to expand...

My nefarious design is revealed.
I had no idea about trapping (other than small, helpless animals) so I did
a little research. The best I can concoct is:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
HandleErr:
Select Case Err.Number
Case 2046 'The command or action 'RunCommand' isn't available now.
Resume ExitHere
Case Else
MsgBox "Unexpected Error " & Err.Number & ": " &
Err.Description, vbCritical, , "Error..."
Resume ExitHere
End Select

End Sub

This isn't working. The error still generates and the debugger points to
the AfterUpdate RunCommand.

Could yo ube so kind as to give me a hint about the On Error?

Thanks, Sir Dirkus Goldgarius.
Click to expand...

<SNORK!>

The body of that event procedure is good, but it's in the wrong place. The
form's Error event is only raised when the data management code that
*Access* is running behind the scenes (to manage the bound form) has an
error. Essentially, Access traps that error (rather the way your code
attempts to do) and then raises the Error event to let you decide what to do
with it. However, the Error event is *not* raise when *your* code causes an
error. Instead, it's up to you to put error-handling logic in your code to
deal with any error your code raises.

So, as I said, your code is misplaced, but would be close to correct if it
were placed in the cbProfileID_AfterUpdate event procedure. You would still
need to activate the error-handling with a suitable "On Error GoTo"
statement, and I think in practice you may want to react to the error in
some way, so try this version of cbProfileID_AfterUpdate:

'---- start of code -----
Private Sub cbProfileID_AfterUpdate()

On Error GoTo HandleErr

Dim strNewProfile As String

With Me.cbProfileID

' Capture the profile ID the user has selected or entered.
strNewProfile = .Value & vbNullString

If Len(strNewProfile) = 0 Then
' We've no idea what the user has in mind, so
' leave this alone.
Exit Sub
End If

' Is the user's entry an existing profile ID?
If .ListIndex = -1 Then
' This is not an existing profile ID.
' If we aren't on a new record, undo this entry,
' go to a new record, and re-enter it there.
If Not Me.NewRecord Then
.Undo
Me.Undo
RunCommand acCmdRecordsGoToNew
.Value = strNewProfile
End If
Else
' This is an existing profile ID.
' Undo the entry on this record and
' go to the record that was entered.
.Undo
Me.Undo
Me.Recordset.FindFirst "txtProfileID=""" & strNewProfile & """"
End If

End With

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case 2046
' "The command or action 'RecordsGoToNew' isn't available now."
' This means we're in read-only mode, and the user has tried to
' enter a new ProfileID.
'*** Uncomment the following statement if you want to
'*** "beep" without a message.
' DoCmd.Beep
'*** Uncomment the following statement if you want to
'*** display a message (which will include a beep).
' MsgBox _
' "Sorry, you don't have permission to add new Profiles.", _
' vbInformation, _
' "New Profile Entered"
Resume ExitHere
Case Else
MsgBox _
"Unexpected Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error..."
Resume ExitHere
End Select
'---- end of code -----

End Sub
'---- end of code -----

Note the commented-out alternatives in the error-handler.
 
The body of that event procedure is good, but it's in the wrong place.  The
form's Error event is only raised when the data management code that
*Access* is running behind the scenes (to manage the bound form) has an
error.  Essentially, Access traps that error (rather the way your code
attempts to do) and then raises the Error event to let you decide what todo
with it.  However, the Error event is *not* raise when *your* code causes an
error.  Instead, it's up to you to put error-handling logic in your code to
deal with any error your code raises.
Click to expand...

I'm not yet a programmer but I gotcha.
So, as I said, your code is misplaced, but would be close to correct if it
were placed in the cbProfileID_AfterUpdate event procedure.  You would still
need to activate the error-handling with a suitable "On Error GoTo"
statement, and I think in practice you may want to react to the error in
some way, so try this version of cbProfileID_AfterUpdate:

'---- start of code -----
Private Sub cbProfileID_AfterUpdate()

    On Error GoTo HandleErr

    Dim strNewProfile As String

    With Me.cbProfileID

        ' Capture the profile ID the user has selected or entered..
        strNewProfile = .Value & vbNullString

        If Len(strNewProfile) = 0 Then
            ' We've no idea what the user has in mind, so
            ' leave this alone.
            Exit Sub
        End If

        ' Is the user's entry an existing profile ID?
        If .ListIndex = -1 Then
            ' This is not an existing profile ID.
            ' If we aren't on a new record, undo this entry,
            ' go to a new record, and re-enter it there.
            If Not Me.NewRecord Then
                .Undo
                Me.Undo
                RunCommand acCmdRecordsGoToNew
                .Value = strNewProfile
            End If
        Else
            ' This is an existing profile ID.
            ' Undo the entry on this record and
            ' go to the record that was entered.
            .Undo
            Me.Undo
            Me.Recordset.FindFirst "txtProfileID=""" & strNewProfile & """"
        End If

    End With

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case 2046
            ' "The command or action 'RecordsGoToNew' isn't available now."
            ' This means we're in read-only mode, and the user has tried to
            ' enter a new ProfileID.
            '*** Uncomment the following statement if you want to
            '*** "beep" without a message.
            ' DoCmd.Beep
            '*** Uncomment the following statement if you want to
            '*** display a message (which will include a beep).
            ' MsgBox _
            '    "Sorry, you don't have permission to addnew Profiles.", _
            '    vbInformation, _
            '    "New Profile Entered"
            Resume ExitHere
    Case Else
        MsgBox _
            "Unexpected Error " & Err.Number & ": " & Err.Description, _
            vbCritical, "Error..."
        Resume ExitHere
    End Select
'---- end of code -----

End Sub
'---- end of code -----

Note the commented-out alternatives in the error-handler.
Click to expand...

I un-commented the MsgBox as it will be valuable in clarifying the
user's no-no. This works a charm, Dirk! Thank VERY much! I don't know
that I'm a programmer yet but your nefarious plan is transforming me
nonetheless! I've learned A LOT here and I truly appreciate your
knowledge!!!
 
Back
Top