Records added programatically to subform not visible

[Jasonm]

I know this question has been asked over and over in this group, and I have
tried all of the solutions that have been offered here with no real
results... If anyone can offer a solution, I would be very grateful.

I am working with a main form frmTests with a single subform frmTestResults
Linked based on a value on the main form. This relationship works great, and
records are updated as I move from record to record on the main form.

When I add a new record on the main form I use a button to retrieve a set of
child records that will need to be added to the subform for a given test
group selected from the main form. This is also working great (I don't do a
lot of this stuff, so it is the little victories that seem to make the
most!!). The problem is that these newly added records are not visible until
I back up a record and then move back to the newly created record on the
main form. Any Ideas? The Code I am using is posted below:

' Set the db to the current database
Set db = DBEngine(0)(0)

' if the current record has not been saved, save it
If Me.Dirty Then
Me.Dirty = False
End If



' Check to see if the records have already been created
strSQL = "SELECT * " _
& "FROM tblTestResults WHERE
tblTestResults.SampleId " _
& "= '" & Me.SampleID & " ' "


Set rs = db.OpenRecordset(strSQL)
intCount = rs.RecordCount
' If the records HAV NOT been created yet Proceed
If intCount = 0 Then
' Create the SQL String to insert all of the Child Records
into tblTestRecords
strSQL = "INSERT INTO tblTestResults ( ConstituentID,
SampleID )" & _
" SELECT tblConstituents.ConstituentId, '" &
Me.txtSampleID & "'" & _
" FROM tblTestGroups INNER JOIN (tblConstituents
INNER JOIN tblConstituents2Groups " & _
"ON tblConstituents.ConstituentId =
tblConstituents2Groups.ConstituentId) " & _
"ON tblTestGroups.GroupId =
tblConstituents2Groups.GroupId " & _
"WHERE (((tblTestGroups.GroupId) = '" &
Me.cboTestGroup.Value & "'))"
' excecute the SQL code
db.Execute strSQL, dbFailOnError

Set db = Nothing

Else
' Other Code HERE for Records ALready Exist
End If



End Sub

 

[RoyVidar]

I know this question has been asked over and over in this group, and
I have tried all of the solutions that have been offered here with no
real results... If anyone can offer a solution, I would be very
grateful.

I am working with a main form frmTests with a single subform
frmTestResults Linked based on a value on the main form. This
relationship works great, and records are updated as I move from
record to record on the main form.

When I add a new record on the main form I use a button to retrieve a
set of child records that will need to be added to the subform for a
given test group selected from the main form. This is also working
great (I don't do a lot of this stuff, so it is the little victories
that seem to make the most!!). The problem is that these newly added
records are not visible until I back up a record and then move back
to the newly created record on the main form. Any Ideas? The Code I
am using is posted below:

' Set the db to the current database
Set db = DBEngine(0)(0)

' if the current record has not been saved, save it
If Me.Dirty Then
Me.Dirty = False
End If



' Check to see if the records have already been
created
strSQL = "SELECT * " _
& "FROM tblTestResults WHERE
tblTestResults.SampleId " _
& "= '" & Me.SampleID & " ' "


Set rs = db.OpenRecordset(strSQL)
intCount = rs.RecordCount
' If the records HAV NOT been created yet Proceed
If intCount = 0 Then
' Create the SQL String to insert all of the Child
Records into tblTestRecords
strSQL = "INSERT INTO tblTestResults ( ConstituentID,
SampleID )" & _
" SELECT tblConstituents.ConstituentId, '" &
Me.txtSampleID & "'" & _
" FROM tblTestGroups INNER JOIN
(tblConstituents INNER JOIN tblConstituents2Groups " & _
"ON tblConstituents.ConstituentId =
tblConstituents2Groups.ConstituentId) " & _
"ON tblTestGroups.GroupId =
tblConstituents2Groups.GroupId " & _
"WHERE (((tblTestGroups.GroupId) = '" &
Me.cboTestGroup.Value & "'))"
' excecute the SQL code
db.Execute strSQL, dbFailOnError

Set db = Nothing

Else
' Other Code HERE for Records ALready Exist
End If



End Sub

You have appended the records, but Access, or the subform isn't yet
aware of it, you need a requery, I think. Perhaps

me!SubformControlName.Form.Requery

Where SubformControlName is the name of your subform control. This
might
differ from the actual subform name.

 

[Jasonm]

Aarggg! I can't tell you how many times that I tried to make this work with
the same (it must not have been exactly the same or it would've worked
right!) code...

Thank you very much. Now I guess I have to move on with finishing up! Dang
it! Seriously, Thanks!

Jm