Quick Search Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003. When I create a new record on one form and move to
another form to make entry, the new record does not show up in the quick
search combo list in the header.
 
I think your first form hasn't turned loose of the new record. Either
closing the first form, or moving it to another record might help. Sometimes,
I've had to restart the database for a new record to be available but that's
unusual.
HTH
Mike
 
You could do this a few ways, but either way you'll have to save the record
and requery the combobox unless/ or until you move to a different record, in
which case it would be saved and requeried automatically. You might put your
code in the commandbutton's On Click event.

If Me.Dirty Then
Me.Dirty = False
Me.MyCombo.Requery
End If

Brian
 
Thanks Mike for responding. If I close the database, the item shows up when
I reopen, but this won't be a good solution for the end user. Any other
ideas? ME
 
Brian

Thanks for responding. I'm new to Access programming (programmed Filemaker
for 15 years and haven't written code since Fortran in the 70s!) and hope to
work on this project today. So far, saving the record, moving to another
record, or performing a requery has not worked. Closing the database and
reopening does cause the record to appear in the combo box, but this won't be
acceptable to the end user. The new record shows up in the table so I know
it's there. I'll keep working on this. If you have other ideas, let me
know. Thanks. ME
 
Come to think of it, I have a button on one of my forms that runs a macro
with the single command "RunCommand" and the command it runs is "refresh". I
think I put it there for exactly this reason. However, that makes the
operation manual rather than automatic. If you are closing the first form
everytime you open the second (or other) form, you might put such a macro on
the on close event of the first form. Otherwise, if you are always opening
the second form from the first one, you might attach it to the on open
property of form2. I was really hoping you were going to say Brian's
solution worked because it looked a lot cleaner than mine.
No guarantees here. I haven't tried it that way yet.
Mike
Thanks Mike for responding. If I close the database, the item shows up when
I reopen, but this won't be a good solution for the end user. Any other
ideas? ME
I think your first form hasn't turned loose of the new record. Either
closing the first form, or moving it to another record might help. Sometimes,
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
Hi ME,

Are you entering data on the main form, or in a subform? And what are their
names, as well as the name of the combobox? Open the form in design view to
bring up its property sheet to get the info.

Brian
 
I had this same problem with one of my comboboxes.

The record you're adding has to be saved. If the form is already open, you
need to requery the combobox in the forms OnActivate event (I'm assuming
you're switching between two different forms).

As a test, use a command button to requery the combobox. If this updates the
combobox, it's just a case of finding the correct event(s) to fire the
requery.

Dave
 
Brian/Mike/Dave I am have been working on this project in bits and pieces
and have been learning a lot about Access. Thanks for your input and
willingness to help me.

I am entering the data on a subform.
Main form: frm Clients Entry.
Subform: sbfrmAdmissionNewReadmit.
The quick search combo box on the header of this form/subform is Combo3,
which I designed using the combo box wizard.

I then move to another form and tabbed subforms and enter data in the
subform. Main form: frm Client Enter Codes Hours.
Subforms: sbfrm tab nonRtss Service Info and sbfrm tab RTSS Hours.
The quick searchcombox box on the header of this form/subform is Combo55,
which I designed using the combo box wizard.

If I open my database, add a record, and move through all the forms, no
problem.
Everything shows up in the combo boxes without saving or refreshing. The
Quick Search combo boxes work perfectly. EVERYTHING WORKS PERFECTLY AND i
DON'T NEED TO SAVE OR REFRES.

HOWEVER, after I add the first record, if I return to enter another new
client on the first form/subform, and then move to the second set of
form/subforms to enter data, problems start to occur. I can refresh and/or
save between form moves, and the new name shows up in the combo box, but the
record does not change to the same as the combo box. I can save everything
as I go along, refresh as I go along, nothing makes the combo box on the
second set of form/subforms bring up the record. I added a requery of
combo55 to the Open Form command button which opens the second set of
form/subforms. That didn't work. Any ideas?

(I am just using the menu bar to refresh and save so far. Since this isn't
working, I haven't built these into any macros. By the way, I don't see
"Refresh" in any macro list.)

Thanks a lot for all your ideas and help. MEYSS

If helpful, here is the code for the combo55 which I find on the "After
Update" line of Events on Properties. I haven't written VBA before, so most
of this code doesn't make much sense to me. But here it is:

Option Compare Database

Private Sub Combo15_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo15], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub btn_Return_to_Client_Entry_Click()
On Error GoTo Err_btn_Return_to_Client_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Clients Entry"

