Moving to the first record in a query

  • Thread starter Thread starter Guest
  • Start date Start date
I have a form: frmText based on tblText

On the form is Combo320 in which I select and display destinations from a
child table: tblDestinations using the following query.

SELECT tblDestinations.DestinationsID, tblDestinations.Destination,
tblDestinations.DestinationType, tblDestinations.Publish FROM tblDestinations
ORDER BY tblDestinations.Destination;

On the After Update property of Combo320, I have the following Event
Procedure:

Private Sub Combo320_AfterUpdate()
Me.Requery
Forms!frmText.Refresh
End Sub

I then have a list box that displays all the record headings from the
selected destination:

SELECT tblText.TextID, tblText.DestinationsID, tblText.Heading FROM tblText
WHERE (((tblText.DestinationsID)=Forms!frmText!Combo320));

Everything works fine, except when I select a new destination with Combo320.
It displays the selected child record on the subform, but doesn’t go to the
joined master record in tblText—thereby modifying the record.

I need to move to the first record for the selected destination.

I tried putting the following into Private Sub Combo320_AfterUpdate() but no
joy.

Me.RecordsetClone.FindFirst "[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark

Help please!

Thank you

Peter
 
Thanks Naeem, but no go.

The original first record is still modified to the newly selected
destination.

Naeem said:
what about:
docmd.GoToRecord acActiveDataObject,,acFirst

Peter Stone said:
I have a form: frmText based on tblText

On the form is Combo320 in which I select and display destinations from a
child table: tblDestinations using the following query.

SELECT tblDestinations.DestinationsID, tblDestinations.Destination,
tblDestinations.DestinationType, tblDestinations.Publish FROM tblDestinations
ORDER BY tblDestinations.Destination;

On the After Update property of Combo320, I have the following Event
Procedure:

Private Sub Combo320_AfterUpdate()
Me.Requery
Forms!frmText.Refresh
End Sub

I then have a list box that displays all the record headings from the
selected destination:

SELECT tblText.TextID, tblText.DestinationsID, tblText.Heading FROM tblText
WHERE (((tblText.DestinationsID)=Forms!frmText!Combo320));

Everything works fine, except when I select a new destination with Combo320.
It displays the selected child record on the subform, but doesn’t go to the
joined master record in tblText—thereby modifying the record.

I need to move to the first record for the selected destination.

I tried putting the following into Private Sub Combo320_AfterUpdate() but no
joy.

Me.RecordsetClone.FindFirst "[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark

Help please!

Thank you

Peter
Click to expand...
Click to expand...
 
Peter said:
I have a form: frmText based on tblText

On the form is Combo320 in which I select and display destinations from a
child table: tblDestinations using the following query.

SELECT tblDestinations.DestinationsID, tblDestinations.Destination,
tblDestinations.DestinationType, tblDestinations.Publish FROM tblDestinations
ORDER BY tblDestinations.Destination;

On the After Update property of Combo320, I have the following Event
Procedure:

Private Sub Combo320_AfterUpdate()
Me.Requery
Forms!frmText.Refresh
End Sub

I then have a list box that displays all the record headings from the
selected destination:

SELECT tblText.TextID, tblText.DestinationsID, tblText.Heading FROM tblText
WHERE (((tblText.DestinationsID)=Forms!frmText!Combo320));

Everything works fine, except when I select a new destination with Combo320.
It displays the selected child record on the subform, but doesn’t go to the
joined master record in tblText—thereby modifying the record.

I need to move to the first record for the selected destination.

I tried putting the following into Private Sub Combo320_AfterUpdate() but no
joy.

Me.RecordsetClone.FindFirst "[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark
Click to expand...


You should not be requerying the form, just the list box.
After doing a rquery, the refresh is redundant and won't
accomplish anything.

It's not clear to me if the record navigation you're try to
do should be moving the main form's or the subform's current
record. For the main form, your code is ok, unless
combo320's BoundColumn is not 1 or the DestinationID is a
text field.

Private Sub Combo320_AfterUpdate()
Me.thelistbox.Requery
Me.RecordsetClone.FindFirst _
"[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Because you are moving to a record, the combo320 control
should not be bound to a field in the table. Double check
that its ControlSource property is empty.
 
Magic!

The control source was bound to DestinationsID.

I don't know how you guys process all this at a distance.

THANK YOU

Peter

Marshall Barton said:
Peter said:
I have a form: frmText based on tblText

On the form is Combo320 in which I select and display destinations from a
child table: tblDestinations using the following query.

SELECT tblDestinations.DestinationsID, tblDestinations.Destination,
tblDestinations.DestinationType, tblDestinations.Publish FROM tblDestinations
ORDER BY tblDestinations.Destination;

On the After Update property of Combo320, I have the following Event
Procedure:

Private Sub Combo320_AfterUpdate()
Me.Requery
Forms!frmText.Refresh
End Sub

I then have a list box that displays all the record headings from the
selected destination:

SELECT tblText.TextID, tblText.DestinationsID, tblText.Heading FROM tblText
WHERE (((tblText.DestinationsID)=Forms!frmText!Combo320));

Everything works fine, except when I select a new destination with Combo320.
It displays the selected child record on the subform, but doesn’t go to the
joined master record in tblText—thereby modifying the record.

I need to move to the first record for the selected destination.

I tried putting the following into Private Sub Combo320_AfterUpdate() but no
joy.

Me.RecordsetClone.FindFirst "[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark
Click to expand...


You should not be requerying the form, just the list box.
After doing a rquery, the refresh is redundant and won't
accomplish anything.

It's not clear to me if the record navigation you're try to
do should be moving the main form's or the subform's current
record. For the main form, your code is ok, unless
combo320's BoundColumn is not 1 or the DestinationID is a
text field.

Private Sub Combo320_AfterUpdate()
Me.thelistbox.Requery
Me.RecordsetClone.FindFirst _
"[DestinationsID] = " & Me![Combo320]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Because you are moving to a record, the combo320 control
should not be bound to a field in the table. Double check
that its ControlSource property is empty.
Click to expand...
 
Back
Top