Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2009
    Posts
    37

    Unanswered: automatically fill in field on form

    I'd like to automatically fill in the field of a form in my database.

    I've written the following query (FirstDateDogWasSighted) which returns the first date Dogs have been sighted:
    Code:
    SELECT TOP 1 Sightings.Date
    FROM (Sightings INNER JOIN DogsatSighting ON Sightings.SightingID = DogsatSighting.SightingID) INNER JOIN Dogs ON DogsatSighting.DogID = Dogs.DogID
    WHERE (((Dogs.DogID)=([Dogs].[DogID])))
    ORDER BY Sightings.Date;
    Now I want to autopopulate a unbound field on the form with this query. In this form there is a field DogID (which is saved to the table Dogs)
    I added a unbound textfield and set it to 'short date' (just like the date in table the query gets the date from).

    As record source is set: =FirstDateDogWasSighted!Date

    I save the form, but when I open the form the field says: #name?

    Where do I go wrong?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can't refer directly to a query like that. You could use a DLookup to get the value from the query.
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    37
    A Dlookup? How and where?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As the control source of a textbox, instead of

    =FirstDateDogWasSighted!Date
    Paul

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It's pretty simple. Post back if you get stuck.
    Paul

  6. #6
    Join Date
    Feb 2009
    Posts
    37
    Thanks, I don't know exactly how it works but I'm gonna try some things.

  7. #7
    Join Date
    Feb 2009
    Posts
    37
    DLookup ("Sightings.SightingID"; "FirstDateDogsWasSighted"; "Dogs.DogID=Dogs.DogID )

    I've looked up the syntax of DLookup:

    DLookup ( expression, domain, [criteria] )

    expression is a field, calculation, control on a form, or function that you wish to return.


    The field I want to see comes from a different table (Sightings.SightingID) than the table where the from is bound to (Dogs) so I guess I should use a function, not sure what that should look like.


    domain
    is the set of records. This can be a table or a query name.


    This should be the query name if I'm correct: FirstDateDogWasSighted

    criteria is optional. It is the WHERE clause to apply to the domain.

    The WHERE clausule from my query is Dogs.DogID=Dogs.DogID but I'm not sure if this would return the current DogID as displayed on the form.

    I read to display the current DogID the DLookup should be something like:
    Code:
    DLookup("DogID", "Dogs", "DogID = " & Forms![Dogs]!DogID)
    But should this be incorporated in the criteria?

    So far I came to something like this:
    Code:
    DLookup ("Sightings.SightingID"; "FirstDateDogsWasSighted"; "Dogs.DogID=Dogs.DogID )
    But that didn't work...

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The query only returns one record, right? If so, you don't need a criteria:

    =DLookup("[Date]", "FirstDateDogsWasSighted")
    Paul

  9. #9
    Join Date
    Feb 2009
    Posts
    37
    It won't accept this as a code. If I put it in the control source and then press OK all is fine, but if I open the properties again there is nothing in the control source...

  10. #10
    Join Date
    Feb 2009
    Posts
    37
    If I copy this code directly in the control source, instead of opening the expression builder first it gives me the following error message:

    "Expression should start with the sign (=)"

    But it does! Anyone an idea about this?

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db?
    Paul

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i guess it is the same as this one

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Feb 2009
    Posts
    37
    Here's an example of my mdb (didn't see it in the other thread).
    I'll be travelling to Africa from today, reaching my destination on Wednesday but then it probably takes a few days to set things up before I'm back on internet. But I will check back later this week.
    Attached Files Attached Files

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I may be blind, just got off a plane, but I don't see a DLookup on any of the forms in that sample. Exactly what form/control are we looking at?
    Paul

  15. #15
    Join Date
    Feb 2009
    Posts
    37
    You're not blind, I made a mistake.
    It should be in this db in the Dog Fill in Form. Still same result, it won't accept the DLookup syntax, but no error message either.
    Attached Files Attached Files

Posting Permissions

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