Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery in a subform (2000)

    Hi guys,
    I have a subform (frmSearchSpec) linked to a mainform (frmCustomers).
    When I create a new record in frmCustomers, then I open the frmSearchSpec, to try to create a record, it will give me problems saying it cannot find CustID(primarykey) in frmCustomers(or tblCustomers).
    I've tried the requery, but no luck.
    here is the button that opens up the subform.
    How can i modify it to make it work accordingly?. Or is there a better alternative?.

    Private Sub cmdViewSpecial_Click()
    On Error GoTo Err_cmdViewSpecial_Click
    Me.CustID.Requery


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmsearchspec"

    stLinkCriteria = "[CustID]=" & Me![CustID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdViewSpecial_Click:
    Exit Sub

    Err_cmdViewSpecial_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewSpecial_Click

    End Sub

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

    Re: Requery in a subform (2000)

    Your terminology is confusing, since you are using "subform" in a sense that is different from the way Access developers use it.

    By a subform, we usually mean a form placed as a control on another form (the main form); the subform is not opened by itself. The subform is linked to the main form by one or more fields (the Link Child Fields and Link Master Fields). In such a main form/subform setup, the subform will automatically display only records that are related to the current record in the main form, and if you create a new record in the subform, the link field(s) will automatically be filled in.

    Although you call frmSearchSpec a subform, you open it as a separate form using DoCmd.OpenForm. There is no automatic linkage in this situation. If you create a new record in frmSearchSpec, CustID will not be filled in, unless you write code to do so.

    I recommend that you consider using the standard main form/subform setup, since Access will do most of the work for you then. If you really need to open frmSearchSpec as a separate form, pass the CustID value to it in the OpenArgs argument:

    DoCmd.OpenForm stDocName, , , stLinkCriteria, , Me.CustID

    and use this in the On Load event of frmSearchSpec to set the default value of CustID in this form:

    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    Me.CustID.DefaultValue = Me.OpenArgs
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery in a subform (2000)

    Hi Hans,
    Ok, I tried doing that, and no luck. I think i'm doing something wrong. Your right, its not a subform, but a popup one. I've tried various other
    ones from my access 2000 book by Alison Balter.
    i've attached the db, basically just open frmCustomers, and click edit to activate the form, and press View Special Rates. This is the link to the frmSearchSpec.
    Your help on this is much appreciated.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Requery in a subform (2000)

    <P ID="edit" class=small>(Edited by patt on 17-Jan-04 08:25. Changed to reinforce what Hans has suggested)</P>Firstly as Hans points out you must send the CustID in the OpenArgs field of the OpenForm command.

    Next in the form frmsearchSpec you need to store the OpenArgs field into the txtCustID field each time you go to add a record, as Hans also pointed you must set up the Default value of the txtCustID field in the Form Load event.

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

    Re: Requery in a subform (2000)

    Sorry, I was offline for a while. I am a bit surprised that you didn't implement my suggestions in the database you posted, but Pat has already pointed out what you should do.

  6. #6
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery in a subform (2000)

    ok got it right this time, after a while of tinkering with it, i managed to get it working, thanks heaps Hans and Pat, your
    help is much appreciated. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Posting Permissions

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