Need Help Creating Event

S

shep

I am running WIN XP Pro and Office 2003 Pro

I have a tab control that contains several forms (tabs). Common fields
among the forms are "ChartNumber" a combo box, and "Patient" a text field.
On any tab, when I select a ChartNumber the Patient field automatically fills
through Event Procedure in the ChartNumber.

I want to create an event for each form "ON Open" that will automatically
open to a new record and show the last ChartNumber and Patient used.

Thanks for any help
 
J

John Vinson

I am running WIN XP Pro and Office 2003 Pro

I have a tab control that contains several forms (tabs). Common fields
among the forms are "ChartNumber" a combo box, and "Patient" a text field.
On any tab, when I select a ChartNumber the Patient field automatically fills
through Event Procedure in the ChartNumber.
Click to expand...

I'm not sure I understand. A Form can contain a tab control, but a Tab
is not a Form; it's just a way of controlling screen space *on* a
Form.

Do you have Subforms on each tab? What's the Recordsource property for
the main form? If you have subforms, what are their Recordsource
properties, and what are their Master/Child Link Fields?
I want to create an event for each form "ON Open" that will automatically
open to a new record and show the last ChartNumber and Patient used.
Click to expand...

Again... I'm not understanding. If you open to a new record, it's a
new, blank record ready for inputting data; I'm not sure what you mean
by "show the last ChartNumber and patient". The new, empty record
won't HAVE anything to show. And what do you mean by "last" in this
context?

Groping in the dark here, let me *guess* that you have a main form
based on a Chart for a particular patient, with subforms for various
information about that chart. If so, you should be able to use the
ChartNumber and PatientID as Master/Child Link Fields to display
information for that patient/that chart.

John W. Vinson[MVP]
 
S

shep

Thanks for your response. I apologize for lack of clarity. I created a
blank form and inserted a tab control with 6 pages (Tabs). On each page I
inserted a subform using an existing form.

When I open the main form, it opens to first page and the first record of
the subform. I would like to open to a new record and when I tab to another
subform, I would like it to open to a new record and with the ChartNumber and
Patient I called up on the previous page auto filled in.

I hope that I have adequately clarified what I am trying to achieve.

Thanks again
 
J

John Vinson

Thanks for your response. I apologize for lack of clarity. I created a
blank form and inserted a tab control with 6 pages (Tabs). On each page I
inserted a subform using an existing form.

When I open the main form, it opens to first page and the first record of
the subform. I would like to open to a new record and when I tab to another
subform, I would like it to open to a new record and with the ChartNumber and
Patient I called up on the previous page auto filled in.

I hope that I have adequately clarified what I am trying to achieve.
Click to expand...

You can set the DataEntry property of the form (or forms) being used
as subforms to Yes to automatically open to the new record.

To get the ChartNumber and Patient automatically filled in, I'd
suggest a slightly different (and more "normal") approach: put two
combo boxes on the main form, unbound, allowing the user to select the
patient and chart number. Make these two controls the Master Link
Field of each subform control:

cboPatientID;cboChartNumber

for example. Set the Child Link Field to the PatientID and ChartNumber
fields in the subform's respective table.

Otherwise, you'll have to write some VBA code to populate the
ChartNumber and Patient fields - and this could be rather complex
code, since you can't easily guarantee that your user always goes
through the pages in the same order, and never skips one.

What information is being entered in these subforms anyway? I hope
you're not storing the patient name redundantly in multiple tables!

John W. Vinson[MVP]
 
S

shep

Thanks
These forms are for scheduling appointments, tracking various kinds of lab
work, and tracking referrals to othe doctors and agencies.

The Patient data is entered into a table. I have a query that pulls the
Patient name and ChartNumber from that table. Some other tables include the
Patient name and ChartNumber as lookup fields from the query. The form's
record sources are those tables. Is that not good?
 
J

John Vinson

Thanks
These forms are for scheduling appointments, tracking various kinds of lab
work, and tracking referrals to othe doctors and agencies.

The Patient data is entered into a table. I have a query that pulls the
Patient name and ChartNumber from that table. Some other tables include the
Patient name and ChartNumber as lookup fields from the query. The form's
record sources are those tables. Is that not good?
Click to expand...

If the mainform is based on this query, and has the two fields, you
should be in good shape: use these fields as the Master Link Field
property of the subforms, with the fieldnames separated by semicolons.
This is how subforms usually work - a main form for the "one" side (a
patient chart in this case) with subforms for the "many" side tables -
appointments, etc.; the Master and Child Link Fields provide the link,
so you don't NEED to use programming or special queries. The subform
features take care of that for you!

I'd recommend AGAINST using Lookup fields, at all. See
http://www.mvps.org/access/lookupfields.htm for a critique. They're
not necessary for building forms, and can be very confusing.


John W. Vinson[MVP]
 
S

shep

