restrict number of entries on subform

[Guest]

I have a main form properties and a subform operators. There is a combo box
(Operator) on the subform, once the user makes a selection it populates
several text boxes on the subform. I only want the user to be able to make
one choice from the combo box but they need to be able to add to the list if
the correct choice is not in the list. Currently you can add however many
records on the sub form for any one main form record. This is bad as there
should only be one operator per property. Please help!

 

[Guest]

Hi Gary,

See if this example is helpful to you:

http://groups.google.com/group/micr..._frm/thread/735142a53f53d215/cffb270391e91ca4

One thing: I just noticed that I made reference to "frmCategoriesSubform". I
should have indicated to add the code behind the Product List form, which
serves as the subform for the Categories form.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

As there can only be one operator per property you don't need a subform at
all for this. Simply having an Operator foreign key in the Properties table
underlying the main form is enough, with a combo box on the main form bound
to the Operator column. Other values from the row in the Operators table to
which the Operator foreign key points can be shown in unbound computed
controls on the main form if desired, either by using the DLookup function,
or by referring to columns in the Operator combo box's RowSource in their
ControlSource properties.

To add a new operator you can use the combo box's NotInList event procedure
to open a form bound to the Operators table when the user types a new
operator value into the combo box. Here's an example for a cities combo
box's NotInList event procedure:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

You'll see that this passes the new city value to the frmCities form. In
that form's Open event procedure the DefaultValue property of the city
control on the form is set with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

 

[John Vinson]

I have a main form properties and a subform operators. There is a combo box
(Operator) on the subform, once the user makes a selection it populates
several text boxes on the subform. I only want the user to be able to make
one choice from the combo box but they need to be able to add to the list if
the correct choice is not in the list. Currently you can add however many
records on the sub form for any one main form record. This is bad as there
should only be one operator per property. Please help!

Sounds like your table structure is questionable here! If there's only
one operator per property, why not have just a field for the
OperatorID on the main form, bound to an OperatorID field in the
properties table? You could use the combo's Not In List event to pop
up a data entry form when the user enters an unknown operator. You
certainly should NOT be copying information about the operator from
the operators table into another table, if that's what you mean by
"populates several text boxes"...

John W. Vinson[MVP]