Requery a Filtered Combo Box

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I have a data entry form that combines information from 3
tables: Players, AAUPrograms, and AAUCoaches. The Players
table contains AAU and AAUCoach fields as the foreign
keys to link a specific player to a specific AAU Program
and to a specific AAU Team. (One AAU Program can have
many AAU Coaches. Sometimes I may only know the Program
and not the Coach - hence my inclusion in the Players
table of the AAU foreign key field.)

On my form, I have a combo box for AAU Program. Once an
AAU program is selected, the next field on the form is
the AAU Coach combo box. This AAU Coach combo box is
filtered (via criteria set in the query the AAU Coach
combo box pulls from) to list only those coaches from
that AAU Program. If the coach is not in the list, I have
an "Add New Coach" command button which opens the
AAUProgramsTeamsCoaches form to the AAU Program record
that has been already selected. I enter the new coach
info and close the form, returning to my Player Entry
Form. My problem is that I cannot get the AAU Coach combo
box to immediately list the new coach I just entered. The
only way I have been able to get it to work is to hit F9
or to build a separate Refresh command button that has to
be pushed. I would like to include a requery method (?)
in the module for "Add New Coach". The following is my
code. What do I need to add and where?

PLEASE HELP!

Private Sub cmdAddNewAAUCoach_Click()
On Error GoTo Err_cmdAddNewAAUCoach_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAUProgramsTeamsCoaches"

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

Exit_cmdAddNewAAUCoach_Click:
Exit Sub

Err_cmdAddNewAAUCoach_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewAAUCoach_Click

End Sub

Thanks!

Melissa
 
Right after the DoCmd.OpenForm step, insert this line:

Me.AAUCoaches_combobox.Requery

(change AAUCoaches_combobox to the actual name of the combo box).
 
Ken:

I entered the line of code you suggested immediately
following the DoCmd.OpenForm statement. (My field name is
simply AAUCoach.)

DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.AAUCoach.Requery

It is not working. Any other suggestions?

Melissa
-----Original Message-----
Right after the DoCmd.OpenForm step, insert this line:

Me.AAUCoaches_combobox.Requery

(change AAUCoaches_combobox to the actual name of the combo box).

--
Ken Snell
<MS ACCESS MVP>

Melissa said:
I have a data entry form that combines information from 3
tables: Players, AAUPrograms, and AAUCoaches. The Players
table contains AAU and AAUCoach fields as the foreign
keys to link a specific player to a specific AAU Program
and to a specific AAU Team. (One AAU Program can have
many AAU Coaches. Sometimes I may only know the Program
and not the Coach - hence my inclusion in the Players
table of the AAU foreign key field.)

On my form, I have a combo box for AAU Program. Once an
AAU program is selected, the next field on the form is
the AAU Coach combo box. This AAU Coach combo box is
filtered (via criteria set in the query the AAU Coach
combo box pulls from) to list only those coaches from
that AAU Program. If the coach is not in the list, I have
an "Add New Coach" command button which opens the
AAUProgramsTeamsCoaches form to the AAU Program record
that has been already selected. I enter the new coach
info and close the form, returning to my Player Entry
Form. My problem is that I cannot get the AAU Coach combo
box to immediately list the new coach I just entered. The
only way I have been able to get it to work is to hit F9
or to build a separate Refresh command button that has to
be pushed. I would like to include a requery method (?)
in the module for "Add New Coach". The following is my
code. What do I need to add and where?

PLEASE HELP!

Private Sub cmdAddNewAAUCoach_Click()
On Error GoTo Err_cmdAddNewAAUCoach_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAUProgramsTeamsCoaches"

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

Exit_cmdAddNewAAUCoach_Click:
Exit Sub

Err_cmdAddNewAAUCoach_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewAAUCoach_Click

End Sub

Thanks!

Melissa
Click to expand...


.
Click to expand...
 
Ken:

I did as you suggested, adding the line of code directly
below the DoCmd.OpenForm line. It still is not working.
The name of my combo box is AAUCoach. I have tried the
following:

Me.AAUCoach.Requery

Me!AAUCoach.Requery

Me![AAUCoach].Requery

None of them work. Any other suggestions?

Thanks!

Melissa
-----Original Message-----
Right after the DoCmd.OpenForm step, insert this line:

Me.AAUCoaches_combobox.Requery

(change AAUCoaches_combobox to the actual name of the combo box).

--
Ken Snell
<MS ACCESS MVP>

Melissa said:
I have a data entry form that combines information from 3
tables: Players, AAUPrograms, and AAUCoaches. The Players
table contains AAU and AAUCoach fields as the foreign
keys to link a specific player to a specific AAU Program
and to a specific AAU Team. (One AAU Program can have
many AAU Coaches. Sometimes I may only know the Program
and not the Coach - hence my inclusion in the Players
table of the AAU foreign key field.)

On my form, I have a combo box for AAU Program. Once an
AAU program is selected, the next field on the form is
the AAU Coach combo box. This AAU Coach combo box is
filtered (via criteria set in the query the AAU Coach
combo box pulls from) to list only those coaches from
that AAU Program. If the coach is not in the list, I have
an "Add New Coach" command button which opens the
AAUProgramsTeamsCoaches form to the AAU Program record
that has been already selected. I enter the new coach
info and close the form, returning to my Player Entry
Form. My problem is that I cannot get the AAU Coach combo
box to immediately list the new coach I just entered. The
only way I have been able to get it to work is to hit F9
or to build a separate Refresh command button that has to
be pushed. I would like to include a requery method (?)
in the module for "Add New Coach". The following is my
code. What do I need to add and where?

PLEASE HELP!

