Combo to find record does not auto expand

[Frank Situmorang]

Hello,

I use the following Allen Brown's tip to find the record and it works. But
instead of scrolling using the arrow bar, to speed up finding the record. Is
there any way to make it auto expand on the Household name?. My combo's row
source is AddressID, Householdname, and adddress 1 and Post Code. I want it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

 

[Stuart McCall]

Hello,

I use the following Allen Brown's tip to find the record and it works. But
instead of scrolling using the arrow bar, to speed up finding the record.
Is
there any way to make it auto expand on the Household name?. My combo's
row
source is AddressID, Householdname, and adddress 1 and Post Code. I want
it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

If you set the combo's Auto Expand property to Yes, that's the behaviour
you'll get.

 

[Frank Situmorang]

No Stuart, I have made it to Yes, but it does not work

Thanks if you still can explain it

Frank

Hello,

I use the following Allen Brown's tip to find the record and it works. But
instead of scrolling using the arrow bar, to speed up finding the record.
Is
there any way to make it auto expand on the Household name?. My combo's
row
source is AddressID, Householdname, and adddress 1 and Post Code. I want
it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

If you set the combo's Auto Expand property to Yes, that's the behaviour
you'll get.

 

[Stuart McCall]

No Stuart, I have made it to Yes, but it does not work

Thanks if you still can explain it

Frank

Hello,

I use the following Allen Brown's tip to find the record and it works.
But
instead of scrolling using the arrow bar, to speed up finding the
record.
Is
there any way to make it auto expand on the Household name?. My combo's
row
source is AddressID, Householdname, and adddress 1 and Post Code. I
want
it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

If you set the combo's Auto Expand property to Yes, that's the behaviour
you'll get.

So you're saying that if you drop down the list, the records are all there,
but typing does not offer any help by guessing the completed text?

In my experience the Access combo autoExpand feature has always 'just
worked'. Not sure what to suggest...

 

[AccessVandal via AccessMonster.com]

Frank,

There are three events that you can use to drop the combobox list
1. On Change
2. On Got Focus
3. On Key Press.
of the control’s event (not the form’s event).

I would recommend that you use the GotFocus event of the control. Once the
control has focus, the combobox list will automatically drop down.

The code is very simple.

Private Sub cboMoveTo_GotFocus()
Me.cboMoveTo.Dropdown
End Sub

The other two events will always cause the event to fire-up whenever there is
an input from the keyboard. The GotFocus event fire only once but if your
users are not savvy, the list will roll up/close and if that’s the case, use
the two other events.

Hello,

I use the following Allen Brown's tip to find the record and it works. But
instead of scrolling using the arrow bar, to speed up finding the record. Is
there any way to make it auto expand on the Household name?. My combo's row
source is AddressID, Householdname, and adddress 1 and Post Code. I want it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

 

[Frank Situmorang]

Vandel,

Sorry, since English is not our mother tongue, what do you mean by fire up.
It seems the following code is ok, when I change the column width property as
follows:
0";1.2";1.2";1.2"

The 2nd column is the household name, when I click the combo and type the
household name, it will autoexpand and that is what I want unless there is
dangerous thing on this afterupdate?:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

The only thing I want I do not need to saved, the word in the combo, how
can we do it, as soon as I move to another record the combo wil be cleared,
is there any way to do it?, sorry I am not too good in Access.

Thanks very much,

Frank

Frank,

There are three events that you can use to drop the combobox list
1. On Change
2. On Got Focus
3. On Key Press.
of the control’s event (not the form’s event).

I would recommend that you use the GotFocus event of the control. Once the
control has focus, the combobox list will automatically drop down.

The code is very simple.

Private Sub cboMoveTo_GotFocus()
Me.cboMoveTo.Dropdown
End Sub

The other two events will always cause the event to fire-up whenever there is
an input from the keyboard. The GotFocus event fire only once but if your
users are not savvy, the list will roll up/close and if that’s the case, use
the two other events.

Hello,

I use the following Allen Brown's tip to find the record and it works. But
instead of scrolling using the arrow bar, to speed up finding the record. Is
there any way to make it auto expand on the Household name?. My combo's row
source is AddressID, Householdname, and adddress 1 and Post Code. I want it
as soon as we type the household name it will be autoexpand.

The following is the VBA as per Allen Brown's tip:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Yhanks for any help.

Frank

 

[AccessVandal via AccessMonster.com]

So it’s not the dropdown or the autoexpand problem? Fire up means the events
in your code will run.

Is the control on the form is bound to the recordsource?

Are you trying to use the combobox to search a record that is bound to a
field in a table?

You cannot use a current record on the form to search a record if it’s bound.
Don’t save if you don’t need to.

To use the existing combobox as a search or filtering to an existing record,
the combobox must be in a new record.

Use the newrecord property instead.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If me.newrecord = True Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Note: If your form is filtered before loading, you’ll need to query the
records from the table instead of the form’s recordsetclone. Your bookmark
line may not work in this case because there’s no record on the filtered form.

You might want to load all records in your form instead?

Vandel,

Sorry, since English is not our mother tongue, what do you mean by fire up.
It seems the following code is ok, when I change the column width property as
follows:
0";1.2";1.2";1.2"

The 2nd column is the household name, when I click the combo and type the
household name, it will autoexpand and that is what I want unless there is
dangerous thing on this afterupdate?:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

The only thing I want I do not need to saved, the word in the combo, how
can we do it, as soon as I move to another record the combo wil be cleared,
is there any way to do it?, sorry I am not too good in Access.

