SubForm Data Display Problems

[Massamiya]

Please help me, I am having problem displaying data on an
unlinked sub form.I am using Acess 2003.
The subform not being populated with data though there is data
in the underlying table from which the record source which is
a query for the sub form draws data.
The make up of my program is as follows, mainform (frm_Estimate) and
a Subform (sfrm_Estimate) which are unlinked.
On the main form are text and combo boxes and option buttons and a
command button named “DisplayData”.
in the click event of the command button, is code to pull data from
several tables and the data is stored in a temporary table named
(TBL_Estimate_Parts) based on the selections and input the users makes
on the main form.
The user has to select one of three option buttons which signifies the
product type (1.Fan 2.Parts 3.Box)..
The difference between the product types is just in the tables from where
data is drawn to populate the Temporary table (TBL_Estimate_Parts).
When either (1.Fan) or (2.Parts) is selected all data is correctly displayed
on the subform, but in the case of option 3 (Box) not a single data
(but an empty record ready for editing) is displayed on the subform though
the underlying table (TBL_Estimate_Parts) has data. When I click on the subform
or stored query for the subform both are populated with data.
When run in debug mode, the subform is populated with data (see screenShot004)
which seems to be cleared.
I don’t know when and in which routine that the data is cleared.
What baffles me is that, data is displayed with the same code when option 1 or 2
is selected.
Here is the setup of the main and subform　　
MainForm (frm_Estimate)　Record Source: None、Filter:None
SubForm (sfrm_Estimate) Record Source :Query
SELECT TBL_Estimate_Parts.* FROM TBL_Estimate_Parts ORDER DISPID） Datasheet View　　RecordSet:ynaset Filter : None
DUMMY Subform: Record Source: None、Filter：None
Source Object Record Sourceummy
Data Entry : No

Private Sub DisplayData_Click()
   Dim qdefrs1 As DAO.QueryDef
   DoCmd.SetWarnings False
   On Error GoTo ErrorHandler
   Set db = OpenDatabase("C:\Estimate.mdb")
   Stq = "DELETE * FROM TBL_Estimate_Parts"     ' Delete all data in temp file
   DoCmd.RunSQL Stq
   K_Estimate.SourceObject = ""            ' Resetting the SourceObject　　　　　　　
   Set rs1 = qdefrs1.OpenRecordset()
   Set rs2 = db.OpenRecordset("TBL_Estimate_Parts", dbOpenDynaset)
 
   If Not (rs1.BOF And rs1.EOF) Then
      rs1.MoveFirst
   End if
   Do Until rs1.EOF
      On Error GoTo ErrorHandler
      rs2.AddNew           
      rs2!Model = NZ(rs1("Model"))
     rs1.MoveNext
   Loop
   K_Estimate.SourceObject = "Sfrm_Estimate"　　　　' Setting the SourceOBject
   Me.K_Estimate.Requery
   Me.K_Estimate.Form.Recordset.MoveFirst
   rs1.Close: Set rs1 = Nothing
   ‘rs2.Close: Set rs2 = Nothing
 ErrorHandlerExit:
    Exit Sub      
 ErrorHandler:  
    If Err = 3021 Then   
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub

 

[Marinus]

Now my Japanese is not that good, the only word I know is konicua or something like that but I would say there is a link missing in the Relations or there is no proper Master Child connection. Hope this puts you in the right direction..

 

[Massamiya]

Now my Japanese is not that good, the only word I know is konicua or something like that but I would say there is a link missing in the Relations or there is no proper Master Child connection. Hope this puts you in the right direction..

Marinus,
Thanks for the prompt response.
Konnichiwa (hope it is afternoon at your end)
The main and the subform are unlinked and I
did mention in my post, there are 3 option buttons
denoting 3 product types, for the first two options "FANS"
and "PARTS" the subform is populated with data.
The problem is with the 3rd option "BOX" which in debug mode
is populated with data but dynamically the subform is not populated
despite the temp table that the select query used has record source for
the subform has data.
The only difference between the option 1&2 with option 3 "BOX" is that
for option 3 data is drawn from a few tables compared to the other 2 options
I am just guessing, maybe for option 3 the program is executed faster.
The line below is executed
K_Estimate.SourceObject = "Sfrm_Estimate"　
before the completion of the deletion of the temp table (the 3 lines) shown below
Stq = "DELETE * FROM TBL_Estimate_Parts" ' Delete all data in temp file
DoCmd.RunSQL Stq
K_Estimate.SourceObject = "" ' Resetting the SourceObject
What do you think ?

 

[Massamiya]

Thanks everyone.
Had the problem fixed. Just for others who might
face the same problem, I had to replace the line
DoCmd.RunSQL stq
after the delete line to CurrentdB.Execute command
Stq = "DELETE * FROM TBL_Estimate_Parts"
CurrentDb.Execute Stq, dbFailOnError

I will refrain as much as possible from using "DoCmd" in
to execute queries.