Begin code automatically

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,
I have an unbound form which has a number of text boxes.
The main one is to enter a stock code. Once I enter a
code the after update code populates the remainder of the
text boxes with info associated with the stock code using
the following... pretty standard.

Private Sub txtExtOdrStkQty_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM etc. etc.)
With rs
Me.txtStkLocation = !StkLocation
Me.txtStkMainCtgy = !StkMainCtgyDesc
etc. etc.

All works fine. When I don't know the stock code I can
double click the stock code field which opens a dialog to
allow me to use various methods to search for the
appropriate code and populates the main form stock code
field with the selected item as it closes. No problems
there but what I want to do is fire the same code as in
the after update (as if I had entered the stock code
myself) to populate the remainder of the text boxes with
associated info. Can anyone assist with an idea on how to
make this happen?
 
One possible solution:
For example purposes we'll call your original form Form1.
First, create a new module and Public subroutine and copy
your code into it:
Public Sub sFillForm1()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset _
("SELECT * FROM etc. etc.)
With rs
Forms!Form1.txtStkLocation = !StkLocation
Forms!Form1.txtStkMainCtgy = !StkMainCtgyDesc
etc. etc.

This will allow you to access the code anywhere within the
database. Then, delete the code in the Private Sub
txtExtOdrStkQty_AfterUpdate() and change it to call the
new public subroutine: sFillForm1
Add the same call (sFillForm1) to the On Close event of
your search form.
NOTE: You will notice in the example above that you have
to change the code a little. Since the code will be called
from multiple locations and is no longer a part of the
original form, you can no longer use Me to refer to
controls. Change all the Me to Forms!Form1
(see example above)
 
You can explicitly call any event by using

Call NameOfEvent

Just add this after the command to close the dialog box and it will act as
if the event was triggered.

Kelvin
 
Back
Top