textbox controsource as recordset field

  • Thread starter Thread starter RedFish
  • Start date Start date
R

RedFish

Hello,
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. Can anyone help? Thanks.

'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. Thanks for any help,

John
 
RedFish said:
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.
Click to expand...


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?
 
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.

Marshall Barton said:
RedFish said:
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.
Click to expand...


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?
Click to expand...
 
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]

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 said:
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.
Click to expand...

"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?
Click to expand...
Click to expand...
 
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 said:
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]

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?
Click to expand...
Click to expand...
Click to expand...
 
RedFish said:
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.
Click to expand...


#Name means that the text box's ControlSource is not the
name of a field in the form's RecordSource table/query.
This implies that either myfield in mytable is not an
acceptable field name OR that you only wanted to assign the
value of the field to the text box (instead of binding the
text box), If the latter is what you really want, then
don't use the ControlSource property:
Me.txtHC = rstHC.Fields("myField")

If I'm still missing the boat, please explain what you're
trying to accomplish and provide a few example values along
with the desired results.
 
Back
Top