Referring to a subform with VBA - database now added

[boo]

Hi



I have a form, frm_Interpreter, which has a subform, frm_Language. I am trying to do a search from a form on which I have a combo box which lists all languages. Can anyone give me any pointers as to how to set out my VBA, so far I have the following attached to my cmdSearch Button, I know I need to define the frm_Interpreter and frm_Language in stLinkCriteria, but not sure how to correctly set this out.

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Interpreter"

stLinkCriteria = "[Language]=" & "'" & Me![cboLanguage] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

Any help is greatly appreciated.

[orange]

see this site
http://access.mvps.org/access/forms/frm0031.htm

[boo]

Thanks for that orange. I am still not sure what to do about the placement of the speech marks, should I go ahead like this -

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Interpreter"

stLinkCriteria = "Forms!frm_Interpreter!frm_Language.Form!Language= " & "'" & Me![cboLanguage] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

thanks again, I'll go try this in the meantime.

[boo]

Hmm, I'm getting a compile error - label not defined

[boo]

Ah, I see that was a schoolboy error, too much cutting and pasting, should have read -


Private Sub cmdSearch_Click()

On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Interpreter"

stLinkCriteria = "Forms!frm_Interpreter!frm_InterpreterLanguage.For m!Language= " & "'" & Me![cboLanguage] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

This is opening my frm_Interpreter, unfortunately the form is not displaying any of the information for the interpreter or the languages. Any suggestions?

cheers

[boo]

If its any help the frm_Interpreter uses tbl_Interpreter as its record source. The subform, frm_Language, uses tbl_Language as its record source. The two tables are linked through a one to many relationship with InterpreterID as the PK in tbl_Interpreter and as a FK in tbl_Language.

[boo]

Here is a copy of the part of the database I am having problems with. The intention is to use frm_SearchInterpreterLangList to select the language, which will then open frm_Interpreter where the Interpreters have that language.

Thanks for any help.

[boo]

No one any ideas on this one? I'm stumped, cant figure it out.

[boo]

I found a way to solve my problem, its not ideal, but it works. I've had to take a copy of the frm_Interpreter and I've then made its record source a query to tbl_Interpreter & tbl_Language. As I say, its working, but means creating a few duplicate forms.