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
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!
Access 2003, VBA
November 20, 2009 4:24 PM
November 20, 2009 9:47 PM