Using a Combo Box to Find Records

Greetings,

I have been reading Allen Browne's Access website and I just tried the trick
to use a combo box to find records on a form. It works great except that the
cbo is empty when the form opens. The form and subform have the right records,
but I have to check the correct entry in the cbo to have it display the entry
data. After that it works until I close and reopen the form. Does anyone know
what I can do to correct this?

Thanks,
Scott

Hi Scott,
Here is a way to do it.
In the form's load or open event put a line of code like this--

Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

The code sets the combo to show the first row from the list.
If the first row of the combo is not the one that matches what the form
shows, you can add an extra line--

Call Me.[NameOfCombo]_AfterUpdate

Replace NameOfCombo of your combo.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Scott_Brasted" u56211@uwe wrote in message news:9f2eceffbb760@uwe...
Greetings,

I have been reading Allen Browne's Access website and I just tried the
trick
to use a combo box to find records on a form. It works great except that
the
cbo is empty when the form opens. The form and subform have the right
records,
but I have to check the correct entry in the cbo to have it display the
entry
data. After that it works until I close and reopen the form. Does anyone
know
what I can do to correct this?

Thanks,
Scott

Oops,
that extra line is wrong.
It should read

Call [NameOfCombo]_AfterUpdate


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Jeanette Cunningham" wrote in message
...
Hi Scott,
Here is a way to do it.
In the form's load or open event put a line of code like this--

Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

The code sets the combo to show the first row from the list.
If the first row of the combo is not the one that matches what the form
shows, you can add an extra line--

Call Me.[NameOfCombo]_AfterUpdate

Replace NameOfCombo of your combo.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Scott_Brasted" u56211@uwe wrote in message news:9f2eceffbb760@uwe...
Greetings,

I have been reading Allen Browne's Access website and I just tried the
trick
to use a combo box to find records on a form. It works great except that
the
cbo is empty when the form opens. The form and subform have the right
records,
but I have to check the correct entry in the cbo to have it display the
entry
data. After that it works until I close and reopen the form. Does anyone
know
what I can do to correct this?

Thanks,
Scott

Good morning Jeanette,

Thanks for the reply. I woke up this morning thinking that I needed a
form_current or on_open event, I just did not know wat to write. I am very
new to vb in Access.

When I put your code in the open or current event, the cbo puts the field
name for the id key (setupID) in and then gives me an error that says "The
value you entered isn't valid for this field . . ." When I add the 2nd line
of code, it is red in the code window and throw an even more ominous error
messsage. Any thoughts?

Best,
Scott

Jeanette Cunningham wrote:
Oops,
that extra line is wrong.
It should read

Call [NameOfCombo]_AfterUpdate

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Scott,
Here is a way to do it.
[quoted text clipped - 28 lines]
Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open
may work, too, but I tend to regard the Load event as the first opportunity
to work with data. If you are checking the value of a field, Open is too
soon.

I'm not sure why Jeanette suggested calling the combo box After Update code.
Perhaps there is something in the code at Allen Browne's web site of which
she has specific knowledge I am lacking, but in any case you need to compile
the code if you have not done so (Debug Compile).

You speak of adding a second line of code, but I don't see where Jeanette
suggested that, so it is not clear to me what you did. It would help if you
post the actual code, as well as the text of any error messages. There is no
way to know what you would regard as ominous.

Scott_Brasted wrote:
Good morning Jeanette,

Thanks for the reply. I woke up this morning thinking that I needed a
form_current or on_open event, I just did not know wat to write. I am very
new to vb in Access.

When I put your code in the open or current event, the cbo puts the field
name for the id key (setupID) in and then gives me an error that says "The
value you entered isn't valid for this field . . ." When I add the 2nd line
of code, it is red in the code window and throw an even more ominous error
messsage. Any thoughts?

Best,
Scott

Oops,
that extra line is wrong.
[quoted text clipped - 9 lines]
Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Buce, thanks for the reply.

Here is the code for the cbo:

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 "[SetupID] = " & 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

As I said it works, as is but show a blank box when the form opens.

Jeanette's code: Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

Jeanette's second respone had the following comment:
Oops,
that extra line is wrong.
It should read
Call [NameOfCombo]_AfterUpdate

None of this does anything to the cbo.

When I try to compile, I get errow I cannot understand. The code the compiler
stosa at is MS code. It came with the db and is part of the switchboard. Here
is the code that contans the place wher the compiler stops:
Private Sub cmdExit_GotFocus()
Dim intOption As Integer

'If the Exit Button has received the focus, turn off the focus on all the
menu options
For intOption = 1 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Next intOption

ExitLabel.FontUnderline = True
End Sub

ExitLable is highlighted by the compiler.

Best,
Scott


BruceM wrote:
The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open
may work, too, but I tend to regard the Load event as the first opportunity
to work with data. If you are checking the value of a field, Open is too
soon.

I'm not sure why Jeanette suggested calling the combo box After Update code.
Perhaps there is something in the code at Allen Browne's web site of which
she has specific knowledge I am lacking, but in any case you need to compile
the code if you have not done so (Debug Compile).

You speak of adding a second line of code, but I don't see where Jeanette
suggested that, so it is not clear to me what you did. It would help if you
post the actual code, as well as the text of any error messages. There is no
way to know what you would regard as ominous.

Good morning Jeanette,

[quoted text clipped - 16 lines]
Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1