Re: textbox controsource as recordset field

Marsh,
Thanks for the suggestion. Here is a paste of the exact chunk of code, only
with field and table names changed to protect the innocent (per company
operating procedure). I've only changed the names, not the number of
fields, tables, or any calculations or anything:

lcSelect = "SELECT myField FROM myTable1 WHERE myField NOT IN ( SELECT [" &
lcImport & "] FROM myTable2 )"
Set rstHC = dbs.OpenRecordset(lcSelect)
If rstHC.RecordCount > 0 Then
Me.txtHC.ControlSource = rstHC.Fields("myField")
Else
Me.txtHC.Visible = False
End If

The field being returned in this query is a text field. I've made no
changes to the textbox, just dropped it on the report. I assume it is set
up to handle text by default. There is no data displayed on the report,
only "#Name?". Does that help? I basically just started this job, don't
want to break their rules yet, but I guess I will if I have to. I actually
used a msgbox in the test I ran that displayed the number of records
returned (17), so I know there is data. My guess here is that it is the
controlsource assignment. Unfortunately, my lack of experience with this
technology means that it is truly a guess. Any suggestions would be very
appreciated. Thanks a lot

john

"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:6kj281tqesstuufsb15uiephro0etfegoa@xxxxxxxxxx
>A suggestion for the future. Post a Copy/Paste of the real
> query, code or expression. You don't want us wasting your
> or our time debugging typos or paraphrased code.
> --
> Marsh
> MVP [MS Access]
>
>
> RedFish wrote:
>>Well, that is not actually the field name, just the name I'm using for
>>posting this. Also, I've got most of the error handling in place,
>>expecially to handle the case of 0 records returned. I will double check
>>this case to make sure it is working properly, though. Perhaps I
>>should've
>>explained. Just trying to cut to the chase :) Thanks Marsh.
>>
>>> RedFish wrote:
>>>>I'm a newbie to Access 2000, trying to set up a report here. I'm
>>>>creating
>>>>a
>>>>recordset with a query, and want to assign the result to a textbox on
>>>>the
>>>>report. I've obviously got something wrong.
>>>>
>>>>'lcImport is defined by getting the result of a different query, on my
>>>>import map.
>>>>lcSelect = "SELECT [" & lcImport & "] AS myfield FROM MyTable1 WHERE ["
>>>>&
>>>>lcImport & "] NOT IN ( SELECT myfield FROM MyTable2 )"
>>>>Set rstCD = dbs.OpenRecordset(lcSelect)
>>>>
>>>>Me.txtCD.ControlSource = rstCD.Fields("myfield")
>>>>
>>>>This results in a type mismatch, and I just don't know enough about how
>>>>Access works to figure out why.
>>>
>>>
>>"Marshall Barton" wrote
>>> Aside from a potential name confusion of "myfield", I think
>>> there is a very obscure thing going on here. If I am
>>> interpreting your code correctly, I think the problem may
>>> arise when the query does not return any records. You
>>> should check the recordset's RecordCount property to make
>>> sure it's greater than zero and add some code to deal with
>>> the case when it is zero?


.

Relevant Pages

  • Re: Issue with order placement...
    ... David's suggestion to use Me.Dirty = False is a good one. ... variable again to re-execute the same query, but I can also tell you this -- ... executing the identical update query to set a field to zero twice in a row ... There is good reason to set ...
    (comp.databases.ms-access)
  • Re: textbox controsource as recordset field
    ... query, code or expression. ... Just trying to cut to the chase:) Thanks Marsh. ... >> RedFish wrote: ... >> the case when it is zero? ...
    (microsoft.public.access.reports)
  • Use of KB article 296249
    ... By suggestion from Marsh, I used KB article # 296249 ... summary data at the foot of every page on a report. ... the sum, I get a zero. ... Can anyone (I guess Marsh!) help me figure out why this is happening? ...
    (microsoft.public.access.reports)
  • Re: Microsoft Cleanliness
    ... preceeds each table with the word TABLE and each query with "QUERY". ... Make sure the import window can't be resized. ... We have a beautiful marsh, Cootes Paradise, here at the west end of Lake ... Ontario but it's been dying for decades because of the invasion of carp, ...
    (comp.databases.ms-access)
  • Re: Microsoft Cleanliness
    ... preceeds each table with the word TABLE and each query with "QUERY". ... We have a beautiful marsh, Cootes Paradise, here at the west end of Lake Ontario but it's been dying for decades because of the invasion of carp, which muddy the water, and uproot the vegetation. ...
    (comp.databases.ms-access)