Prev: Can a Table be opened in datasheet view from a form
Next: How To Access From VBA A Tables Row Lookup Row Source
From: Jan :) on 1 Feb 2010 21:31
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 :)


From: John W. Vinson on 1 Feb 2010 22:39
On Mon, 1 Feb 2010 18:31:00 -0800, "Jan :\)" <abuse(a)localhost.invalid> 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]
From: Dirk Goldgar on 1 Feb 2010 22:44
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 :)" <abuse(a)localhost.invalid> wrote in message
news:e6pQO$6oKHA.1548(a)TK2MSFTNGP02.phx.gbl...
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 :)



From: Jan :) on 1 Feb 2010 23:34
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:de7fm55b1e4lj8s5ktqlljloc85avoj3pm(a)4ax.com...
> On Mon, 1 Feb 2010 18:31:00 -0800, "Jan :\)" <abuse(a)localhost.invalid> 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]
From: Jan :) on 2 Feb 2010 00:04
Hi Dirk! :-)

"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote in message news:5F7E71EF-7BA7-4DC3-AD27-703633ACA4B5(a)microsoft.com...
> 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 :)" <abuse(a)localhost.invalid> wrote in message
> news:e6pQO$6oKHA.1548(a)TK2MSFTNGP02.phx.gbl...
> 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 :)
>
>
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Can a Table be opened in datasheet view from a form
Next: How To Access From VBA A Tables Row Lookup Row Source