pass combobox to subroutine

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Hi out there,

I have a routine that takes values from a multi-column
combobox and loads them into a recordset. I need to call
this routine for different comboboxes, so instead of
hardcoding the name of the combobox in the routine, I want
to put it into an object variable and pass the variable to
the routine.

Soooo, the routine that uses the combobox variable works
fine if I initialize and set the variable within that
routine (which is interesting, but doesn't accomplish what
I need). But when I try to pass the value to the routine,
it blows up. The code that passes the value is called from
an event and looks something like this...

Private Sub MyBtn_Click()
Dim MyCombo as Combobox
set MyCombo = me.ComboBoxName
MyRoutine(MyCombo)
end sub

When I call the routine I get an "Object Required" error
on the line that calls MyRoutine. The debugger seems to
indicate that MyCombo is getting the value of what's in
the combo box and not a reference to the combo box itself.

So my conclusion is that the problem lies in passing the
parameter to the routine, like it's somehow trying to pass
by value instead of by reference. I tried specifying ByRef
in the routine that gets passed the value, but to no avail.

Any ideas??

Thanks!
Rebecca
 
Just curious; but what is the MyRoutine code....
If it is a Function, then the passed value can be in brackets, but if it's a
subroutine, the brackets aren't used. Also, what is the declaration of the
passed variable in the MyRoutine procedure... eg, is it:
Sub MyRoutine(inCombo As Combobox)

Also; the byref, means, the memory reference of the passed variable, is
used. Hence, if the value of it is changed in the called procedure, then the
calling procedure has the value changed... If it is byval, then the value of
the variable is used, so changing it in the called procedure, has no effect
on the calling procedure... This should not give 'Object Required' errors,
so doubt if that is the problem here...
EG:
X = 5
DoSomething X
MsgBox X

Sub DoSomething(ByRef inX As Long)
inX = 6
End Sub
Msgbox will now give 6

Sub DoSomething(ByVal inX As Long)
inX = 6
End Sub
Msgbox will give 5
 
D'uh... meant to add;

Is the code for 'MyRoutine' in a module, or in the form code... The reason I
ask, is that the module, needs to specify the form object, so you may need
to also pass the form object (unless all of the combo boxes are on the same
form, in which case you can hardcode the form in your module).
 
Hi,

Thanks for responding...

Here's the routine that gets the value:

Private Sub AddTicket(MyCombo As ComboBox)

Dim db As Database
Set db = CurrentDb()
Dim TransDetRst As Recordset
Set TransDetRst = db.OpenRecordset _
("TransDetail",dbOpenDynaset)

With TransDetRst
.AddNew
!ProductID = Mycombo.Column(2)
!TransactionID = [Forms]![customersFrm]!
[TransactionsFrm]![TransactionID]
!Amount = Mycombo.Column(3)
!ProductDetailID = Mycombo.Column(0)
!TransType = "Purchase"
.Update
End With

End Sub

It kind of wrapped funny when I pasted it in here, but you
get the idea. So, I'm not changing the value of the
combobox variable once it's passed. But I don't think it's
even getting that far. Seems like it's something in
declaring or setting the combobox variable in the first
place.

Thanks for your ideas...
Rebecca
 
Sorry... disregard the below (about passing the form object). It doesn't
need to be passed, unless you refer to the control on the form, as opposed
to the control that is passed...

Also, try calling MyRoutine directly with the combo box, eg;

MyRoutine Me.Combo1

Where 'Combo1' is the name of the combo box... Have tried this myself and it
works fine, with the following;

Private Sub Combo1_Click()
y Me.Combo1
End Sub

Sub y(x As ComboBox)
MsgBox x.Name
MsgBox x.Text
End Sub
 
Hmm...

Well, all the code is within the same form module, so I'm
just using Me to refer to the form. What strikes me as
curious, is when I'm in the debugger and I pass the cursor
over the line that's getting the error:

MyRoutine(MyCombo)

It shows the value of MyCombo as the value of what's in
the bound column for the combobox.

By the way, I'm using Access2000... do you think that
makes a difference?

Rebecca
 
Hmm....

Tried what you suggested and got the same error. Also, the
debugger shows the value of what's in the combobox even
when I hardcoded it in:

MyRoutine (me.ComboBoxName)

Maybe the problem has to do with the fact that it's a
multicolum combo box? I'm going to try creating a simpler
form and combo box to see if I can test that idea...

Thanks,
Rebecca
 
The code I used, worked fine, which is passing the object in much the same
way. All I can suggest are;

a. Call the sub routine, by;
AddTicket Me.Combo1




Rebecca said:
Hi,

Thanks for responding...

Here's the routine that gets the value:

Private Sub AddTicket(MyCombo As ComboBox)

Dim db As Database
Set db = CurrentDb()
Dim TransDetRst As Recordset
Set TransDetRst = db.OpenRecordset _
("TransDetail",dbOpenDynaset)

With TransDetRst
.AddNew
!ProductID = Mycombo.Column(2)
!TransactionID = [Forms]![customersFrm]!
[TransactionsFrm]![TransactionID]
!Amount = Mycombo.Column(3)
!ProductDetailID = Mycombo.Column(0)
!TransType = "Purchase"
.Update
End With

End Sub

It kind of wrapped funny when I pasted it in here, but you
get the idea. So, I'm not changing the value of the
combobox variable once it's passed. But I don't think it's
even getting that far. Seems like it's something in
declaring or setting the combobox variable in the first
place.

Thanks for your ideas...
Rebecca

-----Original Message-----
Just curious; but what is the MyRoutine code....
If it is a Function, then the passed value can be in brackets, but if it's a
subroutine, the brackets aren't used. Also, what is the declaration of the
passed variable in the MyRoutine procedure... eg, is it:
Sub MyRoutine(inCombo As Combobox)

Also; the byref, means, the memory reference of the passed variable, is
used. Hence, if the value of it is changed in the called procedure, then the
calling procedure has the value changed... If it is byval, then the value of
the variable is used, so changing it in the called procedure, has no effect
on the calling procedure... This should not give 'Object Required' errors,
so doubt if that is the problem here...
EG:
X = 5
DoSomething X
MsgBox X

Sub DoSomething(ByRef inX As Long)
inX = 6
End Sub
Msgbox will now give 6

Sub DoSomething(ByVal inX As Long)
inX = 6
End Sub
Msgbox will give 5





.
Click to expand...
Click to expand...
 
Am using Access 2000 (on XP) myself and it works fine... My combo box is a
multi column (has 3) and the following works perfectly;

Private Sub Combo1_Click()
y Me.Combo1
End Sub

Sub y(x As ComboBox)
MsgBox x.Name
MsgBox x.Text

MsgBox x.Column(0)
MsgBox x.Column(1)
MsgBox x.Column(2)
End Sub

Don't think it's a reference issue, as the objects are coming from Microsoft
Access 9.0 Object Library (though, you may wish to check this, under Tools,
References)...

Another option, is remove the brackets around the passing routine.. It
expects the return to be used, as it thinks you are calling a function, not
a sub routine. Try calling your AddTicket sub routine by;

AddTicket MyCombo
 
Not sure but try declare as Object rather than ComboBox in
both Subs to avoid the default Property "Value".

HTH
Van T. Dinh
MVP (Access)
 
Success!!!!!

OK, I have absolutely NO idea why this worked, but it did.
When I copied your code directly it worked, but when I
tried to replicate it myself it didn't. Then I noticed
that when you called MyRoutine you didn't put the passed
parameter in parentheses.

So, I tried it with no parentheses, and it worked. I
didn't even think you could pass a parameter to a routine
without putting it in parentheses. Shows you what I know!

Thanks SOOOO MUCH!!
:)
Rebecca
 
SOLVED - I tried changing my code and the following happened;

AddTicket (MyCombo)
gave error 'Object Required'

AddTicket MyCombo
worked fine....
 
Yup, functions require paranthesis, as they return a value. Sub routines
don't unless, you use the 'Call' statement....
 
Thanks!!!

Great minds think alike, I just posted the same message
somewhere else in this thread!

Thanks again for your help.... I REALLY appreciate it!
:)
Rebecca
 
Back
Top