Replace FindFirst to GoToLast (1 Viewer)

theDBguy · Apr 6, 2019

Zydeceltico said:
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.?

Hi. The code you're using is not what I am intending. Using Me.txtSetupStart refers to the control (textbox) on the form. I intend for you to use the field name in the table for where you store the Start and Stop values. So, in other words

Code:

Me.Filter = "FieldNameHere = " & OpenArgsHere & " AND FieldNameHere Is Not Null AND  FieldNameHere Is Null"

Hope it makes sense...

Zydeceltico · Apr 6, 2019

theDBguy said:
Hi. The code you're using is not what I am intending. Using Me.txtSetupStart refers to the control (textbox) on the form. I intend for you to use the field name in the table for where you store the Start and Stop values. So, in other words

Code:

Me.Filter = "FieldNameHere = " & OpenArgsHere & " AND FieldNameHere Is Not Null AND FieldNameHere Is Null"

Hope it makes sense...

Here it is:

Code:

Private Sub Form_Load()
If Me.OpenArgs > "" Then
    Me.Filter = "InspectionEvent_FK=" & Me.OpenArgs & " AND SetupStart Is Not Null AND SetupStop Is Null"
    Me.FilterOn = True
End If

Does it matter that both fields are Date/Time datatype? Both are set to a cndButton that when clicked returns =Now()

theDBguy · Apr 6, 2019

Zydeceltico said:
Does it matter that both fields are Date/Time datatype? Both are set to a cndButton that when clicked returns =Now()

No, it doesn't matter. We're just checking if the field is empty/null or not. Are you still getting any parameter prompts?

Zydeceltico · Apr 6, 2019

theDBguy said:
No, it doesn't matter. We're just checking if the field is empty/null or not. Are you still getting any parameter prompts?

Yes. For the InspectionEvent_FK.

Also worth noting, OpenArgs was used to open the form the first time to pass InspectionEvent_FK to the second form.

So maybe it isn't necessary to check for OpenArgs the second time as InspectionEvent_FK already exists in the table record.

Zydeceltico · Apr 6, 2019

Zydeceltico said:
Yes. For the InspectionEvent_FK.

Also worth noting, OpenArgs was used to open the form the first time to pass InspectionEvent_FK to the second form.

So maybe it isn't necessary to check for OpenArgs the second time as InspectionEvent_FK already exists in the table record.

If I cancel or OK out the parameter request the form opens but to the first record not the second (the one with Start=Is Not Null and Stop =Is Null). The first record Start and Stop are both Not Null on purpose in order to test.

theDBguy · Apr 6, 2019

Zydeceltico said:
If I cancel or OK out the parameter request the form opens but to the first record not the second (the one with Start=Is Not Null and Stop =Is Null). The first record Start and Stop are both Not Null on purpose in order to test.

Hi. If you're still getting a parameter prompt, then we're still not using the correct name for the field. When you click OK on the prompt, are all the ID's the same on the first and second and so on records on the form? For instance, you said the form opens to a first record and then you go to the second record. Do these two records belong to the same parent record? For now, try removing the OpenArgs part. So, in the Open event, try this instead:

Code:

If IsNull(Me.OpenArgs) Then
    Me.Filter = "SetupStart Is Not Null AND SetupStop Is Null"
    Me.FilterOn = True
End If

Zydeceltico · Apr 6, 2019

theDBguy said:
Hi. If you're still getting a parameter prompt, then we're still not using the correct name for the field. When you click OK on the prompt, are all the ID's the same on the first and second and so on records on the form? For instance, you said the form opens to a first record and then you go to the second record. Do these two records belong to the same parent record? For now, try removing the OpenArgs part. So, in the Open event, try this instead:

Code:

If IsNull(Me.OpenArgs) Then Me.Filter = "SetupStart Is Not Null AND SetupStop Is Null" Me.FilterOn = True End If

Changed. It is still requesting parameter value but I see why that is now. There is a cbo on the second form that takes a value from a cbo on the first form. This is the semi-related issue that I mentioned above. I have that one figured out in regards to this post. Totally different issue in totally different post today.

So I clicked through the parameter dialog - et Voila! It opened to the correct record! Yeah!

THANK YOU

Now I need to work on the other issue.

Seriously - thank you for sticking with me on that one!

theDBguy · Apr 6, 2019

Zydeceltico said:
Changed. It is still requesting parameter value but I see why that is now. There is a cbo on the second form that takes a value from a cbo on the first form. This is the semi-related issue that I mentioned above. I have that one figured out in regards to this post. Totally different issue in totally different post today.

So I clicked through the parameter dialog - et Voila! It opened to the correct record! Yeah!

THANK YOU

Now I need to work on the other issue.

Seriously - thank you for sticking with me on that one!

Hi. Glad to hear you got it somewhat working. I'm afraid it might not be the best solution since I couldn't understand what else is going on with your form. Hopefully, once everything else falls into place, we might be able to come up with a better solution. Good luck!

Zydeceltico · Apr 6, 2019

