Setting the Combo Box Default Value

[Joy Mendleson]

Hi there,

I would like to enhance my combo box by changing the default value in the
following way, if it is possible...

The form is called Course Information.

Here is some sample data:

PrimaryKey Course No. Start Date Course Name
------------ ----------- ----------- -------------
1 11 03-May-04 Fire Prevention
2 11 10-May-04 Fire Prevention
3 12 04-May-04 Ladders
4 01 05-May-04 Smoke Detectors

The values in my combo box are sorted by Course No. (A) Start Date (A).
The primary key is not displayed, but it is used to put the selected record
in text boxes.
The query is primary key, Course No (ASC), Course Start Date (ASC) and
Course Name.

So right now, when I open the form the value displayed in the combo box is
Course No. 11,
and when I click the arrow, the box opens at 11 03-May-04 Fire
Prevention.
I assume it does this because this entry has primary key = 1.

I would like my combo box to display course 01, it just seems more
intuitive to display the first course number (which can change),
and scroll down, than to display a number in the middle!

Any thoughts? Thanks!

Joy

 

[Ruskin]

If you have a look at your 'Properties' box, for the combo box, there is a
'Default Value' setting. Have you tried using this...

NOTE: I would suggest, not setting it to '01', unless your course numbers
don't change.. It would be better to set it to something like;
=DMin("[Course Number Field]","Course Table")

 

[Dirk Goldgar]

Hi there,

I would like to enhance my combo box by changing the default value in
the following way, if it is possible...

The form is called Course Information.

Here is some sample data:

PrimaryKey Course No. Start Date Course Name
------------ ----------- ----------- -------------
1 11 03-May-04 Fire
Prevention 2 11 10-May-04
Fire Prevention 3 12
04-May-04 Ladders 4 01
05-May-04 Smoke Detectors

The values in my combo box are sorted by Course No. (A) Start Date
(A). The primary key is not displayed, but it is used to put the
selected record in text boxes.
The query is primary key, Course No (ASC), Course Start Date (ASC) and
Course Name.

So right now, when I open the form the value displayed in the combo
box is Course No. 11,
and when I click the arrow, the box opens at 11 03-May-04 Fire
Prevention.
I assume it does this because this entry has primary key = 1.

I would like my combo box to display course 01, it just seems more
intuitive to display the first course number (which can change),
and scroll down, than to display a number in the middle!

Any thoughts? Thanks!

Joy

It sounds to me like your first concern is to get the combo box's list
in the right order. You can do this quite easily by basing your combo
box, not on the table directly, but on a query that sorts the table into
ascending sequence on the Course No. field.

I'm puzzled, though, by some things you said about the way the form and
combo box are behaving now. Is this a bound form? If so, what is its
Record Source? Is the combo box a bound control? If so, what is its
Control Source? Is the Row Source of the combo box the same as the
Record Source of the form?

 

[Joy Mendleson]

Hello,

Thank you for fantasticly quick reply; I am still a little stuck!

Private Sub Form_Open(Cancel As Integer)
MsgBox ("the value is " & DMin("[Course Number]", "course table"))
End Sub

This worked. the value is 001

Now using the Combo Box Property:
Default Value =DMin("[Course Number]","course table")
It didn't work. It still shows 011
Maybe you can't use this expression here, or maybe my syntax is wrong.
....Crazy!

SELECT [course table].[Course ID], [course table].[Course Number], [course
table].[Course Start Date], [course table].[Course Name] FROM [course table]
ORDER BY [course table].[Course Number], [course table].[Course Start Date];

That is the query.

Any ideas? Thanks!

Joy

 

[Ruskin]

Sorry Joy,

Just realised, this probably wont work.... Showing DMIN in a message box,
will be fine, as it finds the minimum value of the course number. But the
combo box bound column, is actually the primary key (in this case, number
4)... Hence, you may have to do something like;

Private Sub Form_Open(Cancel As Integer)
Dim minCourse As String

