Home > IT Answers > Development > Issue with a sub procedure and its arguments.
McNitro
190 pts.
Q:
Issue with a sub procedure and its arguments.
Access 2003 forms, VBA, ComboBox, byRef variables, sub procedure, arguments
I'm trying to build a sub procedure to create a list for a combobox.  Basically, the db has sererate fields in the client table for first and last names and I want to create a combobox that lists each in one line. The real point of the question is whether or not I can build a sub procedure so I can call that procedure (create the list) at any time without having to write it over and over again. 

Below is the call from the forms sub procedure, where 'ListGenerators' is the module name, 'CreateContactsCombo' is the sub name, and the arguments are the Form name and existing comboboxes to be used to build the contacts list. (i.e. the results from cboFirstName and cboLastName are combined to create a list of full names in cboContactName)

   

 Call ListGenerators.CreateContactsCombo(Forms(ProjectLogin), cboFirstName, cboLastName, cboContactName)

 




 Public Sub CreateContactsCombo(frmName As Form, cboFirst As _ ComboBox, cboLast As ComboBox, cboFull As ComboBox)

     MsgBox cboFirst.Name     MsgBox cboLast.Name     MsgBox cboFull.Name     MsgBox frmName.Name

          NoOfContacts = frmName!cboLast.ListCount     RowPosition = 0     Forms(frmName).cbo.AddItem ""     Do Until RowPosition = NoOfContacts         With Forms(frmName)             ContactFullName = Forms(frmName).cboFirst.Value & " " & _                    Forms(frmName).cboLast.Value             .cboFull.AddItem (ContactFullName)             RowPosition = RowPosition + 1             .cboFirst = Forms(frmName).cboFirst.ItemData(RowPosition)             .cboLast = Forms(frmName).cboLast.ItemData(RowPosition)         End With     Loop

 End Sub


The message boxes in this code are for information only...to see if the byRef variables are being brought into the procedure correctly...which they seem to be.  I'm running into an issue with the first line after the message boxes. With frmName!cboLast.ListCount I get a runtime error '2645': "can't find the field 'cboLast' referred to in your expression. I don't understand this considering "MsgBox cboLast.Name" give me the correct reference of "cboLastName".

I've also tried to us define frmName as string, and my code read "Forms(frmName).cboLast.ListCount". When using this I get the error "Application-defined or object-defined error"

I'm kind of playing here.  I can write the code into the forms procedure without using a call to another procedure, but I thought it would be nice to use a seperate sub. I may want to call it from other procedures in the future, might make my code less "cleaner", and save me some time.

Thanks in advance!



Software/Hardware used:
Access 2003, VBA
ASKED: Nov 20 2009  4:24 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
McNitro
190 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
Last Answered: Nov 20 2009  4:24 PM GMT by McNitro   190 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   32725 pts.  |   Nov 20 2009  6:50PM GMT

I have never worked with access, but it seems to me that your message boxes are not helping at all, because you are displaying information from the combos the procedure receives as parameters, but in the rest of your code you are not using them.

In other words, inside the procedure:

frmName!cboLast <> cboLast

If you are going to use the combos this way (in the case that that is possible, which I don’t really know) inside the procedure:

frmName!cboLast

Then you should not need to send them as parameters to the procedure (and vice versa).

 

McNitro   190 pts.  |   Nov 20 2009  9:20PM GMT

But I think the message boxes are showing me the information I want to use. When I run the code the message boxes provide me with the following:

MsgBox <a href="http://cboFirst.Name" title="http://cboFirst. " target="_blank">cboFirst.Name</a> = cboFirstName
MsgBox <a href="http://cboLast.Name" title="http://cboLast. " target="_blank">cboLast.Name</a> = cboLastName
MsgBox <a href="http://cboFull.Name" title="http://cboFull. " target="_blank">cboFull.Name</a> = cboContactName
MsgBox <a href="http://frmName.Name" title="http://frmName. " target="_blank">frmName.Name</a> = ProjectLogin

So…

Shouldn’t frmName!cboLast = Form(ProjectLogin)!cboLastName ????

 

McNitro   190 pts.  |   Nov 20 2009  9:24PM GMT

Sorry…forgot to use the text editor. One of those days. :)
My message boxes give me the following:

MsgBox <a href="http://cboFirst.Name" title="http://cboFirst. " target="_blank">cboFirst.Name</a> = cboFirstName
MsgBox <a href="http://cboLast.Name" title="http://cboLast. " target="_blank">cboLast.Name</a> = cboLastName
MsgBox <a href="http://cboFull.Name" title="http://cboFull. " target="_blank">cboFull.Name</a> = cboContactName
MsgBox <a href="http://frmName.Name" title="http://frmName. " target="_blank">frmName.Name</a> = ProjectLogin

 

McNitro   190 pts.  |   Nov 20 2009  9:25PM GMT

Ugh…still trying to figure out these message boards. Sorry

 

Carlosdl   32725 pts.  |   Nov 20 2009  9:47PM GMT

Shouldn’t frmName!cboLast = Form(ProjectLogin)!cboLastName ????

I don’t think so.

cboLast = Form(ProjectLogin)!cboLastName

and

frmName!cboLastName = Form(ProjectLogin)!cboLastName

But I guess frmName!cboLast doesn’t exist.