Facing problems in binding Control Source

[Public]

Hi,
I am facing some problems in binding the control source of a textbox to a
query that is NOT in the record set of Form (I don't want to change the
record set query). I want to show the course name when a user selects a
course ID

What I did is as follows:
1) I Created a query that have something like CourseID and CoruseName (I
called it like Courses)
2) CourseID in the query is coming from the same form that have the textbox
3) I went to the form and bound the textbox for the course name like this
[Courses]![CourseName]

And I am only seeing #Name?

Moreover, I am not sure whether this is helpful or not, but I am using MS
Access 2003.

Regards

 

[Jeanette Cunningham]

Public,
use DLookup, see vba help on DLookup.

Me.TheTextBoxName = DLookup("[CourseName]", "qryCourse", "[CourseID] = " &
Me![CourseID]")

Assuming that you have a query called qryCourse with the CourseID and the
CourseName in it. If not, create the query.
Do not put any criteria in the criteria row of the query.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Rob Parker]

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want, however, since
it seems that CourseID is in the current recordsource (if it's not, there is
no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and CourseName
fields to your existing query which is the form's recordsource; join on the
CourseID field, set the join properties to "show all records from (existing
table in query) and only matching records from (new Courses table/query in
query)" -this will allow existing records to be shown if CourseID or
CourseName is missing. Then bind your textbox to the CourseName field which
is now available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria clause to
return the CourseName for the CourseID field of the current record; it will
be an expression similar to:
=dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will need
delimiters:
=dLookup("CourseName","Courses","CourseID = '" & [CourseID] & "'")
Expanded for clarity, that's:
=dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & " ' ")

HTH,

Rob

 

[Public]

Thanks for your help. I t worked.
One more thing: If I want to make the criteria come from more than one
source, how would I write it? (for example, if I want the criteria to be
CourseID AND SectionID)

Regards

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want, however, since
it seems that CourseID is in the current recordsource (if it's not, there is
no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and CourseName
fields to your existing query which is the form's recordsource; join on the
CourseID field, set the join properties to "show all records from (existing
table in query) and only matching records from (new Courses table/query in
query)" -this will allow existing records to be shown if CourseID or
CourseName is missing. Then bind your textbox to the CourseName field which
is now available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria clause to
return the CourseName for the CourseID field of the current record; it will
be an expression similar to:
=dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will need
delimiters:
=dLookup("CourseName","Courses","CourseID = '" & [CourseID] & "'")
Expanded for clarity, that's:
=dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & " ' ")

HTH,

Rob

Hi,
I am facing some problems in binding the control source of a textbox
to a query that is NOT in the record set of Form (I don't want to
change the record set query). I want to show the course name when a
user selects a course ID

What I did is as follows:
1) I Created a query that have something like CourseID and CoruseName
(I called it like Courses)
2) CourseID in the query is coming from the same form that have the
textbox 3) I went to the form and bound the textbox for the course
name like this [Courses]![CourseName]

And I am only seeing #Name?

Moreover, I am not sure whether this is helpful or not, but I am
using MS Access 2003.

Regards

 

[Rob Parker]

I assume that your referring to the dLookup method, and that you have a
SectionID field in the table/query where the CourseName is coming from; also
that SectionID is in the current form's recordsource. The syntax will be:
=dLookup("CourseName","Courses","CourseID = " & [CourseID] & " AND
SectionID = " & [SectionID])

Again, if the ID is a text field, you'll need single-quote delimiters as I
showed previously.

HTH,

Rob

Thanks for your help. I t worked.
One more thing: If I want to make the criteria come from more than one
source, how would I write it? (for example, if I want the criteria to
be CourseID AND SectionID)

Regards

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want,
however, since it seems that CourseID is in the current recordsource
(if it's not, there is no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and
CourseName fields to your existing query which is the form's
recordsource; join on the CourseID field, set the join properties to
"show all records from (existing table in query) and only matching
records from (new Courses table/query in query)" -this will allow
existing records to be shown if CourseID or CourseName is missing.
Then bind your textbox to the CourseName field which is now
available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria
clause to return the CourseName for the CourseID field of the
current record; it will be an expression similar to:
=dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will
need delimiters:
=dLookup("CourseName","Courses","CourseID = '" & [CourseID] &
"'") Expanded for clarity, that's:
=dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & "
' ")

HTH,

Rob

Hi,
I am facing some problems in binding the control source of a textbox
to a query that is NOT in the record set of Form (I don't want to
change the record set query). I want to show the course name when a
user selects a course ID

What I did is as follows:
1) I Created a query that have something like CourseID and
CoruseName (I called it like Courses)
2) CourseID in the query is coming from the same form that have the
textbox 3) I went to the form and bound the textbox for the course
name like this [Courses]![CourseName]

And I am only seeing #Name?

Moreover, I am not sure whether this is helpful or not, but I am
using MS Access 2003.

Regards

 

[Public]

Thank you guys!
It solved the problem.
However, I faced another problem now, The value that I want to show is a
hyperlink (like link to the content of that course). I have made sure that
the text box's property of is Hyperlink is "Yes". Howerver, it shows me
something like
#[MyCourseName]#. Why these additional '#' are showing up?

Regards

I assume that your referring to the dLookup method, and that you have a
SectionID field in the table/query where the CourseName is coming from; also
that SectionID is in the current form's recordsource. The syntax will be:
=dLookup("CourseName","Courses","CourseID = " & [CourseID] & " AND
SectionID = " & [SectionID])

Again, if the ID is a text field, you'll need single-quote delimiters as I
showed previously.

HTH,

Rob

Thanks for your help. I t worked.
One more thing: If I want to make the criteria come from more than one
source, how would I write it? (for example, if I want the criteria to
be CourseID AND SectionID)

Regards

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want,
however, since it seems that CourseID is in the current recordsource
(if it's not, there is no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and
CourseName fields to your existing query which is the form's
recordsource; join on the CourseID field, set the join properties to
"show all records from (existing table in query) and only matching
records from (new Courses table/query in query)" -this will allow
existing records to be shown if CourseID or CourseName is missing.
Then bind your textbox to the CourseName field which is now
available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria
clause to return the CourseName for the CourseID field of the
current record; it will be an expression similar to:
=dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will
need delimiters:
=dLookup("CourseName","Courses","CourseID = '" & [CourseID] &
"'") Expanded for clarity, that's:
=dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & "
' ")

HTH,

Rob


Public wrote:
Hi,
I am facing some problems in binding the control source of a textbox
to a query that is NOT in the record set of Form (I don't want to
change the record set query). I want to show the course name when a
user selects a course ID

What I did is as follows:
1) I Created a query that have something like CourseID and
CoruseName (I called it like Courses)
2) CourseID in the query is coming from the same form that have the
textbox 3) I went to the form and bound the textbox for the course
name like this [Courses]![CourseName]

And I am only seeing #Name?

Moreover, I am not sure whether this is helpful or not, but I am
using MS Access 2003.

Regards

 

[Jeanette Cunningham]

have a look at:
The PrepareHyperlink() function can also be used to massage a file name so
it will be handled correctly as a hyperlink.

On this site
http://www.allenbrowne.com/func-GoHyperlink.html


Jeanette Cunningham -- Melbourne Victoria Australia

Thank you guys!
It solved the problem.
However, I faced another problem now, The value that I want to show is a
hyperlink (like link to the content of that course). I have made sure that
the text box's property of is Hyperlink is "Yes". Howerver, it shows me
something like
#[MyCourseName]#. Why these additional '#' are showing up?

Regards

I assume that your referring to the dLookup method, and that you have a
SectionID field in the table/query where the CourseName is coming from;
also
that SectionID is in the current form's recordsource. The syntax will
be:
=dLookup("CourseName","Courses","CourseID = " & [CourseID] & " AND
SectionID = " & [SectionID])

Again, if the ID is a text field, you'll need single-quote delimiters as
I
showed previously.

HTH,

Rob

Thanks for your help. I t worked.
One more thing: If I want to make the criteria come from more than one
source, how would I write it? (for example, if I want the criteria to
be CourseID AND SectionID)

Regards


:

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want,
however, since it seems that CourseID is in the current recordsource
(if it's not, there is no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and
CourseName fields to your existing query which is the form's
recordsource; join on the CourseID field, set the join properties to
"show all records from (existing table in query) and only matching
records from (new Courses table/query in query)" -this will allow
existing records to be shown if CourseID or CourseName is missing.
Then bind your textbox to the CourseName field which is now
available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria
clause to return the CourseName for the CourseID field of the
current record; it will be an expression similar to:
=dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will
need delimiters:
=dLookup("CourseName","Courses","CourseID = '" & [CourseID] &
"'") Expanded for clarity, that's:
=dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & "
' ")

HTH,

Rob


Public wrote:
Hi,
I am facing some problems in binding the control source of a textbox
to a query that is NOT in the record set of Form (I don't want to
change the record set query). I want to show the course name when a
user selects a course ID

What I did is as follows:
1) I Created a query that have something like CourseID and
CoruseName (I called it like Courses)
2) CourseID in the query is coming from the same form that have the
textbox 3) I went to the form and bound the textbox for the course
name like this [Courses]![CourseName]

And I am only seeing #Name?

Moreover, I am not sure whether this is helpful or not, but I am
using MS Access 2003.

Regards