Combo Box Event probs

  • Thread starter Thread starter John Bildy
  • Start date Start date
J

John Bildy

Hi all,

Newbie problems I'm afraid.

I have a combo box on an Orders form which selects customers.

I am trying to create an on_change event. However, the VB window opens up on
a different form (Add Customer). Why would this happen?

All I want to do is set the focus on another field after a selection has
been made in the combo box. I want the focus to go to employee field.

Help appreciated.
 
Hi all,

Newbie problems I'm afraid.

I have a combo box on an Orders form which selects customers.

I am trying to create an on_change event. However, the VB window opens up on
a different form (Add Customer). Why would this happen?

All I want to do is set the focus on another field after a selection has
been made in the combo box. I want the focus to go to employee field.
Click to expand...

The Change event has a rather misleading name; it fires whenever you
hit any keystroke in the control. I think you want the AfterUpdate
event instead, and that there may be code already in the AfterUpdate
which opens the Add Customer form.
 
John Bildy said:
Hi all,

Newbie problems I'm afraid.

I have a combo box on an Orders form which selects customers.

I am trying to create an on_change event. However, the VB window
opens up on a different form (Add Customer). Why would this happen?
Click to expand...

How are you going about creating the event procedure? If you just open
the VB Editor, by pressing Alt+F11, for example, that won't create or
open the code module of the form you're working on. The easiest way
(IMO) to create the event procedure you want is to have form open in
design view, open the property sheet of the combo box, go to the Event
tab, and double-click on the line for the event you're interested in.
That should set the event property itself to "[Event Procedure]", at
which point you can click on the builder button (caption "...") at the
end of the line, and you'll be placed in the VB Editor, in the form's
code module, inside the shell of an event procedure constructed for the
purpose. All you have to do is fill in the VBA code that goes between
the "Private Sub" and "End Sub" lines.

Incidentally, I would not use the Change event. That event is fine if
the user uses the mouse to select an item from the dropdown list, but if
the user types in the text box portion of the control, the event will
fire for each keystroke. Instead, use the AfterUpdate event. That will
fire only once, after the user's modification to the control, whether by
keyboard or mouse, has been accepted.
All I want to do is set the focus on another field after a selection
has been made in the combo box. I want the focus to go to employee
field.
Click to expand...

I'd expect your event procedure to look something like this:

Private Sub Customer_AfterUpdate()

Me!Employee.SetFocus

End Sub

You'd have to substitute your own control names for "Customer" and
"Employee", naturally.
 
Hi Dirk & John V,

Yep, that's what I did. Set event procedure and clicked event builder
button. And even trying it for After Update, i.e. clicking on the builder
button, vb opens in the Add Customer form. Cursor is at the very top where
it says option compare database.

Anyway, I deleted the combo box and replaced it. It works fine. I must have
corrupted the original somehow.

Thanks heaps guys for your prompt help.

Cheers,
 
Hi,

Newbie me again.

Now that I have sorted out my combo box problem on my Orders form, I would
like to have my combo box updated with new data immediately after I add a
new customer. At the moment I have a refresh button on the form. I also add
a new customer from a command button (Add Customer which opens the Customers
Form in data entry mode.)

My combo box control source is Orders.CustomerID and its rowsource is
qryCustomerNames.

I assume I have to put the requery on an event (exit?) on the Customers
form. But what is the code I use.

Thanks heaps for any help.
 
John Bildy said:
Hi,

Newbie me again.

Now that I have sorted out my combo box problem on my Orders form, I
would like to have my combo box updated with new data immediately
after I add a new customer. At the moment I have a refresh button on
the form. I also add a new customer from a command button (Add
Customer which opens the Customers Form in data entry mode.)

My combo box control source is Orders.CustomerID and its rowsource is
qryCustomerNames.

I assume I have to put the requery on an event (exit?) on the
Customers form. But what is the code I use.

Thanks heaps for any help.
Click to expand...

There are two ways to handle this:

(1) You can have your Add Customer button open the Customers form in
dialog mode, so that the command button's code pauses until the
Customers form is closed. Then you can requery the combo box in the
command button's Click event code immediately following the call to
DoCmd.OpenForm. It might look something like this:

Private Sub Add_Customer_Click()

DoCmd.OpenForm "Customers", _
Datamode:=acFormAdd, WindowMode:=acDialog

Me!Customer.Requery

End Sub

I prefer this approach.

(2) If you don't want to open the Customers form in dialog mode, you can
put code in the Close event of the Customers form that requeries the
combo box on the Orders form, if that form is currently loaded. For
Access 2000 or later, the code might look like this:

Private Sub Form_Close()

If CurrentProject.AllForms("Orders").IsLoaded Then
Forms!Orders!Customer.Requery
End If

End Sub

The reason I don't like this approach so much is just that I don't like
to clutter up the secondary form with code that may not be needed.
 
Back
Top