Display and Empower Multiple Subforms

[mestst64]

Combo boxes cboTN and cboSB are on my main form. cboTN is a search combo box that updates combo box cboSB with one of 3 values (ID, IDC, or MC).

I would like to do the following:

1:
If the after-update value of cboSB is ID, subform sf1 will be visible.
If the after-update value of cboSB is IDC, subform sf2 will be visible.
If the after-update value of cboSB is MC, subform sf3 will be visible.
(only one subform visible at a time since they are stacked in one place on my main form)

2:
Once the appropriate subform is displayed, is it possible to use option buttons to set the property of the subform to either Add-only mode or Edit-only mode? If so, how? (perhaps some sort of Case A, Case B scenario with the option buttons?)

If you can help with either of the above; example codes would help my novice skills greatly!

 

[ansentry]

To do this;

1:
If the after-update value of cboSB is ID, subform sf1 will be visible.
If the after-update value of cboSB is IDC, subform sf2 will be visible.
If the after-update value of cboSB is MC, subform sf3 will be visible.
(only one subform visible at a time since they are stacked in one place on my main form)

In the afterupdate of cboSB
Use select case to display or hide you subforms.

 

[mestst64]

Thanks very much John - I will try this (had used this with option buttons in another section so it sounds like a good idea!).

Any advice on how/if I can tell each subform to function in either "add" or "edit" mode?

 

[ansentry]

Any advice on how/if I can tell each subform to function in either "add" or "edit" mode?

Have a look at the attached sample.I have used a toggle button on the main form.

 

[mestst64]

John,

Thanks again! Having the form open in one state and using the button to switch to another may cut my option group in half (great!).

I'm new to code; how does AllowAdditions compare with acFormAdd. I was wondering if I could use acFormAdd and acFormEdit as my controls, but would that not work, or do you know another solution? What I'm thinking of now, would be to have the form open in either add or edit mode, and change it to the other with only one button (instead of two) for each form.

Thanks very much again, and let me know if you have further advice on this--

 

[ansentry]

You ask;

how does AllowAdditions compare with acFormAdd

I have only ever used the acFormOpenDataMode like this;

 DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly

or you could have:

DoCmd.OpenForm stDocName, , , stLinkCriteria, 2

What I'm thinking of now, would be to have the form open in either add or edit mode, and change it to the other with only one button (instead of two) for each form.

The sample that I send you does that using this code in On Open event of the subform.

' Lock all text boxes on form
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        ctl.Locked = True
    End If
Next ctl
Set ctl = Nothing
Me.AllowAdditions = False

 

[mestst64]

John,

Yes, but your button allows both add and edit, and it's very important for my database that only one option is available at a time. Am still going to work on this however, and in the meantime, if you have any further suggestions, please post. Thanks!

 

[ansentry]

Yes, but your button allows both add and edit, and it's very important for my database that only one option is available at a time.

So I understand what you want;

(1) When the main form opens you want the subform to show all record BUT the records should be locked (NO Edits) and the user should not be able to add records (Allow Additions = False).

(2) When the user clicks the button you want the reverse of above?

When they go to a new record on the form you want (1) to activate,
correct?

Solution:

Replace the toggle with a command button. When you do this you will have to make changes to code and it location.

If this is what you want to do then post back and I will edit the sample I sent you to reflect the changes.

 

[mestst64]

John,

Refering to your first reply in this string,

1) First, I want only one of the three subforms to become visible based on selection in combo box.

2) I want that subform to be in either add-only or edit-only mode when it opens (with all records shown).

3) I want to allow the viewer to be able to change that mode (to whatever's opposite of how the subform opens - add, or edit, mode) if they desire, by clicking a command button.

Hope this clarifies, am sorry for my ignorance, but I really want to tackle this. Thanks for your continued help!---

 

[mestst64]

If / Then Error Message

I have the following code in the After Update event of my combo box and the On Current event of my main form. If I try to open the main form, I get an error message that reads: "Compile error, Block If without End If" (it also highlights the End Sub in blue). If I take the code out of the main form, I still get the same message after an update to my combo box. What have I done wrong?

Private Sub cboresponsetype_AfterUpdate()
If Me.cboresponsetype = "A" Then
Me.sbfAAdd.Visible = True
Me.sbfBAdd.Visible = False
Me.sbfCAdd.Visible = False
Else
If Me.cboresponsetype = "B" Then
Me.sbfAAdd.Visible = False
Me.sbfBAdd.Visible = True
Me.sbfCAdd.Visible = False
Else
If Me.cboresponsetype = "C" Then
Me.sbfAAdd.Visible = False
Me.sbfBAdd.Visible = False
Me.sbfCAdd.Visible = True
End If
End Sub

 

[clerics]

Let try to use this friend !

Select Case Me.cboresponsetype 
        Case "A"
            Me.sbfAAdd.Visible = True
            Me.sbfBAdd.Visible = False
            Me.sbfCAdd.Visible = False
        Case "B"
            Me.sbfAAdd.Visible = False
            Me.sbfBAdd.Visible = True
            Me.sbfCAdd.Visible = False
        Case "C"
            Me.sbfAAdd.Visible = False
            Me.sbfBAdd.Visible = False
            Me.sbfCAdd.Visible = True        
End Select