Date last filled...

For anyone who might be interested....here is the code that has finally
worked which was provided by assistance off-group;

Me.txtLastFilled = Nz(DMax("IssueDate", "tblMeds", "RxName = '" &
Me.cmbRxName & "'"), Me.IssueDate)

My apologies to Daryl for the confusion I caused, as I was trying to look in
the wrong field on the table for the data to put in the txtLastFilled
control on the form. He was on the right path and after a good deal of
stepping through the entry process it finally dawned on me that I needed to
use the IssueDate field in the table as the date for the txtLastFilled
control on the form, not the LastFilled field.

Thank you again, Daryl for your time and patience, it is very much
appreciated. :-)

Jan


"Daryl S" wrote in message
...
Jan -

Do the fields and table names match your database? What datatype is the
txtLastFilled? Is txtLastFilled the name of the control on your form that
is
bound to the LastFilled field? I assume it is a date field, and that the
field LastFilled in tblMeds is also a date field. I see you changed my
[FillDate] with [LastFilled]. I assumed you had two dates on your
records -
one being the last date it was filled, and one being the current date it
is
being filled. I would expect the record you are adding on the form should
have the 'current date' of the last record, not the 'last date' of the
last
record. You know your need, so put whichever field you need in there.
Without the RxID, you should have:

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", "[RxName] = '" &
Me.cmbRxName & "'")

--
Daryl S


"Jan " wrote:

Hi Daryl,

I copy/pasted the code as you have it here, but, it is still giving the
error.

I don't really need the RxID. 'Renewed' prescriptions are recorded as
new prescriptions, as even though they have the same medication name,
they have a different Rx number and start date, and may also be ordered
by a different provider, or the strength or dosage may be different, so I
treat it as a new Rx. I only need to show the last date any Rx was
refilled. I don't need the RxID, the Rx name is sufficient for my needs.

Thanks for you help..

Jan

"Daryl S" wrote in message
...
Jan -

Missed an equal sign:

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", "[RxID] = '" &
Me.RxID & "' AND [RxName] = '" & Me.cmbRxName & "'")

You can remove the memberID if this is all for one member (your dad).
I am
not sure about the RxID, though. If your dad was prescribed DrugA, and
this
prescription had 3 refills, then I am assuming you would have one RxID
for
this, so you would have one record for when you first filled it, then
another
when you refilled it the first time, etc. Then what happens when the
last
refill is used and you ask the doctor for a new prescription for DrugA?
I
would assume it is a new prescription (e.g. new RxID), even though it
is the
same medication (DrugA). In that case, do you want to find the
last-filled
record for DrugA, or the RxID? If you just care about the DrugA, then
you
can remove the RxID pieces from the above line. If you need that, then
keep
it in.

Hope that helps!
--
Daryl S


"Jan " wrote:

Hi Daryl :-)

Yes..your thoughts and assumption are correct. The app is for
personal use and only for my dads meds.

The sample you provided threw an immediate error of "Compile error,
Expected: Expression." Even after changing the names to those in my
app I am still getting the error. So not sure what is triggering it.

Me.txtLastFilled DMax("[LastFilled]", "tblMeds", "[RxID] = '" &
Me.RxID & "' AND [RxName] = '" & Me.cmbRxName & "'")

I am not sure if the MemberID in your sample is to represent a member
ID, but, the only ID that I have in the table is RxID as the PK. So, I
may be misunderstanding that part. I have put this in the BeforeDate
Event of the txtLastFilled control. Not sure that is correct either.
But, this looks as if it might work once the kinks are ironed out. :-)

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

Jan


"Daryl S" wrote in message
...
Jan -

I read this as you are looking for the last [FillDate] for a
prescription so
you can put it in the current record's [LastFilled] field. I made
an
arbitrary assumption that this is for a single member or user, but
you can
remove that if this is not the case. You will need to change the
table,
control, and field names to suit your database, but this should get
you going:

Me.LastFilled DMax("[FillDate]", "tblPrescriptions", "[MemberID] =
'" &
Me.MemberID & "' AND [PrescripName] = '" & Me.cboPrescrip & "'")

If the name of your prescription is not the bound field in the combo
box ,
then you may need to indicate which column contains that name (e.g.
Me.cboPrescrip.column(1) or something.)

Hope that helps!
--
Daryl S


"Jan " wrote:

Hi all :-) Access 2007, Vista Ultimate SP2 32 bit

I have a data entry form for entering prescription information. In
need to be able to enter the date that the existing prescription
was last filled. .

I am in need of a means to look up the name of the prescription
selected in the combo box for that field, and display the date that
prescription was last filled for the new entry before it is saved.
I am not sure how best to have it look up the data in the table.

Field name in the table is LastFilled and is a Date/Time data type.
The current ControlSource is the LastFilled field in the table.

Any suggestions would be very much appreciated.

Jan
.

.

.