stLinkCriteria = "[Client ID]=" & Me![Client ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Return_to_Client_Entry_Click:
Exit Sub

Err_btn_Return_to_Client_Entry_Click:
MsgBox Err.Description
Resume Exit_btn_Return_to_Client_Entry_Click

End Sub
Private Sub btn_Enter_Visit_Hours_Click()
On Error GoTo Err_btn_Enter_Visit_Hours_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Enter Visits Hours"

stLinkCriteria = "[NewReadmitID]=" & Me![NewReadmitID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Enter_Visit_Hours_Click:
Exit Sub

Err_btn_Enter_Visit_Hours_Click:
MsgBox Err.Description
Resume Exit_btn_Enter_Visit_Hours_Click

End Sub
Private Sub btn_Switchboard_Click()
On Error GoTo Err_btn_Switchboard_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Switchboard_Click:
Exit Sub

Err_btn_Switchboard_Click:
MsgBox Err.Description
Resume Exit_btn_Switchboard_Click

End Sub

Private Sub QuickSearchCodeForm_AfterUpdate()

End Sub
Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo59_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo59], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
I'm afraid I can't tell you why your second combo box isn't working. I'm
pretty new at this too. I can tell you that "refresh" isn't directly in the
list of macro actions. However, "run command" is in the list and "refresh"
is in the list of commands under the "run command" action. However, since it
didn't work from the menu command, I don't know how much good that
information really does you.
Mike


Brian/Mike/Dave I am have been working on this project in bits and pieces
and have been learning a lot about Access. Thanks for your input and
willingness to help me.

I am entering the data on a subform.
Main form: frm Clients Entry.
Subform: sbfrmAdmissionNewReadmit.
The quick search combo box on the header of this form/subform is Combo3,
which I designed using the combo box wizard.

I then move to another form and tabbed subforms and enter data in the
subform. Main form: frm Client Enter Codes Hours.
Subforms: sbfrm tab nonRtss Service Info and sbfrm tab RTSS Hours.
The quick searchcombox box on the header of this form/subform is Combo55,
which I designed using the combo box wizard.

If I open my database, add a record, and move through all the forms, no
problem.
Everything shows up in the combo boxes without saving or refreshing. The
Quick Search combo boxes work perfectly. EVERYTHING WORKS PERFECTLY AND i
DON'T NEED TO SAVE OR REFRES.

HOWEVER, after I add the first record, if I return to enter another new
client on the first form/subform, and then move to the second set of
form/subforms to enter data, problems start to occur. I can refresh and/or
save between form moves, and the new name shows up in the combo box, but the
record does not change to the same as the combo box. I can save everything
as I go along, refresh as I go along, nothing makes the combo box on the
second set of form/subforms bring up the record. I added a requery of
combo55 to the Open Form command button which opens the second set of
form/subforms. That didn't work. Any ideas?

(I am just using the menu bar to refresh and save so far. Since this isn't
working, I haven't built these into any macros. By the way, I don't see
"Refresh" in any macro list.)

Thanks a lot for all your ideas and help. MEYSS

If helpful, here is the code for the combo55 which I find on the "After
Update" line of Events on Properties. I haven't written VBA before, so most
of this code doesn't make much sense to me. But here it is:

Option Compare Database

Private Sub Combo15_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo15], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub btn_Return_to_Client_Entry_Click()
On Error GoTo Err_btn_Return_to_Client_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Clients Entry"

