Bookmark and refresh form

[rajeevs]

Hi All

I have two issues to put forward.
First is bookmarking / or highlighting a particular record in a form. The form is continuous and the records are from a query result. One of the record in that form will be always the last added record from the table. The form display the records in a sort order from the query. What i need is to highlight or bookmark a particular field of that record (the last record added) whatever the position of the record in the form to get the user's attention to this record.
Second issue is in the same form when it opens with the records i want to perform a calculation automatically and update one of the field with the new result and save the updated record.
I already mention that the form displayes query result.
There are two date fields/controls and three time fields/controls.
The calculation need to be based on the dates and two time fields and the time need to be updated as per the calculation perofrmed.
The calculation can be done after the form loaded and can place command button to perform this action.
Please guide me how to achieve this.

Thank you all

[ChipR]

To highlight a particular record, you can add a field to your table/query and mark that record as true and the rest false. Then, do conditional formatting based on that field.
For your second issue, is there any reason you can't run an update query?

[rajeevs]

Hi ChipR
Sorry for the late reply. I think my explanation about the issue is not clear. I would like to explain because with your answer i am nowhere reaching for the result. What i am doing is adding records to a table through a form and on click of a button from that form it opens another form which is a result of a query and that form contains few other records and the last added record. But the records are in a sort order already by a time field from the query. So I want to get focus on the record which is added last in the table. Now the focus is always on the first record of the form. I been searching net about the bookmark and recordclone things but i am not sure what would solve the issue.
Please help
Thank you

[ChipR]

I see what you mean. You don't want to highlight the record, you just want to select it. Is the time field in the query the time the record was created, or some other unrelated time? Is the record you want to move to the last record of the recordset, or do you need to find it based on some other criteria?

[rajeevs]

Hi
Thank you for prompt reply.
My table has few time fields related to each record. My Main form is used to enter new data to the table and the time field is an entry by the user.. That is an unbound form. Once the user finish the entry of a new record he click a button which add the record to the table and it opens another form. The second form record source is a query. This form display the records as per the criteria in query. The second form always contain the currently added record also. This record will be placed according to the sorted time filed. But I want either the cursor focus on this particular record or highlight any filed of that record wherever the position of that record in the second form when the second form opens. Waiting for your reply
Thank you

[ChipR]

What is the primary key by which you can find the new record in the form that is opened?

[rajeevs]

Hi
So fast. Thank you
There is no primary key, but the main form is still kept open and one field is unique say field name as NAME and the NAME field value is still available in the main form. The second form also has the same field.
So while opening the second form it can be used as a reference

[ChipR]

I'll have some code you can use in a second. Would the NAME field be text or number?

[rajeevs]

Hi
It is a text field.

[ChipR]

Ok, I would suggest using the OpenArgs argument to the DoCmd.OpenForm to pass the NAME to the new form.

'OpenArgs is the 7th argument to DoCmd
DoCmd.OpenForm "Other Form Name", , , , , , Name
 
'In the newly opened form
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorHandler
 
  Dim rs As Object
 
  If Not IsNull(OpenArgs)
    Set rs = Me.RecordsetClone
    rs.FindFirst "NAME = """ & OpenArgs & """"
    If Not rs.nomatch Then
      Me.Bookmark = rs.Bookmark
    End If
  End If
 
ExitCode:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error Number: " & Err.Number & vbcrlf & "Description: " & Err.Description
 
End Sub

[rajeevs]

Hi ChipR
Excellent !!!
This is what i was really looking for. Thank you so much.
I tried your code and it does what i was looking for the last one week.
Thank you once agin.
And can i mention another issue as part of the same thread?
With the second form which is opened with records has time fields that already i mentioned. I need to find the difference of the time from this recordset.
if record1 has time filed 1030 and record2 has timefield 1036 and record3 has timefield 1048, i need to show the time difference in the footer of the form (or anywhere in the form) that diff1=0006 diff2=0012.
May be i am not clear. But with this the user can find the next time slot avialable for the new record he added and can edit that record. I can find the Dmax and do the calculation but that is not the requirement. I need a function which cylce through all the records and find the difference of time from 1st record to the last record. Hope you can understand what i mean.

Once again thank you for the patience you have shown and the help

[ChipR]

I'm worried that this approach will be very slow, but it's the only thing I can think of without adding another field to the table.

 = [TimeField] - DMax ("TimeField", "myTable", "TimeField < " & [TimeField])

If these are dates, then something like DateDiff may be required.
If this form shows records in a continuous fashion, this will have to be in a function call, or they will all be set to the last calculation.