I created a query to create "Patient" e.g., Doe, Joe J. from Patient table
that has LastName, FirstName, MiddleInitial fields and the query includes
ChartNumber from the table. ChartNumber is automatically generated on the
form used to post Patient Data.

I created other tables for scheduling appointments, lab work etc and
included Patient and ChartNumber as lookup fields. Now I am mindful of your
advice against lookup fields and I am trying to figure out how to get those
fields functional on a form without lookup. I created forms from these
tables.

I created a main form from the query with ChartNumber and Patient on the
form. I then added a tab control with pages for needed subforms and added
them from the existing forms. I linked ChartNumber and Patient between the
subforms and the main form with the wizzard, so I did not separate them with
semicolons.

When I open the main form, the ChartNumber and Patient on the sub form
match those on the main form. When I goto next record in the mainform, the
subform ChartNumber and Patient match those on the main form.

If I search (contrl + F) in the ChartNumber field on the main form and enter
a different #, it goes to that number and enters the correct Patient (based
on code Ofer gave me) and the ChartNumber and Patient on subform matches
those on the main form.

It appears that all is working well, but I am concerned about your concern
that I am storing ChartNumber and Patient in several tables and that I am
using lookup fields.

I will appreciate any further advise and I greatly appreciate what you have
already done to help me.
 
J

John Vinson

I created a query to create "Patient" e.g., Doe, Joe J. from Patient table
that has LastName, FirstName, MiddleInitial fields and the query includes
ChartNumber from the table. ChartNumber is automatically generated on the
form used to post Patient Data.
Ok...

I created other tables for scheduling appointments, lab work etc and
included Patient and ChartNumber as lookup fields. Now I am mindful of your
advice against lookup fields and I am trying to figure out how to get those
fields functional on a form without lookup. I created forms from these
tables.
Click to expand...

There is NOTHING wrong with using Lookups - combo boxes - on Forms.
They are *ABSOLUTELY* routine and appropriate; and they can be created
very readily using the Combo Box wizard on the forms design toolbar.

The criticism is not leveled against lookup *tables* but against
Microsoft's misdesigned Lookup Field type, in tables.

The lookup wizard has the advantage that it makes creating a Combo Box
on a form a couple of mouseclicks easier. The cost of this advantage
is tne multiple disadvantages listed at the MVP website
http://www.mvps.org/access/lookupfields.htm.

By all means use Lookups on your form... just not in your tables!
I created a main form from the query with ChartNumber and Patient on the
form. I then added a tab control with pages for needed subforms and added
them from the existing forms. I linked ChartNumber and Patient between the
subforms and the main form with the wizzard, so I did not separate them with
semicolons.
Click to expand...

If you open the main form in design view, and view the Properties of
one of the subforms, what are the Master and Child Link Fields? I'm
not certain that the wizard is smart enough to do two-field links...
When I open the main form, the ChartNumber and Patient on the sub form
match those on the main form. When I goto next record in the mainform, the
subform ChartNumber and Patient match those on the main form.
Click to expand...

so it's probably OK...
If I search (contrl + F) in the ChartNumber field on the main form and enter
a different #, it goes to that number and enters the correct Patient (based
on code Ofer gave me) and the ChartNumber and Patient on subform matches
those on the main form.
Good.

It appears that all is working well, but I am concerned about your concern
that I am storing ChartNumber and Patient in several tables and that I am
using lookup fields.
Click to expand...

Well, if these are in fact tables for which the parent entity is a
particular combination of patient and chart, I have no concerns at
all. Sounds like a good design in fact!
I will appreciate any further advise and I greatly appreciate what you have
already done to help me.
Click to expand...

Sounds like you're on a roll - just keep it up and don't hesitate to
post back if you have problems!

John W. Vinson[MVP]
 
S

shep

Thanks again for your help!

John Vinson said:
There is NOTHING wrong with using Lookups - combo boxes - on Forms.
They are *ABSOLUTELY* routine and appropriate; and they can be created
very readily using the Combo Box wizard on the forms design toolbar.

The criticism is not leveled against lookup *tables* but against
Microsoft's misdesigned Lookup Field type, in tables.

The lookup wizard has the advantage that it makes creating a Combo Box
on a form a couple of mouseclicks easier. The cost of this advantage
is tne multiple disadvantages listed at the MVP website
http://www.mvps.org/access/lookupfields.htm.

By all means use Lookups on your form... just not in your tables!


If you open the main form in design view, and view the Properties of
one of the subforms, what are the Master and Child Link Fields? I'm
not certain that the wizard is smart enough to do two-field links...


so it's probably OK...


Well, if these are in fact tables for which the parent entity is a
particular combination of patient and chart, I have no concerns at
all. Sounds like a good design in fact!


Sounds like you're on a roll - just keep it up and don't hesitate to
post back if you have problems!

John W. Vinson[MVP]
Click to expand...
 
Top