Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: How do I set the source of a text field as a query?

    I have made a non-functional form at the moment with all of the formatting I want including lots of blank text boxes. I want to assign the source of these text boxes as the fields of a query I already have created. How do I do this?

    I will then navigate through the records using the navigation buttons at the bottom of the window (Or ill create another button later).

  2. #2
    Join Date
    Aug 2012
    Posts
    70
    e.g. The field I want to be assigned to one text box is named "BookingDate" and this is in a query called "BookingsFull".

    I do not want to assign it to the table.

  3. #3
    Join Date
    Aug 2012
    Posts
    70
    I thought I would be able to do it by going into the property sheet and setting the data source as "=[BookingsFull]![BookingDate]" but all I get is "#NAME?"

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by kkid View Post

    ...I do not want to assign it to the table...
    So use the Query as the RecordSource for the Form. If all of the data for all Controls is coming from this Query it's the only reasonable approach to use!

    But if you only need to retrieve a single Field, from a Table or Query, you cannot directly assign the Value from a Table/Query to a Control on a Form. To retrieve a value not part of the Form's RecordSource, you have to use the DLookup function. The exact syntax varies with the Datatype of the Field being used as the Criteria and where you're using the function. You have to have some kind of ID number that is unique to a given Record, in order to do this, of course.

    In VBA code, you'd use something like this:


    If the BookingID Field is defined as a Number

    Code:
     Me.BookingDate = DLookup("BookingDate", "BookingsFull", "[BookingID] = " & Me. BookingID)

    If the BookingID Field is defined as Text

    Code:
    Me.BookingDate = DLookup("BookingDate", "BookingsFull", "[BookingID] = '" & BookingID & "'")


    If you were doing this using the target Field's Control Source Property you'd use

    If the BookingID Field is defined as a Number

    Code:
    = DLookup("BookingDate", "BookingsFull", "[BookingID] = " & [BookingID])

    If the BookingID Field is defined as Text

    Code:
    = DLookup("BookingDate", "BookingsFull", "[BookingID] = '"[BookingID] & "'")


    Linq ;0)>
    Last edited by Missinglinq; 01-27-13 at 12:37.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2012
    Posts
    70
    Ah!

    I never knew about that record source thing (I am really novice!).


    Beware, novice explanation ahead
    It wasn't ALL in that query but most of it was, what I did after looking at the recordsource thing in the properties sheet was click the ... and dragged all of the aspects I wanted into the query and saved it. Then I could use the drop down menu to specify the source for each text field.

    I am sure that this isn't the best way to do it and you are probably cringing at what I did, but it works and that's all I need.



    Thanks fro all your help!

Posting Permissions

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