Combo Box and Subform

[Guest]

I have a form which has a combo box. This allows the user to display all the
record details on the main form based upon the selection in the combo box.
Also attached to the main form is a sub form which shows all the other
records that are related to the record being displayed on the main form.
The user can click a command button on the sub-form which will then display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The record
selectors at the bottom of the form go from saying 'record 1 of XXX' to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and requery the
combo box and also on the click action of the combo box but to no avail.
Below is the exact code I have used, however I am new to code and am not sure
if this is all I need to make it work or even if this is the best way of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

Any help gratefully received

Richard

 

[Allen Browne]

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

 

[Guest]

Combo22 is unbound

Below is the code underneath the command button within the sub-form

could the command button be the cause of my problems

 

[Guest]

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform, could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

 

[Allen Browne]

Richard, I don't understand the connection between this command button and
the form's Current event, nor whether this is in the form named [Sample
Results] or another form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform, could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

 

[Guest]

The command button is on the subform which is displayed on/in the main form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results] 'current'
event other than I have been placing the 'Requery' code in the Main forms
'current event' in an attempt to solve the problem I am encountering when the
command button has been clicked

Richard, I don't understand the connection between this command button and
the form's Current event, nor whether this is in the form named [Sample
Results] or another form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform, could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to display all
the
record details on the main form based upon the selection in the combo
box.
Also attached to the main form is a sub form which shows all the other
records that are related to the record being displayed on the main
form.
The user can click a command button on the sub-form which will then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The record
selectors at the bottom of the form go from saying 'record 1 of XXX' to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and am
not
sure
if this is all I need to make it work or even if this is the best way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

 

[Allen Browne]

Okay, I've just re-read this thread, and here's my current understanding of
what you are doing:
a) Main form is in form view (shows one record), bound to a table.

b) Subform is in Continuous view (shows many records), bound to the *same*
table.

c) Combo22 is an unbound combo on the main form. It's purpose is to limit
the records shown in the subform.

d) Subform has a command button that appears beside each record. Clicking
the command button should make that the current record in the main form.

I assume that selecting a record in Combo22 still shows multple records in
the subform. Therefore Combo22's RowSource is not the same table: it must be
a lookup (perhaps limiting the subform to one type or category.)

It that's the case, you can remove the code from Combo22's AfterUpdate.
Instead set the Link Master Fields property of the subform control to
Combo22, and the Link Child Fields property to the matching field in the
subform. Now the subform won't show any records until you choose something
in the combo, and then the subform will show the matching records.

The remaining part of the job is the code for the command button in the
subform. The idea is to FindFirst in the main form's RecordsetClone, and
then set its Bookmark so it displays the record. This kind of thing:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.NewRecord Then
Beep
Else
strWhere = "[ID] = " & Me.[ID]
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in main form. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub

You will need to use your field name in the line starting:
strWhere =

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The command button is on the subform which is displayed on/in the main
form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results]
'current'
event other than I have been placing the 'Requery' code in the Main forms
'current event' in an attempt to solve the problem I am encountering when
the
command button has been clicked

Richard, I don't understand the connection between this command button
and
the form's Current event, nor whether this is in the form named [Sample
Results] or another form.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform,
could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

:

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in
the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to display
all
the
record details on the main form based upon the selection in the
combo
box.
Also attached to the main form is a sub form which shows all the
other
records that are related to the record being displayed on the main
form.
The user can click a command button on the sub-form which will then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The
record
selectors at the bottom of the form go from saying 'record 1 of XXX'
to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and am
not
sure
if this is all I need to make it work or even if this is the best
way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

 

[Guest]

Thanks for the reply, I have to go to another job now but will try this
either later today or tomorrow and will let you know how I get on.

Many thanks for your patience and help

Okay, I've just re-read this thread, and here's my current understanding of
what you are doing:
a) Main form is in form view (shows one record), bound to a table.

b) Subform is in Continuous view (shows many records), bound to the *same*
table.

