Replace FindFirst to GoToLast (2 Viewers)

[Zydeceltico]

Hi All -

How do I modify the following code to go to the most recent record? You can see I tried inserting acCmdRecordGoToLast after the FindFirst but it doesn't work.

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    RunCommand acCmdRecordsGoToLast
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
      Me.NavigationButtons = False
    End If
    Else
        Me.cboPartType.RowSource = "qryFinalProductComponents2"
  End If
End Sub

I also tried adding an If to the Else at the end like this:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
      Me.NavigationButtons = False
    End If
    Else
        If Not Me.NewRecord Then
            RunCommand acCmdRecordsGoToLast
            Me.cboPartType.RowSource = "qryFinalProductComponents2"
        End If
  End If
End Sub

This goes to the most recent record but it opens a "Enter Parameter Value" dialog box looking for "InspectionEvent_FK " which I don't want it to do as it is already recorded and shows up in the record after I click Cancel or OK in the dialog box.

What am I missing?

Thanks a bunch!

Tim

 

[Zydeceltico]

I tried changing FindFirst to FindLast which also doesn't work. The form still opens to the first record.

And fyi - Data Entry is set to No for this form.

 

[theDBguy]

Hi. Can you describe what you’re trying to do using plain words? If you’re trying to navigate the form to a specific record when it opens, one common approach is to use a bookmark.

 

[Zydeceltico]

Hi. Can you describe what you’re trying to do using plain words? If you’re trying to navigate the form to a specific record when it opens, one common approach is to use a bookmark.

I have a form that has 2 textboxes. one is called txtStart. The other is called txtStop. Each of their labels has been converted into a button that when clicked populates the corresponding text box with =Now().

There could be a lot of time between the Start and the Stop meaning that I may need to close the form and come back to it later to click Stop.

I originally wanted to re-open the form to the most recent record as I thought it would be the most likely one that I would need to click Stop on.

In the interim since originally posted I realized that I could instead create a query that checks both text boxes for Start=Is Not Null and Stop = Null which would return much more accurately to the record I am looking for.

SO now I am trying to discern how to use that query to populate my form which still uses the following code on Load. I'm still scratching my head - just differently now.

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
      Me.NavigationButtons = False
    End If
    Else
        Me.cboPartType.RowSource = "qryFinalProductComponents2"
  End If
End Sub

 

[theDBguy]

Hi. Ignoring the code for now... So, are you trying to open the form to the "first" record matching the criteria: Start Is Not Null AND Stop Is Null? If so, does it matter if the form only displays this and only this one matching record?

 

[The_Doc_Man]

I originally wanted to re-open the form to the most recent record as I thought it would be the most likely one that I would need to click Stop on.

I'm going to assume that your .RecordSource includes some type of ORDER BY clause to keep times in chronological order.

The way to do this is in the form's .OnOpen event code, use the .RecordSetClone to do a .FindLast or other search, copy the .BookMark, and use that to move the current record on the form. In the linked thread, the post DOES NOT show using .RecordSetClone, but could have. (And in fact the person showing how to use bookmarks gets gigged for having opened a separate recordset, when .RecordSetClone is open and available.)

https://www.tek-tips.com/viewthread.cfm?qid=303886

 

[Zydeceltico]

Hi. Ignoring the code for now... So, are you trying to open the form to the "first" record matching the criteria: Start Is Not Null AND Stop Is Null? If so, does it matter if the form only displays this and only this one matching record?

It would be prudent to return all records that fit that criteria. And yes - to answer your question directly - I am trying to open the form to the "first" record matching the criteria: Start Is Not Null AND Stop Is Null.

 

[Zydeceltico]

I'm going to assume that your .RecordSource includes some type of ORDER BY clause to keep times in chronological order.

The way to do this is in the form's .OnOpen event code, use the .RecordSetClone to do a .FindLast or other search, copy the .BookMark, and use that to move the current record on the form. In the linked thread, the post DOES NOT show using .RecordSetClone, but could have. (And in fact the person showing how to use bookmarks gets gigged for having opened a separate recordset, when .RecordSetClone is open and available.)

https://www.tek-tips.com/viewthread.cfm?qid=303886

Before I start working with your idea......does this mean that I use my code as is and ADD code for a RecordsetClone? Or do I fogo my current Load code and replace with RecodsetClone?

Yes - I will have an ORDER BY DateTime.

 

[theDBguy]

It would be prudent to return all records that fit that criteria. And yes - to answer your question directly - I am trying to open the form to the "first" record matching the criteria: Start Is Not Null AND Stop Is Null.

Hi. If you only want records fitting this criteria, then you don't even need code. If you sort the data correctly, then the form should automatically open to the first matching record. Makes sense?
For example: Create a query like

SELECT * FROM TableName WHERE Start Is Not Null AND Stop Is Null ORDER BY Start

then, bind the form to this query; and when you open it, the first record should be the first Start with an empty Stop.

 

[Zydeceltico]

Hi. If you only want records fitting this criteria, then you don't even need code. If you sort the data correctly, then the form should automatically open to the first matching record. Makes sense?
For example: Create a query like

SELECT * FROM TableName WHERE Start Is Not Null AND Stop Is Null ORDER BY Start

then, bind the form to this query; and when you open it, the first record should be the first Start with an empty Stop.

That does make sense. Thanks. The only thing I'm wondering is how to bind the form to the query as I open the form initially to click Start in which case the above would be a problem. The second time I open that form is from a different button on a different form. I'm wondering if there is a way to tell that button's On_Click to make this query the source for the second opening?

 

[theDBguy]

