Label based on Query result - returns #Name?

[baulrich]

I have a label on form that I'd like to populate with the result of the query. That query is defined so that it uses the value of another label on the form as criteria. It seems that I've used this method before and it's worked.



The query is based on a Linked SQL table (didn't think that should matter). I nosed around enough to see that the problem could be with field names, so I tried using field name aliases in the query and then referencing those in the label.

Any ideas what is wrong? Values in use are listed below.

thanks....


Label Control Source = "=[qry_frm_job_edit_job_lookup]![Field1]" (Field1 is the alias I'm testing with)

qry_frm_job_edit_job_lookup = "SELECT TCM_JCJob.JobStructureRID AS Field1, TCM_JCJob.JobStructure1 AS Field2, TCM_JCJob.Name AS Field3
FROM TCM_JCJob
WHERE (((TCM_JCJob.JobStructureRID)=[Forms]![frm_job_Edit]![Text_TCM_jobstructureRID]));"

[pbaldy]

I assume you mean a textbox. You can't get the value that way unless the form is bound to the query. If not, look at DLookup().

[Missinglinq]

Paul, as well as myself, are assuming that you're actually talking about Textboxes, rather than Labels, because Labels have neither Values nor ControlSources. Also, it's not possible that this method has ever worked for you. As Paul said, you'd have to use the DLookup() function for this kind of thing.

We really need to have the Label/Textbox thing clarified, before getting further into this thing for you.

Linq ;0)>

[baulrich]

Thanks guys. I actually am using a label, not a textbox. Just because the info being displayed in the control is intended to not be edited by the user. But I had already tried using a textbox instead and get the exact same error. If it's possible to make the textbox work that's fine, I'll just change the visual properties of it to grey it out. For what it's worth, when I create a label, it does have a "ControlSource" just like the textbox.

I tried the DLookup() function but can't get it to work.

It seems I'm basically accomplishing the same thing successfully on another form, but the results of the query are displayed in a list box rather than a label or text box. My next step was going to be trying to create a listbox with only 1 row and see if I could display the desired field from the query.

I'll attach a screenshot of the form, as it may help to visualize what I'm trying to do.

thanks....

[pbaldy]

Is this appropriate to the situation?

http://www.baldyweb.com/Autofill.htm

[baulrich]

Thanks... I think I'm getting close to figuring this out. What you suggested... using hidden fields from a combobox column based on a query is exactly what I need to do... In fact I'm doing that very thing on the version of this form that allows users to create a new record. This is the "edit" version of that form and in this case the "linked job" ID field in the record has (optionally) already been populated. This means I have the ID that I need to look up the job info from the other table (on which this form is NOT based). Right now, that combobox is based on a query that shows the users ALL of the records in the remote table. And it needs to be that way in case they want to use the combobox to change the linked job ID.

So, the thing I don't know how to do is... leave that combobox so it shows all the records as it does now, but have it's default value (on form load) be based on the existing linked job ID, which may already exist. I know how to use the values from controls on the form as filter criteria for the query on which the combox is based... but if I do that in this case, the user will only see the one entry that matches the linked job that's already assigned.

Thinking out loud... I guess I could create a second, hidden combobox based on a query that did use control's value as criteria. And the original combobox would allow the user to change that ID. Surely there's a better way than that.

thanks....

[June7]

Since when does Label control have a ControlSource? Labels have a Caption property on the Format tab. Under the Data tab I see only the SmartTags property.

[pbaldy]

If the combo is bound, it should already come up with the selected value. The row source can still be all values.

[baulrich]

Thanks pbaldy!! That combobox was not bound and that was the problem. I was making that WAY harder than it needed to be!!

[pbaldy]

Happy to help!