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

stardustvega

Member
Local time
Today, 03:37
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.
 
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.
 
Do you have a field named "Inactive" that is bound to a control on the form?
 
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.
 
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

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.)
 
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!
 
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.
 
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)
 
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?
 
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
 
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

Back
Top Bottom