Set Frm= Forms(FormName) - refer to subform 2

[hermanlaksko]

I need to refer / set Frm=Forms(FormName) to a sub form
I use the following function it works fine when I "set" to a main form however when setting to a mainforms subform I get an error
"can't find the form 'MainForm!SubForm.Form' referred to in a macro expression or Visual Basic code.

Sub SetUdvalg(FrmNavn As String)
Dim Re As DAO.Recordset, GemType As Integer, SNr, SQL, Frm As Form
Set Frm = Forms(FrmNavn) '- Problem is here
Select Case Frm.RapList
Case 94: GemType = 2
Case 87: GemType = 1
Case Else: GemType = 0
End Select
SQL = "SELECT SetNavn FROM Project WHERE TypeNr =" & GemType & " GROUP BY SetName "
Set Re = CurrentDb.OpenRecordset(SQL)
Frm.Udvalg.RowSource = SQL
End Sub

This works - SetUdvalg "MyForm"
This will not work - SetUdvalg "MyForm!SubForm.Form"
This will not work - SetUdvalg "MyForm!SubForm"
This will not work - SetUdvalg "Forms!MyForm!SubForm.Form"



Herman
Say no to macros

 

[Andrzejek]

You pass you FrmNavn to the Sub as String. Did you try to pass it as a Form instead?
If you pass it as a Form (or as a String) you still HAVE to have a listbox (?) named RapList and another control named Udvalg, and they HAVE to be named the same way on any Form you pass. You may consider to pass those 2 controls instead of the Form:
[tt]
Sub SetUdvalg(ByRef lst As Listbox, ByRef udv As ???)[/tt]

this way you can name those controls wahever you want on your Form.

Just a suggestion...

BTW, in:
Dim Re As DAO.Recordset, GemType As Integer, [red]SNr, SQL[/red], Frm As Form

SNr and SQL are Variants

Have fun.

---- Andy

 

[hermanlaksko]

Hi Andy

Thx for your reply.
I am aware of the controles needed on the form (that is part of the trick) and I have tryed the As Form pass.
I am aware of the option to incl. a listbox name to this sub etc. but this is not needed here.

So the question is still how to pass a mainform and a sub form to the sub SetUdvalg and not get an error ;-)


Herman
Say no to macros

 

[PHV]

the question is still how to pass a mainform and a sub form to the sub SetUdvalg and not get an error
Don't pass a String (ie the name of the form) but an Access.Form object, eg:
Sub SetUdvalg(Frm As Form)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hermanlaksko]

Hi PHV

Thank you for this suggestion, it does infact work.
I tryed this before, without succes some time back why it failed I'll never know, but never mind.

This solution also gives me a more elegant sub ie:

Sub SetUdvalg(Frm As Form)
Dim Re As DAO.Recordset, GemType As Integer, SNr, SQL
Select Case Frm.RapList

​

Case 94: GemType = 2

​

Case 87: GemType = 1

​

Case Else: GemType = 0
End Select
SNr = GetUser
SQL = "SELECT SetNavn FROM Project WHERE TypeNr =" & GemType & " GROUP BY SetName "
Set Re = CurrentDb.OpenRecordset(SQL)
Frm.Udvalg.RowSource = SQL
If Not IsBlank(Re!Setnavn) Then Frm.Udvalg = Re!Setnavn
End Sub

Thanks Andy I can see now that you also suggested this.

Herman
Say no to macros

 

[Andrzejek]

If it works and you are happy, you are done.

But I would still investigate passing - instead of a Form - the 2 controls that you play with. This way you can name them whatever you want on any form.

But that's juat me...

Have fun.

---- Andy