Solved Error referring to object in form with query as record source

[stardustvega]

I have a continuous form filtered to a query, so that it only shows entries where the inactive box is NOT checked. When I click a button, I want the current record to update to change the value of the inactive field to 'true'.

The query which is the record source for this particular form looks like this:

SELECT [Junction Events-Vendors].*, [Junction Events-Vendors].Inactive
FROM [Junction Events-Vendors]
WHERE ((([Junction Events-Vendors].Inactive)=False));

My simplified VBA for troubleshooting looks like this:

MsgBox (Inactive.Value)

When I run this I get "Object Required".

Okay, I thought maybe I need to be more explicit. I added an "Inactive" field to my form to see what the control source was and then tried this:

MsgBox ([Junction Events-Vendors].Inactive.Value)

This time I get "Microsoft Access can't find the field |1 referred to in your expression."

I suspect my problem has to do with the fact that the fact that my form is based on a query, but I'm not sure how to fix it.

 

[Eugene-LS]

MsgBox (Inactive.Value)

Try:

MsgBox Me!Inactive

 

[stardustvega]

Try:

MsgBox Me!Inactive

Nope. Returned Error: "Microsoft Access can't find the field "Inactive" referred to in your expression."

 

[Eugene-LS]

Nope. Returned Error: "Microsoft Access can't find the field "Inactive" referred to in your expression."

I suspect the name of the object is wrong
...
Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.

 

[Pat Hartman]

Do you have a field named "Inactive" that is bound to a control on the form?

 

[stardustvega]

Do you have a field named "Inactive" that is bound to a control on the form?

I eventually don't want to have it visible on there, but I put it onto the form for troubleshooting and I am still getting the errors shown here.

Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.

Yep, stand by.

 

[stardustvega]

I suspect the name of the object is wrong
...
Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.

Here you go! I am currently working on the form labelled "Subform Vendor List". It should write to the "Inactive" field in the "Junction Events-Vendors".

 

[stardustvega]

Oh, and the code fires when you hit the little "X" icon. (This is meant to be a soft delete that hides a record by making it inactive rather than truly deleting it.)

 

[Eugene-LS]

Oh, and the code fires when you hit the little "X" icon. (This is meant to be a soft delete that hides a record by making it inactive rather than truly deleting it.)

Fixed ... It was wrong RecordSource query in form

 

[stardustvega]

Got it. I was trusting Access to build the Query for me. Is the issue that I had pulled in [Junction Events-Vendors].Inactive twice in my original query? Also, thank you very much!

 

[stardustvega]

As a note, I renamed the thread based on the actual problem so that it's easier for people to locate it when searching the forums for a similar problem.

 

[Eugene-LS]

Got it. I was trusting Access to build the Query for me. Is the issue that I had pulled in [Junction Events-Vendors].Inactive twice in my original query? Also, thank you very much!

Absolutely right!

 

[stardustvega]

Hrm. Now I'm having issues because when I try to refresh, it doesn't show the change, and trying to force a save doesn't work.

Is this a case where I should be using DAO to update the record instead of doing it this way perhaps, and then just refreshing my record when that's run?

 

[Eugene-LS]

Hrm. Now I'm having issues because when I try to refresh, it doesn't show the change, and trying to force a save doesn't work.

Try:

Private Sub BtnMakeInactive_Click()
    Me!Inactive = True
    Me.Dirty = False 'Saving data
    MsgBox Me!Inactive
    Me.Requery
End Sub

 

[stardustvega]

Try:

Private Sub BtnMakeInactive_Click()
    Me!Inactive = True
    Me.Dirty = False 'Saving data
    MsgBox Me!Inactive
    Me.Requery
End Sub

Actually simply the line Me.Requery did it! I guess that in this case, I needed to use Me.Requery insead of Me.Refresh (I'm guessing because the form is pulling from a query.)

And now I have two new methods (.Dirty and .Requery) to add to my toolbox.