Can't exit loop

[marianne]

I need to be able to update a table using a form. I have been trying without
success to write code to run an append query with a loop. What is happening
is that the loop doesn't end and access stops responding.Obviously I have it
well and truly wrong. Could someone please help me. The code is as follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate) AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency, tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER JOIN
qryOldestRec ON tblService_Date_and_Times.recNum = qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency = tblContracts.Freq) AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End Date]));

 

[Ken Snell \(MVP\)]

Most likely you have a timing issue... the code runs on while the query is
doing its thing.

I do not understand your code's logic, however. You are inserting a record
into the "tblService_Date_and_Times " table, but your DLookup is looking at
the "tblContracts" table. How is "tblContracts" supposed to be updated with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub

 

[marianne]

thank-you, I'm almost there. I get a run-time error 2467. "The expression you
entered refers to an object that is closed or doesn't exist. De-bug points to
the line

Do Until Me.ServDate >= Me.ConEndDate

As you can tell I've changed that line.

Thanks in advance

Most likely you have a timing issue... the code runs on while the query is
doing its thing.

I do not understand your code's logic, however. You are inserting a record
into the "tblService_Date_and_Times " table, but your DLookup is looking at
the "tblContracts" table. How is "tblContracts" supposed to be updated with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

I need to be able to update a table using a form. I have been trying
without
success to write code to run an append query with a loop. What is
happening
is that the loop doesn't end and access stops responding.Obviously I have
it
well and truly wrong. Could someone please help me. The code is as
follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate)
AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency, tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER JOIN
qryOldestRec ON tblService_Date_and_Times.recNum =
qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency = tblContracts.Freq)
AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End
Date]));

 

[marianne]

I forgot to say that when I close the de-bug window, the new records have
been inserted into the table.

Most likely you have a timing issue... the code runs on while the query is
doing its thing.

I do not understand your code's logic, however. You are inserting a record
into the "tblService_Date_and_Times " table, but your DLookup is looking at
the "tblContracts" table. How is "tblContracts" supposed to be updated with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

I need to be able to update a table using a form. I have been trying
without
success to write code to run an append query with a loop. What is
happening
is that the loop doesn't end and access stops responding.Obviously I have
it
well and truly wrong. Could someone please help me. The code is as
follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate)
AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency, tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER JOIN
qryOldestRec ON tblService_Date_and_Times.recNum =
qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency = tblContracts.Freq)
AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End
Date]));

 

[Ken Snell \(MVP\)]

Do you have a control on the form named "ConEndDate"? Or a field in the
form's RecordSource named "ConEndDate"? If not, what is ConEndDate?
--

Ken Snell
<MS ACCESS MVP>

thank-you, I'm almost there. I get a run-time error 2467. "The expression
you
entered refers to an object that is closed or doesn't exist. De-bug points
to
the line

Do Until Me.ServDate >= Me.ConEndDate

As you can tell I've changed that line.

Thanks in advance

Most likely you have a timing issue... the code runs on while the query
is
doing its thing.

I do not understand your code's logic, however. You are inserting a
record
into the "tblService_Date_and_Times " table, but your DLookup is looking
at
the "tblContracts" table. How is "tblContracts" supposed to be updated
with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example
shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

I need to be able to update a table using a form. I have been trying
without
success to write code to run an append query with a loop. What is
happening
is that the loop doesn't end and access stops responding.Obviously I
have
it
well and truly wrong. Could someone please help me. The code is as
follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]",
"[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate)
AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency,
tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER
JOIN
qryOldestRec ON tblService_Date_and_Times.recNum =
qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency = tblContracts.Freq)
AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End
Date]));

 

[marianne]

Yes I do. It's the date the contract ends. It's on the form as a Text Box

Do you have a control on the form named "ConEndDate"? Or a field in the
form's RecordSource named "ConEndDate"? If not, what is ConEndDate?
--

Ken Snell
<MS ACCESS MVP>

