Form design with comboboxes and the Data Entry property

[kdirvin]

Hello!

I have a main form based on a “Projects” table and a subform based on the related “Funding Documents” table. The main form has three comboboxes: Year, Management Office, and Project Number. Values displayed in the Project Number combobox are limited by what the user selects in the Year and Management Office comboboxes (I set the Row Source property of the “Project Number” combobox to a query of the Projects table and set criteria as the “Fiscal Year” and “Management Office” comboboxes)

I created the form so that selecting a value from the “Project Number” combobox populates the subform with records from the related “Funding Documents” table. Since the main form’s “Data Entry” property is set to “No,” the form opens populated with the first record. Because of this, when the user starts selecting new information in the comboboxes, it changes the data in the underlying Projects table.

I really need a form that (1) opens blank, (2) does not change data in the underlying table with the selection of values in the comboboxes, and (3) allows the user to display records in the subform based on the selection from the Project Number combobox. I am not sure what I want is possible, but any help is GREATLY appreciated.

Thanks!!

 

[RuralGuy]

It sounds like you are going to want unbound ComboBoxes. There may be other issues but we can tackle them one at a time.

 

[vbaInet]

For (1) - you can do this:

DoCmd.OpenForm "NameOfForm",,,, acFormAdd

That will open up your form in Add mode.

 

[kdirvin]

Thank you all so much for your help. Changing the comboboxes to unbound solved the problem of combobox selections modifying data in the underlying table. I also placed a "GoToRecord New" macro in the "On Load" property of the main form, which opens it blank.

I have one follow-up question for this form. I created a "Save" button to save the current record and display a new one. I want this button to clear all controls on my form, but at present it only clears the bound controls (in the subform) and not my unbound comboboxes (in the main form).

I found this code for clearing all unbound controls on a form:

Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox ) Then
If (Len(Trim$(ctl.ControlSource & vbNullString)) = 0) Then
ctl.Value = Null
End If
End If
Next ctl

Is the correct code for clearing all unbound controls on a form? If so, where would I place this in the "Save" button's code...I don't want to mess with the macro that saves the record and navigates to a new one.

Thank you again!!

 

[CBrighton]

If the button and the controls to be cleared are on the same form then add it to the current save button's code.

The code is mostly right, but it seems to only set the value to null if the combobox is a ZLS.

I expect the = should be changed to > in your code, meaning if there is one or more character it is replaced with null.

:edit:

Or change the If to an If Not:

If [B]Not[/B] (Len(Trim$(ctl.ControlSource & vbNullString)) = 0) Then

 

[vbaInet]

Actually what the OP is trying to do is clear all unbound controls so the code is fine as is because it's checking the ControlSource property to determine whether it's unbound.

You put the code in the Current event of the form with these additions:

[COLOR=red]If Me.NewRecord Then[/COLOR]
    Dim ctl As Control
    
    For Each ctl In Me.Controls
        If (ctl.ControlType = acComboBox) Then
            If (Len(Trim$(ctl.ControlSource & vbNullString)) = 0) Then
                ctl.Value = Null
            End If
        End If
    Next ctl
[COLOR=red]End If[/COLOR]

 

[CBrighton]

My bad, didn't notice it was controlsource rather than value.

All good then.