Textbox ControlSource Question

[RonL]

Hello. This is really basic, but I gotta face up that I don't understand it. Googling gives a ton of stuff, but most of it at a higher level than this must be.

I have a textbox in the header section of a form. I want it to display a value based on this query:

SELECT CamerasTotal
FROM tblShoots
WHERE (([tblShoots]![ShootsID]=[frmShotList]![ShootsFK]));

tblShoots is 1 to many related to tblShotList and the recordsource for frmShotList is a query based on tblShotList (and the problem is the same if I substitute that query for frmShotList in the where clause.)



When I test this query, it asks me for the frmShotList!ShootsFK, even though frmShotList is open and a particular record, which contains a value for ShootsFK, is current. So it's no wonder the textbox shows the #Name error when I make that query its controlsource. What am I missing? Note that I don't really care about *binding* the textbox, I just want it to display a value based on other data in the form. Should I be using a different control? Is this some sort of scope issue?

Thanks for any help. -Ron

[pbaldy]

Well, that isn't the correct syntax:

Forms Refer to Form and Subform properties and controls

Further, you can't use SQL as the control source of a textbox. You can use DLookup:

DLookup Usage Samples

Or you could have that SQL as the row source of a combo.

[RonL]

Thank you, thank you! I'd seen those links before, but bookmarking them now. And I gotta repeat 100 times: a combo box value reference must include the .column(n) property!

-Ron

[pbaldy]

Happy to help! To clarify, a combo reference needs the column specified if you want a column other than the bound column.

[RonL]

Happy to help! To clarify, a combo reference needs the column specified if you want a column other than the bound column.

Which is my case.

New issue: The following

?nz(DLookUp("CamerasTotal","tblShoots","tblShoots!ShootName = '" & [Forms]![frmShotList]![cboShootName].column(1) & "'"),"No cboShootName")

works in the immediate window (when form is in form view), giving the expected result whether cboShootName is populated or not. It also works as the control source for the textbox after pasting it in in design view and going to form view, whether the cbo is populated or not. But leaving it as the control source and closing the form causes Access to crash when next opening the form in form view. This probably has something to do with the fact that the cboShootName is null on form load (despite my using the Nz function), and I'll deal with that appropriately. But the problem sure has cost me a lot of time to figure out. To facilitate development, more precise insight into what's happening would be appreciated. Is there some sort of OnLoad catch-all error handler?

Thx, Ron