Here is my original code. I put a breakpoint on Private Sub
Opened form and Stepped through.
It stops on the 2nd strQry with an error that says
Too few parameters, Expected 2.
Let me clarify something.
There is a main form and a subform. The date field is on
the subform. If I open the main form nothing happens after
entering the date.
If I only open the subform, then it jumps to the Module window.
What am I doing wrong. It seems like such a simple thing
to loop through the records and fill in the date field.
The name field and Med RecNumber field are already filled in.
PLEASE stick with me on this until we are successful.
Private Sub TherapyDate_AfterUpdate()
Const SQLTemplate = "SELECT [txtDay], [TherapyDate]
FROM [tblMinutes] " _
& "WHERE [Med RecNumber] = | AND [txtDay] > | " _
& "ORDER BY [txtDay]"
Dim datNext As Date
Dim strQry As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo ErrorHandler
datNext = Me.[TherapyDate] + 1# 'Build the SQL query
by replacing parameters in the
'template with values from
the subform controls
strQry = Replace(SQLTemplate, "|", Me.[Med RecNumber], 1)
strQry = Replace(SQLTemplate, "|", Me.[txtDay], 1)
'Open a recordset
containing the rows to be changed
Set db = CurrentDb()
Set rst = db.OpenRecordset(strQry) 'Loop trough the
rows, updating the dates
Do While Not rst.EOF
rst.Edit
rst![TherapyDate] = datNext
rst.Update
rst.MoveNext
datNext = datNext + 1#
Loop 'Done
ErrorExit:
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox "Runtime error " & Err.Number & vbCr &
Err.Description, vbExclamation
Resume ErrorExit
End Sub
-----Original Message-----
ahh, sorry
so set a breakpoint
then open form
change date in txtDate
then you will see that code stopped at breakpoint
use F8 to step
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
I must be doing something wrong. When I hit F9 it places a
breakpoint, then when I hit F5 it opens a Macro screen. I
don't know what to do then. If I type a name it opens up a
new module window with no code.
-----Original Message-----
no, first place cursor at start of code and hit F9
then hit f5 to start proc
VBA will stop at marked line
then F8 to step through code
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
I would like to, however, not sure how to do so. Do I place
cursor at start of code and hit F8? I've tried that and
again nothing happens. The cursor doesn't move.
-----Original Message-----
Try to step through code to see what it really doing.
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
I removed r.Edit. Opened form and entered date. Nothing
happened. It did not even go to a new record or field.
-----Original Message-----
i think you are using ADODB recordset, it does not have
Edit method, so you
can just omit it
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
Here is my code.
When I try to Compile it, it tells me r.Edit is not a
Member or method.
Private Sub txtDate_AfterUpdate()
Dim r As Recordset
Dim dateOfTreatment As Date
Set r = Me.RecordsetClone
dateOfTreatment = Me.[txtDate]
r.MoveNext
dateOfTreatment = [txtDate] + 1
While Not r.EOF
r.Edit
r![txtDate] = dateOfTreatment
dateOfTreatment = dateOfTreatment + 1
r.Update
r.MoveNext
Wend
End Sub
-----Original Message-----
Okay... I'm going to assume that you have many
records
for a single patient and that your form's recordset
is set
properly:
<begin code>
Dim r As Recordset
Dim dateOfTreatment As Date
Set r = Me.RecordsetClone 'Take the current form's
recordset
dateOfTreatment = Me.dateOfTreatmentOnForm
r.MoveNext 'to move past the currently selected
record, which already has the date in it.
dateOfTreatment = dateOfTreatment + 1
While Not r.EOF 'to continue until we get to the
last
record in the form
r.edit 'claim our intent to edit this record
r!TreatmentDate = dateOfTreatment 'edit it
dateOfTreatment = dateOfTreatment + 1
'update the
date to the next in linear fashion
r.Update 'actually update the record
r.MoveNext 'move to the next record in this
recordset
Wend
</begin code>
This would work well in an afterupdate for the date
field... otherwise you'd have to have a button in the
detail area of the form, which would repeat down the
page,
and really who wants that...
HTH.
Cheers,
..Win
-----Original Message-----
Okay:
We are a rehab clinic. We need to see patients for a
specified length of time in order to qualify for
Medicare
payment. Medicare pays for 100 days. There are 3
therapies.
The length of time can be a combination of 1, 2
or all
therapies.
If a therapist views the existing time given
patient, he
can determine how much more time is needed. By
viewing a
weeks worth of time and when, he can plan
accordingly.
Now: I have already set up the necessary tables and
forms
which will pull up an individual patient and show 7
days
of
possible therapy, but not have the date or minutes
listed
yet.
I don't know if more info is needed by you.
-----Original Message-----
I guess what I'm getting at is why would you add 50
records? Will there be
an entry on every record?
It sounds like a classic one-to-many relationship.
You
would normally only
make entries to the table that are needed. My
guess is
tat you are going to
create 5 records per person and only end up using
a few.
I can understand
wanting a list of fifty dates in a form, but you
don't
have to right records
to a table to do so. Creating a form with fifty
blank
dates for data entry
does not require you to create fifty table
entries. You
could have it only
right records for lines with entries (for example).
Accomplishing your goal of 'not seeing the patient
until
the thrid day'
tells me right off the bat that day 1, 2 and 3
do not
need
to be in the
table.
You might share what you are trying to accomplish in
more
detail. There is
a lot of GREAT KNOWLEDGE on this website and I'm
sure
some
of the MVPs could
give you guidance to building the fiels properly.
Rick B
message
For whatever reason, that's what my boss wants.
A patient may not be seen until the 3rd day after
admission.
So that date will be entered in Day 3. From that
point on,
the rest of the dates need to be filled in.
If it's possible to do please show code.
-----Original Message-----
This sounds a little strange. Are you sure
this is
the
best way to build
your database? What are you trying to accomplish?
"Darlene" <
[email protected]>
wrote
in
message
I open a form which displays 50 records:
Field headings are
Name Day Date Minutes
John Doe 1
John Doe 2
John Doe 3
John Doe 4
etc.
User will open form and enter a Date in record
(once); it
can be in any record, not necessarily the first
record.
Once the Date is entered, I want to have
code to
fill in
the remaining records with the next
succeeding Date
until
all 50 records are filled in. Ex:
Day 2 Date 9/5/04
Day 3 9/6/04
Day 4 9/7/04 and so on.
Thanks
.
.
.
.
.
.
.
.