Thanks very much,

Frank

 

[Frank Situmorang]

Vandal:

My main purpose is just to jump directly to the enquired record. So my combo
is unbound source of row. You know my users are very used to excel, so they
said Excel is much better, because in the form they can only see one record.
So that is why I want a combo to find and to lurk the record and by typing
inital name, it will auto expand to the inteded record.

When I jump to the record and then move to another record using the
navigation arrow, I still see the enquired name, how can we make it
automaticaly delete the typed name/the searched name.

Thanks in advance for your help.

Frank

So it’s not the dropdown or the autoexpand problem? Fire up means the events
in your code will run.

Is the control on the form is bound to the recordsource?

Are you trying to use the combobox to search a record that is bound to a
field in a table?

You cannot use a current record on the form to search a record if it’s bound.
Don’t save if you don’t need to.

To use the existing combobox as a search or filtering to an existing record,
the combobox must be in a new record.

Use the newrecord property instead.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If me.newrecord = True Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Note: If your form is filtered before loading, you’ll need to query the
records from the table instead of the form’s recordsetclone. Your bookmark
line may not work in this case because there’s no record on the filtered form.

You might want to load all records in your form instead?

Vandel,

Sorry, since English is not our mother tongue, what do you mean by fire up.
It seems the following code is ok, when I change the column width property as
follows:
0";1.2";1.2";1.2"

The 2nd column is the household name, when I click the combo and type the
household name, it will autoexpand and that is what I want unless there is
dangerous thing on this afterupdate?:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

The only thing I want I do not need to saved, the word in the combo, how
can we do it, as soon as I move to another record the combo wil be cleared,
is there any way to do it?, sorry I am not too good in Access.

Thanks very much,

Frank

 

[Cindy]

Since the combo box is not bound, it does not update when the user
moves to another record using the navigation buttons. You have two
choices: after doing the find, reset the combo box to NULL in your
After Update code. That means the user will NEVER see anything in
this box after they make a selection: they use the combo to GET to
the correct record, and it immediately clears itself out. Another
option is to put the combo box "in synch" with the record you are on.
It is one line of code on the On Current event of the form. Something
like: me.cboKeyField.Value = me.lngPrimaryKey

Replace the cboKeyField with the name of your combo box. Replace the
lngPrimarykey with the field that represents the bound column in the
column box from your form's recordset.

Now as you move through records using the navigation buttons, the
combo box will "update itself" to always match the record you are on.

Vandal:

My main purpose is just to jump directly to the enquired record. So my combo
is unbound source of row. You know my users are very used to excel, so they
said Excel is much better, because in the form they can only see one record.
So that is why I want a combo to find and to lurk the record and by typing
inital name, it will auto expand to the inteded record.

When I jump to the record and then move to another record using the
navigation arrow, I still see the enquired name, how can we make it
automaticaly delete the typed name/the searched name.

Thanks in advance for your help.

Frank

So it’s not the dropdown or the autoexpand problem? Fire up means theevents
in your code will run.

Is the control on the form is bound to the recordsource?

Are you trying to use the combobox to search a record that is bound to a
field in a table?

You cannot use a current record on the form to search a record if it’s bound.
Don’t save if you don’t need to.

To use the existing combobox as a search or filtering to an existing record,
the combobox must be in a new record.

Use the newrecord property instead.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

   If me.newrecord = True Then
              'Search in the clone set.
       Set rs = Me.RecordsetClone
       rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
       If rs.NoMatch Then
           MsgBox "Not found: filtered?"
       Else
           'Display the found record in the form.
           Me.Bookmark = rs.Bookmark
       End If
       Set rs = Nothing
   End If

End Sub

Note: If your form is filtered before loading, you’ll need to query the
records from the table instead of the form’s recordsetclone. Your bookmark
line may not work in this case because there’s no record on the filtered form.

You might want to load all records in your form instead?

Vandel,
Sorry, since English is not our mother tongue, what do you mean by fire up.
It seems the following code is ok, when I change the column width property as
follows:
0";1.2";1.2";1.2"
The 2nd column is the household name, when I click the combo and type the
household name, it will autoexpand and that is what I want unless there is
dangerous thing on this afterupdate?:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset
   If Not IsNull(Me.cboMoveTo) Then
       'Save before move.
       If Me.Dirty Then
           Me.Dirty = False
       End If
       'Search in the clone set.
       Set rs = Me.RecordsetClone
       rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
       If rs.NoMatch Then
           MsgBox "Not found: filtered?"
       Else
           'Display the found record in the form.
           Me.Bookmark = rs.Bookmark
       End If
       Set rs = Nothing
   End If
End Sub
The only thing I want I  do not need to saved, the word in the combo, how
can we do  it, as soon as I move to another record the combo wil be cleared,
is there any way to do it?, sorry I am not too good in Access.
Thanks very much,
Frank

 

[AccessVandal via AccessMonster.com]

Hi Frank,

It's a very simple code that will remove the previous entry. Use the Form's
current event.

Private Sub Form_Current()
Me.cboMoveTo = ""
End Sub

This will remove it whenever you move to to another record.

Since the combobox is not bound, you don't need to check the combobox
conditions nor the NewRecord.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

If you want to prevent users from editing the current record or if they make
a mistake of editing the record while doing a search at the same time, You
might want include code to check the Form's Dirty property.

Just simply prevent the edits by me.undo.

If Me.Dirty = True Then
me.undo
end if
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AddresID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing