Solved

Adding options to a combobox - Access95

Posted on 1998-08-02
7
359 Views
Last Modified: 2008-02-26
My problem is this.  I have a dropdown combobox with a list of options that have been pulled from a table.  This will be an argument for a query.  The problem is I want to add the option of "ALL" into the combobox(as in wanting to query all of the values in the combobox), but I don't want to hard code the table's values into it the combobox(it must go to the database to get the most up-to-date values, as they can change.)

Also, is there anyway that you can start an Access DB with the Access95 program minimised so it can't be seen(just the forms visible)
0
Comment
Question by:sbishop
  • 4
  • 2
7 Comments
 

Expert Comment

by:chanlaw
Comment Utility
sbishop,

Your can find your solution on the Access help file. Type AddAllToList function in the Index Tab-page under help.
Your can also find an example on the sample database "Developer solutions" database which comes with MS Access 97. (It should also be included in access 95.) Open the DeveloperSolutions Form. Select "Work with combo boxes, list boxes, ...", and then choose All "(all)" to a list.

Cheers,
Kinfai.
0
 

Author Comment

by:sbishop
Comment Utility
Kinfai,
That doesn't work Kinfai.  Help returns nothing similar to AddAllToList.  I am only running Access95 and it's on a Uni computer, so they don't have any example databases to look at.
Where would this function be written?  In the combobox code for what, OnClick? Thanks.

sbishop
0
 

Accepted Solution

by:
chanlaw earned 120 total points
Comment Utility
sbishop,

OK, here is the electronic version of the help file :

When you use a list box or combo box to enter selection criteria, you might want to be able to specify all records. On the AddAllToList form, the list in the SelectCustomer combo box includes "(All)."

Overview
Create a function that adds "(All)" to the list in a list box or combo box. Create a list box or combo box, and set its RowSourceType property to the name of the function.

Objects Used in This Example

Object      Name
Form      AddAllToList
Module      AddAllToListModule
Step by Step

1      Create the AddAllToListModule module.
a)      Create the AddAllToList function. (see below)

2      Create the AddAllToList form.
a)      Set the following form properties.

Property      Setting
RecordSource      Customers
Caption      Add All To List
DefaultView      Single Form
ViewsAllowed      Form
ScrollBars      Neither
RecordSelectors      No
AutoCenter      Yes
ShortcutMenu      No
b)      Add a form header and footer to the form. Set the Height property of the form footer to 0".
c)      Create a combo box in the form header, and set its properties as follows.

Property      Setting
Name      SelectCustomer
RowSourceType      AddAllToList
RowSource      CustomerList
ColumnCount      2
ColumnWidths      3";0"
BoundColumn      2
LimitToList      Yes
Tag      1
AfterUpdate      [Event Procedure]
d)      In the detail section of the form, add text boxes for the CustomerID, CompanyName, ContactName, Phone, and Fax fields.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access 97.

Subject      Chapter
Creating event procedures      Chapter 2, "Introducing Visual Basic"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function AddAllToList(ctl As Control, lngID As Long, lngRow As Long, _
            lngCol As Long, intCode As Integer) As Variant

      ' Adds "(All)" to the top of a combo box or list box.

      ' You can add "(All)" in a different column of the combo box
      ' or list box by setting the control's Tag property to a different
      ' column number, or display text other than "(All)" by appending
      ' a semicolon(;) and the text you want to display. For example,
      ' setting the Tag property to "2;<None>" displays "<None>"

' in the second column of the list.

            Static dbs As Database, rst As Recordset
            Static lngDisplayID As Long
            Static intDisplayCol As Integer
            Static strDisplayText As String
            Dim intSemiColon As Integer

      On Error GoTo Err_AddAllToList
            Select Case intCode
                  Case acLBInitialize
                        ' See if function is already in use.
                        If lngDisplayID <> 0 Then
                              MsgBox "AddAllToList is already in use by another control!"
                              AddAllToList = False

Exit Function
                        End If

                        ' Parse the display column and display text from Tag property.
                        intDisplayCol = 1
                        strDisplayText = "(All)"
                        If Not IsNull(ctl.Tag) Then
                              intSemiColon = InStr(ctl.Tag, ";")
                              If intSemiColon = 0 Then
                                    intDisplayCol = Val(ctl.Tag)
                              Else
                                    intDisplayCol = Val(Left(ctl.Tag, intSemiColon - 1))
                                    strDisplayText = Mid(ctl.Tag, intSemiColon + 1)

End If
                        End If

                        ' Open the recordset defined in the RowSource property.
                        Set dbs = CurrentDb
                        Set rst = dbs.OpenRecordset(ctl.RowSource, dbOpenSnapshot)

                        ' Record and return the lngID for this function.
                        lngDisplayID = Timer
                        AddAllToList = lngDisplayID

                  Case acLBOpen
                        AddAllToList = lngDisplayID

                  Case acLBGetRowCount
                        ' Return number of rows in recordset.
                        On Error Resume Next

rst.MoveLast
                        AddAllToList = rst.RecordCount + 1

                  Case acLBGetColumnCount
                        ' Return number of fields (columns) in recordset.
                        AddAllToList = rst.Fields.Count

                  Case acLBGetColumnWidth
                        AddAllToList = -1

                  Case acLBGetValue
                        If lngRow = 0 Then
                              If lngCol = intDisplayCol - 1 Then
                                    AddAllToList = strDisplayText
                              Else
                                    AddAllToList = Null
                              End If
                        Else

rst.MoveFirst
                              rst.Move lngRow - 1
                              AddAllToList = rst(lngCol)
                        End If
                  Case acLBEnd
                        lngDisplayID = 0
                        rst.Close
            End Select

Bye_AddAllToList:
      Exit Function

Err_AddAllToList:
      MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList"
      AddAllToList = False
      Resume Bye_AddAllToList
End Function

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:chanlaw
Comment Utility
OK, sbishop,

Here is a much simpler way to solve your problem :

For example, you have a table Customers. In RowSource property of control you write:

SELECT id,name FROM Customers UNION SELECT 0 AS id,'(All)' AS
name FROM Customers ORDER BY name

Thats all! no terrific functions, no errors, no headache.

The solution was suggested by a clever guy called Dmitry Aupuchtin.
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Nice one !
0 should be changed to '*' thought.
0
 

Author Comment

by:sbishop
Comment Utility
Thanks Chanlaw, I used your method.  Much eaiser!

0
 

Expert Comment

by:chanlaw
Comment Utility
No need to thank me. If you do appreciate, send compliments to Dmitry Apuchtin:  damian@ems.ru
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now