Multiple "WHERE" condition on form button not recognizing existing field

[kelann]

Hi, all!



I have a report and a form both drawn from the same table (ResponsesT).

The report (TrackEventRep) contains the following (among others):

A button (ButtonUpdateStatusForm).
Fields RespEventName and RespOrgName.

The button opens a form (TrackUpdateStatusF) that contains the following (among others):

Fields RespEventName and RespOrgName. Same field names as the report.

The button is supposed to open the form so that a particular organization's RSVP status for a particular event can be updated; thus it should open the form for the record that equals both RespOrgName and RespEventName.

Here's my Event Procedure for the button:

Private Sub ButtonUpdateStatusForm_Click()
DoCmd.OpenForm "TrackEventStatusF", acViewPreview, , "RespOrgName=" & Me.RespOrgName & " AND RespEventName=" & Me.RespEventName & "'"
End Sub

However, I am getting Run-time error '2465' that says "Microsoft Access can't find the field 'RespEventName' referred to in your expression. I've checked and checked and checked, and it's there, with no typos. I've googled and googled and googled to make sure I have the best understanding of the language.

Any thoughts, you master debuggers????

Thank you so much!

--ak

[kelann]

Rats! Now it's changed it's mind and is giving me this error!

Compile error:

Method or data member not found, and it highlights Me.RespOrgName

[June7]

If RespOrgName and RespEventName are text fields then need apostrophe delimiters for the criteria (you have a lone apostrophe after RespEventName):

"RespOrgName='" & Me.RespOrgName & "' AND RespEventName='" & Me.RespEventName & "'"

Date/Time type would use # delimiters, number type no delimiters.

[kelann]

Thank you, June!

I've updated the code as follows, but it's still giving me "can't find the field RespEventName".

DoCmd.OpenForm "TrackEventStatusF", acViewPreview, , "RespOrgName='" & Me.RespOrgName & "' AND RespEventName='" & Me.RespEventName & "'"

Any furhter thoughts?

Much appreciated!!!!!

--ak

[ssanfu]

I don't follow your process. You open a REPORT to open a Form??? Seems backwards to me. AFAIK, you can't select a record on a report to get variables that would then be used to filter a form. (But I don't use A2010)

DoCmd.OpenForm "TrackEventStatusF", acViewPreview, , "RespOrgName='"  & Me.RespOrgName & "' AND RespEventName='" &  Me.RespEventName & "'"

And if you open the form in print preview mode (acViewPreview), you wouldn't be able to set the RSVP status anyway.

Check the control name on the form.
Check the Control source in the control.
Delete the control and re-add it. (worked for me several times- some kind of corruption)

[kelann]

Well, what they have is a report of everyone's status as to who is coming and who isn't, etc., and after viewing the report, they might want to update the status of one of the organizations.

Thanks, re acViewPreview -- I deleted that part. I would like to add "duh!"

You might be right ... re variables on a report ... I surely don't know! I am learning, but I'm not as far as those details yet!!

Thoughts, anyone?

Alternatives that immediately pop to mind?

--ak

[June7]

I just tested a button on a report to Debug.Print a value from the current record. The button is in the Detail section. Report in ReportView. The button works.

This was with Access 2007.

I tested ! (bang) and . (dot) in the code.

Either should work but . (dot) provokes intellisense popup tips in VBA.

I will look at your db posted in other thread.

[June7]

There isn't a form named TrackEventStatusF. I changed code to TrackUpdateStatusF. I put the RespEventName field in Detail section and the code runs.

[kelann]

June,

1. I am a moron. You don't know how many times I looked at those verifying the accuracy of the field name!!
2. You are a genius! I would never have thought that moving that into the detail section would have made a difference.

Thank you so very, very much!

--ak