Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 12th, 2003, 09:07 AM
Authorized User
 
Join Date: Oct 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Binding textbox to query - Please help!!

Hi, All!

I don't know why this is giving me such a headache.

I bound a textbox on a form to a qry (qryCount_Now).

qryCount_Now is based on another query (qryNow).

This one (qryNow) is based on a table (tblSpecialEvents). This query gives me all of the records for all events for the current date with >=Date() And <DateAdd("d",1,Date())

In the Control Source for the textbox I used the Expression Builder and created =[qryCount_Now]![CountOfEvent]. I've noticed that the brackets will sometimes disappear and then reappear.

When I open the form I get "#Name?" in the textbox.

Thanks in advance,

vbprogwb
(Bob)
 
Old October 12th, 2003, 09:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Bob,

Instead of clicking the ellipsis next to the control source property of your textbox to invoke the expression builder, just click the drop-down arrow. This will give you a list of all the fileds in the query that your form is bound to. Just select the field name CountOfEvents. You don't want the fully qualified Collection!MemberName syntax. Using '=' makes Access think you are calling a function or expression to populate the control. You just want the field name.

HTH,

Bob

 
Old October 12th, 2003, 10:56 AM
Authorized User
 
Join Date: Oct 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, Bob!

   Thanks for responding!

   The textbox is based (bound - ?) on/to a different query than the one the form is based on.

   Going out with the wife in a few minutes (dinner and a play). If you need more information please let me know.

   Thanks again!

Later,

Bob



 
Old October 12th, 2003, 03:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Not sure what you're trying to do. I suspect though that if the control isn't bound to the forms underlying recordsource, and you want it to display a field from a different recordsource, you'll need to use DAO.

 
Old October 12th, 2003, 04:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

That is, unless the control has its own Record Source property, like a combo box or list box. Text boxes don't. They bind to and display fields from the form's record source, or display a read-only value drived from an expression that is generated at run-time but doesn't get stored in the database.

 
Old October 13th, 2003, 10:51 AM
Authorized User
 
Join Date: Oct 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, Bob

Thanks for responding!

I got this off of a different forum and made the appropriate changes to the field names in the following code, but I still get a variety of errors:

=DCount("event_date","tblSpecialEvents","event_dat e >= Date() AND event_date < Date() + 1")

I tried different syntac with this, but I continue to get various error messages: #Name?; . . . wrong number of arguments; Something like #error?

I tried;

 Count("Date, tblSpecial_Event, Date >= Date() AND Date < Date() + 1")

=DCount("Date, tblSpecial_Event, Date >= Date() AND Date < Date() + 1")

=DCount(""Date", "tblSpecial_Event", "Date" = AND Date < Date() +1")

=DCount(""Date", _tblSpecial_Event", "Date" >= Date() AND "Date" < Date() + 1") " You may have entered an operand without an operator

What I'm trying to do is find the number of events on a certain date.

I did:

I bound a textbox on a form to a field (CountofEvents) in

qryCount_Now. I even tried changing the caption and used Events,

both in the query and Control Source. qryCount_Now (counts the

number of events in qryNow based on another query (qryNow). This

query (qryNow) gives me all of the records for all events for the

current date with >=Date() And <DateAdd("d",1,Date()) which are

stored in tblSpecial_Event.

This Private Sub isn't finished, but I want to do something like this:


Private Sub Form_Open(Cancel As Integer)

[Date].SetFocus
If [Date].Text = "" Then
        lblNoEvents.Caption = "No Events For Today"
Else lblNoEvents.Caption = "You have" & [Count_Now].Text & "events today!"
End If
End Sub

  Thanks for your help!! If you need more information please feel free to ask!

Thanks in advance,

vbprogwb
(Bob)

 
Old October 13th, 2003, 10:57 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All you want is the count of the items on that query to show up on your text box?



Sal
 
Old October 13th, 2003, 11:23 AM
Authorized User
 
Join Date: Oct 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, Sal!!

   Thanks for responding!!

  Yes the :D qryCount_Now :D does a count of Events found in qryNow. qryNow finds all of the Events in the tblSpecial_Events.

  I need the Count for: "You have 3 events today!"

  I've never had anything drive me so silly!!

Thanks in advance,

vbprogwb
(Bob)

 
Old October 13th, 2003, 12:48 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In your query qryCount_Now insert another field and add something like this:

Expr1: " You Have " & [CountOfPrimaryKey] & " Appointments today"

make it an expression
use this as the recordsource for your form and the field Expr1 as the source for your textbox.

Is this the only bound field that you are using for your form?



Sal
 
Old October 13th, 2003, 12:55 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Or just set the form to continuous form and set the textbox on the footer and add something like this:

="You have " & Count([DateToCount]) & " Appointments today"

as the recordsource for the textbox. It will always be the count of the items on the form.

Sal





Similar Threads
Thread Thread Starter Forum Replies Last Post
textbox binding tryout greenbriar BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 4 February 14th, 2008 08:11 PM
Binding Results of an Xpath query to a datagrid cowa ADO.NET 2 November 17th, 2003 02:40 AM
binding of textbox sdibartolomeo ADO.NET 0 November 10th, 2003 02:26 PM
Binding TextBox from 3 Tables melvik C# 0 July 6th, 2003 02:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.