thank-you, I'm almost there. I get a run-time error 2467. "The expression
you
entered refers to an object that is closed or doesn't exist. De-bug points
to
the line

Do Until Me.ServDate >= Me.ConEndDate

As you can tell I've changed that line.

Thanks in advance

Most likely you have a timing issue... the code runs on while the query
is
doing its thing.

I do not understand your code's logic, however. You are inserting a
record
into the "tblService_Date_and_Times " table, but your DLookup is looking
at
the "tblContracts" table. How is "tblContracts" supposed to be updated
with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example
shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

I need to be able to update a table using a form. I have been trying
without
success to write code to run an append query with a loop. What is
happening
is that the loop doesn't end and access stops responding.Obviously I
have
it
well and truly wrong. Could someone please help me. The code is as
follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]",
"[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate)
AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency,
tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER
JOIN
qryOldestRec ON tblService_Date_and_Times.recNum =
qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency = tblContracts.Freq)
AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End
Date]));

 

[Ken Snell \(MVP\)]

OK - let's find out which control the VBA says doesn't exist. When you run
the code, and the debugger window appears, click Debug button. Then hold the
cursor over the Me.ServDate text and see what the popup text says -- does it
say "object closed...doesn't exist"? Do the same for the Me.ConEndDate text.

What is the Control Source of the control that is showing the error in the
above test? What is the RecordSource of the form?

--

Ken Snell
<MS ACCESS MVP>

Yes I do. It's the date the contract ends. It's on the form as a Text Box

Do you have a control on the form named "ConEndDate"? Or a field in the
form's RecordSource named "ConEndDate"? If not, what is ConEndDate?
--

Ken Snell
<MS ACCESS MVP>

thank-you, I'm almost there. I get a run-time error 2467. "The
expression
you
entered refers to an object that is closed or doesn't exist. De-bug
points
to
the line

Do Until Me.ServDate >= Me.ConEndDate

As you can tell I've changed that line.

Thanks in advance



:

Most likely you have a timing issue... the code runs on while the
query
is
doing its thing.

I do not understand your code's logic, however. You are inserting a
record
into the "tblService_Date_and_Times " table, but your DLookup is
looking
at
the "tblContracts" table. How is "tblContracts" supposed to be updated
with
a new value when you are not adding new records to it?

Setting aside my question about your code's logic, this code example
shows
an alternative, more preferred way of running an action query:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]",
"[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

I need to be able to update a table using a form. I have been trying
without
success to write code to run an append query with a loop. What is
happening
is that the loop doesn't end and access stops responding.Obviously I
have
it
well and truly wrong. Could someone please help me. The code is as
follows:

Private Sub repeat_Click()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]",
"[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

DoCmd.OpenQuery ("qryServDate")

If Me.ServDate >= DLookup("[End Date]", "[tblContracts]",
"[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]") Then Exit
Do

Loop

End Sub

The query is as follows:
INSERT INTO tblService_Date_and_Times ( Service_Plan_ID, ServDate,
Start_Time, End_Time, Carer, Frequency, ConSt_Date, ConEnd_Date )
SELECT tblService_Date_and_Times.Service_Plan_ID,
DateAdd("d",tblFrequency!Calculation,tblService_Date_and_Times!ServDate)
AS
ServDate, tblService_Date_and_Times.Start_Time,
tblService_Date_and_Times.End_Time, tblService_Date_and_Times.Carer,
tblService_Date_and_Times.Frequency,
tblService_Date_and_Times.ConSt_Date,
tblService_Date_and_Times.ConEnd_Date
FROM ((tblFrequency INNER JOIN tblService_Date_and_Times ON
tblFrequency.Frequency = tblService_Date_and_Times.Frequency) INNER
JOIN
qryOldestRec ON tblService_Date_and_Times.recNum =
qryOldestRec.MaxOfrecNum)
INNER JOIN tblContracts ON (tblFrequency.Frequency =
tblContracts.Freq)
AND
(tblService_Date_and_Times.Service_Plan_ID = tblContracts.[Service
No])
WHERE
(((DateAdd("d",[tblFrequency]![Calculation],[tblService_Date_and_Times]![ServDate]))<=[tblContracts]![End
Date]));

 

