Date last filled...

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

You could make the RowSource of your combo box contain that information and
not need to do any lookup.

Assuming Table1 contains the details of each prescription filled, and Table2
has the descriptions associated with each prescription, your query would be
something like:

SELECT Table1.DrugId, Table2.DrugDesc, Max(Table1.LastFilled) As LastFilled
FROM Table1 INNER JOIN Table2
ON Table1.DrugId = Table2.DrugId
GROUP BY Table1.DrugId, Table2.DrugDesc
ORDER BY Table2.DrugDesc

Make sure the ColumnCount of the combo box is set to 3. To refer to the
value of LastFilled for the currently selected row in the combo box, use
Me!NameOfCombobox.Column(2) (The Column collection starts numbering at zero,
so Column(2) refers to the content of the third column)


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Jan " wrote in message
...
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

"Douglas J. Steele" wrote in message ...
You could make the RowSource of your combo box contain that information and
not need to do any lookup.

Assuming Table1 contains the details of each prescription filled, and Table2
has the descriptions associated with each prescription, your query would be
something like:

SELECT Table1.DrugId, Table2.DrugDesc, Max(Table1.LastFilled) As LastFilled
FROM Table1 INNER JOIN Table2
ON Table1.DrugId = Table2.DrugId
GROUP BY Table1.DrugId, Table2.DrugDesc
ORDER BY Table2.DrugDesc

Make sure the ColumnCount of the combo box is set to 3. To refer to the
value of LastFilled for the currently selected row in the combo box, use
Me!NameOfCombobox.Column(2) (The Column collection starts numbering at zero,
so Column(2) refers to the content of the third column)

Hi Doug :-)

The prescription information is in the one table:
tblMeds
RxID (autonumber)
RxName (text)
RxNo (text)
DateFilled (Date/Time)
LastFilled (Date/Time)
RxType (text)
RxFor (text)
Comments (memo)

Thank you for your time and help.

Jan




--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Jan " wrote in message
...
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

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
.

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
.

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
.

.

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
.

.

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
.

.

.

Hi Daryl,

Yes, the txtLastFilled is the name of the control on the entry form, and LastFilled is the name of the field in the table. I do have two different fields in the table for the dates;

IssueDate = Date Rx was first issued - Date/Time data type
LastFilled = Date Rx was last refilled - Date/Time data type

Both are Short Date format on the table, and there is a control for each on the form and they are Short Date format.

I have copy/pasted the new code you have provided and still getting the error. All the text is in red. The table and form reference information in the code appears to be correct. I can't figure out what 'expression' it is looking for.

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
.

.

.

Jan -

Red code usually means there is some syntax error somewhere. If you type in
Me. in the code window, do you get the options for txtLastFilled and
cmbRxName? These would be the control names on your form. You might have a
typo there. If you still don't have it, copy/paste your code in your next
posting so we can see it - we might spot the issue that way.

--
Daryl S


"Jan " wrote:

Hi Daryl,

Yes, the txtLastFilled is the name of the control on the entry form, and LastFilled is the name of the field in the table. I do have two different fields in the table for the dates;

IssueDate = Date Rx was first issued - Date/Time data type
LastFilled = Date Rx was last refilled - Date/Time data type

Both are Short Date format on the table, and there is a control for each on the form and they are Short Date format.

I have copy/pasted the new code you have provided and still getting the error. All the text is in red. The table and form reference information in the code appears to be correct. I can't figure out what 'expression' it is looking for.

Jan