Combo Box On New Record Only

[Jeff G]

All -

I have a form that has a combo box that is bound to a field...simple enough.

My question is...is there a way to show the combo box on new records only
and show the previously entered value in a Text Field for existing records?

Thanks in advance.

Jeff G

 

[Jeff Boyce]

Jeff

Why? Why not let the existing records show their value in the same
combobox?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

If this isn't a continuous form, try code like:

Private Sub Form_Current()

If Me.NewRecord Then
Me.MyCombo.Visible = True
Me.MyTextBox.Visible = False
Else
Me.MyCombo.Visible = False
Me.MyTextBox.Visible = True
End If

End If

It'll work for a continuous form as well, but all rows will have a combo
box for new rows. When you try to use the combo box, though, you'll have to
move to that row, so the Current event will fire and the combo box will
disappear.

 

[Jeff G]

That would be fine, but I'd like it to be read-only on existing records so
the existing ones couldn't be changed.

 

[Jeff G]

It is on a continuous form.

If this isn't a continuous form, try code like:

Private Sub Form_Current()

If Me.NewRecord Then
Me.MyCombo.Visible = True
Me.MyTextBox.Visible = False
Else
Me.MyCombo.Visible = False
Me.MyTextBox.Visible = True
End If

End If

It'll work for a continuous form as well, but all rows will have a combo
box for new rows. When you try to use the combo box, though, you'll have
to move to that row, so the Current event will fire and the combo box will
disappear.

 

[Douglas J. Steele]

You can put code in the BeforeUpdate event of the combo box:

Private Sub MyCombo_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = False Then
Me.MyCombo.Undo
Cancel = True
End If

End Sub

 

[Dale Fye]

I would just set it's locked property in the forms Current Event:

Private sub Form_Current

me.cbo_Whatever.Locked = not me.newrecord

end sub

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Jeff G]

Dale -

That worked.

Can different recordsets be retrieved for the Combo Box if it's a new record
vs. an old one.

What I'm trying to accomplish is...I have a Notes form that users from
different departments enter their notes for a Project. I'd like to show
them all Notes, but only let them choose from a designated group of items in
the Combo Box. If I set the Source for the Combo Box for just the items I
want them to choose from, it only displays those choices regardless of it
being a new or old record.


Hope this makes sense...

 

[Douglas J. Steele]

In a continuous form, setting the property for one control changes it for
all instances of that control on the form. However, remember that they can
only work with a single record at a time. If you put the code in the form's
Current event, the row source will always be correct for the row with which
they're currently working (even if it's wrong for the other rows).

 

[David W. Fenton]

It is on a continuous form.

I generally recommend against making continuous forms editable,
precisely because of these kinds of UI issues. I use continuous
forms for display, and use a different subform to display the
details. If you set the link fields of the detail form to use the PK
value of the continuous form, it will constantly be in synch as you
navigate in the continuous form.

 

[Dale Fye]

Doing this in a continuous form could result in the rowsource for the combo
box not containing the value associated with that control (in the records
that are not new). The way I generally get around this is to set the
AllowAdditions property of the continuous form to False. Then I add an Add
button to the forms footer, and if the user clicks that, I popup an input
form. Then when they close the input form, I requery the continuous form
and move the focus to the record that was just created.

HTH
Dale

 

[Jeff G]

Dale -

That way worked perfectly.

Thanks.

Jeff