Update table when entry is saved

Hi all :-) Access 2007, Vista Ultimate SP2

I have an entry form I use to enter the prescription information for my disabled father, for whom I am the primary caregiver, so that I can keep track of all his prescription information, and which are active or retired. I have created the data entry form for entering all the information which is based on the table, and the information in the combo boxes is based on related queries. What I am in need of is a means to refresh/requery the table/query after each entry so that the information in the combo box lists are immediately updated with the new information for the next entry.

I don't know if I should have a code in the Form's After Update Event to update the table as soon as I save the new record, or if there should be a code added to the Save button so that it will requery when the record is saved and the form ready for the next entry. Some information may be new, such as a new physician, dept., prescription, etc., and sometimes I have more than one prescription to enter with some of the same new information. It would make things much more efficient if the new information was immediately updated.

Any assistance would be very much appreciated.

Jan

On Mon, 1 Feb 2010 18:31:00 -0800, "Jan :\)" wrote:


I don't know if I should have a code in the Form's After Update Event to
update the table as soon as I save the new record, or if there should be a
code added to the Save button so that it will requery when the record is saved
and the form ready for the next entry. Some information may be new, such as a
new physician, dept., prescription, etc., and sometimes I have more than one
prescription to enter with some of the same new information. It would make
things much more efficient if the new information was immediately updated.


The record is already saved in the AfterUpdate event - as the name of the
event implies!

You can Requery a combo box in that event, if its rowsource has changed; but -
ordinarily - this wouldn't happen in the update of a main form. Do you perhaps
have all your data in one table (rather than having a table of Prescriptions,
a table Physicians, etc.?)

--

John W. Vinson [MVP]

Hi, Jan -

May I be permitted a terminological quibble, since we have known each other
so long? In programming parlance, one does not have "a code"; one has
"some code" or just "code". "Code", in the sense of programming language,
is not countable, like pebbles. It's more like a fluid: just as you can
have "water", "some water", "a lot of water", but not "a water", you can
have "code", "some code", "a lot of code", but not "a code".

Now, about your problem. If your combo boxes are querying the same table
where your data entry form is saving records, then there is no other table
to update. All you have to do is use the form's AfterUpdate event to
requery each combo box. Something modelled on this:

'------ start of example code ------
Private Sub Form_AfterUpdate()

Me.cboPhysician.Requery
Me.cboDepartment.Requery
Me.cboPrescription.Requery

End Sub
'------ end of example code ------

Of course, you'd have to use the names of your own combo boxes, which are
likely o be different from these.

If I have misunderstood you, and you have other tables serviing as the
rowsources of your combo boxes, please explain with more detail.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


"Jan " wrote in message
...
Hi all :-) Access 2007, Vista Ultimate SP2

I have an entry form I use to enter the prescription information for my
disabled father, for whom I am the primary caregiver, so that I can keep
track of all his prescription information, and which are active or retired.
I have created the data entry form for entering all the information which is
based on the table, and the information in the combo boxes is based on
related queries. What I am in need of is a means to refresh/requery the
table/query after each entry so that the information in the combo box lists
are immediately updated with the new information for the next entry.

I don't know if I should have a code in the Form's After Update Event to
update the table as soon as I save the new record, or if there should be a
code added to the Save button so that it will requery when the record is
saved and the form ready for the next entry. Some information may be new,
such as a new physician, dept., prescription, etc., and sometimes I have
more than one prescription to enter with some of the same new information.
It would make things much more efficient if the new information was
immediately updated.

Any assistance would be very much appreciated.

Jan

"John W. Vinson" wrote in message ...
On Mon, 1 Feb 2010 18:31:00 -0800, "Jan :\)" wrote:


I don't know if I should have a code in the Form's After Update Event to
update the table as soon as I save the new record, or if there should be a
code added to the Save button so that it will requery when the record is saved
and the form ready for the next entry. Some information may be new, such as a
new physician, dept., prescription, etc., and sometimes I have more than one
prescription to enter with some of the same new information. It would make
things much more efficient if the new information was immediately updated.


The record is already saved in the AfterUpdate event - as the name of the
event implies!

