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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"richard" <(E-Mail Removed)> wrote in message
news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
>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

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



"Allen Browne" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "richard" <(E-Mail Removed)> wrote in message
> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
> >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
>
>

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" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "richard" <(E-Mail Removed)> wrote in message
> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
> >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
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"richard" <(E-Mail Removed)> wrote in message
news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>>
>> "richard" <(E-Mail Removed)> wrote in message
>> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
>> >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

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

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "richard" <(E-Mail Removed)> wrote in message
> news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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.
> >>
> >> "richard" <(E-Mail Removed)> wrote in message
> >> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
> >> >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
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"richard" <(E-Mail Removed)> wrote in message
news:F6728DF2-A12E-4F8D-AC9B-(E-Mail Removed)...
> 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
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> "richard" <(E-Mail Removed)> wrote in message
>> news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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.
>> >>
>> >> "richard" <(E-Mail Removed)> wrote in message
>> >> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
>> >> >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

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

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "richard" <(E-Mail Removed)> wrote in message
> news:F6728DF2-A12E-4F8D-AC9B-(E-Mail Removed)...
> > 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
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >>
> >> "richard" <(E-Mail Removed)> wrote in message
> >> news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> "richard" <(E-Mail Removed)> wrote in message
> >> >> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
> >> >> >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
>
>

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



"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "richard" <(E-Mail Removed)> wrote in message
> news:F6728DF2-A12E-4F8D-AC9B-(E-Mail Removed)...
> > 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
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >>
> >> "richard" <(E-Mail Removed)> wrote in message
> >> news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> "richard" <(E-Mail Removed)> wrote in message
> >> >> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
> >> >> >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
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"richard" <(E-Mail Removed)> wrote in message
news:703C1284-E18A-4537-B850-(E-Mail Removed)...
> 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
>
> "Allen Browne" wrote:
>
>> 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 =
>>
>> "richard" <(E-Mail Removed)> wrote in message
>> news:F6728DF2-A12E-4F8D-AC9B-(E-Mail Removed)...
>> > 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
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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.
>> >>
>> >> "richard" <(E-Mail Removed)> wrote in message
>> >> news:28F2E7B5-5B5C-4AD9-8A7E-(E-Mail Removed)...
>> >> > 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" wrote:
>> >> >
>> >> >> 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.
>> >> >>
>> >> >> "richard" <(E-Mail Removed)> wrote in message
>> >> >> news:EA205B6C-281D-41F2-8E68-(E-Mail Removed)...
>> >> >> >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