Reference a Fields Values in the Record Source Query

[padlocked17]

I have a query on a form that is providing all of the information I need for everything on my form.

The problem is I don't know how to refer to a field's value in the query in VBA without having a hidden text field on the form.

I know I can reference a combo box's query to include criteria such as [Forms]![cboSelectStudent] if the control is actually on the form, but how would I refer to a value in the form's query that doesn't have a control on the form.

I've been searching for everything I can think to call this but haven't been able to come up with anything.

Thanks.

 

[boblarson]

If you are talking about the query underlying the form (its recordsource) then, if the code is in the module of the form itself -

Me!YourFieldNameHere or if on a different form than the code is on:

Forms!YourFormName!YourFieldNameHere

 

[padlocked17]

Alright. I must be overlooking something. I have a main form that's form record source is a query containing among other pieces of info, containts a CourseID. I have a combo box on the main form that moves to a different record based on that query and repopulated all of the sub forms on a tab control. On one of the subforms, I have a combo box that has a query as it's record source that is comprised of a tests table, a courses table and a testcourseassociation table. The query here works fine if I simply enter in the course number as the criteria. It returns all of the tests that are associated with each course. When I go back and use my main form and the combo box selecting a record, the combo box in the subform doesn't refresh.

I added a button that would manually refresh all values on the form, and I still can't seam to figure out why it's not working.

Do I need to add a requery command of the subform combo box when an update is made on the combox box selector on the main form?

 

[boblarson]

Do I need to add a requery command of the subform combo box when an update is made on the combox box selector on the main form?

Yes, you would need to do that.

 

[padlocked17]

Awesome. That did the trick. For some reason or another I thought changing records would auto-refresh. What was I thinking? I forgot I was working with Access.

 

[Pat Hartman]

Having to requery the combo in the subform sort of makes sense if you think about it. Access doesn't automaticfally requery it because Access doesn't know that it changes for each record.

Going back to your original question - recordSource fields that are not bound to controls are available in a form's class module. However, this is not the case for reports. Access replaces your report's recordSource query with one of its own and it eliminates any fields that are not actually bound to controls. That means that if you need fields for calculations that you didn't intend to display, you still need to bind them to a control and hide them or they will not be available in the report's class module.

 

[padlocked17]

Access doesn't automaticfally requery it because Access doesn't know that it changes for each record.

Yeah, you would be correct on that. For as much as Access is nice, I still like to complain about it every now and then

Going back to your original question - recordSource fields that are not bound to controls are available in a form's class module. However, this is not the case for reports. Access replaces your report's recordSource query with one of its own and it eliminates any fields that are not actually bound to controls.

This is good information to know. I for sure would have been sitting down trying to reference those fields and have them not be available. You would have seen another post coming up here on the forums.