Private Sub cmdAddNewAAUCoach_Click()
On Error GoTo Err_cmdAddNewAAUCoach_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAUProgramsTeamsCoaches"

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

Exit_cmdAddNewAAUCoach_Click:
Exit Sub

Err_cmdAddNewAAUCoach_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewAAUCoach_Click

End Sub

Thanks!

Melissa
Click to expand...


.
Click to expand...
 
Sorry....I overlooked that your DoCmd.OpenForm step is not opening the form
in dialog mode. If you do that, then the code I gave you should work.

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog


Else, put the Requery line of code in the frmAAUProgramsTeamsCoaches form's
OnClose event, but use the full reference to the first form:

[Forms]![NameOfDataEntryForm]![AAUCoach].Requery
--
Ken Snell
<MS ACCESS MVP>


melissa said:
Ken:

I did as you suggested, adding the line of code directly
below the DoCmd.OpenForm line. It still is not working.
The name of my combo box is AAUCoach. I have tried the
following:

Me.AAUCoach.Requery

Me!AAUCoach.Requery

Me![AAUCoach].Requery

None of them work. Any other suggestions?

Thanks!

Melissa
-----Original Message-----
Right after the DoCmd.OpenForm step, insert this line:

Me.AAUCoaches_combobox.Requery

(change AAUCoaches_combobox to the actual name of the combo box).

--
Ken Snell
<MS ACCESS MVP>

Melissa said:
I have a data entry form that combines information from 3
tables: Players, AAUPrograms, and AAUCoaches. The Players
table contains AAU and AAUCoach fields as the foreign
keys to link a specific player to a specific AAU Program
and to a specific AAU Team. (One AAU Program can have
many AAU Coaches. Sometimes I may only know the Program
and not the Coach - hence my inclusion in the Players
table of the AAU foreign key field.)

On my form, I have a combo box for AAU Program. Once an
AAU program is selected, the next field on the form is
the AAU Coach combo box. This AAU Coach combo box is
filtered (via criteria set in the query the AAU Coach
combo box pulls from) to list only those coaches from
that AAU Program. If the coach is not in the list, I have
an "Add New Coach" command button which opens the
AAUProgramsTeamsCoaches form to the AAU Program record
that has been already selected. I enter the new coach
info and close the form, returning to my Player Entry
Form. My problem is that I cannot get the AAU Coach combo
box to immediately list the new coach I just entered. The
only way I have been able to get it to work is to hit F9
or to build a separate Refresh command button that has to
be pushed. I would like to include a requery method (?)
in the module for "Add New Coach". The following is my
code. What do I need to add and where?

PLEASE HELP!

Private Sub cmdAddNewAAUCoach_Click()
On Error GoTo Err_cmdAddNewAAUCoach_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAUProgramsTeamsCoaches"

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

Exit_cmdAddNewAAUCoach_Click:
Exit Sub

Err_cmdAddNewAAUCoach_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewAAUCoach_Click

End Sub

Thanks!

Melissa
Click to expand...


.
Click to expand...
Click to expand...
 
-----Original Message-----
Sorry....I overlooked that your DoCmd.OpenForm step is not opening the form
in dialog mode. If you do that, then the code I gave you should work.

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog


Else, put the Requery line of code in the
Click to expand...
frmAAUProgramsTeamsCoaches form's
OnClose event, but use the full reference to the first form:

[Forms]![NameOfDataEntryForm]![AAUCoach].Requery
--
Ken Snell
<MS ACCESS MVP>


melissa said:
Ken:

I did as you suggested, adding the line of code directly
below the DoCmd.OpenForm line. It still is not working.
The name of my combo box is AAUCoach. I have tried the
following:

Me.AAUCoach.Requery

Me!AAUCoach.Requery

Me![AAUCoach].Requery

None of them work. Any other suggestions?

Thanks!

Melissa
-----Original Message-----
Right after the DoCmd.OpenForm step, insert this line:

Me.AAUCoaches_combobox.Requery

(change AAUCoaches_combobox to the actual name of the combo box).

--
Ken Snell
<MS ACCESS MVP>

I have a data entry form that combines information from 3
tables: Players, AAUPrograms, and AAUCoaches. The Players
table contains AAU and AAUCoach fields as the foreign
keys to link a specific player to a specific AAU Program
and to a specific AAU Team. (One AAU Program can have
many AAU Coaches. Sometimes I may only know the Program
and not the Coach - hence my inclusion in the Players
table of the AAU foreign key field.)

On my form, I have a combo box for AAU Program. Once an
AAU program is selected, the next field on the form is
the AAU Coach combo box. This AAU Coach combo box is
filtered (via criteria set in the query the AAU Coach
combo box pulls from) to list only those coaches from
that AAU Program. If the coach is not in the list, I have
an "Add New Coach" command button which opens the
AAUProgramsTeamsCoaches form to the AAU Program record
that has been already selected. I enter the new coach
info and close the form, returning to my Player Entry
Form. My problem is that I cannot get the AAU Coach combo
box to immediately list the new coach I just
Click to expand...
entered.
The
only way I have been able to get it to work is to
Click to expand...
hit
F9
or to build a separate Refresh command button that
Click to expand...
has
to
be pushed. I would like to include a requery method (?)
in the module for "Add New Coach". The following is my
code. What do I need to add and where?

PLEASE HELP!

Private Sub cmdAddNewAAUCoach_Click()
On Error GoTo Err_cmdAddNewAAUCoach_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAUProgramsTeamsCoaches"

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

Exit_cmdAddNewAAUCoach_Click:
Exit Sub

Err_cmdAddNewAAUCoach_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewAAUCoach_Click

End Sub

Thanks!

Melissa


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top