stLinkCriteria = "[Client ID]=" & Me![Client ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Return_to_Client_Entry_Click:
Exit Sub

Err_btn_Return_to_Client_Entry_Click:
MsgBox Err.Description
Resume Exit_btn_Return_to_Client_Entry_Click

End Sub
Private Sub btn_Enter_Visit_Hours_Click()
On Error GoTo Err_btn_Enter_Visit_Hours_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Enter Visits Hours"

stLinkCriteria = "[NewReadmitID]=" & Me![NewReadmitID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Enter_Visit_Hours_Click:
Exit Sub

Err_btn_Enter_Visit_Hours_Click:
MsgBox Err.Description
Resume Exit_btn_Enter_Visit_Hours_Click

End Sub
Private Sub btn_Switchboard_Click()
On Error GoTo Err_btn_Switchboard_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Switchboard_Click:
Exit Sub

Err_btn_Switchboard_Click:
MsgBox Err.Description
Resume Exit_btn_Switchboard_Click

End Sub

Private Sub QuickSearchCodeForm_AfterUpdate()

End Sub
Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo59_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo59], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Hi ME,
Click to expand...
[quoted text clipped - 40 lines]
quick
Click to expand...
Click to expand...
 
Thanks for the hint on the location of the macro action, "refresh." My
learning curve is sort of steep! ME

Mikal via AccessMonster.com said:
I'm afraid I can't tell you why your second combo box isn't working. I'm
pretty new at this too. I can tell you that "refresh" isn't directly in the
list of macro actions. However, "run command" is in the list and "refresh"
is in the list of commands under the "run command" action. However, since it
didn't work from the menu command, I don't know how much good that
information really does you.
Mike


Brian/Mike/Dave I am have been working on this project in bits and pieces
and have been learning a lot about Access. Thanks for your input and
willingness to help me.

I am entering the data on a subform.
Main form: frm Clients Entry.
Subform: sbfrmAdmissionNewReadmit.
The quick search combo box on the header of this form/subform is Combo3,
which I designed using the combo box wizard.

I then move to another form and tabbed subforms and enter data in the
subform. Main form: frm Client Enter Codes Hours.
Subforms: sbfrm tab nonRtss Service Info and sbfrm tab RTSS Hours.
The quick searchcombox box on the header of this form/subform is Combo55,
which I designed using the combo box wizard.

If I open my database, add a record, and move through all the forms, no
problem.
Everything shows up in the combo boxes without saving or refreshing. The
Quick Search combo boxes work perfectly. EVERYTHING WORKS PERFECTLY AND i
DON'T NEED TO SAVE OR REFRES.

HOWEVER, after I add the first record, if I return to enter another new
client on the first form/subform, and then move to the second set of
form/subforms to enter data, problems start to occur. I can refresh and/or
save between form moves, and the new name shows up in the combo box, but the
record does not change to the same as the combo box. I can save everything
as I go along, refresh as I go along, nothing makes the combo box on the
second set of form/subforms bring up the record. I added a requery of
combo55 to the Open Form command button which opens the second set of
form/subforms. That didn't work. Any ideas?

(I am just using the menu bar to refresh and save so far. Since this isn't
working, I haven't built these into any macros. By the way, I don't see
"Refresh" in any macro list.)

Thanks a lot for all your ideas and help. MEYSS

If helpful, here is the code for the combo55 which I find on the "After
Update" line of Events on Properties. I haven't written VBA before, so most
of this code doesn't make much sense to me. But here it is:

Option Compare Database

Private Sub Combo15_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo15], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub btn_Return_to_Client_Entry_Click()
On Error GoTo Err_btn_Return_to_Client_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Clients Entry"

stLinkCriteria = "[Client ID]=" & Me![Client ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Return_to_Client_Entry_Click:
Exit Sub

Err_btn_Return_to_Client_Entry_Click:
MsgBox Err.Description
Resume Exit_btn_Return_to_Client_Entry_Click

End Sub
Private Sub btn_Enter_Visit_Hours_Click()
On Error GoTo Err_btn_Enter_Visit_Hours_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Enter Visits Hours"

stLinkCriteria = "[NewReadmitID]=" & Me![NewReadmitID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Enter_Visit_Hours_Click:
Exit Sub

Err_btn_Enter_Visit_Hours_Click:
MsgBox Err.Description
Resume Exit_btn_Enter_Visit_Hours_Click

End Sub
Private Sub btn_Switchboard_Click()
On Error GoTo Err_btn_Switchboard_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Switchboard_Click:
Exit Sub

Err_btn_Switchboard_Click:
MsgBox Err.Description
Resume Exit_btn_Switchboard_Click

End Sub

Private Sub QuickSearchCodeForm_AfterUpdate()

End Sub
Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo59_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo59], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Hi ME,
Click to expand...
[quoted text clipped - 40 lines]
quick
search combo list in the header.
Click to expand...
Click to expand...
Click to expand...
 
Brian/Mike/Dave
I have corrected my problem by adding a line to the macro for my command
buttons to apply a filter, immediately followed by a command to remove the
filter. My macro isn't as tidy as I would like, but this works. By reading
through a lot of the posts on combo boxes, I thought maybe I had a filtering
problem, even though the filter icon was not on. Your help on this is very
much appreciated. ME

MEYSS said:
Brian/Mike/Dave I am have been working on this project in bits and pieces
and have been learning a lot about Access. Thanks for your input and
willingness to help me.

I am entering the data on a subform.
Main form: frm Clients Entry.
Subform: sbfrmAdmissionNewReadmit.
The quick search combo box on the header of this form/subform is Combo3,
which I designed using the combo box wizard.

I then move to another form and tabbed subforms and enter data in the
subform. Main form: frm Client Enter Codes Hours.
Subforms: sbfrm tab nonRtss Service Info and sbfrm tab RTSS Hours.
The quick searchcombox box on the header of this form/subform is Combo55,
which I designed using the combo box wizard.

If I open my database, add a record, and move through all the forms, no
problem.
Everything shows up in the combo boxes without saving or refreshing. The
Quick Search combo boxes work perfectly. EVERYTHING WORKS PERFECTLY AND i
DON'T NEED TO SAVE OR REFRES.

HOWEVER, after I add the first record, if I return to enter another new
client on the first form/subform, and then move to the second set of
form/subforms to enter data, problems start to occur. I can refresh and/or
save between form moves, and the new name shows up in the combo box, but the
record does not change to the same as the combo box. I can save everything
as I go along, refresh as I go along, nothing makes the combo box on the
second set of form/subforms bring up the record. I added a requery of
combo55 to the Open Form command button which opens the second set of
form/subforms. That didn't work. Any ideas?

(I am just using the menu bar to refresh and save so far. Since this isn't
working, I haven't built these into any macros. By the way, I don't see
"Refresh" in any macro list.)

Thanks a lot for all your ideas and help. MEYSS

If helpful, here is the code for the combo55 which I find on the "After
Update" line of Events on Properties. I haven't written VBA before, so most
of this code doesn't make much sense to me. But here it is:

Option Compare Database

Private Sub Combo15_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo15], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub btn_Return_to_Client_Entry_Click()
On Error GoTo Err_btn_Return_to_Client_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Clients Entry"

stLinkCriteria = "[Client ID]=" & Me![Client ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Return_to_Client_Entry_Click:
Exit Sub

Err_btn_Return_to_Client_Entry_Click:
MsgBox Err.Description
Resume Exit_btn_Return_to_Client_Entry_Click

End Sub
Private Sub btn_Enter_Visit_Hours_Click()
On Error GoTo Err_btn_Enter_Visit_Hours_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm Enter Visits Hours"

stLinkCriteria = "[NewReadmitID]=" & Me![NewReadmitID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Enter_Visit_Hours_Click:
Exit Sub

Err_btn_Enter_Visit_Hours_Click:
MsgBox Err.Description
Resume Exit_btn_Enter_Visit_Hours_Click

End Sub
Private Sub btn_Switchboard_Click()
On Error GoTo Err_btn_Switchboard_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_Switchboard_Click:
Exit Sub

Err_btn_Switchboard_Click:
MsgBox Err.Description
Resume Exit_btn_Switchboard_Click

End Sub

Private Sub QuickSearchCodeForm_AfterUpdate()

End Sub
Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo59_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NewReadmitID] = " & Str(Nz(Me![Combo59], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Brian Bastl said:
Hi ME,

Are you entering data on the main form, or in a subform? And what are their
names, as well as the name of the combobox? Open the form in design view to
bring up its property sheet to get the info.

Brian
Click to expand...
Click to expand...
 
Back
Top