Open a form through combo boxes (1 Viewer)

[chohan78]

hi,

I have designed a main form which have buttons for about 30 different companies name to open, each button opens an individual company form. Now the comnpanies list is growing day by day so i want to create a combo box which select the company name and open the relevant company's form.
could anyone help me in this regard, do i need to create a table which lists all the companies and then add a combo box on a main screen which select the company name and open the form but i need a VBA code for that.
please help in this matter.

 

[pr2-eugin]

Hello chohan78, Welcome to AWF..

You are correct in thinking that you need a table. This will help you keep all company names intact, even if the list grows you will be able to just add them, remove them or change them without going through a lot of hassle.

Basically this is what you need. Create a table, say tblCompany with nothing much but only three (or Four) fields,
tblCompany
compID - AutoNumber - Primary Key
companyName - Text
formName - Text
companyActive - Yes/No

the last one is completely optional. This is because, you can just tick them if they are an active client of your. If it is False it means they are not active. So create the ComboBox based on the table above in the form. Select all fields into the Query. Say your Query is somethign like..

SELECT compID, companyName, formName 
FROM tblCompany
[COLOR=Blue][I]WHERE companyActive = True[/I][/COLOR]
ORDER BY companyName;

Then adjust your column widths to show only the Company name.
ColumnCount : 3
ColumnWidth : 0cm;2.45cm;0cm
Bound Column: 1

Finally on the click of the button, have code like..

Private Sub buttonName_Click()
    If Me.comboBoxName.ListIndex = -1 Then Exit Sub
    DoCmd.OpenForm Me.comboBoxName.Column(2)
End Sub

 

[chohan78]

Hi
Thanks for that but in the last line Me.comboBoxName.Column(2) which column are you using for reference.
Thanks

 

[pr2-eugin]

I am refering to the Third Column. ComboBoxes in VBA are Zero based, by which I mean 0 is First Column, 1 is second column, 2 is the third column and so on.. Sorry I forgot to mention that in my post earlier.

 

[chohan78]

Hi,
Thanks again I have used the exactly same code but it is not working...

 

[pr2-eugin]

Could you show some sample data in the tblCompany, RowSource of the ComboBox, ComboBox properties that I mentioned in Post#2.

PS: Please refrain from using the phrases it is not working, Explain exactly "WHAT" is not working?

Is there any Error? #Name Error? Compiler Error? Wrong value picked up?

 

[chohan78]

hi,

i have created the table of tblCompany and added data for the company names and the relevant forms names in a formname column which i want to open. created a form based on the table and hide all the fileds. Draw a combo box with the name combo_01 on the form and added the below in Row source properties of the combo box:
SELECT [tblCompany].[compID], [tblCompany].[companyName], [tblCompany].[formName] FROM tblCompany;

Then i created a button and on its click event i added the following code:

Private Sub Command10_Click()
If Me.combo_01.ListIndex = -1 Then Exit Sub
DoCmd.OpenForm Me.combo_01_Label.Column(2)
End Sub

It gives me an error message""" Compile Error: - Method or Data memeber not found

could you please help in this one.

thanks

 

[pr2-eugin]

Why are you referencing the ComboBox Label when you need is the ComboBox?

Private Sub Command10_Click()
    If Me.combo_01.ListIndex = -1 Then Exit Sub
    DoCmd.OpenForm Me.combo_01.Column(2)
End Sub

 

[chohan78]

hi,

getting again error message on first line in .combo_01 ???

 

[chohan78]

thanks..... just spotted... i entered wrong name to the combo box just check the properties and it is combo8 not combo_01.... thanks it start working....

great stuff...

 

[pr2-eugin]

Glad you have it working.. Good Luck..

 

[chohan78]

hi,
Need customers in ascending Order ??? shall i make changes in the record source property of combo box " By Ascending" ???

 

[pr2-eugin]

Yes, something like..

SELECT [tblCompany].[compID], [tblCompany].[companyName], [tblCompany].[formName] 
FROM tblCompany
ORDER BY [tblCompany].[companyName];

 

[chohan78]

Excellent !
thanks

 

[pr2-eugin]

No problemo.

 

[chohan78]

hi,

how to clear the combo box after selection ???