Subform pop up (access 2003 )

Topic

New Reply

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

Reply | Quote

Replies

You could use

...
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.

Reply | Quote

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

Reply | Quote

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 for instructions.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ".

Reply | Quote

Thanks works great..

Reply | Quote

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

Reply | Quote

The zip file you attached appears to be empty. I tried both opening it directly and downloading it.

Reply | Quote

Try this file Hans…

Reply | Quote

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.

Reply | Quote