Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms question (2002)

    I have a form (frmCmprbl) with a subform (sfrDtlCmprbl) as a datasheet view. I'd like the subform datasheet to act like a listbox and change controls on the main form when a row is selected. I've read some other posts and the example on the access web, but can't seem to get it to work with the SelTop property in the OnTimer event. I also can't seem to get some calculations to work on some of the controls on the main form as well. I've got the code working in a button (see below), but I'd like it to be able to run as I select a row in the datasheet subform. My questions:

    How can I get the cmdRfrshSlctd_Click() code or similar logic to run when a new record is selected in the subform datasheet?
    How can I get similar code in RefreshCalcs() to execute as the controlsource of the text box controls on the main form?

    Command button code from the main form:
    Private Sub cmdRfrshSlctd_Click()

    Dim rstMain As DAO.Recordset
    Set rstMain = Me.RecordsetClone

    rstMain.FindFirst "PrprtsSbjct_ID = " & Me!sfrDtlCmprbl.Form!PrprtsSbjct_ID & " AND PrprtsAdtr_ID = " & Me!sfrDtlCmprbl.Form!PrprtsAdtr_ID

    If rstMain.NoMatch Then

    MsgBox "Error!" & vbCrLf & "No record found"
    Else

    RefreshCalcs
    Me.imgSbjctPhoto.Picture = GetPhoto(Left(Forms!frmMtchd!PID_2, 9))
    Me.imgCmprblPhoto.Picture = GetPhoto(Left(Forms!frmCmprbl.Form.sfrDtlCmprbl.Fo rm!PID_2, 9))

    Me.Bookmark = rstMain.Bookmark
    End If

    rstMain.Close
    Set rstMain = Nothing

    End Sub


    RefreshCalcs sub:
    Sub RefreshCalcs()

    Dim strSQL As String


    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID)
    Forms!frmCmprbl!txtAvgPrcSlctd = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)

    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID) & " AND Lgl_Dscrptn_2 = " & Chr(34) & Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!Lgl_Dscrptn _2 & Chr(34)
    Forms!frmCmprbl!txtAvgPrcSbdvsn = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)

    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID) & " AND Schl_Dstrct_Text = " & Chr(34) & Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!Schl_Dstrct _Text & Chr(34)
    Forms!frmCmprbl!txtAvgPrcSchlDstrct = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)

    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID) & " AND Zip_Code = " & Chr(34) & Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!Zip_Code & Chr(34)
    Forms!frmCmprbl!txtAvgPrcZipCd = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)

    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID) & " AND Schl_Dstrct_Text = " & Chr(34) & Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!Schl_Dstrct _Text & Chr(34)
    Forms!frmCmprbl!txtAvgPrcSchlDstrct = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)

    strSQL = "Incld_Cmprbl = True AND PrprtsSbjct_ID = " & str(Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!PrprtsS bjct_ID) & " AND Adtr_Map = " & Chr(34) & Forms!frmCmprbl.Form.sfrDtlCmprbl.Form!Adtr_Map & Chr(34)
    Forms!frmCmprbl!txtAvgPrcAdtrMap = Round(DAvg("Full_Cnsdrtn", "qryCmprblSlctn", strSQL), 0)


    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Forms question (2002)

    Have you tried putting the code that you now have in cmdRfrshSlctd_Click into the Form_Current event of the subform? You'll probably need to put a line On Error Resume Next at the beginning to avoid timing conflicts between the main form and the subform.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms question (2002)

    I did, but when the form opened it ran the Form_Current event. For some reason, when the code ran it wouldn't allow my subform's recordsource to be modified. Is that what you mean by timing conficts?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Forms question (2002)

    When a form with a subform is opened, the initialization of the two is closely interwoven, and code that interacts with both the main form and the subform diring this initialization usually runs into trouble. Although I dislike putting On Error Resume Next at the beginning of a procedure, this is one of the situations where you need it.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms question (2002)

    Thanks Hans. That seemed to take care of it, the main form updates now without error. I need to create some multi column reports, any good reference material you could suggest? Preferably free...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Forms question (2002)

    Microsoft has some free downloads that might be of interest:
    Access 2000 Sample: Report Topics
    Microsoft Access 2000: Building Applications with Forms and Reports

    If you have specific questions, you can search this forum, and/or post them here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •