Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unanswered: Base a query on two textbox values to populate a continuous form help needed!

    Hi all,

    I know this might be a simple problem to fix, but im having a nightmare of a time to get it sorted.

    Basically, i have a form that is to display a list of database entries between a specific date range. I have tried creating a query that looks at the relevant text boxes in order to get the required data, but when i try to open the form (displayed as a subform in this case) it still asks for the user to input the date values, even though it has already been done on the main interface.

    If anyone could suggest a better way to populate the form in order to remove this setback, i'd really appreciate it.

    Cheers!

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Could we see the code you are using ?
    Darasen

  3. #3
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    It's just the usual (more than likely leaky way) of doing it:

    In the On_click Event of the Search element:
    Code:
    Forms!Main_Menu!subMainDetail.SourceObject = "NCRsByUserDate"
    The subform "NCRsByUserDate" uses the query "NCRsByDate" as the record source, and the form is a continuous form.

    The query was set out in design view, but here's the info via SQL view (i don't know how to populate the subform using an SQL setup yet).

    Code:
    SELECT AddNCR.NCR_nr, AddNCR.date_entry, AddNCR.Description, AddNCR.Status, AddNCR.Prod_name, AddNCR.Prod_cat, AddNCR.Client
    FROM AddNCR
    WHERE (((AddNCR.date_entry) Between [Forms]![frmNCRSubMenu_date]![EaliestDate] And [Forms]![frmNCRSubMenu_date]![LatestDate]));
    Hope it helps.
    Last edited by cruickshanks; 03-01-05 at 10:13.

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Could someone please help me out with this? Im really stuck just now.

  5. #5
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by cruickshanks
    Could someone please help me out with this? Im really stuck just now.
    Have you tried running this query as a query, meaning, the SQL-statement is tested and judged working?

    I'm not too expert on this, but maybe its possible that you have to use the following:


    SELECT AddNCR.NCR_nr, AddNCR.date_entry, AddNCR.Description, AddNCR.Status, AddNCR.Prod_name, AddNCR.Prod_cat, AddNCR.Client
    FROM AddNCR
    WHERE (AddNCR.date_entry Between '" & [Forms]![frmNCRSubMenu_date]![EaliestDate] & "' And '" &[Forms]![frmNCRSubMenu_date]![LatestDate] & "' "));


    Again, I'm not sure, I've never used dates in SQL (yet ) but usually numbers in WHERE-statements need to be between ' ' ...

  6. #6
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks for the help avlan. I created the query using the design window, and it runs outwith the form. that is to say that it'll come up with the right information if i physically type in the dates that i want to use.

    I've looked at the Access 95 Developers handbook (i use Access97 at work, but we only have a book on a95), and it says that the way im doing it will work, but it obviously isn't for some reason, and i just can't for the life of me figure out why.

    It could be the fact im using Text Boxes rather than combo boxes, but should there really be a difference?

  7. #7
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by cruickshanks
    Thanks for the help avlan. I created the query using the design window, and it runs outwith the form. that is to say that it'll come up with the right information if i physically type in the dates that i want to use.

    I've looked at the Access 95 Developers handbook (i use Access97 at work, but we only have a book on a95), and it says that the way im doing it will work, but it obviously isn't for some reason, and i just can't for the life of me figure out why.

    It could be the fact im using Text Boxes rather than combo boxes, but should there really be a difference?
    YES because afaik you can NOT use txtboxes to show multiple results. You need to use comboboxes!

  8. #8
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Sooo, by using text boxes as the source elements for a query, it cannot understand what it is being told?

  9. #9
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by cruickshanks
    Sooo, by using text boxes as the source elements for a query, it cannot understand what it is being told?
    ermmm wait, I think i misunderstood, I thought you were using txtboxes for the OUTPUT. That wont work.

    hmz. So the query you have works in the debug-window but NOT in the subform. So the conclusion must be something goes wrong in the step of translating the query to the subform.

    Try to find out what doesnt work: Can you get a simple rigid table to display in the subform (just checking if you do not have an error there)? Can u show the query in the form itself (just try to display it in a combobox or smt)? Try to work out step by step where the failure is by removing every variable and adding them one by one.

    If Access asks for user-input, its usually (that is, from my very short experience with access ) is a result of some sort of typo, f.i. I had a txtfield named 'txttest' and I had the typo txtest = DDusers.value at one point... Then it asked for user input.

  10. #10
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    I've just tried using a rough table as the criteria, and it still asks for user input.

    I think this is going to take a while to sort out.

  11. #11
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    the thing that gets me peeved is the fact that it worked on my login script for another db i created. This is a simple thing and i can't understand why it isn't working.

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Here are two options:

    1)
    Set the recordsource of the subform to permanently include the WHERE condition.
    .recordsource = SELECT columnA FROM tableA WHERE dateColumn = " & [Forms]![formA]![control]

    2)
    Set the recordsource through code.

    Same syntax as above, except through code.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    In Access the syntax for a date is to surround the dates with the # parameter in US date format.

    The best way of filtering your sub form is to have a button on the main form along with your 2 text boxes. In the OnClick event of the button put code along these lines:

    Private Sub MyFilterButton_Click()
    Dim strSQL As String
    strSQL = "SELECT * FROM MyTableOrQueryName WHERE (" & _
    "MyDateRangeField Between #" & Format(Me("My Start Date Text Box Name").Value,"mm/dd/yyyy") & "# And #" & Format(Me("My End Date Text Box Name").Value,"mm/dd/yyyy") & "#)"
    Me("Name of Subform").Form.Recordsource = strSQL
    End Sub

    Justin

  14. #14
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Justin, Thanks for the help, but i need a little clarification.

    I have set out the code as:

    Code:
    Private Sub Label20_Click()
    
    Dim strSQL As String
    strSQL = "SELECT * FROM AllNCR WHERE (" & _
    "MyDateRangeField Between #" & Format(Me("EarliestDateBox").Value, "dd/mmm/yy") & "# And #" & Format(Me("LatestDateBox").Value, "dd/mmm/yy") & "#)"
    Forms!Main_Menu!subMainDetail.SourceObject = "NCRsByUserDate"
    Me("NCRsByUserDate").Form.RecordSource = strSQL
    End Sub
    However, when i run the code i get the following error:

    Can't find field "NCRsByUserDate" referred in your expression. - You may have misspelled the field name, or the field may have been renamed or deleted.

    It then highlights the following element of code when i clicked on the Debug button:

    Me("NCRsByUserDate").Form.RecordSource = strSQL

    any suggestions on how i can deal with this error?

  15. #15
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Your sub-form is called 'subMainDetail' and you are trying to reference the properties of that sub-form, such as the form that it actually points to and its recordsource. So the correct code should be:

    Me("subMainDetail").SourceObject = "NCRsByuserDate"
    Me("subMainDetail").Form.RecordSource = strSQL

    If you use Me("subMainDetail") - you can refer to the properties of the control
    If you use Me("subMainDetail").Form - you can refer to the properties of the sub-form

    Justin

Posting Permissions

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