c) Combo22 is an unbound combo on the main form. It's purpose is to limit
the records shown in the subform.

d) Subform has a command button that appears beside each record. Clicking
the command button should make that the current record in the main form.

I assume that selecting a record in Combo22 still shows multple records in
the subform. Therefore Combo22's RowSource is not the same table: it must be
a lookup (perhaps limiting the subform to one type or category.)

It that's the case, you can remove the code from Combo22's AfterUpdate.
Instead set the Link Master Fields property of the subform control to
Combo22, and the Link Child Fields property to the matching field in the
subform. Now the subform won't show any records until you choose something
in the combo, and then the subform will show the matching records.

The remaining part of the job is the code for the command button in the
subform. The idea is to FindFirst in the main form's RecordsetClone, and
then set its Bookmark so it displays the record. This kind of thing:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.NewRecord Then
Beep
Else
strWhere = "[ID] = " & Me.[ID]
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in main form. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub

You will need to use your field name in the line starting:
strWhere =

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The command button is on the subform which is displayed on/in the main
form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results]
'current'
event other than I have been placing the 'Requery' code in the Main forms
'current event' in an attempt to solve the problem I am encountering when
the
command button has been clicked

Richard, I don't understand the connection between this command button
and
the form's Current event, nor whether this is in the form named [Sample
Results] or another form.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform,
could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

:

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in
the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to display
all
the
record details on the main form based upon the selection in the
combo
box.
Also attached to the main form is a sub form which shows all the
other
records that are related to the record being displayed on the main
form.
The user can click a command button on the sub-form which will then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The
record
selectors at the bottom of the form go from saying 'record 1 of XXX'
to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and am
not
sure
if this is all I need to make it work or even if this is the best
way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

 

[Guest]

couldn't resist looking at your post in detail before heading out to work

regarding your assumptions below

a) correct assumption

b) correct assumption

d) correct assumption

c) Combo22 is unbound(selection SQL statement is below), however its purpose
is to allow user to select records from tblSampleDetails and display selected
record in frmSampleDetails. Event for the Combo22 is 'Click' not 'After
Update', don't know if this is important.
Subform is based upon tblBatchDetails and Link and Master Child Fields point
to the field [Batch number]. I have looked at these Child Fields and Combo22
is not available for selection
SQL Statement in Row Source for Combo22

SELECT [Sample Details].[Our Sample Number], [Sample Details].[Batch Number]
FROM [Sample Details];

SQL Statement in Record Source of Subform

Okay, I've just re-read this thread, and here's my current understanding of
what you are doing:
a) Main form is in form view (shows one record), bound to a table.

b) Subform is in Continuous view (shows many records), bound to the *same*
table.

c) Combo22 is an unbound combo on the main form. It's purpose is to limit
the records shown in the subform.

d) Subform has a command button that appears beside each record. Clicking
the command button should make that the current record in the main form.

I assume that selecting a record in Combo22 still shows multple records in
the subform. Therefore Combo22's RowSource is not the same table: it must be
a lookup (perhaps limiting the subform to one type or category.)

It that's the case, you can remove the code from Combo22's AfterUpdate.
Instead set the Link Master Fields property of the subform control to
Combo22, and the Link Child Fields property to the matching field in the
subform. Now the subform won't show any records until you choose something
in the combo, and then the subform will show the matching records.

The remaining part of the job is the code for the command button in the
subform. The idea is to FindFirst in the main form's RecordsetClone, and
then set its Bookmark so it displays the record. This kind of thing:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.NewRecord Then
Beep
Else
strWhere = "[ID] = " & Me.[ID]
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in main form. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub

You will need to use your field name in the line starting:
strWhere =

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The command button is on the subform which is displayed on/in the main
form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results]
'current'
event other than I have been placing the 'Requery' code in the Main forms
'current event' in an attempt to solve the problem I am encountering when
the
command button has been clicked

