VB Update recordset in subform 1

[TaylorTot]

Hello,

I'm trying to update my subform with a recordset. Unfortunately, it is only listing the last record over and over again. I know I need to create a count, but I'm not sure how to accomplish this. Below is my code:

Private Sub cboAssetNumber_AfterUpdate()
     If Not IsNull(Me!cboAssetNumber) Then
        Me!txtAddress1 = Me!cboAssetNumber.Column(1)
        Me!txtCity = Me!cboAssetNumber.Column(2)
        Me!txtState = Me!cboAssetNumber.Column(3)
        Me!txtZipCode = Me!cboAssetNumber.Column(4)
        
     Dim db As Database
     Dim rst As DAO.Recordset
     Dim qdf As QueryDef
     Dim intd As Long
    
        
    Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
    qdf.Parameters(0) = Forms![frmmain]![txtZipCode]
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Set [fsubProperties].Form.Recordset = rst
  
  With rst
  Do Until .EOF
   
    [Forms]![frmmain]![fsubProperties]![Miles] = rst("Miles")
    [Forms]![frmmain]![fsubProperties]![txtCompanyName] = rst("CompanyName")
    [Forms]![frmmain]![fsubProperties]![txtContactName] = rst("ContactName")
    [Forms]![frmmain]![fsubProperties]![txtOfficePhone] = rst("OfficePhone")
    [Forms]![frmmain]![fsubProperties]![txtMLA] = rst("MLA Signed")
    [Forms]![frmmain]![fsubProperties]![txtActiveAssets] = rst("ActiveAssets") & "/" & rst("MaxAssetsAssigned")
    [Forms]![frmmain]![fsubProperties]![txtLicense] = rst("Licensed Expired")

    .MoveNext
  Loop
  End With
   
 End If
End Sub

Please help!

 

[jadams0173]

I'm not sure how this relates to the problem, but a continuous form that is unbound will display the same record all the time. The continuous form must be bound to show a different set of records. HTH.



I tried to have patience but it took to long! -DW

 

[PHV]

Doesn't the following suffice ?
Set [fsubProperties].Form.Recordset = rst

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TaylorTot]

Yes, that line worked when my control source was = to the fields, but I removed that because when the form was opened the field had #Name in them because the query hadn't run yet.

Is there a way to hide the #Name?

 

[PHV]

Is there a way to hide the #Name?
By running the query with a non existent zip code in the Open event procedure of the subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TaylorTot]

Worked like a charm! Thank you!

Can I ask one more question?

If the original filter produces no results then an error form opens, if the end user selcts pick the next closest record, then I want the subform (fsubproperties) to populate with this one record.

The code I have is:

Private Sub cmdNextRecord_Click()

        
     Dim db As Database
     Dim rst As DAO.Recordset
     Dim qdf As QueryDef
    
    DoCmd.OpenForm ("fsubproperties")
    Set qdf = CurrentDb.QueryDefs("GetFirstRecordInQuery_SelQry")
    qdf.Parameters(0) = Forms![frmmain]![txtZipCode]
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Set Forms![fsubProperties].Recordset = rst
    
 
 
End Sub

This works but it opens a new subform, I would prefer it use the existing subform.

Thank you for your help.

 

[PHV]

Don't use the OpenForm method.
Set Forms!frmmain!fsubProperties.Form.Recordset = rst

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TaylorTot]

I can't thank you enough