Database not refreshing fast enough? Updating records - and reading them back - not updated!

[Morris]

Hello everyone!

I'll try to explain it as clear as I can.

I've got an Access table and a form. A Table has 25 records, one of the
columns is a True/False 'ToRun' field. Now I want people to set which
records are supposed to run by checking the checkbox. So I created a
form, created a checkbox which is linked to the field in the table.

After they went through all the records they press a button "GO!" which
is supposed to close this form, open another one and fill the listbox
with selected records. The code under the GO button is like this:

Private Sub Command19_Click()

stDocName = "frmCreateSFX"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.Name

Exit Sub

the code in the frmCreateSFX form in the Form_Open event is like this:

strSQL = "SELECT * FROM FilesToBuild WHERE FilesToBuild.ToRun = True"

rstJobsLeft.ActiveConnection = CurrentProject.Connection
rstJobsLeft.Open strSQL, , adOpenDynamic, adLockOptimistic

strlist = "Client Name;Name Of File;Service;Manual;File Type;"

If Not rstJobsLeft.BOF Then
rstJobsLeft.MoveFirst
Do Until rstJobsLeft.EOF
strlist = strlist & rstJobsLeft.Fields("Client") & ";" & _
rstJobsLeft.Fields("Name_Of_File") &
";" & _
rstJobsLeft.Fields("DataType") & ";" &
_

IIf(rstJobsLeft.Fields("ManualIntervention") = True, "YES", "NO") & ";"
& _
IIf(rstJobsLeft.Fields("CreateExe") =
True, "EXE", "ZIP") & ";"
intNoOfFiles = intNoOfFiles + 1
rstJobsLeft.MoveNext
Loop
End If
lstFiles.SetFocus
Me.lstFiles.RowSource = strlist

And now - If I marked two jobs to run and pressed Go - it went fine,
the listfile showed two jobs. Then, for testing purposes, I cleared
both , marked one of them back, pressed Go - it still shows the
previous selection of two !. I broke the code execution after pressing
Go - looked Up the FilesToBuild table - it showed only one job marked
ToRun, but then again - the SELECT statement (strSQL = "SELECT * FROM
FilesToBuild WHERE FilesToBuild.ToRun = True"
) returned two of them..

Any ideas?

 

[Rick Brandt]

Hello everyone!

I'll try to explain it as clear as I can.

I've got an Access table and a form. A Table has 25 records, one of
the columns is a True/False 'ToRun' field. Now I want people to set
which records are supposed to run by checking the checkbox. So I
created a form, created a checkbox which is linked to the field in
the table.

After they went through all the records they press a button "GO!"
which is supposed to close this form, open another one and fill the
listbox with selected records. The code under the GO button is like
this:

Private Sub Command19_Click()

stDocName = "frmCreateSFX"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.Name

Exit Sub

the code in the frmCreateSFX form in the Form_Open event is like this:

strSQL = "SELECT * FROM FilesToBuild WHERE FilesToBuild.ToRun = True"

rstJobsLeft.ActiveConnection = CurrentProject.Connection
rstJobsLeft.Open strSQL, , adOpenDynamic, adLockOptimistic

strlist = "Client Name;Name Of File;Service;Manual;File Type;"

If Not rstJobsLeft.BOF Then
rstJobsLeft.MoveFirst
Do Until rstJobsLeft.EOF
strlist = strlist & rstJobsLeft.Fields("Client") & ";" & _
rstJobsLeft.Fields("Name_Of_File") &
";" & _
rstJobsLeft.Fields("DataType") & ";" &
_

IIf(rstJobsLeft.Fields("ManualIntervention") = True, "YES", "NO") &
";" & _
IIf(rstJobsLeft.Fields("CreateExe") =
True, "EXE", "ZIP") & ";"
intNoOfFiles = intNoOfFiles + 1
rstJobsLeft.MoveNext
Loop
End If
lstFiles.SetFocus
Me.lstFiles.RowSource = strlist

And now - If I marked two jobs to run and pressed Go - it went fine,
the listfile showed two jobs. Then, for testing purposes, I cleared
both , marked one of them back, pressed Go - it still shows the
previous selection of two !. I broke the code execution after pressing
Go - looked Up the FilesToBuild table - it showed only one job marked
ToRun, but then again - the SELECT statement (strSQL = "SELECT * FROM
FilesToBuild WHERE FilesToBuild.ToRun = True"
) returned two of them..

Any ideas?

I suspect that the last entry is not yet saved when the second form's query is
run. Either add a line of code to save the record on the first form before
opening the second one or swap the two lines so that the first form is closed
before the second form is opened (yes the lines after closing the first form
will still run).

 

[Morris]

I suspect that the last entry is not yet saved when the second form's query is
run. Either add a line of code to save the record on the first form before
opening the second one or swap the two lines so that the first form is closed
before the second form is opened (yes the lines after closing the first form
will still run).

That's what I suspect as well.

Either add a line of code to save the record on the first form before
opening the second one

How would I do that?

Cheers,
Morris

 

[Rick Brandt]

That's what I suspect as well.


How would I do that?

Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord

 

[Morris]

Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord

Thanks a lot.

I did the first way - switching the order in which the new form
opens/old closes and works fine.

I guess it's some stupid buffer thing which is flushed to disk on
formclose.