dual use of combo box

  • Thread starter Thread starter Randall Arnold
  • Start date Start date
R

Randall Arnold

I'm going nuts on this one.

My client wants to use a combo box to filter certain records on a form while
in record Edit mode, and use the same combo to select a value to add to a
new record while in record Add mode. My assumption was that I could test to
see if an Add operation was in progress and allow the field value to change
if so; otherwise, I would Cancel the change in the form's BeforeUpdate event
(since in Edit mode I want to use the combo *solely* for filtering of
records-- the field value should never be changed on this form). Naturally,
the combo is bound so that adding records is automatic and browsing the
records in Edit mode shows the proper field value.

Problem is, this idea doesn't work. The program crashes on the filter (in
the combo's change event sub) and says I'm trying to access a property "|"
(!!!???!!!) that doesn't exist.

Surely someone has done this before, but I can't find any examples. Anyone
know a resource to steer me toward? Many thanks!

Randall Arnold
 
I would create two combo boxes, one to find an existing record
(cboChooseCombo) and another to add new values (cboEnterCombo). Then make
them alternately visible and invisible depending on whether you are in the
NewRecord of the form. If you placed them one on top of the other and sized
them exactly the same, it would look like the same combo had dual roles.

It would require something like the following code in the On Current event
of the form.

Private Sub Form_Current()
If Me.NewRecord Then
cboChooseCombo.Visible = False
cboEnterCombo.Visible = True
Else
cboChooseCombo.Visible = True
cboEnterCombo.Visible = False
End If
End Sub
 
That's certainly a workable method. I was just hoping for something more
"elegant" (sorry...lol). I figured I could change properties on the fly if
nothing else-- if I only knew what to change that would work.

For now I'll implement your idea, though. Thanks!

Randall Arnold
 
Randall,

There's certainly nothing wrong with Roger's suggestion. If by "elegant" you
mean "complex", then I think you're on the wrong path. Roger's solution is
elegant in my opinion, because it provides the required functionality in a
very simple and manageable way. The only thing I would change, and this is
simply a matter of personal preference, is to do the assignment like this:
cboChooseCombo.Visible = Not (Me.NewRecord)
cboEnterCombo.Visible = (Me.NewRecord)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Actually, no, complex is FAR from what I mean by elegant! In this case I
meant SIMPLER, as in avoiding the use of overlapping controls that have to
be switched on and off. I personally try to avoid those approaches because
it gives me form design headaches sooner or later. I'm already having to do
the visibility thing with a Tab control, since it can't host another tab
control (&%$%#&^^%!!!) and it's making me nuts when I have to modify the
form...

Randall Arnold
 
Randall,

I share your frustration with nested tab controls - I'm doing the same thing
right now. Just make sure to Group all the controls on your child tab
control (Format --> Group).

I still believe Roger's suggestion is the way to go, but there is something
you might like to try. Place one of the control's (maybe cboEnterCombo)
somewhere near the bottom of the form. Then make two additions to the code I
gave:
cboChooseCombo.Visible = Not (Me.NewRecord)
cboEnterCombo.Top = IIf(Me.NewRecord, Me.cboChooseCombo.Top,
Me.cboEnterCombo.Top)
cboEnterCombo.Left = IIf(Me.NewRecord, Me.cboChooseCombo.Left,
Me.cboEnterCombo.Left)
cboEnterCombo.Visible = (Me.NewRecord)

Now we're getting inelegant, but it'll work!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Your method IS more elegant -- in the old-time programming sense: the fewest
lines of code to produce the desired effect. I use it myself quite often.
But giving answers in the newsgroups, I always err on the side of
understandability (if that's a word). The IF statement is more readily
understood than flipping boolean expressions. Especially for new
programmers. For the similar reasons, I usually use 'IF varBoolean = True'
rather than 'IF varBoolean'.
 
I've been using the boolean method for a long time... even got some money
from the VBPJ magazine years ago for submitting it as a tip. What amazes
me, though, is how many professional programming examples I see using
IF/THENs when a boolean solution is so much simpler.

Anyway, at least this is a workable solution. Thanks again Roger!

Randall Arnold
 
Back
Top