Populating a form field with a queried value

M

Melissa

I have a field on my form that is bound to the underlying table. I added an unbound field that is invisible, which I tried to control with a record from a query (related by the main unique identifier). Then, I tried to use setvalue to populate the bound field. All I get is an error saying that my fields are different types. Also, how do I make it so that the populated value is dependent on the unique identifier

Thank you soooo much,
Melissa
 
M

[MVP] S.Clark

If I had to speculate, I'd guess that the query used for the unbound field
has the datatype mismatch issue. For example, suppose that the bound field
is text, and the query has a where clause like, "CategoryName=" &
txtCategoryName. If CategoryName is text, then you would need to use
quotation marks to explicit state to Access to use text. "CategoryName='" &
txtCategoryName & "'".

What is the controlsource of the unbound field?
What is the exact error msg?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Melissa said:
I have a field on my form that is bound to the underlying table. I added
an unbound field that is invisible, which I tried to control with a record
from a query (related by the main unique identifier). Then, I tried to use
setvalue to populate the bound field. All I get is an error saying that my
fields are different types. Also, how do I make it so that the populated
value is dependent on the unique identifier?
 
K

Ken Snell

Can you provide more details? It's hard to "see" all that you have from this
description.

What is the format of the field to which the control is bound?

What is the format of the unbound control?

What is the method that you're using to "control with a record from a
query"?

What is the relationship between the value and the unique identifier?


--
Ken Snell
<MS ACCESS MVP>

Melissa said:
I have a field on my form that is bound to the underlying table. I added
an unbound field that is invisible, which I tried to control with a record
from a query (related by the main unique identifier). Then, I tried to use
setvalue to populate the bound field. All I get is an error saying that my
fields are different types. Also, how do I make it so that the populated
value is dependent on the unique identifier?
 
M

Melissa

The control source for the unbound field is : =[SURGDATA Query]![Sum Of SURGETYPEID]
Basically, each record has a unique casenumber. The query counts all my records and then sums them in the query. I want to deposit that value in the table field: "# Lengthenings"
I tried to specifiy that query, ghost box and field all were in number format.
 
K

Ken Snell

You cannot reference a query in a control source the way you have. Instead,
you use a DLookup function to get the value from the query:

=DLookup("Sum Of SURGETYPEID", "SURGDATA Query", "[PrimaryKeyField]=" &
[ControlNameWithValue])

--
Ken Snell
<MS ACCESS MVP>

Melissa said:
The control source for the unbound field is : =[SURGDATA Query]![Sum Of SURGETYPEID]
Basically, each record has a unique casenumber. The query counts all my
records and then sums them in the query. I want to deposit that value in
the table field: "# Lengthenings"
I tried to specifiy that query, ghost box and field all were in number
format.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top