LINK ITEMS IN COMBO BOX (NOT LINK BOX)

[baybie]

HELLO GUYS, IN RESPONSE TO:

I would like to know how to link items on a list box to say a query, form or a report. I have a list box with items like: Search - By Customer, By Company Name by Product. I would specifically like to know how to link these items on the list box to the related forms or queries
The list box located on the main siwtchboard form would have 4 sections/headings, Orders, Search, View and Reports and would be arranged in the following order:

Order

Search By Customer name
By Company Name
By Channel Partner

View

Products
Payment Methods
Shipping Methods
Employees

Reports

Sales By Customer
Sales By Employee
Sales By Product Sales
By Channel Partner

everything in the list box with the exception of the headings would be linked to respective forms or reports.

I WAS GIVEN THE FOLLOWING:

Create a table named "tblMenuItems" with the following columns:
Field Name Data Type Description
---------- --------- -----------
Sequence Number(Integer) <- Primary key
MenuItem Text Text to show in list box
ItemType Text(1) F=Form, R=Report
ItemName Text Form/Report name
You could enter this information directly into the list box if you wanted, but keeping it in a table makes it easier to make changes later. The Sequence field lets you change the order of items in the list, or insert new items in the middle.

In your list box control (let's call it lstMenu), set the following properties:
Row Source Type: Table/Query
Row Source: tblMenuItems
Column Count: 4
Column Width: 0;;0;0
Bound Column: 1

In lstMenu's DoubleClick event, use code like the following to open the form or report:
Private Sub lstMenu_DoubleClick()
Dim strType As String, strName As String

With lstMenu
strType = .Column(2, .ListIndex)
strName = .Column(3, .ListIndex)
Select Case strType
Case &quot;F&quot;
DoCmd.OpenForm strName
Case &quot;R&quot;
DoCmd.OpenReport strName
End Select
End With
End Sub

BEING A NOVICE IN V.B:
WITH REGARDS TO THE LAST BIT CONCERNING THE EVENT PROCEDURE, WOULD I HAVE TO CREATE THE SAME PROCEDURE FOR EACH ITEM IN THE COMBO BOX? I KNOW I SAID LINK BOX ABOVE BUT WHAT I MEANT WAS COMBO BOX.

 

[Darrylles]

Hi bay,

No, you would copy and paste this code into the lstmenu listbox 'double-click' event. (A combo is simply an enhanced listbox).

Which means that this code would run whenever ANY item in the listbox (or combo) was double-clicked.

This answer ya?

Why didn't you just add another question to your original question?
If you lost it, then click the 'tick' icon on the Tek-Tips first page to display all posts concerning you (or your log-in). This'll list all your entries.

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[baybie]

Thanks Darry, i didn't paste into the original thread because people tend to bypass your thread once they see that it's been replied to.
I was aware that you paste the code into the On doubleClick event procedure, sorry if i didn't make it clear (I do that sometimes)but the confusing part was whether or not i should have this bit of the code:

strType = .Column(2, .ListIndex)
strName = .Column(3, .ListIndex)
Select Case strType
Case &quot;F&quot;
DoCmd.OpenForm strName
Case &quot;R&quot;
DoCmd.OpenReport strName


for each item in the combo box or will this one bit of code suffice for all the items, I thought it only made reference to one item in the list and the form/Report that it's linked to?

 

[Darrylles]

Hi bay,

No the above code takes the item 'Name' that is currently highlighted in the combo along with it's type 'F' for form, 'R' for report.

Dependant on the R/F it then runs the Report or Form with the highlighted name.

This enables you to add many forms/reports in the future to the table - the combo picks up and displays these and this code will work as it is without any changes.

Does that answer your question?

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; AND &quot;A pointer to a solution is better than the solution - you'll remember the solution via a pointer&quot;. darrylles@totalise.co.uk

 

[baybie]

Thanks Darrylle, haven't replied since as i've been away.

 

[baybie]

After almost a month i've finally tried this and it hasn't worked...
When you double click on the item in the combo box i get, Runtime error 424 Object Required. When i debug, this is what's hightlighted in yellow from the On DblClick event:

strType = .Column(2, .ListIndex)
strName = .Column(3, .ListIndex)