Solved Error referring to object in form with query as record source (1 Viewer)

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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:

Code:
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:

Code:
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:

Code:
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.
 

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
Try:
Code:
MsgBox Me!Inactive
Nope. Returned Error: "Microsoft Access can't find the field "Inactive" referred to in your expression."
 

Eugene-LS

Registered User.
Local time
Tomorrow, 02:12
Joined
Dec 7, 2018
Messages
481
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

Super Moderator
Staff member
Local time
Today, 19:12
Joined
Feb 19, 2002
Messages
42,977
Do you have a field named "Inactive" that is bound to a control on the form?
 

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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".
 

Attachments

  • TestDB.accdb
    928 KB · Views: 190

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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

Registered User.
Local time
Tomorrow, 02:12
Joined
Dec 7, 2018
Messages
481
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
 

Attachments

  • TestDB_v02.zip
    205.1 KB · Views: 196

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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

Registered User.
Local time
Tomorrow, 02:12
Joined
Dec 7, 2018
Messages
481
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! (y)
 

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
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

Registered User.
Local time
Tomorrow, 02:12
Joined
Dec 7, 2018
Messages
481
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:
Code:
Private Sub BtnMakeInactive_Click()
    Me!Inactive = True
    Me.Dirty = False 'Saving data
    MsgBox Me!Inactive
    Me.Requery
End Sub
 

stardustvega

Member
Local time
Today, 18:12
Joined
Feb 4, 2022
Messages
36
Try:
Code:
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. :)
 

Users who are viewing this thread

Top Bottom