Richard, I don't understand the connection between this command button
and
the form's Current event, nor whether this is in the form named [Sample
Results] or another form.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform,
could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

:

If you want to reload the list of items available in the combo named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used in
the
LinkMasterFields of your subform, the subform should show the correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to display
all
the
record details on the main form based upon the selection in the
combo
box.
Also attached to the main form is a sub form which shows all the
other
records that are related to the record being displayed on the main
form.
The user can click a command button on the sub-form which will then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The
record
selectors at the bottom of the form go from saying 'record 1 of XXX'
to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and am
not
sure
if this is all I need to make it work or even if this is the best
way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

 

[Allen Browne]

AfterUpdate is preferred to Click: responds to keyboard properly, applies
only after the value is changed.

You can type Combo22 into the Link Master Fields property of the subform
control.

Subform's RecordSource did not appear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

couldn't resist looking at your post in detail before heading out to work

regarding your assumptions below

a) correct assumption

b) correct assumption

d) correct assumption

c) Combo22 is unbound(selection SQL statement is below), however its
purpose
is to allow user to select records from tblSampleDetails and display
selected
record in frmSampleDetails. Event for the Combo22 is 'Click' not 'After
Update', don't know if this is important.
Subform is based upon tblBatchDetails and Link and Master Child Fields
point
to the field [Batch number]. I have looked at these Child Fields and
Combo22
is not available for selection
SQL Statement in Row Source for Combo22

SELECT [Sample Details].[Our Sample Number], [Sample Details].[Batch
Number]
FROM [Sample Details];

SQL Statement in Record Source of Subform

Okay, I've just re-read this thread, and here's my current understanding
of
what you are doing:
a) Main form is in form view (shows one record), bound to a table.

b) Subform is in Continuous view (shows many records), bound to the
*same*
table.

c) Combo22 is an unbound combo on the main form. It's purpose is to limit
the records shown in the subform.

d) Subform has a command button that appears beside each record. Clicking
the command button should make that the current record in the main form.

I assume that selecting a record in Combo22 still shows multple records
in
the subform. Therefore Combo22's RowSource is not the same table: it must
be
a lookup (perhaps limiting the subform to one type or category.)

It that's the case, you can remove the code from Combo22's AfterUpdate.
Instead set the Link Master Fields property of the subform control to
Combo22, and the Link Child Fields property to the matching field in the
subform. Now the subform won't show any records until you choose
something
in the combo, and then the subform will show the matching records.

The remaining part of the job is the code for the command button in the
subform. The idea is to FindFirst in the main form's RecordsetClone, and
then set its Bookmark so it displays the record. This kind of thing:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.NewRecord Then
Beep
Else
strWhere = "[ID] = " & Me.[ID]
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in main form. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub

You will need to use your field name in the line starting:
strWhere =

The command button is on the subform which is displayed on/in the main
form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results]
'current'
event other than I have been placing the 'Requery' code in the Main
forms
'current event' in an attempt to solve the problem I am encountering
when
the
command button has been clicked

:

Richard, I don't understand the connection between this command button
and
the form's Current event, nor whether this is in the form named
[Sample
Results] or another form.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform,
could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

:

If you want to reload the list of items available in the combo
named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used
in
the
LinkMasterFields of your subform, the subform should show the
correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to
display
all
the
record details on the main form based upon the selection in the
combo
box.
Also attached to the main form is a sub form which shows all the
other
records that are related to the record being displayed on the
main
form.
The user can click a command button on the sub-form which will
then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The
record
selectors at the bottom of the form go from saying 'record 1 of
XXX'
to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and
am
not
sure
if this is all I need to make it work or even if this is the best
way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub

 

[Guest]

Sorry about the missing SQL

statement is as follows

SELECT [Sample Details].[Our Sample Number], [Sample Details].[Batch
Number], [Sample Details].[Clients Sample Number] FROM [Sample Details] ORDER
BY [Sample Details].[Our Sample Number] DESC;

