"Invalid Qualifier" error when passing string param to function

[sunil128]

-Compile Error: Invalid Qualifier

Hi All
I have written a simple function to populate various combo boxes in my application. The comboboxes are on various seperate forms so i decided to create the function as a public function and i wrote it in a global.bas module (not sure if this was the right thing to do so let me know if you think this should be changed)

Anyway the main problem is that when I pass the combobox_name to the function e.g. "Form1.cboBranchName" i get the invalid qualifier error

For info the sStoredProcName string parameter seems to pass without problem

P.s. My Vb knoweldge is very rusty and isnt great to begin with so please can you break it down a simply as u can, and feel free to point out any other glaring errors ;-)

Code:

The called function
****************************************************
Public Function PopulateComboBoxes(sStoredProcName As String, sComboBox As String, intCol As Integer, bNotSpecified As Boolean)

Dim sqlcon3 As New adodb.Connection
Dim Rs3 As New adodb.Recordset
Dim temp1 As Variant
Dim sqlstr As String
Dim i As Integer


sqlcon3.Open "Provider=sqloledb;data source=szs005;initial catalog=CallLog;", "shoezone", "property"

With Rs3
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With

Set Rs3 = sqlcon3.Execute(sStoredProcName)

Rs3.MoveFirst
Do While Not Rs3.EOF
sComboBox.AddItem Rs3.Fields(intCol).Value
sComboBox.ItemData(sComboBox.NewIndex) = Rs3.Fields _(intCol).Value
Rs3.MoveNext
Loop

sComboBox.Text = sComboBox.List(0)
Rs3.Close
sqlcon3.Close

End Function
*****************************************************


The Calling function
******************************************************
Private Sub PopulateFilterBys()

'Get Branch List
Call PopulateComboBoxes("Exec branch", "Form1.cboBranch", 0, True)

End Sub
************************************************************

 

[keenanbr]

Public Function PopulateComboBoxes(sStoredProcName As String, sComboBox As [COLOR=red]ComboBox[/color], intCol As Integer, bNotSpecified As Boolean)


Call PopulateComboBoxes("Exec branch", [COLOR=red]Form1.cboBranch[/color], 0, True)

 

[sunil128]

Hi keenanbr, many thanks for your reply. I tried the method you suggested just before you posted it! I saw a simlar thread talking about passing the actual object rather than a string description of it.

However it still didnt work :-(

When i step thru the code and hover over 'sComboBox' in function PopulateComboBoxes it is empty i.e. sComboBox = ""

Any idea why?

 

[Andrzejek]

Since you can call this function from different Forms, try:

Public Function PopulateComboBoxes(sStoredProcName As String, [blue]fForm As Form[/blue], sComboBox As ComboBox, intCol As Integer, bNotSpecified As Boolean)


Call PopulateComboBoxes("Exec branch", [blue]Me,[/blue] cboBranch, 0, True)

Have fun.

---- Andy

 

[keenanbr]

What happens if you hover over cboBranch in the call?

 

[sunil128]

Thanks but but this does not work either!

sCombobox is still empty (i.e. sCombobox = "") and when i hover over fForm there is no context sensitve help at all!

 

[sunil128]

If I hover over cboBranch in the call it is exactly the same as it is in the called function i.e. sCombobox =

 

[keenanbr]

well, are you sure sCombobox is in fact a combobox (i.e. ther is a combobox named cboBranch on form Form1)

 

[sunil128]

Yep, cbobranch definatley does exist, i get the same thing when testing other comboboxes on the same form.

One minor thing form1 is actually saved as different name in its windows folder - it is called frmtestConnect.frm
so when u see it in the project window it looksl like this
form1(Connecttest1.frm)

 

[keenanbr]

I don't see any problem with that. I wrote a small test prog just to see if what i was telling you was correct and it works fine. can you try the same. just a form form1 with a combobox and a module with your function.

 

[Andrzejek]

OK, try this, place a combo box named Combo1 on the Form:

Option Explicit

Sub Test([blue]ByRef[/blue] cbo As ComboBox)

With cbo
    .Clear
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Three"
    .AddItem "Four"
    .ListIndex = 0
End With

End Sub

and

Option Explicit

Private Sub Form_Load()

Call Test(Combo1)

End Sub

I know this works, I just run it.
Important part is ByRef


Have fun.

---- Andy

 

[sunil128]

Hi andy thanks for that bit of code, it made the principles of whats going on much clearer. I also got it too work too without a problem

I also changed your code to match the layout of my code, so I put sub test into a global.bas file and declared it as public sub.

I then changed my actual code so that it was a also a public sub rather than a function (the only difference i could see between your & my code) but I still the same thing!

This is starting to bug me now! And you know this is just gonna a be a silly little thing

 

[BobRodes]

You would get the behavior that you describe if your stored procedure returned an empty recordset. Have you verified that it in fact returns records? If so, how have you done this?

 

[sunil128]

Hi everyone, thanks for all your previous replies, just to let you know i kinda managed to get this to work thanks to a collegues suggestion, but the solution makes no sense whatsoever!

Basically i tried to make may code as similar as possible to the example by Andrzejek, so i trimmed down my code so that i was just passing the combobox as a parameter.

The only difference we could then see between my code and Andrzejek's example was that he did not clear the text property form his combobox i.e. his still said 'combo1'

So i set my cboArea.text property to 'cboArea' (it was previously set to nothing) and it worked!

When i hovered over the sComboBox it used to say sComboBox = "",now it says sComboBox = "cboArea", but this implies to me it was always looking for string???

And the reason im confused is that i thought we were passing the combobox as an object and not the string from te combo.text property!

But whats even more confusing is this, just as a test i cleared the @combo1.text' property in Andrzejek's so that it was empty, when hovering over the parameter 'cbo' is said cbo="" so i was fully expecting it to fail as my code did before, but it still worked!

 

[strongm]

>When i hovered

Start with this insight: hovering over a variable referencing a combobox will show the value of the default property of the combobox, which is .Text