minCourse = DMin("[Course Number]", "course table")
cboCombo.DefaultValue = DLookup("[PrimaryKey]", "course table", _
"[course number]='" & minCourse & "'")

End Sub

 

[Joy Mendleson]

Hi Dirk,

Bound Form ( not sure where I check this)
Form Record Source is: course table

Bound Combo Box ( not sure where I check this)
Combo Box Control Source is [blank]
Row Source of Combo Box is SELECT [course table].[Course ID], [course
table].[Course Number], [course table].[Course Start Date], [course
table].[Course Name] FROM [course table] ORDER BY [course table].[Course
Number], [course table].[Course Start Date];

There are additional fields in the [course table] that aren't in the SQL
query.

Thanks for looking at this,

Joy

 

[Joy Mendleson]

Hi again,

Thanks for your time. Here is my amended code:
It still gives 011 when I open the form.


Private Sub Form_Open(Cancel As Integer)
Dim minCourse As String

MsgBox ("the value is " & DMin("[Course Number]", "course table"))

minCourse = DMin("[Course Number]", "course table")

cboSelectCourse.DefaultValue = DLookup("[Course ID]", "course table", _
"[Course Number]='" & minCourse & "'")
End Sub

Private Sub Form_Current()
'Move to a new record
'Synchronize the combo box to the Course record
'This line is needed when you use the navigation bar to change records
Me.cboSelectCourse = Me.[Course ID]

If Not Me.NewRecord Then 'At a different record, it is locked
Me.AllowEdits = False 'Disallow Edits
Me.lblEditMode.Caption = "LOCKED"
Me.cmdLockUnlock.Caption = "&Unlock"
Me.cmdAddNew.Enabled = True
Me.cmdSave.Enabled = False
Me.cmdDelete.Enabled = False
Else 'If the record is new, then allow
edits
Me.AllowEdits = True 'Allow Edits
Me.lblEditMode.Caption = "UNLOCKED"
Me.cmdLockUnlock.Caption = "&Lock"
Me.cmdAddNew.Enabled = False
Me.cmdSave.Enabled = True
Me.cmdDelete.Enabled = False
End If

Me.cmdUndo.Enabled = False
End Sub

It is 1245 AM so I will look for your answer later. Thanks so much!

Joy

 

[Dirk Goldgar]

Bound Form ( not sure where I check this)

If the form has a record source, it is "bound" -- that is, bound to a
table or query.

Form Record Source is: course table

So the form is bound.

Bound Combo Box ( not sure where I check this)

If a control has a Control Source, it is "bound" -- that is, bound to a
particular field in the record source.

Combo Box Control Source is [blank]

So the combo box is unbound.

Row Source of Combo Box is SELECT [course table].[Course ID], [course
table].[Course Number], [course table].[Course Start Date], [course
table].[Course Name] FROM [course table] ORDER BY [course
table].[Course Number], [course table].[Course Start Date];

There are additional fields in the [course table] that aren't in the
SQL query.

This query will sort the combo box's list in ascending order by [Course
Number] and [Course Start Date], so the list *should* be in the sequence
you want. Are you saying that it isn't? Note that the list sequence
itself has no effect on the default value of the combo box (unless you
write code to make it so).

What are the combo box's Column Count and Column Widths properties?
Column Count *should* be 4.

What is this combo box being used for? To look up a course and position
the form to the record for that course? Does that function work?

 

[Joy Mendleson]

Hi,

Thanks Mr./Ms. Ruskin for your help!! You brightened my day!! Your idea
worked and here is the code for posterity...

Private Sub Form_Open(Cancel As Integer)

Dim minCourse As String
Dim rs As Object

minCourse = DMin("[Course Number]", "course table")

cboSelectCourse.DefaultValue = DLookup("[Course ID]", "course table", _
"[Course Number]='" & minCourse & "'")

' Find the record that matches the control.

Set rs = Me.Recordset.Clone
rs.FindFirst "[Course ID] = " & Str(Me![cboSelectCourse])
Me.Bookmark = rs.Bookmark
End Sub

Regards,

Joy