Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding options to a combobox - Access95

Posted on 1998-08-02
7
Medium Priority
?
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 

Expert Comment

by:chanlaw
ID: 1957858
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
ID: 1957859
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 360 total points
ID: 1957860
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Expert Comment

by:chanlaw
ID: 1957861
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
ID: 1957862
Nice one !
0 should be changed to '*' thought.
0
 

Author Comment

by:sbishop
ID: 1957863
Thanks Chanlaw, I used your method.  Much eaiser!

0
 

Expert Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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