That does make sense. Thanks. The only thing I'm wondering is how to bind the form to the query as I open the form initially to click Start in which case the above would be a problem. The second time I open that form is from a different button on a different form. I'm wondering if there is a way to tell that button's On_Click to make this query the source for the second opening?

Okay, no, let's forget my idea. I didn't know you're using the same form as with the rest of the records with Stop entered. So, you may still have two choices here.
1. Use the bookmark approach, or
2. Simply filter the form to the same criteria
Now, depending on how you open the form, it might dictate whether you can or should apply the criteria or not. Let me know if this doesn't make sense, and I'll elaborate.


PS. By the way, if you click Start and then Stop later, wouldn't you only have one record with a Stop missing at a time? Unless, this is a multi-user app.

 

[Zydeceltico]

Okay, no, let's forget my idea. I didn't know you're using the same form as with the rest of the records with Stop entered. So, you may still have two choices here.
1. Use the bookmark approach, or
2. Simply filter the form to the same criteria
Now, depending on how you open the form, it might dictate whether you can or should apply the criteria or not. Let me know if this doesn't make sense, and I'll elaborate.


PS. By the way, if you click Start and then Stop later, wouldn't you only have one record with a Stop missing at a time? Unless, this is a multi-user app.

It's a multi-user app. But besides that there are two tables involved. This form is bound to the second one. The first one passes a PK to the second one so I could actually have many of these "incomplete" at once.

Here's the code I use to open it the first time from frmInspectionEvent:

DoCmd.OpenForm "frmCoilChange", , , , , acDialog, Me.InspectionEvent_PK

And here's the code I use to open it the second time from frmMainMenu:

    DoCmd.OpenForm "frmCoilChange", acNormal, , , acFormEdit
    Forms!frmCoilChange.cboPartType.Enabled = False

and here is it's Load event code:

  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindLast "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
      Me.NavigationButtons = False
    End If
    Else
        Me.cboPartType.RowSource = "qryFinalProductComponents2"
  End If

The qryFinalProductComponents2 brings back the recorded value of a selection from a combobox when the form was first opened.

I am working on a workaround for that by taking it out of the equation in another post that I posted this morning. That one is a real brain teaser.

https://www.access-programmers.co.uk/forums/showthread.php?t=304572

 

[theDBguy]

So, just for a quick test, comment out the Load event code you have and add the following code in the Open event instead.

If Me.OpenArgs > "" Then
    Me.Filter = "InspectionEvent_FK=" & Me.OpenArgs & " AND Start Is Not Null AND Stop Is Null"
    Me.FilterOn = True
End If

I have a feeling I am missing something still...

 

[Zydeceltico]

So, just for a quick test, comment out the Load event code you have and add the following code in the Open event instead.

If Me.OpenArgs > "" Then
    Me.Filter = "InspectionEvent_FK=" & Me.OpenArgs & " AND Start Is Not Null AND Stop Is Null"
    Me.FilterOn = True
End If

I have a feeling I am missing something still...

When I do that I am presented with an Enter Parameter Value dialog box requesting "Start." If I hit OK instead of entering a Date/Time a second Enter Parameter Value dialog opens requesting "Stop."

 

[Zydeceltico]

When I do that I am presented with an Enter Parameter Value dialog box requesting "Start." If I hit OK instead of entering a Date/Time a second Enter Parameter Value dialog opens requesting "Stop."

If I cancel or hit OK out of the second dialog, the form opens and it shows that a filter is applied.

 

[theDBguy]

When I do that I am presented with an Enter Parameter Value dialog box requesting "Start." If I hit OK instead of entering a Date/Time a second Enter Parameter Value dialog opens requesting "Stop."

Okay, please make sure to replace Start and Stop with the correct field names for your table and try again.

 

[Zydeceltico]

Okay, please make sure to replace Start and Stop with the correct field names for your table and try again.

I also entered some test data one record complete and one record incomplete (needing a Stop).

Changed the code back to what you suggested.

Open from second button and am presented with a Parameter Dialog wanting the InspectionEvent_FK from the original code and click OK and it goes to the first COMPLETE record not the one defined as Start = Is Not Null, STop - Is Null although I can use navigation to go to the second record which is the one that I was hoping would open - the one with a Start time and needing a Stop time.

 

[Zydeceltico]

Here is the code I used:

If Me.OpenArgs > "" Then
Me.Filter = "InspectionEvent_FK=" & Me.OpenArgs & " AND Me.txtSetupStart Is Not Null AND Me.txtSetupStop Is Null"
Me.FilterOn = True
End If


Should I have called out the controls like this:
Forms!frmCoilChange.txtSetupStart instead of Me.?

 

[Zydeceltico]

Open from second button and am presented with a Parameter Dialog wanting the InspectionEvent_FK from the original code and click OK and it goes to the first COMPLETE record not the one defined as ...

I should note that I did not enter a value in the Parameter dialog. I simply clicked OK.

 

[Zydeceltico]

Here is the entire Sub with original code commented out:

Private Sub Form_Load()
If Me.OpenArgs > "" Then
    Me.Filter = "InspectionEvent_FK=" & Me.OpenArgs & " AND Me.txtSetupStart Is Not Null AND Me.txtSetupStop Is Null"
    Me.FilterOn = True
End If
  'Dim rs As DAO.Recordset
  'If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    'Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    'rs.FindLast "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    'If rs.NoMatch Then  'it does not exist so you need to create it
      'DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      'Me.InspectionEvent_FK = Me.OpenArgs
      'Me.NavigationButtons = False
    'End If
    'Else
        'Me.cboPartType.RowSource = "qryFinalProductComponents2"
  'End If
End Sub