Combo Box -- How do I...

[Erich Kohl]

Okay, here's the deal:

Let's say a form is based on a table.

This form has Field1 (PrimeKey), Field2, Field3, etc.

This form also has a subform which shows related records in another
table. The main fields on the main form (Field1, Field2, etc.) are
simply there for show, not for editing.

Now, I would like to set Field1 as a Combo Box so that the user
can simply select the value of this field from its list. I want to
offer this because I think there's a chance my end users will not
always want to do a filter.

Upon selecting from the Combo Box, all the other displayed fields on
the form should change to reflect the current record being displayed.

But it seems that no matter what I try -- which so far mainly has been
binding the Combo Box to the field in question and loading the rows
with the values in that field -- Access thinks that I'm trying to
*change* the contents of that field. This is not what I want. I
simply want the user to select one of the items as a means of
displaying that particular record.

And when I set the Locked property of the Combo Box to True, Access
still displays the list, but it won't let me pick one.

How do I solve this? Do I need to write code to do what I want?

Thanx in advance!

[Anthony England]

"Erich Kohl" <ek***@sbcglobal.net> wrote in message
news:e6********************************@4ax.com...

Okay, here's the deal:

Let's say a form is based on a table.

This form has Field1 (PrimeKey), Field2, Field3, etc.

This form also has a subform which shows related records in another
table. The main fields on the main form (Field1, Field2, etc.) are
simply there for show, not for editing.

Now, I would like to set Field1 as a Combo Box so that the user
can simply select the value of this field from its list. I want to
offer this because I think there's a chance my end users will not
always want to do a filter.

Upon selecting from the Combo Box, all the other displayed fields on
the form should change to reflect the current record being displayed.

But it seems that no matter what I try -- which so far mainly has been
binding the Combo Box to the field in question and loading the rows
with the values in that field -- Access thinks that I'm trying to
*change* the contents of that field. This is not what I want. I
simply want the user to select one of the items as a means of
displaying that particular record.

And when I set the Locked property of the Combo Box to True, Access
still displays the list, but it won't let me pick one.

How do I solve this? Do I need to write code to do what I want?

Thanx in advance!

The key to this trick is that the combobox needs to be unbound - that is
your combobox should have a blank ControlSource. Then you write code in the
AfterUpdate event to say what should happen.
Perhaps the form will be re-set to have one record: "SELECT * FROM MyTable
WHERE ID=237"
Perhaps you will only filter on this record
Perhaps you leave all records showing - just go to the selected record.

[Erich Kohl]

On Mon, 3 Apr 2006 23:06:47 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

The key to this trick is that the combobox needs to be unbound - that is
your combobox should have a blank ControlSource. Then you write code in the
AfterUpdate event to say what should happen.
Perhaps the form will be re-set to have one record: "SELECT * FROM MyTable
WHERE ID=237"
Perhaps you will only filter on this record
Perhaps you leave all records showing - just go to the selected record.

I got it to work, however...

How do I get it so that _all_ the records continue to be displayed,
instead of just the one?

I put in a SELECT statement inside AfterUpdate, however it causes the
recordset to filter down to just that single record with the right ID.

Can I navigate somehow -- inside the code -- with a FindFirst type of
method?

Thanks again for your help.

[Anthony England]

"Erich Kohl" <ek***@sbcglobal.net> wrote in message
news:9e********************************@4ax.com...

On Mon, 3 Apr 2006 23:06:47 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

The key to this trick is that the combobox needs to be unbound - that is
your combobox should have a blank ControlSource. Then you write code in
the
AfterUpdate event to say what should happen.
Perhaps the form will be re-set to have one record: "SELECT * FROM MyTable
WHERE ID=237"
Perhaps you will only filter on this record
Perhaps you leave all records showing - just go to the selected record.

I got it to work, however...

How do I get it so that _all_ the records continue to be displayed,
instead of just the one?

I put in a SELECT statement inside AfterUpdate, however it causes the
recordset to filter down to just that single record with the right ID.

Can I navigate somehow -- inside the code -- with a FindFirst type of
method?

Thanks again for your help.

FindFirst is exactly what you should use.
For example, if you had a table "tblContact" with an autonumber primary-key
field called "ConID" you would create a combobox on the form with no
ControlSource, ConID as the first column and a BoundColumn of 1. If the
combo was named "cboConID" the code in the AfterUpdate event might be:
Private Sub cboConID_AfterUpdate()

