Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switching Record Source in Forms (2000)

    I am trying to create a search form with 4 different fields on which a user can search. I want the user to be able to search on any of these 4 fields that they choose. I have 14 separate queries that take care of all of the conditions (i.e., user enters search criteria for Field1 & Field3 or Field2 & Field3 or Field1 & Field2 & Field3).

    I have a feeling that my problem lies in the fact that I need to get all of these conditions in one query, unfortunately. But, I want to create a form that will display just Field1 of the search results. I want the form to execute the query that it needs and display those results. Is that possible?

    Thanks,
    Kindra

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Switching Record Source in Forms (2000)

    Yes it is possible. The most common approach is to use unbound text boxes to get the data you need to construct a SQL statement, and then assemble the SQL string and execute it using VBA to return a recordset to be displayed. In that case you may want to create a temporary table to hold the data, or you could actually construct a SQL query and then save it as a QueryDef and set the data property for Field 1 and the form to that query.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switching Record Source in Forms (2000)

    Thanks for the suggestion -- I did create a table using the make-table query and wrote the VB Code for what I was doing and it seems to work. However, when I try to run another query (i.e., add a field of criteria to search on) and don't close the form, it gives me an error that says: "Cannot define field more than once" -- it gives me all of the warnings up to that point, but then doesn't execute.

    What do I need to do to execute the second query? Or does the user need to exit out of the form each time?

    Thanks!
    Kindra

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Switching Record Source in Forms (2000)

    Hi Wendell,

    I think this is on the same idea so I'm asking this in this thread rather than starting a new one. If I use and unbound text box to hold the filter data and I refer back to it in the SQL of the query, how do I set the query to show all records if the unbound box is null? For example, if I have a report that opens based on a date range stored in txtstartdate on a form. I have a second report, a duplicate of the first one, that shows all records. Rather than keep building two and three sets of reports, I'd like to be able to use the same report. If there is a date in txtstartdate it would show only records for that date. If txtStartDate is null than all records would show. Right now, if txtstartdate is null, no records show.

    Thanks,
    Leesha

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Switching Record Source in Forms (2000)

    What you need to do is to test if txtStartDate is null in the query.
    Why don't you post your query here and let us have a look.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Switching Record Source in Forms (2000)

    Hi Pat,

    Here is the query and stripped down form to enter the date range from. My goal is to have the query either return only records between the date range on the form, or if the date range is null all the records would be returned.

    Thanks,
    Leesha

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Switching Record Source in Forms (2000)

    In that situation, you probably need to either delete the temporary table as soon as they change any criteria on the form, and then recreate the table. As I reflect on this a bit more, it seems in most cases the data is displayed on another form, but in some cases the SQL string is simply made the source for the form the unbound criteria text boxes are placed on. Are you trying to display data in a continuous form or a single record form?
    Wendell

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Switching Record Source in Forms (2000)

    As Pat indicated, you need to check to see if each text box is null, and in that case there is no WHERE clause in the SQL String - then it should work fine.
    Wendell

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Switching Record Source in Forms (2000)

    Hi Wendell,

    OK bear with me as this is another new one for me. I'm not sure how to set or should say define the "where" statement in the query. I went to view by totals and set the total for care_date to where but the results were no records. I'm sure its more invovlved than this but don't know where to go next.

    Leesha

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Switching Record Source in Forms (2000)

    What you could do is to setup 3 queries, they would be:
    1. If neither date was chosen, then query 1 would have no WHERE clause.
    2. If only the start date was entered, then query 2 would have a WHERE cluase testing care_date >= start date.
    3. If only the end date was entered, then query 3 would have a WHERE clause testing care_date <= end date.

    Another way to do this is to have a query like query 1 where there is no where clause and when you call the report you define the WHERE clause in the DoCmd.OpenReport command depending upon the condition of the start and end dates. Here is an example of code behind a button on the dates form.
    <pre> Dim sW As String
    If IsNull(txtStartDate) Then
    If IsNull(txtEndDate) Then
    sW = ""
    Else
    sW = "care_date <= #" & Format(txtEndDate, "mm/dd/yy") & "#"
    End If
    Else
    If IsNull(txtEndDate) Then
    sW = "care_date >= #" & Format(txtStartDate, "mm/dd/yy") & "#"
    Else
    sW = "care_date >= #" & Format(txtStartDate, "mm/dd/yy")
    sW = sW & "# AND care_date <= #" & Format(txtEndDate, "mm/dd/yy") & "#"
    End If
    End If
    DoCmd.OpenReport "rpt", acViewPreview, , sW
    </pre>


  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Switching Record Source in Forms (2000)

    OMG Pat, I'm either totally brain dead from this database, or there is no hope. This looks like it would be harder than just having two separate reports (which is how I've been doing it all along). One opens to show all names and the other opens based on the date range in txtstartdate and txtenddate. I will simply never understand when to put in the commas, quotes etc. I'm not giving up as I can't believe how far I've come since last year at this time!

    Leesha

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Switching Record Source in Forms (2000)

    This snippet of code is from your procedure cmdDeleteRecords_Click and actually defines the SQL string that makes up the query:
    <font color=448800><font face="Georgia">
    ' Modify the Query.</font color=448800>
    <font color=blue> Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryDeleteISFSelectedRecords")
    Q.SQL = "Select * From tblISFTemp Where [ID] In(" & Criteria & _
    ");"
    Q.Close</font color=blue></font face=georgia>

    The line:
    Q.SQL = "Select * From tblISFTemp <font color=red>Where</font color=red> [ID] In(" & Criteria & _
    ");"
    contains the WHERE clause in the SQL statement - I made it <font color=red>RED</font color=red>.
    Wendell

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Switching Record Source in Forms (2000)

    Thanks Wendell. I'm still trying to "get" when to put in all the different symbols so my syntax isn't wrong.

    Leesha

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switching Record Source in Forms (2000)

    Right now I'm just trying to get the right query to run and make the table -- I haven't linked the table to a form yet. However, when I enter in multiple criteria into my form and then try to run the query, it tells me that it "Cannot define field more than once." I have tried deleting the table and also tried putting a "Refresh" button on my form (through the control wizard) but it says "The command or action 'Refresh' isn't available now."

    If I convert my queries back to Select queries (from Make Table) then the queries will run just fine... ???

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Switching Record Source in Forms (2000)

    That error message usually indicates you are trying to create two fields with the same name in your Make Table query. That works fine in Select queries, but not in action queries in general. Check your query very carefully and see if you don't have two fields with the same name.
    Wendell

Posting Permissions

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