combo box problems

  • Thread starter Claire Rohan via AccessMonster.com
  • Start date
C

Claire Rohan via AccessMonster.com

hi there,
I have a combo box in the form header of my main form which I wish to use to
select a record. I use the combo box wizard to create it and selected the
"find a record based on..." option. the problem is that when the user clicks
on the down arrow of the combo box, while it opens to show the values, the
user cannot select a value so the correct record is not then shown..
I would also like to know how to use a filter so that only the records in
which a certain field (called closedate) is null, are available for the user
to scroll through??
many thanks,
claire rohan
 
K

Ken Sheridan

Claire:

Lets take a simple example. Say you have a table Contracts with columns
amongst others of contacted (a numeric primary key), contracttitle (text) and
your closedate column (date/time). To get the form to show only those rows
where the closedate column is Null you'd base the form on a query:

SELECT *
FROM Contracts
WHERE closedate IS NULL
ORDER BY contracttitle;

The combo box in the form's header to enable you to select a record by
contacttitle from all non-Null closedate contacts would have a RowSource
property of:

SELECT contractID, contracttitle
FROM Contracts
WHERE closedate IS NULL
ORDER BY contracttitle;

The properties of this combo box would be:

ControlSource: leave blank
BoundColumn: 1
ColumnCount 2
ColumnWidths 0cm;8cm or rough equivalent in inches

The first dimension of the last property must be zero to hide the first
column, the second dimension isn't crucial so long as its at least as wide as
the control.

In the AfterUpdate event procedure of the combo box put the following code:

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "contractID = " & cboFindContract
Me.Bookmark = rst.Bookmark

where cboFindContract is the name of the combo box.

When a user selects an item from the combo box the code will execute and
find the matching record in the clone of the form's underlying recordset. It
then sets the form's Bookmark property to that of the recordset clone, which
moves the form to the first matching contractID.

You can add an extra refinement by putting the following in the form's
Current event procedure:

Me.cboFindContract = Me.contractID

This keeps the combo box in sync with the form if the user navigates to a
different contract record with the built in navigation buttons or the
keyboard.

Ken Sheridan
Stafford, England
 
Top