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

    Subform requery/refresh (2002)

    I have a form that has a tabl control and several subforms. I have a problem refreshing a subform, I'm hoping someone can tell me what I'm doing wrong. In the cmdAdd_Click event of the frmMain form, I change focus to another subform (lines 118-121). I'd like to refresh the frmMtchdSlctn subform with the new data entered from frmPrprtyEntry (if applicable) and set focus to that new record. I'm able to set focus, but I can't get the subform to refresh and set it's focus to the new record entered. I attached a small copy of the database. Any help would be greatly appreciated.

    Thanks...

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

    Re: Subform requery/refresh (2002)

    cmdAdd is not on the main form, but on srfPrprtyEntry. I can't test anything, however, since there are no tables in the database.

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

    Re: Subform requery/refresh (2002)

    Sorry about that. I have a hard time getting the mdb small enough to post. This should work.

    Thanks...

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

    Re: Subform requery/refresh (2002)

    Still can't do much. Combo boxes are not populated, making it impossible to enter data. If you want us to help you, please make it possible to do so.

    If necessary, you can save the database in Access 97 format before zipping it; that will cause a considerable reduction in size.

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

    Re: Subform requery/refresh (2002)

    Hans,

    I'm really sorry about that, I thought you were just looking for mainly VBA syntax. I finally got it under 100k in 97 format with some data. I ran some tests myself with this mdb and it worked. Lines 131-133 of the cmdAdd_Click event from the frmPrprtyEntry form is where I'm having trouble. I want the frmMtchdSlctn to contain the new data entered in frmPrprtyEntry, but no matter what I try I can't get the subform to refresh. Any help is appreciated.

    Thanks.

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

    Re: Subform requery/refresh (2002)

    I'll look at it later today. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Subform requery/refresh (2002)

    You must position sfrMtchd to the correct record first; after that you can do something with frmMtchSlctn (you seem to set it to a new record in all cases now, as far as I can see.) The code to locate the address in sfrMtchd could look like this:

    With Me.Parent!sfrMtchd.Form.RecordsetClone
    .FindFirst "STRT_ADRS = " & Chr(34) & Me.Address & Chr(34)
    If .NoMatch = False Then
    Me.Parent!sfrMtchd.Form.Bookmark = .Bookmark
    End If
    End With

    Insert this in the appropriate place(s), before setting focus to frmMtchSlctn.

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

    Re: Subform requery/refresh (2002)

    Hans,

    Is there something different I need to do if sfrMtchd has a query as it's record source? The sfrMtchd RecordsetClone record count is 0 when I step through the code and I'm not sure why. Since there's no match it doesn't update the bookmark property. Cool stuff by the way, first time I ever looked at recordsetclone and bookmark. I attached the mdb with the updated code you sent me, it's lines 131-137 of the frmPrprtyEntry.

    Thanks.

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

    Re: Subform requery/refresh (2002)

    Insert the line

    Me.Parent!sfrMtchd.Requery

    before the code with the recordsetclone. You may not even need the latter code any more.

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

    Re: Subform requery/refresh (2002)

    Hans,

    The requery worked, I tried that before put I wasn't using Parent, so I always got an error. The record entered on the first tab now appears in the second tab. I have a couple of questions though. frmMtchdSlctn uses qryMtchdSlctn as it's record source, which returns two records in query view. frmMtchd uses qryMtchdToAdtr as it's record source and returns two records as well. However, the frmMtchd and frmMtchdSlctn only show one record. My goal is to allow frmMtchSlctn to act as a list box in a way. I'd like to be able to click or select a record from frmMtchdSlctn and have frmMtchd change to display data for that selected record. I thought about just using a list box control instead of a subform, but I want to be able to sort by columns and apply filtering eventually. Which is why I elected for the subform.

    After all that here are my questions: Why does the form and subform only contain one record when the queries they use as a record source return two records? Based on what I discussed earlier (selection subform), do you think I can accomplish all this with my current design?

    I attached the latest version.

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

    Re: Subform requery/refresh (2002)

    frmMtchd shows only one record since it is a single form, and you have turned off the navigation buttons.
    frmMtchdSlctn shows only one record since it is a subform of frmMtchd, linked by ID, so for each record in frmMtchd, the subform will only display the corresponding record(s). For that reason, you cannot use frmMtchdSlctn to select a record in frmMtchd: the link works from parent to subform, not the other way round. I would prefer a combo box or list box to select a record, but if you want to use the subform, you should break the link with the parent, i.e. clear the Link Child Fields and Link Master Fields property of frmMtchdSlctn as a control on frmMtchd. You will probably have to use a command button on frmMtchd to activate the filtering, for you can't display a command button on frmMtchdSlctn since it is a datasheet form.

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

    Re: Subform requery/refresh (2002)

    Hans,

    That makes perfect sense. It seems so obvious now, but I sure didn't see it on my own. I'll give the list box a try instead of the subform, I think for now the simplest approach is probably best. As always, I appreciate the help.

Posting Permissions

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