Results 1 to 12 of 12
  1. #1
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    DlookUp Function.

    Hello there,

    Some times things are so simple but we are not able to understand so become very difficult.

    i have a table;

    tbleTestInfo
    TestID PK


    TestName
    FileID FK (From table fileid and filename) FiledID is hidden.
    Note

    when i go to a new table with TestNo to generate and create a form there so for TestID i put a code dlookp to choose the name of it on the next box and same i am looking for FileID which is already define in tblTestInfo to come up with each test.

    If it works so only FileID appears which is not to seen as want to see filename. how to do that?

    thanks

    zee

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Language barrier strikes again! LOL! Not sure what you are asking. Do you want to set up a combobox to show the FileName and not the FileID in the RowSource? This would not involve DLookup function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    well, u are right. lets learn day by day and move on and i am sure there will be a day when things will be fine.

    As u know my data structure of labdb.

    I have a Test1form where i have fields of TestID, TestName, SectionID, StageID and so on.

    So all those info is already saved in TblTestInfo when we are going to give a name of a new test in master tble.

    so now i am looking here to pull out the all data with dlookup funtion which is in master tble belongs to testid and happend every time but issue is only when i put this function:

    Private Sub TestID_AfterUpdate()
    TestName = DLookup("TestName", "tblTestInfo", "TestID =" & TestID)
    FileID = DLookup("FileID", "tblTestInfo", "TestID =" & TestID)
    StndID = DLookup("StndID", "tblTestInfo", "TestID =" & TestID)
    SectionID = DLookup("SectionID", "tblTestInfo", "TestID =" & TestID)

    End Sub

    it shows for the sectuionid the pk field which is numeric but it should be the text as SectionName.

    When i create the combobox for master tblTestInfo from tblsectioninfo and so on so it recommended to hide the PK and shows the second field when we select from it but here it shows the 1st field which shud not be in real bcz i want to show the text.

    thanks for ur patience with me.

    tke care.

    zee

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Are you trying to save this test info to the 'master' table? Should NOT do this. Save only the TestID. Retrieve the test detail info when needed by joining tables in a query. Query jointype would be 'Show all records from master ...' Bind textboxes to the test info fields (not the ID field). Set the textboxes as Locked and TabStop No. DLookup not needed. The combobox will still be bound to the testID field in master table. Select test in combobox, related info will display in the textboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hello there, i do agree but can you give one of example or in detail how to do that. thanks

    zee

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Those are detailed instructions.

    The RecordSource of the form would be a query that includes a join to the TestInfo table.

    If you want to provide your project for analysis, I will look at.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Please find the file here. thanks

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You attached accde file. Cannot analyse accde version because cannot open anything in design view and cannot see code. Cannot modify accde version. Need the accdb.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    i am sorry. it will be ok now.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Attached is modification of your project to demonstrate what I was describing. The table structure appears incomplete. You have fields in tblTestInfo for SectionID, OccurID, StageID, StndID but no source tables with information about these entities, nothing for these ID's to link to, no name values are available for the controls on the forms. I removed the name fields from tblTest1 because their presence there did not make sense. Need either source tables for these ID's to link to or save the names in tblTestInfo instead.

    EDIT: purpose served, file removed.
    Last edited by June7; 09-22-2011 at 12:20 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    cap.zadi is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    i am sorry for that. please look into now, i have attached the complete data base. Thanks for ur patience and support.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The revised project I provided you should give you enough guidance to apply the demonstrated techniques to your full project. Change the RecordSource for frmTest1 to:
    SELECT tblTest1.*, tblTestInfo.*, tblOccurInfo.OccurName, tblStageInfo.StageName, tblSectionInfo.SectionName, tbleStandardInfo.StndName
    FROM tbleStandardInfo RIGHT JOIN (tblOccurInfo RIGHT JOIN (tblSectionInfo RIGHT JOIN (tblStageInfo RIGHT JOIN (tblTestInfo RIGHT JOIN tblTest1 ON tblTestInfo.TestID = tblTest1.TestID) ON tblStageInfo.StageID = tblTestInfo.StageID) ON tblSectionInfo.SectionID = tblTestInfo.SectionID) ON tblOccurInfo.OccurID = tblTestInfo.OccurID) ON tbleStandardInfo.StndID = tblTestInfo.StndID;

    If you made the name fields for the 4 additional tables the Primary/Foreign key instead of the Autonumber, these additional joins would not be needed for this form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. using the and function in a dlookup statement
    By englisap in forum Programming
    Replies: 10
    Last Post: 01-10-2011, 09:53 PM
  2. Format in Dlookup function
    By tpcervelo in forum Forms
    Replies: 6
    Last Post: 10-22-2010, 10:23 AM
  3. dlookup function problem
    By bdaniel in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:55 AM
  4. Replies: 3
    Last Post: 10-06-2009, 02:11 PM
  5. Need Help for Dlookup function
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 01-04-2006, 08:18 AM

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