You can Requery a combo box in that event, if its rowsource has changed; but -
ordinarily - this wouldn't happen in the update of a main form. Do you perhaps
have all your data in one table (rather than having a table of Prescriptions,
a table Physicians, etc.?)

Hi John! :-)

Yes, as the amount of information needed is small the data is in one table, wth separate queries for each field, such as Provider, Location, RxName, RxType, etc. which is used for the combo boxes. The entry form is used to enter the data into the table. In the past year my dad has had several fill-in providers as his primary provider of many years has been out on medical disability. Thus, there have been new provider names for updates of some of his existing prescriptions, new prescriptions, new procedures that have been done, etc, so data in these areas have changed fairly often. Last week he saw a new provider who renewed several of his prescriptions and ordered new ones. When I entered his name in the Provider control along with the rest of the information, then saved the record, the new name did not appear in the combo box list for Provider with the next entry. The same for other cb's such as location and RxType, etc. So, I had to re-enter that information.

Jan


--

John W. Vinson [MVP]

Hi Dirk! :-)

"Dirk Goldgar" wrote in message ...
Hi, Jan -

May I be permitted a terminological quibble, since we have known each other
so long? In programming parlance, one does not have "a code"; one has
"some code" or just "code". "Code", in the sense of programming language,
is not countable, like pebbles. It's more like a fluid: just as you can
have "water", "some water", "a lot of water", but not "a water", you can
have "code", "some code", "a lot of code", but not "a code".

As we have known each other for a good while, the terminology correction is very much appreciated. Not being a programmer I do forget to watch my terminology P's & Q's at times, and I appreciate your taking the time to correct my lapse. :-)

Now, about your problem. If your combo boxes are querying the same table
where your data entry form is saving records, then there is no other table
to update. All you have to do is use the form's AfterUpdate event to
requery each combo box. Something modelled on this:

'------ start of example code ------
Private Sub Form_AfterUpdate()

Me.cboPhysician.Requery
Me.cboDepartment.Requery
Me.cboPrescription.Requery

End Sub
'------ end of example code ------

Of course, you'd have to use the names of your own combo boxes, which are
likely o be different from these.

If I have misunderstood you, and you have other tables serviing as the
rowsources of your combo boxes, please explain with more detail.

Yes, the combo boxes are all querying the same table. Putting it in the form AfterUpdate does make it simplier. I thought perhaps I would need to do something for each combo box on the form individually. I have a similar entry form for his appointments that this should also work with.

Thank you very much for your time and help, it is truly appreciated.

Jan
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


"Jan " wrote in message
...
Hi all :-) Access 2007, Vista Ultimate SP2

I have an entry form I use to enter the prescription information for my
disabled father, for whom I am the primary caregiver, so that I can keep
track of all his prescription information, and which are active or retired.
I have created the data entry form for entering all the information which is
based on the table, and the information in the combo boxes is based on
related queries. What I am in need of is a means to refresh/requery the
table/query after each entry so that the information in the combo box lists
are immediately updated with the new information for the next entry.

I don't know if I should have a code in the Form's After Update Event to
update the table as soon as I save the new record, or if there should be a
code added to the Save button so that it will requery when the record is
saved and the form ready for the next entry. Some information may be new,
such as a new physician, dept., prescription, etc., and sometimes I have
more than one prescription to enter with some of the same new information.
It would make things much more efficient if the new information was
immediately updated.

Any assistance would be very much appreciated.

Jan

On Mon, 1 Feb 2010 20:34:15 -0800, "Jan :\)" wrote:

Yes, as the amount of information needed is small the data is in one table,
wth separate queries for each field, such as Provider, Location, RxName,
RxType, etc. which is used for the combo boxes. The entry form is used to
enter the data into the table. In the past year my dad has had several
fill-in providers as his primary provider of many years has been out on
medical disability. Thus, there have been new provider names for updates of
some of his existing prescriptions, new prescriptions, new procedures that
have been done, etc, so data in these areas have changed fairly often. Last
week he saw a new provider who renewed several of his prescriptions and
ordered new ones. When I entered his name in the Provider control along with
the rest of the information, then saved the record, the new name did not
appear in the combo box list for Provider with the next entry. The same for
other cb's such as location and RxType, etc. So, I had to re-enter that
information.


