Rowsource Combobox

[Gert]

I store the contents of my comboboxes in seperate tables and initialise them
while loading the relevant form. To do so I call the sub below for every
combobox on the form. This works well for normal forms, however subforms are
not found (for unclear reasons).

I use the following call to do this:
Call InitKeuzelijst("ListName", "Name combobox", "form name")

Can someone point me in the right direction?

Thanks

Sub InitKeuzelijst(NaamLijst As String, veldnaam As String, FormNaam As
String)

Dim Sqlq As String

Sqlq = "SELECT TblKLElement.Omschrijving, TblKLElement.ElementID FROM
tblKeuzelijst "
Sqlq = Sqlq & "INNER JOIN TblKLElement ON TblKeuzelijst.KeuzelijstID =
TblKLElement.KeuzelijstID "
Sqlq = Sqlq & "WHERE (((TblKeuzelijst.Omschrijving)= " & Chr(34) &
NaamLijst & Chr(34) & "));"
Forms(FormNaam).Controls(veldnaam).RowSource = Sqlq


End Sub

Call InitKeuzelijst("Inventaris beschikbaar", "Keuzelijst met invoervak28",
"frmMaksin")

 

[Douglas J. Steele]

Subforms aren't actually part of the Forms collection. You can only refer to
them via their parent:

Forms(NameOfParentForm).Controls(NameOfSubformControlonParentForm).Form.Controls(NameOfControlOnSubform)

Note that depending on how the subform was added to the parent form, the
name of the subform control may be different than the name of the form being
used as a subform.

 

[John Spencer]

One way around this might be to pass the form and not the form name.

If you are calling the code from an event on a form or subform then you should
be able to use something like

Call InitKeuzelijst("ListName", "Name combobox", ME)

And modify your sub to

Sub InitKeuzelijst(NaamLijst As String, veldnaam As String, _
FormNaam As Form)

Dim Sqlq As String

Sqlq = "SELECT TblKLElement.Omschrijving, TblKLElement.ElementID" & _
" FROM tblKeuzelijst " & _
" INNER JOIN TblKLElement " & _
" ON TblKeuzelijst.KeuzelijstID = TblKLElement.KeuzelijstID " & _
" WHERE (((TblKeuzelijst.Omschrijving)= " & _
Chr(34 & NaamLijst & Chr(34) & "));"

FormNaam.Controls(veldnaam).RowSource = Sqlq

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Douglas J. Steele]

Or just pass a reference to the list box, rather than a reference to the
form and the name of the list box.