[Ken Snell \(MVP\)]

What is the Control Source fro each of the controls (ServDate and
ConEndDate)? What is the RecordSource of the form?

How does the value of either ServDate or ConEndDate change so that the test
will eventually be true? I don't see anything in the code that will change
their values.
--

Ken Snell
<MS ACCESS MVP>

Thanks for all this Ken

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= Me.ConEndDate

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub

< snipped >

 

[marianne]

this is a query to update a table based on the frequency of a shift. If the
shift is weekly then the ServDate(Service Date) will be incremented in the
query by 7 days until the ConEndDate(Contract End Date) is reached.

So, to answer your question it changes with the running of the append query

 

[marianne]

My apologies, I didn't read your reply from the beginning so I've missed part
of the answer.

The control source of ServDate is the initial entry into the form. The
control source of ConEndDate is from the tblContracts which has the end date
of the contract.
The record source of the form is tblService_Date_and_Times.

 

[Ken Snell \(MVP\)]

A form's data are not refreshed unless you tell the form to refresh the
data. From your description in the other post ("this is a query to update a
table based on the frequency of a shift. If the shift is weekly then the
ServDate(Service Date) will be incremented in the query by 7 days until the
ConEndDate(Contract End Date) is reached."), you are expecting the form's
ConEndDate value to change as the table is updated by the query.

One way to do this is to have your code refresh the form's data -- best way
to do that is to insert a Me.Requery step in the loop after the update query
is run. (I am assuming that the ConEndDate control has an expression using
DLookup or some such function to get the value that you want to test. Is
this right?) But I would be more inclined to put the actual test in the code
as a step -- that way, the form does not need to be requeried, which may
cause all kinds of "changes" on the form in terms of the record that is
active, etc. So why not put the test right in the code:

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("EndDateField", "tblContracts", "Criterion
expression here")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub

 

[marianne]

I've just tried what you suggested but it comes back with the same run-time
error message. The code I used was as follows

Private Sub repeat_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()

Do Until Me.ServDate >= DLookup("[End Date]", "[tblContracts]", "[Service
No]=Forms![frmService_Date_and_Times]![Service_Plan_ID]")

dbs.Execute "qryServDate", dbFailOnError
DoEvents

Loop

dbs.Close
Set dbs = Nothing
End Sub

 

[marianne]

Sorry, another addition. I should have checked the whole thing before replying.
When I changed the code I also went into the query and took out the criteria
that the query was not to run if the servdate was >= conenddate. When I ran
the code the loop continued so the table was enormous. The form was not
updated until I closed it and reopened the form. I have tried the Me.Requery
several times before and it doesn't seem to work.

 

[Ken Snell \(MVP\)]

Time ran out on me tonite... I'll post a reply tomorrow .. sorry for the
delay.

--

Ken Snell
<MS ACCESS MVP>

Sorry, another addition. I should have checked the whole thing before
replying.
When I changed the code I also went into the query and took out the
criteria
that the query was not to run if the servdate was >= conenddate. When I
ran

< snipped >

 

[Ken Snell \(MVP\)]

I am not "seeing" your setup clearly in my mind's eye, sorry. If you'd like
to email me a copy of your database, zip it up and send it to me -- you can
get my email address from my "reply to" email address if you remove the
words this is not real from the munged email address.

I'll take a look and see what is happening.
--

Ken Snell
<MS ACCESS MVP>

Sorry, another addition. I should have checked the whole thing before
replying.
When I changed the code I also went into the query and took out the
criteria
that the query was not to run if the servdate was >= conenddate. When I
ran
the code the loop continued so the table was enormous. The form was not
updated until I closed it and reopened the form. I have tried the
Me.Requery
several times before and it doesn't seem to work.

< snipped >