Form not updating during loop

[Guest]

I have a table I am trying to update using a form. The table has several
fields a few are ServDate (the date a service is provided) ConEndDate (this
is the contract end date) and the frequency that the service is to be
provided (weekly, fortnightly etc). When I have entered the first record i.e.
ContractNo,ServiceDate,StartTime, EndTime and Employee, I would then like it
to update the table with all the shifts based on the information using a
button to update the table.

I have an append query which I would like to run until the contract End date
is reached. I have tried everything, but I can't get the form to refresh
every time a new record is created in the table therefore the loop doesn't
end.The code I am using is:

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

StDocName = "qryServDate"

Do While Me.ServDate <= Me.ConEndDate

DoCmd.OpenQuery StDocName, acNormal, acEdit
Me.Refresh

If Me.ServDate >= Me.ConEndDate Then Exit Do

Loop

End Sub

Can someone please help me. I am going nuts trying to work this out.

 

[strive4peace]

Hi Marianne,

Me.ServDate
is never changing!

so if Me.ServDate <= Me.ConEndDate
the loop will never end...

are you wanting to loop by day?

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Yes, thats right, and that's exactly the problem. I need it to increment by
the number of days that is in their contract. i.e. weekly (7), fortnightly
(14) etc. What I need to happen in the table is happening, but the loop keeps
going because the field ServDate in the form doesn't update to what is in the
table.

I hope this makes sense

Thanks

 

[strive4peace]

Then you need to increment the date in your code...

Hi Marianne,

I have to run and didn't finish this ... hope you get the
idea...

'~~~~~~~~~~~~~~~~~~~
Sub LoopThroughRecordset_NumID()

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim mNumID as long, mStr as string
Dim s as string, r As Recordset
Dim mDate as date, mDate1 as date, mDate2 as date
Dim StDocName As String

StDocName = "qryServDate"

'open the recordset
'create an SQL statement to select the records you want
'here is some generic syntax for SQL statements from one
table

s = _
& " SELECT Field1, Field2, Field3" _
& " FROM tablename " _
& " WHERE conditions " _
& " GROUP BY fieldlist " _
& " HAVING conditions for fields that are grouped" _
& " ORDER BY fieldlist;

Set r = CurrentDb.OpenRecordset(s,dbOpenSnapshot)

mDate1 = Me.Date1
mdate2 = Me.Date2

mDate = me.Date1

'note - you should check data to make sure it is valid

Do

'execute query
currentdb.execute StDocName

'change the value of mDate
if r.eof then goto Proc_Exit
r.movenext
if r.eof then goto Proc_Exit

mDate = r!DateFieldname

Loop Until mDate >= mDate2

Proc_Exit:
On error resume next
'close the recordset
r.close
'release the recordset variable
Set r = Nothing

'got through ok -- exit sub
Exit Sub

Proc_Err:
MsgBox Err.Description, , "ERROR " _
& Err.Number & " LoopThroughTable"
'press F8 to step through code and debug
'comment next line after debugging
Stop : Resume
Resume Proc_Exit
End function
'~~~~~~~~~~~~``


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day