Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform pop up (access 2003 )

    I have a subform I am using on a form. I would like to modify the code below that I have for a button on the form. I would like to use the master and child field link in the code below to only show candidates related to the main form. The link is candidate id. I have this working with a subform on the form but I would like to have this only display as a pop-up once the user clicks the button on the form. Is this possible? Thanks..

    Private Sub Candidates_Click()
    On Error GoTo Err_Candidates_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CandidatesCPR_Frm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Candidates_Click:
    Exit Sub

    Err_Candidates_Click:
    MsgBox Err.Description
    Resume Exit_Candidates_Click

    End Sub

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

    Re: Subform pop up (access 2003 )

    You could use
    [code]
    ...
    stDocName = "CandidatesCPR_Frm"
    stLinkCriteria = "[candidate id] = " & Me.[candidate id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    ...
    [/code[
    I have assumed that candidate id is a number field.
    If you want the user to be able to add new records in the popup form, you need to add code to the On Load event of CandidatesCPR_Frm to set the DefaultValue property of the candidate id field to that in the parent form.

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Hi Hans. I tried using what you mentioned but when I use the code below it's giving me an error saying Access can not find the field '/' (there is a straight line between the quotes) referred to in your expression. I know I have the field EIRID on both forms. Not sure what's happening..

    Private Sub Candidates_Click()
    On Error GoTo Err_Candidates_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CandidatesCPR_Frm"
    stLinkCriteria = "[EPRID] = " & Me.[EPRID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria


    Exit_Candidates_Click:
    Exit Sub

    Err_Candidates_Click:
    MsgBox Err.Description
    Resume Exit_Candidates_Click

    End Sub

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

    Re: Subform pop up (access 2003 )

    Make absolutely sure that you have the names correct. In your post, you mention both EIRID and EPRID - that would be enough for Access to display an error message.

    If you cannot find the error, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Hans that got me closer stupid mistake on my part. They only thing now is it partially works. It gives me a parameter box with a heading of the EIRID (same as mainform EIRID). If I paste the EIRID it gives me the results I want. Any clues? If not I'll add a sample db......

    Private Sub Candidates_Click()
    On Error GoTo Err_Candidates_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CandidatesCPR_Frm"
    stLinkCriteria = "[EIRID] = " & Me.[EIRID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria


    Exit_Candidates_Click:
    Exit Sub

    Err_Candidates_Click:
    MsgBox Err.Description
    Resume Exit_Candidates_Click

    End Sub

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

    Re: Subform pop up (access 2003 )

    The parameter box means that EIRID is not recognized as a valid field name. It'd be easiest if you could attach a stripped down and zipped copy of the database.

  7. #7
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Here's a sample. Click on the + sign and click the candidates button. Thanks Hans..

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

    Re: Subform pop up (access 2003 )

    I had expected EIRID to be a number field, but it is text. Since text values have to be enclosed in quotes, change the line

    stLinkCriteria = "[EIRID] = " & Me.[EIRID]

    to

    stLinkCriteria = "[EIRID] = " & Chr(34) & Me.[EIRID] & Chr(34)

    Chr(34) is the double quote character <code>"</code>.

  9. #9
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Thanks works great..

  10. #10
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Hans the last thing I'm trying to do here is make another pop-up subform so they can have some stored escalation data about the record. I want to have the EIRID from the main form for populate the EIRID field on the subform popup. I tried something below but it doesn't work. I've attached another sample db can you please take a look. Thanks..

    Private Sub EscalateCPR_Click()
    On Error GoTo Err_Candidates_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "EscalatedCPRS"
    stLinkCriteria = Me("EIRID").DefaultValue = """" & Me("EIRID").Value & """"

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    'Me("EIRID").DefaultValue = """" & Me("EIRID").Value & """"


    Exit_Candidates_Click:
    Exit Sub

    Err_Candidates_Click:
    MsgBox Err.Description
    Resume Exit_Candidates_Click
    End Sub

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

    Re: Subform pop up (access 2003 )

    The zip file you attached appears to be empty. I tried both opening it directly and downloading it. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  12. #12
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform pop up (access 2003 )

    Try this file Hans...

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

    Re: Subform pop up (access 2003 )

    You have included EscalatedCPRS as a subform on PNDATA_Frm. Why then do you want to open it as a popup form?

    You can set the default value as follows:

    1) Pass the value of EIRID from PNDATA_Frm to EscalatedCPRS in the OpenArgs argument of DoCmd.OpenForm:

    Private Sub EscalateCPR_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stOpenArgs As String

    On Error GoTo Err_Candidates_Click

    stDocName = "EscalatedCPRS"
    stLinkCriteria = "EIRID=" & Chr(34) & Me("EIRID") & Chr(34)
    stOpenArgs = Me("EIRID")
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArgs

    Exit_Candidates_Click:
    Exit Sub

    Err_Candidates_Click:
    MsgBox Err.Description
    Resume Exit_Candidates_Click
    End Sub

    2) Use the value of OpenArgs in the On Open event of EscalatedCPRS to set the default value

    Private Sub Form_Open(Cancel As Integer)
    Me.EIRID.DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
    End Sub

    See attached version.

Posting Permissions

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