Text box or Label

[Guest]

Hey all,

I have open issues log that is tracked by user name. So the user starts the
database and selects there user name and clicks ok. On the initial form I
wanted it to show this:

You have [ 2 ] open issues in your log.

So I set the record source of the from to the corresponding query and put a
text box with a control source of the field I wanted. It works just great,
unless there is at least one item in their log. If there are zero items in
there log then the whole form comes up blank. The query is pretty simple it
just looks at the user name and checks to see if the Resolved box was checked
and counts the items that aren't resolved.

Is there a way that I could have a text box or Label display the value of a
query without having that query as the forms control source? There will only
ever be one record in the query. Or am I soing somthing wrong?

Thanks!

James O

 

[fredg]

Hey all,

I have open issues log that is tracked by user name. So the user starts the
database and selects there user name and clicks ok. On the initial form I
wanted it to show this:

You have [ 2 ] open issues in your log.

So I set the record source of the from to the corresponding query and put a
text box with a control source of the field I wanted. It works just great,
unless there is at least one item in their log. If there are zero items in
there log then the whole form comes up blank. The query is pretty simple it
just looks at the user name and checks to see if the Resolved box was checked
and counts the items that aren't resolved.

Is there a way that I could have a text box or Label display the value of a
query without having that query as the forms control source? There will only
ever be one record in the query. Or am I soing somthing wrong?

Thanks!

James O

You can use a DLookUp.

="You have " & Nz(DLookUp("[FieldName]","QueryName"),"no") & " open
issues ..."

 

[roadie.girl]

You will probably want to write some code for the form so you don't
have to have the display of the message displayed on the form.

your form is coming up blank because the recordset you are selecting is
null.

something like (untested)

Sub Form_OnLoad()

sqlstring = "SELECT COUNT(Issues) as NoIssues FROM myTable WHERE
UserName=" & [Forms]![InitialformWhereUserSelectsName]![userName]

Set rs = CurrentDb.OpenRecordset(sqlString, dbOpendynaset,
dbSeechanges)

Me.txtOpenIssues = "You have [" & rs!NoIssues & " ] open issues in your
log."

does this make sense?

 

[Guest]

Thanks for the reply, having a little trouble though. In the text box where I
type the dselect its coming up with Error# when I run it. The field its
looking at is a count field, would that matter? There will only ever be one
record. Thanks

Hey all,

I have open issues log that is tracked by user name. So the user starts the
database and selects there user name and clicks ok. On the initial form I
wanted it to show this:

You have [ 2 ] open issues in your log.

So I set the record source of the from to the corresponding query and put a
text box with a control source of the field I wanted. It works just great,
unless there is at least one item in their log. If there are zero items in
there log then the whole form comes up blank. The query is pretty simple it
just looks at the user name and checks to see if the Resolved box was checked
and counts the items that aren't resolved.

Is there a way that I could have a text box or Label display the value of a
query without having that query as the forms control source? There will only
ever be one record in the query. Or am I soing somthing wrong?

Thanks!

James O

You can use a DLookUp.

="You have " & Nz(DLookUp("[FieldName]","QueryName"),"no") & " open
issues ..."

 

[fredg]

Thanks for the reply, having a little trouble though. In the text box where I
type the dselect its coming up with Error# when I run it. The field its
looking at is a count field, would that matter? There will only ever be one
record. Thanks

Hey all,

I have open issues log that is tracked by user name. So the user starts the
database and selects there user name and clicks ok. On the initial form I
wanted it to show this:

You have [ 2 ] open issues in your log.

So I set the record source of the from to the corresponding query and put a
text box with a control source of the field I wanted. It works just great,
unless there is at least one item in their log. If there are zero items in
there log then the whole form comes up blank. The query is pretty simple it
just looks at the user name and checks to see if the Resolved box was checked
and counts the items that aren't resolved.

Is there a way that I could have a text box or Label display the value of a
query without having that query as the forms control source? There will only
ever be one record in the query. Or am I soing somthing wrong?

Thanks!

James O

You can use a DLookUp.

="You have " & Nz(DLookUp("[FieldName]","QueryName"),"no") & " open
issues ..."

1) What is DSelect?

2) Make sure the name of the control is NOT the same as the name of
any field or table used in it's control source expression.

3) Change [Fieldname] and [QueryName] to whatever the actual field and
query names are.

 

[Guest]

Thank you so much Fred, got it working. Sorry, I meant Dlookup not Dselect.
The problem was that the field that I was trying to reference had an invalid
character in it. I have seen Dlookup's before but not like the one you used,
thanks very much, this will be very useful in the future!

James O

Thanks for the reply, having a little trouble though. In the text box where I
type the dselect its coming up with Error# when I run it. The field its
looking at is a count field, would that matter? There will only ever be one
record. Thanks

On Tue, 21 Feb 2006 09:40:27 -0800, James O wrote:

Hey all,

I have open issues log that is tracked by user name. So the user starts the
database and selects there user name and clicks ok. On the initial form I
wanted it to show this:

You have [ 2 ] open issues in your log.

So I set the record source of the from to the corresponding query and put a
text box with a control source of the field I wanted. It works just great,
unless there is at least one item in their log. If there are zero items in
there log then the whole form comes up blank. The query is pretty simple it
just looks at the user name and checks to see if the Resolved box was checked
and counts the items that aren't resolved.

Is there a way that I could have a text box or Label display the value of a
query without having that query as the forms control source? There will only
ever be one record in the query. Or am I soing somthing wrong?

Thanks!

James O

You can use a DLookUp.

="You have " & Nz(DLookUp("[FieldName]","QueryName"),"no") & " open
issues ..."

1) What is DSelect?

2) Make sure the name of the control is NOT the same as the name of
any field or table used in it's control source expression.

3) Change [Fieldname] and [QueryName] to whatever the actual field and
query names are.