Date last filled...

[Jan :\)]

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]

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!)

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 :\)]

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

 

[Daryl S]

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!

 

[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

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

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
.

 

[Daryl S]

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

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

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

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 :\)]

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

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

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

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


:

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
.

.

 

[Daryl S]

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

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

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

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


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


:

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 :\)]

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

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

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

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


:

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


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


:

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
.

.

.

 

[Daryl S]

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.

 

[Jan :\)]

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

Jan

 

[Daryl S]

Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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.

.

 

[Jan :\)]

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the confusion on my part, I have moved the code to the OnClick event for the cmbRxName as instructed. However, it does not seem to matter where I put it, and I can't tell if it would work or not as I am still getting the same compile error; "Expected: Expression."

Jan

Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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


:

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]

Jan -

OK, let's try to build the expression before putting it in the DMax
command... Try this:


Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)
Dim txtCriteria as string

txtCriteria = "[RxName] = '" & Me.cmbRxName & "'"

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", txtCriteria)

End Sub

--
Daryl S

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the confusion on my part, I have moved the code to the OnClick event for the cmbRxName as instructed. However, it does not seem to matter where I put it, and I can't tell if it would work or not as I am still getting the same compile error; "Expected: Expression."

Jan

Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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


:

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


.

.

 

[Jan :\)]

Daryl...

.....'k...making progress. Got it in the BeforeUpdate event of the txtLastFilled control on the form, and now the error is "Compile error: Syntax error."

Jan

Jan -

OK, let's try to build the expression before putting it in the DMax
command... Try this:


Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)
Dim txtCriteria as string

txtCriteria = "[RxName] = '" & Me.cmbRxName & "'"

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", txtCriteria)

End Sub

--
Daryl S

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the confusion on my part, I have moved the code to the OnClick event for the cmbRxName as instructed. However, it does not seem to matter where I put it, and I can't tell if it would work or not as I am still getting the same compile error; "Expected: Expression."

Jan

Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S


:

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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


:

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]

Jan -

What line is the compile error on?

I remember you are on Access 2007, but I don't have that available now. If
the compile error is on the DMax statement, then please check the help on
DMax - maybe something changed between Access 2003 and Access 2007?

--
Daryl S

Daryl...

.....'k...making progress. Got it in the BeforeUpdate event of the txtLastFilled control on the form, and now the error is "Compile error: Syntax error."

Jan

Jan -

OK, let's try to build the expression before putting it in the DMax
command... Try this:


Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)
Dim txtCriteria as string

txtCriteria = "[RxName] = '" & Me.cmbRxName & "'"

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", txtCriteria)

End Sub

--
Daryl S

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the confusion on my part, I have moved the code to the OnClick event for the cmbRxName as instructed. However, it does not seem to matter where I put it, and I can't tell if it would work or not as I am still getting the same compile error; "Expected: Expression."

Jan


Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S


:

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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


:

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


.

.

.

 

[Jan :\)]

Thanks, Daryl, I will see what I can find in the Help files. I truly appreciate your time and help.

Jan

Jan -

What line is the compile error on?

I remember you are on Access 2007, but I don't have that available now. If
the compile error is on the DMax statement, then please check the help on
DMax - maybe something changed between Access 2003 and Access 2007?

--
Daryl S

Daryl...

.....'k...making progress. Got it in the BeforeUpdate event of the txtLastFilled control on the form, and now the error is "Compile error: Syntax error."

Jan

Jan -

OK, let's try to build the expression before putting it in the DMax
command... Try this:


Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)
Dim txtCriteria as string

txtCriteria = "[RxName] = '" & Me.cmbRxName & "'"

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", txtCriteria)

End Sub

--
Daryl S


:

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the confusion on my part, I have moved the code to the OnClick event for the cmbRxName as instructed. However, it does not seem to matter where I put it, and I can't tell if it would work or not as I am still getting the same compile error; "Expected: Expression."

Jan


Jan -

This code should be in the BeforeUpdate event of the cmbRxName control or
the form, not the txtLastFilled control. You could also call this in the
Click event of the cmbRxName so you could see the results before tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S


:

Daryl,

Here is the code that I have in the BeforeUpdate event for the txtLastFilled control, which was copy/pasted as is from your last reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but, I can't seem to spot it.

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


:

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


.

.

.

 

[Jan :\)]

Hi Daryl,

Just a follow-up on this, I still have not been able to find where the error
is coming from, but, as still working to see if I can make the code work.

Thanks for your time and help.

Jan

Jan -

What line is the compile error on?

I remember you are on Access 2007, but I don't have that available now.
If
the compile error is on the DMax statement, then please check the help on
DMax - maybe something changed between Access 2003 and Access 2007?

--
Daryl S

Daryl...

.....'k...making progress. Got it in the BeforeUpdate event of the
txtLastFilled control on the form, and now the error is "Compile error:
Syntax error."

Jan

Jan -

OK, let's try to build the expression before putting it in the DMax
command... Try this:


Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)
Dim txtCriteria as string

txtCriteria = "[RxName] = '" & Me.cmbRxName & "'"

Me.txtLastFilled = DMax("[LastFilled]", "tblMeds", txtCriteria)

End Sub

--
Daryl S


:

Hi Daryl,

Thank you for your continued assistance and patience. Sorry for the
confusion on my part, I have moved the code to the OnClick event for
the cmbRxName as instructed. However, it does not seem to matter where
I put it, and I can't tell if it would work or not as I am still
getting the same compile error; "Expected: Expression."

Jan


Jan -

This code should be in the BeforeUpdate event of the cmbRxName
control or
the form, not the txtLastFilled control. You could also call this
in the
Click event of the cmbRxName so you could see the results before
tabbing out
or saving the record.

So, copy the code to the form's BeforeUpdate event and remove the
txtLastFilled_BeforeUpdate procedure, and let us know how that goes.

--
Daryl S


:

Daryl,

Here is the code that I have in the BeforeUpdate event for the
txtLastFilled control, which was copy/pasted as is from your last
reply:
*********************************
Private Sub txtLastFilled_BeforeUpdate(Cancel As Integer)

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

End Sub
***********************************

When I type Me. in the code window I am not offered any options at
all.

It seems to be highlighting the ampersands with the error message.

Here are the relevant field names copy/pasted from the tblMeds:
RxName - data type Text
LastFilled - data type Date/Time - Short Date

Here are the Control names copy/pasted from the entry form:
cmbRxName
txtLastFilled

Not saying I haven't missed something in the spelling or such, but,
I can't seem to spot it.

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


:

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


.

.

.

 

[Jan :\)]

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

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

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

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


:

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


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


:

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
.

.

.