AfterUpdate is preferred to Click: responds to keyboard properly, applies
only after the value is changed.

You can type Combo22 into the Link Master Fields property of the subform
control.

Subform's RecordSource did not appear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

couldn't resist looking at your post in detail before heading out to work

regarding your assumptions below

a) correct assumption

b) correct assumption

d) correct assumption

c) Combo22 is unbound(selection SQL statement is below), however its
purpose
is to allow user to select records from tblSampleDetails and display
selected
record in frmSampleDetails. Event for the Combo22 is 'Click' not 'After
Update', don't know if this is important.
Subform is based upon tblBatchDetails and Link and Master Child Fields
point
to the field [Batch number]. I have looked at these Child Fields and
Combo22
is not available for selection
SQL Statement in Row Source for Combo22

SELECT [Sample Details].[Our Sample Number], [Sample Details].[Batch
Number]
FROM [Sample Details];

SQL Statement in Record Source of Subform

Okay, I've just re-read this thread, and here's my current understanding
of
what you are doing:
a) Main form is in form view (shows one record), bound to a table.

b) Subform is in Continuous view (shows many records), bound to the
*same*
table.

c) Combo22 is an unbound combo on the main form. It's purpose is to limit
the records shown in the subform.

d) Subform has a command button that appears beside each record. Clicking
the command button should make that the current record in the main form.

I assume that selecting a record in Combo22 still shows multple records
in
the subform. Therefore Combo22's RowSource is not the same table: it must
be
a lookup (perhaps limiting the subform to one type or category.)

It that's the case, you can remove the code from Combo22's AfterUpdate.
Instead set the Link Master Fields property of the subform control to
Combo22, and the Link Child Fields property to the matching field in the
subform. Now the subform won't show any records until you choose
something
in the combo, and then the subform will show the matching records.

The remaining part of the job is the code for the command button in the
subform. The idea is to FindFirst in the main form's RecordsetClone, and
then set its Bookmark so it displays the record. This kind of thing:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.NewRecord Then
Beep
Else
strWhere = "[ID] = " & Me.[ID]
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in main form. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub

You will need to use your field name in the line starting:
strWhere =

The command button is on the subform which is displayed on/in the main
form
and only when the command button is clicked does the problem occur. The
command button has no relation to the main forms [Sample Results]
'current'
event other than I have been placing the 'Requery' code in the Main
forms
'current event' in an attempt to solve the problem I am encountering
when
the
command button has been clicked

:

Richard, I don't understand the connection between this command button
and
the form's Current event, nor whether this is in the form named
[Sample
Results] or another form.

may have just had a problem with the old comp so reposting my reply

Me.Combo22.Requery did not work within the forms current event

Below is the code underneath the command button within the subform,
could
this be the problem.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sample Results"

stLinkCriteria = "[Our Sample Number]=" & Me![Our Sample Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
Thanks

Richard

:

If you want to reload the list of items available in the combo
named
Combo22, the code would be:
Me.Combo22.Requery

But, if Combo22 is bound to a field, and the bound column is used
in
the
LinkMasterFields of your subform, the subform should show the
correct
records without needing to requery the combo.

I have a form which has a combo box. This allows the user to
display
all
the
record details on the main form based upon the selection in the
combo
box.
Also attached to the main form is a sub form which shows all the
other
records that are related to the record being displayed on the
main
form.
The user can click a command button on the sub-form which will
then
display
the record selected in the sub form, in the main form.
The problem then is that this stops the combo box working. The
record
selectors at the bottom of the form go from saying 'record 1 of
XXX'
to
saying 'record 1 of 1'
I have tried the Requery command on form current view to try and
requery
the
combo box and also on the click action of the combo box but to no
avail.
Below is the exact code I have used, however I am new to code and
am
not
sure
if this is all I need to make it work or even if this is the best
way
of
getting the job done

Private Sub Form_Current()
DoCmd.Requery "Combo22"
End Sub