On Error GoTo Err_Handler

Dim lngConID As Long

lngConID = CLng(Nz(Me.cboConID.Value, 0))

If lngConID > 0 Then
With Me.RecordsetClone
.FindFirst "ConID=" & CStr(lngConID)
If .NoMatch Then
MsgBox "Cannot locate record" & vbCrLf & _
"ConID=" & CStr(lngConID), _
vbExclamation
Else
Me.Recordset.Bookmark = .Bookmark
End If
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Erich Kohl]

On Tue, 4 Apr 2006 09:28:19 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

FindFirst is exactly what you should use.
For example, if you had a table "tblContact" with an autonumber primary-key
field called "ConID" you would create a combobox on the form with no
ControlSource, ConID as the first column and a BoundColumn of 1. If the
combo was named "cboConID" the code in the AfterUpdate event might be:

Private Sub cboConID_AfterUpdate()

End Sub

I coded it the way you described, and it works perfectly.

Now, uh . . . heh . . . one more question (and then I'll leave you
alone, I promise!). ;-)

What event occurs when the user clicks on the record navigation
buttons? I want the text in the combo box to change when that
happens.

[Anthony England]

"Erich Kohl" <ek***@sbcglobal.net> wrote in message
news:de********************************@4ax.com...

On Tue, 4 Apr 2006 09:28:19 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

FindFirst is exactly what you should use.
For example, if you had a table "tblContact" with an autonumber
primary-key
field called "ConID" you would create a combobox on the form with no
ControlSource, ConID as the first column and a BoundColumn of 1. If the
combo was named "cboConID" the code in the AfterUpdate event might be:

Private Sub cboConID_AfterUpdate()

End Sub

I coded it the way you described, and it works perfectly.

Now, uh . . . heh . . . one more question (and then I'll leave you
alone, I promise!). ;-)

What event occurs when the user clicks on the record navigation
buttons? I want the text in the combo box to change when that
happens.

Don't worry about asking - I don't mind answering.
The event you are looking for is the Current event, and you could write
something like this to make sure the combobox stays synchronized with the
current record. It basically says, when a record becomes current check to
see if the id of the combobox matches the id of the record. If not change
the value of the combobox so it matches:
Private Sub Form_Current()

On Error GoTo Err_Handler

Dim lngConID As Long

lngConID = CLng(Nz(Me!ConID, 0))

If lngConID > 0 Then
If Nz(Me.cboConID, 0) <> lngConID Then
Me.cboConID = lngConID
End If
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Bob Quintal]

Erich Kohl <ek***@sbcglobal.net> wrote in
news:de********************************@4ax.com:

On Tue, 4 Apr 2006 09:28:19 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

FindFirst is exactly what you should use.
For example, if you had a table "tblContact" with an
autonumber primary-key field called "ConID" you would create a
combobox on the form with no ControlSource, ConID as the first
column and a BoundColumn of 1. If the combo was named
"cboConID" the code in the AfterUpdate event might be:

Private Sub cboConID_AfterUpdate()

End Sub

I coded it the way you described, and it works perfectly.

Now, uh . . . heh . . . one more question (and then I'll leave
you alone, I promise!). ;-)

What event occurs when the user clicks on the record
navigation buttons? I want the text in the combo box to
change when that happens.

The OnCurrent event triggers whenever you change records.
--
Bob Quintal

PA is y I've altered my email address.

[Erich Kohl]

On Tue, 4 Apr 2006 22:26:22 +0100, "Anthony England"
<ae******@oops.co.uk> wrote:

Don't worry about asking - I don't mind answering.
The event you are looking for is the Current event, and you could write
something like this to make sure the combobox stays synchronized with the
current record. It basically says, when a record becomes current check to
see if the id of the combobox matches the id of the record. If not change
the value of the combobox so it matches:
Private Sub Form_Current()

On Error GoTo Err_Handler

Dim lngConID As Long

lngConID = CLng(Nz(Me!ConID, 0))

If lngConID > 0 Then
If Nz(Me.cboConID, 0) <> lngConID Then
Me.cboConID = lngConID
End If
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Thanks, guys.

I have an Office Specialist certificate for Access, but it really
didn't involve knowing anything about programming in VBA. This is why
I've been asking for help.