Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit") (1 Viewer)

[pitt_ph]

Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Hey all,

I'm not sure if this is a feasible possibility, but is it possible to change the value of a control on a form (i.e., a combobox) depending on what mode a form is opened to?

The combobox that I have right now is bound to a field in a table, called "FormID". I'd ideally like to have a combobox either:

A) be able to add a FormID into its bound table only if the form is open in "Add" mode.

OR

B) be able to search for a particular record, by FormID, only if the form is open in "Edit" mode.

I saw this code on entries related to using a combo box to search for a particular ID - but it appears that for this code to work, the combo box MUST be unbound:

On Error GoTo myError Dim rst As DAO.Recordset Set rst = Me.RecordsetClone rst.FindFirst "ID = " & Me!ctlSearch Me.Bookmark = rst.Bookmark leave: Me!ctlSearch = Null If Not rst Is Nothing Then Set rst = Nothing Exit Sub myError: MsgBox "Record Not Found" Resume leave

Meanwhile, I saw this code in the Microsoft Access EditMode Property Help, which appears to change how the edit mode operates under different editing states:

Sub EditModeX()     Dim dbsNorthwind As Database    Dim rstEmployees As Recordset     Set dbsNorthwind = OpenDatabase("Northwind.mdb")    Set rstEmployees = _       dbsNorthwind.OpenRecordset("Employees", _       dbOpenDynaset)     ' Show the EditMode property under different editing     ' states.    With rstEmployees       EditModeOutput "Before any Edit or AddNew:", .EditMode       .Edit       EditModeOutput "After Edit:", .EditMode       .Update       EditModeOutput "After Update:", .EditMode       .AddNew       EditModeOutput "After AddNew:", .EditMode       .CancelUpdate       EditModeOutput "After CancelUpdate:", .EditMode       .Close    End With     dbsNorthwind.Close  End Sub  Function EditModeOutput(strTemp As String, _    intEditMode As Integer)     ' Print report based on the value of the EditMode     ' property.    Debug.Print strTemp    Debug.Print "  EditMode = ";     Select Case intEditMode       Case dbEditNone          Debug.Print "dbEditNone"       Case dbEditInProgress          Debug.Print "dbEditInProgress"       Case dbEditAdd          Debug.Print "dbEditAdd"    End Select  End Function

Thank you in advance for your feedback!

 

[Pat Hartman]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Add a separate combo that is used ONLY for searching. Make it unbound. For the bound combo, you can lock it when the form is in edit mode.

In the form's Current event:

If Me.NewRecord Then
    Me.SomeCombo.Locked = False
Else
    Me.SomeCombo.Locked = True
End If

Using a singe combo both for data entry and for searching is just too confusing and dangerous.

 

[pitt_ph]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Hiya, Pat! Thanks for the feedback! Believe it or not, your suggestion is what I eventually ended up doing, although I didn't lock the control -- I enabled/disabled the control, because the 'ghosting' effect is much easier (visually) to tell at a glance if a control is viable or not.

Unfortunately, I was having trouble trying to set up the unbound combobox using the code that I described in my first post. I tried manipulating the RowSource property of the control so that it would be an unbound control, but cloning the dataset to be used, with regards to FormID. However, after a certain point, I got errors (and unfortunately, I'll have to wait until I'm at a work computer to share those errors in detail.)

In general, I'm not sure what type of structure the VBA should have to allow an unbound control to act as a filter, using the recordsetclone property, but I can certainly tell you that the code I placed before probably wasn't clean/elegant.

Any thoughts?

 

[Pat Hartman]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

When you add an unbound combo to a bound form, the wizard will ask what you want to do with it and "find a record" is one of the options so the wizard will build the find for you. It builds it as an embedded macro which you can then convert to code.

 

[pitt_ph]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Hey Pat, I had one question about the code you previously wrote. You wrote:

In the form's Current event:

If Me.NewRecord Then
    Me.SomeCombo.Locked = False
Else
    Me.SomeCombo.Locked = True
End If

Can this type of thinking be applied to a button as well? That is, depending on if a new record is being entered into the form or not, can you tell a *single* button to open up another form in edit mode or in add mode?

 

[pitt_ph]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Can this type of thinking be applied to a button as well? That is, depending on if a new record is being entered into the form or not, can you tell a *single* button to open up another form in edit mode or in add mode?

I tried to work on something that was along these lines. Essentially, I have enabled a form to be open in either Add or Edit mode. Depending on the mode that the form is open in, I'd like for a single button to route the user back to the appropriate switchboard.

At first, I tried to go with "Me.NewRecord", but I wasn't having much luck with that (although it seems that would be the better option). I then tried "Me.AllowEdits", but I'm not sure if that is specifically helpful.

It appears that for the VBA below, only the first form (the data entry switchboard) opens, but the second form (the data editing switchboard) cannot open if new records are not allowed, or if edits are explicitly defined for the form. (For what it's worth, Command221_Click() is a button unbound to a particular field.)

Private Sub Command221_Click()

If Me.AllowEdits = True Then
    DoCmd.OpenForm "data_ENTRY_switchboard", , , , acFormReadOnly
    Close acForm
Else
    DoCmd.OpenForm "data_EDITING_switchboard", , , , acFormReadOnly
    Close acForm
End If

End Sub

Any thoughts? Thanks in advance!

 

[Pat Hartman]

Re: Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit")

Before you add event code to a control, it is best to give it a proper name. That way you don't end up with objects named command221. You can recover by renaming the control but this will orphan the code so you'll need to cut it and paste it into the new spot.

I use a simple scheme for navigating back to the calling form. Each time I open a form, I pass the name of the calling form in the OpenArgs and then reference that to go back. Use something like this in your "back" event

DoCmd.OpenForm frm.OpenArgs
DoCmd.Close acForm, Me.Name

Use something like this in your "goto" event

Private Sub PolNo_Click()
    DoCmd.OpenForm "frmPolicy", acNormal, , "PolID = " & Me.PolID, acFormEdit, , Me.Parent.Name
    Me.Parent.Visible = False
End Sub

In the preceeding example, the "goto" occurs in a subform so it is necessary to pass the name of the parent form. If you are going from the main form, use Me.Name rather than Me.Parent.Name.