I don't know why your newsreader doesn't linewrap, but it sure makes it hard
to reply!!!!

The amount of data is one factor but it's *ALMOST IRRELEVANT*.

You should have a different table for each Entitytype. A Provider is a
real-life person, an Entity; you should have a table of Providers. When you
need to add a provider, you can use the NotInList event of a combo box, or
popup a Provider form.

The same applies to Locations, RxNames, etc.

You have not said what you're using as the RowSources of these combo boxes.
Are they value lists? lookup tables? Select Distinct from your main table? or
what?
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message ...
On Mon, 1 Feb 2010 20:34:15 -0800, "Jan :\)" wrote:

Yes, as the amount of information needed is small the data is in one table,
wth separate queries for each field, such as Provider, Location, RxName,
RxType, etc. which is used for the combo boxes. The entry form is used to
enter the data into the table. In the past year my dad has had several
fill-in providers as his primary provider of many years has been out on
medical disability. Thus, there have been new provider names for updates of
some of his existing prescriptions, new prescriptions, new procedures that
have been done, etc, so data in these areas have changed fairly often. Last
week he saw a new provider who renewed several of his prescriptions and
ordered new ones. When I entered his name in the Provider control along with
the rest of the information, then saved the record, the new name did not
appear in the combo box list for Provider with the next entry. The same for
other cb's such as location and RxType, etc. So, I had to re-enter that
information.


I don't know why your newsreader doesn't linewrap, but it sure makes it hard
to reply!!!!

Don't know either..all wraps fine here. Using Windows Mail in Vista.

The amount of data is one factor but it's *ALMOST IRRELEVANT*.

You should have a different table for each Entitytype. A Provider is a
real-life person, an Entity; you should have a table of Providers. When you
need to add a provider, you can use the NotInList event of a combo box, or
popup a Provider form.

The same applies to Locations, RxNames, etc.

Thanks John.

You have not said what you're using as the RowSources of these combo boxes.
Are they value lists? lookup tables? Select Distinct from your main table? or
what?

RowSource is Select Distinct, such as:
SELECT DISTINCT tblMeds.Provider
FROM tblMeds;

Jan

On Tue, 2 Feb 2010 07:07:57 -0800, "Jan :\)" wrote:

You have not said what you're using as the RowSources of these combo boxes.
Are they value lists? lookup tables? Select Distinct from your main table? or
what?

RowSource is Select Distinct, such as:
SELECT DISTINCT tblMeds.Provider
FROM tblMeds;

In that case just requery each Combo Box individually in the Form's
AfterUpdate or Current event. Overkill and will usually waste time but it'll
work.
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message ...
On Tue, 2 Feb 2010 07:07:57 -0800, "Jan :\)" wrote:

You have not said what you're using as the RowSources of these combo boxes.
Are they value lists? lookup tables? Select Distinct from your main table? or
what?

RowSource is Select Distinct, such as:
SELECT DISTINCT tblMeds.Provider
FROM tblMeds;

In that case just requery each Combo Box individually in the Form's
AfterUpdate or Current event. Overkill and will usually waste time but it'll
work.

Thanks, John. I appreciate your time and help.

Jan

--

John W. Vinson [MVP]

On Tue, 2 Feb 2010 11:06:32 -0800, "Jan :\)" wrote:


"John W. Vinson" wrote in message ...
On Tue, 2 Feb 2010 07:07:57 -0800, "Jan :\)" wrote:

You have not said what you're using as the RowSources of these combo boxes.
Are they value lists? lookup tables? Select Distinct from your main table? or
what?

RowSource is Select Distinct, such as:
SELECT DISTINCT tblMeds.Provider
FROM tblMeds;

In that case just requery each Combo Box individually in the Form's
AfterUpdate or Current event. Overkill and will usually waste time but it'll
work.

Thanks, John. I appreciate your time and help.

Jan

You're welcome. It's great to see your smiling face!
--

John W. Vinson [MVP]