theDBguy said:
Hi. Glad to hear you got it somewhat working. I'm afraid it might not be the best solution since I couldn't understand what else is going on with your form. Hopefully, once everything else falls into place, we might be able to come up with a better solution. Good luck!

Yeah - I'll still need to figure out how to let the code know that if "I click button1 then open with this portion of the Load event and if I click this other button then use this other portion of the Load code." But I am a million miles further than I was."

Thanks again! I'll keep you updated.

theDBguy · Apr 6, 2019

Zydeceltico said:
Yeah - I'll still need to figure out how to let the code know that if "I click button1 then open with this portion of the Load event and if I click this other button then use this other portion of the Load code." But I am a million miles further than I was."

Thanks again! I'll keep you updated.

That is why I am including the OpenArgs check in the code I suggested. It's possible you could make it so if there's an OpenArgs passed, then react differently than when there's no OpenArgs passed. Or, if there's always going to be an OpenArgs passed, then perhaps include the information of whether to go one way or another. This could be in the OpenArgs too or in a global variable or TempVar or table data, etc.

Zydeceltico · Apr 6, 2019

theDBguy said:
That is why I am including the OpenArgs check in the code I suggested. It's possible you could make it so if there's an OpenArgs passed, then react differently than when there's no OpenArgs passed. Or, if there's always going to be an OpenArgs passed, then perhaps include the information of whether to go one way or another. This could be in the OpenArgs too or in a global variable or TempVar or table data, etc.

Maybe something like my original code but use an Else for when there is no OpenArgs passed? Because - currently - OpenArgs is only passed when the form is first called.

theDBguy · Apr 6, 2019

Zydeceltico said:
Maybe something like my original code but use an Else for when there is no OpenArgs passed? Because - currently - OpenArgs is only passed when the form is first called.

Right. Try it out. Use an Else branch or simply negate the check for the OpenArgs value.

Zydeceltico · Apr 7, 2019

theDBguy said:
Right. Try it out. Use an Else branch or simply negate the check for the OpenArgs value.

Quick question - and no - I haven't researched it yet - been tearing out a bathroom.

Does the filter work strictly with table fields?

theDBguy · Apr 7, 2019

Zydeceltico said:
Does the filter work strictly with table fields?

Hi. The short answer is yes. Here's the longer answer. When we use Me.Filter, we are talking about a property of the form. This is the same property on the property sheet of the form as show below.

It is similar to a WHERE clause in a query. Now, what we're actually filtering here is the Record Source of the form (the first property shown in the above image). So, if the form is bound to a table, then we are filtering the fields of that table. If the form is bound to a query, then we are filtering the columns of that query, which are basically fields of one or more tables.

Zydeceltico · Apr 7, 2019

theDBguy said:
Hi. The short answer is yes. Here's the longer answer. When we use Me.Filter, we are talking about a property of the form. This is the same property on the property sheet of the form as show below.

It is similar to a WHERE clause in a query. Now, what we're actually filtering here is the Record Source of the form (the first property shown in the above image). So, if the form is bound to a table, then we are filtering the fields of that table. If the form is bound to a query, then we are filtering the columns of that query, which are basically fields of one or more tables.

I have been unfamiliar with filters. That seems enormously helpful especially since 90% of my forms are bound to tables.

theDBguy · Apr 7, 2019

Zydeceltico said:
I have been unfamiliar with filters. That seems enormously helpful especially since 90% of my forms are bound to tables.

If you have datasheet forms, filtering through the dropdown arrows uses the same form property.

MajP · Apr 7, 2019

I think your question and approach may have changed from the initial question

How do I modify the following code to go to the most recent record

However, to answer that specific question you simply change
rs.FindFirst "InspectionEvent_FK = " & me.openArgs
to
rs.FindLast "InspectionEvent_FK = " & me.openArgs

The recordset object has a findlast method just like findfirst that does exactly what you are asking.
https://docs.microsoft.com/en-us/of...abase-reference/recordset-findlast-method-dao

Locates the last record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only

Cronk · Apr 8, 2019

When I do that I am presented with an Enter Parameter Value dialog box requesting "Start.

Replace "Start" with the name of the corresponding field in the form's recordsource.

Zydeceltico · Apr 8, 2019

MajP said:
I think your question and approach may have changed from the initial question

However, to answer that specific question you simply change
rs.FindFirst "InspectionEvent_FK = " & me.openArgs
to
rs.FindLast "InspectionEvent_FK = " & me.openArgs

The recordset object has a findlast method just like findfirst that does exactly what you are asking.
https://docs.microsoft.com/en-us/of...abase-reference/recordset-findlast-method-dao

Thanks. I did change my approach.

I tried to use FindLast and for some reason I could not get it to work.

I will retry using it (FindLast) at some point in order to add another tool to my toolbox. Thanks you again!

Replace FindFirst to GoToLast (1 Viewer)

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Attachments

Registered User.

I’m here to help

You've got your good things, and you've got mine.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread