Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Source for a form (2000)

    How do any of you handle this situation:

    You want a form to open up without a record source, then after making choices from a couple of unbound fields, use a record source for the form. I'm looking to see what others do to avoid the #Name? when the form first opens. I am hoping to avoid having to set the control source for each field upon making the choices in the unbound fields.

    TIA

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Record Source for a form (2000)

    Hi
    Could you put some unbound controls in the form Header or footer. Use the contents of these once populated as the basis of a query . Then use docmd findrecord ..
    Alternatively create a record set from the querydef and populate the form detail from the record set.

    Cheers
    Geof

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Record Source for a form (2000)

    I'm not sure I would ever have that situation, since it requires that each source have the same field names! However, I'd set my recordsource equal to one of the possible alternatives, but include a WHERE clause I knew would not find any matching records (for example, if primarykey field is an autonumber, I look for ID=0). This would give me an empty recordset, so no data is display; yet no #Name error either.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Source for a form (2000)

    Yeah, that was stupid of me. I should make one basic SQL statement in code without the where clause. Create the where clause based on the choices made in the unbound fields, then set the record source to that SQL statement created by joining the two strings.
    And set the form to look for the value I know wouldn't be found.

    Is this what you're saying I could do?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Source for a form (2000)

    I usually open my bound forms with an empty recordset by using a where clause that always evaluates to false. Something like this:

    SELECT * FROM tblWhatever WHERE 1=2

    As soon as Jet realizes that the condition is always false, it returns an empty records, and the form opens very quickly. Then, when the user does whatever has been prescribed to specify a record or records, I create a SQL string that includes a specific WHERE condition to return the specified record or record and set the recordsource to that new SQL string.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Record Source for a form (2000)

    >>Is this what you're saying I could do? <<

    Pretty much so. What I also said to use a WHERE clause in the initial SQL string, but one that would guarentee returning an empty recordset. This way you wouldn't waste time initially loading the form with a recordset you knew would change as soon as user made his/her selections.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Source for a form (2000)

    Thanks for the input!

    I've got strSQL = to the SQL statement that will give me the fields and records I need from 3 tables. In my form's on open event I've got the recordsource=strSQL & strWhere which looks for a null in the Primary Key field of one of the tables, thus giving me my empty recordset when the form opens. The recordsource property in form design doesn't need to be set.

    Then based on whichever field is picked from the four in my combo box, I then set my strWhere string to an appropriate where clause.

    This works great as no one can hit the Remove/Filter sort button and display all the records like they could if I simply stuck with an ApplyFilter method.

    Thanks again

Posting Permissions

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