Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: Binding textbox to query

    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 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())" (quotation marks not included).

    In the Control Source 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)

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15

    Re: Binding textbox to query

    Originally posted by vbprogwb
    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 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())" (quotation marks not included).

    In the Control Source 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)

    I'm not sure what you mean when you say "I bound a textbox on a form to a qry (qryCount_Now)". You don't bind textboxes to queries - you bind them to fields within queries. I assume you mean that you bound the text box to a date field in the query "qryCount_Now".


    Anyway, it looks as if you want the textbox to display the number of events in the table "tblSpecialEvents" with today's date. If this in fact is what you're trying to do, can do it like this:

    put this expression in the textbox's ControlSource property (assuming the date in question is called "event_date"):

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


    As for the #NAME? error you're seeing, in this case it usually means the form cannot determine the name of the field in the textbox's controlsource, something not uncommon when using a COUNT function

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    Hi, Bostonjoe!

    Thanks for responding!

    I put in the Control Source you gave me below with a few changes:

    This is exactly what you sent me, but I made the appropriate changes for the exact field.

    =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 qury 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 tblSpecialEvents.

    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 !! 3 !! events today!"
    End If
    End Sub

    Thanks for your help!! If you need anymoreinformation, please let me know.

    Thanks in advance,

    Bob

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh dear....

    lets go back to bostonjoe's DCOUNT(). it is expecting two or three string arguments: "field", "table", and (optionally) "criteria"
    most of your experiments do not conform.

    also look at:
    event_date >= Date() AND event_date < Date() + 1
    greater than or equal to today AND less than tomorrow
    is precisely TODAY
    so there is no obvious reason to complicate your life.
    "event_date =" & Date()
    for the third argument??????

    izy

  5. #5
    Join Date
    Sep 2003
    Posts
    30

    Binding a textbox

    Hi, izyrider!

    Thanks for taking time to respond! Thanks for your help!!

    I tried several combinations of the folowing:

    =DCount(""Date", "tblSpecial_Event", "Date" = & Date()" )

    and I get #name? or other error messages.

    Have something to do will return later on!!

    Thanks, All!!

    Bob

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    damn, you forced me to type the code...

    Private Sub butGo_Click()
    Dim isDate As Double
    isDate = Date
    Text11.Value = DCount("aDate", "aTable", "aDate = " & isDate)
    End Sub

    works.


    sorry bout the earlier which doesn't work

    izy

  7. #7
    Join Date
    Sep 2003
    Posts
    30

    Count_Now Working

    izyrider,

    I got the form working in around about way.

    I wasn't going to use the txtbox except as a reference. No clicking allowed! But your code open my eyes up! I'm relatively new to Access and VBA.

    Private Sub Form_Open(Cancel As Integer)
    Dim count As Integer
    Dim isDate As Double
    isDate = Date
    [Date].SetFocus
    If [Date].Text = "" Then
    lblNoEvents.Caption = "No Events For Today"
    Else
    count = DCount("Date", "tblSpecial_Event", "Date = " & isDate)
    lblNoEvents.Caption = "You have" & " " & count & " " & "event(s) today!"
    End If
    End Sub

    As a neophyte, I have a question or two to ask if you wouldn't mind answering them.

    A Brief Synopsis:

    I'm tutoring at a local college. My 2 tutee's have this assignment. Yes, I know they need to do it, but if I don't know how to do it, how can I tutor?

    **&&

    Is it me or isn't this too clear!

    Assignment:
    Create a menu driven database application which will contain the following parts:

    1.) Table(s) to hold special event data
    2.) Form(s) to enter special event data
    3.) Macro(s) to run menu navigation.

    The application will use Visual Basic to determine if an event is ready to be triggered to the user. Once the event is displayed the user will have the opportunity to print the event, release the event, or "SNOOZE" the event for another time. I'm working on the 3 command buttons next.

    **&&

    I don't like to assume, so what I would like to know is:

    1.) You can't make an Access application into an executable and behind the scene check over the tables and pop up when it finds an event for the current date?

    2.) This will only work when you turn Access on and you have the application title and form listed in the Startup box?

    Thanks again for your help and when you get the chance, can you please answer my questions?!

    Thanks in advance,

    vbprogwb
    (Bob)

  8. #8
    Join Date
    Jan 2012
    Posts
    3
    Quote Originally Posted by vbprogwb View Post
    I'm tutoring at a local college. My 2 tutee's have this assignment. Yes, I know they need to do it, but if I don't know how to do it, how can I tutor?
    Well then there, folks, now you know what's wrong with public education in the U. S. of A.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Well, you know the old adage: "those who can do, and those who can't teach!" The only college level Access course I ever took was taught by a prof who didn't know how to do anything in Access! He taught by reading, word for word, from the textbook, and would occasionally miss an entire page, at which time one of the student would have to point this out to him!

    After the second such class I reported this to the department chairperson, telling her that I could teach myself Access a whole lot cheaper than taking the course! She agreed and called the office to refund my tuition, and a new prof took over the class the following week!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •