Results 1 to 5 of 5
  1. #1
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19

    Dlookup as control source on continuous form.

    Hello,



    I have been using one table to store all information for appointments. This works, but is really a bad way to do it, since these are the fields I'm using as an example.

    Date, StartTime, StudentName, TutorFName, TutorLName, MathDetails, CompDetails, ScienceDetails

    So, I'll be duplicating each of the fields staring at TutorFName many times, not ideal. So, I'm now trying to have it reference another table, and use an Initial as an identifier.

    AppointmentTable:

    Day, StartTime, StudentName, TutorInitials

    StaffListTable:

    TutorInitials, TutorFName, TutorLName, MathDetails, CompDetails, ScienceDetails

    This worked fine, if my form was working with one tutor at a time, since the Staff list values will not change. I used DLookup to make this work. The problem is with my continuous form, to show all tutor appointments who do a certain subject.

    So, for example, I have a form to filter it by those who do science, so it will show all tutors who do not have Null for their [ScienceDetails], which works fine, but my form displays this way:

    SubjectForm: Uses AppointmentTable as record source.

    (Only StudentName can be entered in)

    Day, StartTime, StudentName, TutorFName, TutorLName, ScienceDetails

    I need TutorFName, TutorLName, ScienceDetails to be pulled from the StaffListTable based on the initial attached to each appointment. I tried this:

    The text box where I want the ScienceDetails is called Details.

    Me.Details.ControlSource = DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")

    This will make the Textbox Details show "#Name?".

    When I display the DLookup with a Msgbox like this:

    MsgBox DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")


    It correctly shows the ScienceDetails based on the TutorInitials in the first record displayed, so all the table names, and field names are not mispelled, and the DLookup does work.

    If I try:

    Me.Details = DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")

    It displays the correct details dor the first record, and duplicates it throughout all the records.

    How can I get it to show the correct value for each field?

    Thank you so much in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The DLookup is not correct, as you haven't concantenated the ending quotes. Try

    Me.Details = DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    I mistyped it, like I said, it is diplaying correctly with msgbox.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried just making it the control source?

    =DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & [TutorInitials] & "'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    That does work, but I need to be able to change it dynamically in the code.

    If I just paste that in the box, it works, but I need to be able to change it.

    Edit: I figured it out. If I do this:

    Me.Details.ControlSource = "=DLookup(""[ScienceDetails]"", ""StaffListTable"", ""TutorInitials = '"" & Me.[TutorInitials] & ""'"")"

    I had to use double quotes to make it work. It was just inserting the details of the first entry into the control source, not the code. So the control source would be something like "Biology.", not =DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'"). Thanks anyway for the help.
    Last edited by Ramun_Flame; 10-24-2012 at 01:01 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Control Source on Form Reading Value in Table
    By hammer187 in forum Forms
    Replies: 1
    Last Post: 10-23-2012, 02:43 PM
  2. DLookup in Control Source
    By bgephart in forum Forms
    Replies: 2
    Last Post: 08-28-2012, 02:06 PM
  3. DLookup() for Text Box Control Source
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 02-06-2012, 02:21 PM
  4. Form control source standards
    By scampbell in forum Forms
    Replies: 10
    Last Post: 11-11-2011, 09:34 AM
  5. Dlookup as Control Source
    By alsoto in forum Forms
    Replies: 1
    Last Post: 08-28-2011, 07:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums