Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    requery problems (2000)

    This is probably very simple but its taking me a while.
    I have a main for called [Reg] on it are two sub forms [30%cost] and [10%cost] they are on different pages on a tab control.
    The main form is used to view records only. To add a new 10 or 30 % record there is a command button which opens up a data entry form. When I close this form I want the newly entered info to appear on the [30%cost] or [10%cost] page without having to go back and forward a record on the main form.

    Any ideas?

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

    Re: requery problems (2000)

    If the command button is on the main form, try something like this:

    ' Open data entry form. Make sure it will be closed before we continue.
    DoCmd.OpenForm FormName:="frmDataEntry", WindowMode:=acDialog
    ' Requery the subform.
    Me.sbfSubForm.Requery
    ' Set focus to subform, so that the next command will refer to that.
    Me.sbfSubForm.SetFocus
    ' Go to last record
    RunCommand acCmdRecordsGoToLast

    If the command button is on the appropriate subform, it becomes

    DoCmd.OpenForm FormName:="frmDataEntry", WindowMode:=acDialog
    Me.Requery
    RunCommand acCmdRecordsGoToLast

    Of course, you should modify this. I don't know how you pass information to the data entry form.

    If the subform is re-sorted during the requery, the newly added record may not be the last record any more. In that case, you can use DAO to find the record and select it in the subform.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery problems (2000)

    Thanks for that it works in all but one respect.

    As there are two sub forms on two different tabs on the main form - how can I make sure that after the data entry form closes I go to the tab that contains the updated data.
    There is a field in the data entry form which has a value of either 30 or 10.
    The two sub forms are filtered to show only the 30 series data or 10 series data.
    At the moment the dataform closes and because I used the 30 subform as the set focus in the line
    Me.sbfSubForm.SetFocus
    it obviosly goes to that.
    If I enter 10 series data I want to return to the 10 tab. How can that be done?

    If you can follow that, you deserve a medal.

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

    Re: requery problems (2000)

    One possibility:

    Put an unbound and invisible text box on the main form, say txtSeries.
    Set this to 10 or 30 from the data entry form, for instance in the routine used to save the record:

    Forms!frmMainForm!txtSeries = Me.[name of field that can be 10 or 30]

    (substitute the appropriate names)

    Then, in the OnClick routine of the command button on the main form, before DoCmd.Openform...:

    Me.txtSeries = null ' Erase previously set value

    and after DoCmd.OpenForm ....:

    Select Case Me.txtSeries
    Case 10
    Me.sbfSubForm10.Requery
    Me.sbfSubForm10.SetFocus
    Case 30
    Me.sbfSubForm30.Requery
    Me.sbfSubForm30.SetFocus
    End Select

    (subsitute the appropriate names again)

    You can use a global variable instead of an invisible text box if you prefer.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery problems (2000)

    Thank you, thank you , thank you

    It works.

Posting Permissions

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