Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7

    HELP! Display a query result into form (SOLVED!)

    Dear All,



    Good Day! I need your guidance to solve this problem. I have been stuck for 2 days already.

    I am having a form and the record source is link from a table called tblTask.
    tblTask keeps many different field and many different date.

    In the form, after I enter the Start Date and End Date for the task, the system should show me the Workdays.

    The Workdays will come from query, qry3. After I did some calculation.


    Now, I manage to query the workdays for a particular task already, but I don't know how to show the result into the form.
    Besides that, there will be more than one fields which need to display the result from different queries, so I cannot link the form to a query.

    I have tried to use the DLookUp method but it show the error 2001.

    I am attaching with my sample code and I really hope you all can help me to solve this problem.


    TQVM!
    Last edited by leanne; 06-21-2010 at 01:38 AM.

  2. #2
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    After looking at your file , i just made a subform on your form based on qry3..
    This displayed the results of qry3 on the Form
    Apologies if this is not what you are after.

  3. #3
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Hi, Flekrad.

    Can I see how you made the changes?!?

    I am still trying on this portion too. But I cant point the subform result into the master form.

    This is what I got. (Plz refer image2)

    The txtWorkdays in master form keep showing the 1st record from subform. When I move the cursor, it cannot move, even the record in subform has changed.

    Plz help!!! TQVM!

  4. #4
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    Hopefully im on the right track.
    When i opened your form i had the error appear , and my screen seemed to flicker .
    i clicked the subform from the tolbox and used the wizard to create it and pointed it to qry 3.
    After it displayed my cursor to react strangely , I deleted the text box Workdays with the error message, and then all seemed to be fine.

  5. #5
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Flekrad,

    Ya, I want to show the TotalWorkday.

    But I dun want to show the subForm listing because in my actual program, there are many fields to show other data. This TotalWorkday is just one of fields in my Main Form. I still need do the same things for another 2 more queries and text fields.

    So, I must show the result from SubForm to Main Form.

  6. #6
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    Ok sorry i was going down the wrong path, (still new to access)was going to suggest
    =[Form].[qry3subform]![TotalWorkday]
    (recently learnt this and has been very handy-normally i had used it to point to a text box on a subform)
    but i found in another thread (where you posted http://www.access-programmers.co.uk/...d.php?t=194606 ) that it wasnt working
    Last edited by Flekrad; 06-17-2010 at 06:41 AM.

  7. #7
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    Ok try this out, build a subform based on Qry2 , and in the footer of this subform put a textbox in with =[Workday]-[CountOfALDate] in the text side of it , take note of the number of the text box(mine is text6 for this trial)
    oN your main form make a text box to point at this textbox in the subform by putting in the command =[Form].[qry2subform]![Text6]
    save and goto form view to see calculation working , then back to design and in the Properties of the main form on the Event tab goto On Load and use the Choose Builder Option and in Code builder write the code
    Private Sub Form_Load()
    Me!qry2subform.Visible = False
    End Sub
    This seems to work here, again hopefully this is what you are after

    sorry i am unsure how to upload the database for you.
    Last edited by Flekrad; 06-17-2010 at 06:41 AM.

  8. #8
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Flekrad, that is excellent!!!
    By using your method, now my database can really move, I got what I want already!

    I have changed the [qry2subform] to [Child6] coz my subForm is Child6.

    TQVM!!! Really thanks for your help!
    I have surfed through many forums / posts but cannot find what I really want.

    TQ! TQ! Arigator! Terima Kasih (Malay) !!!

  9. #9
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    Excellent , Glad it worked

  10. #10
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Now I have another small problem is,
    When the EndDate is null, the txtWorkday will show #Error

    you try to delete one of the EndDate in tblTask, then the result in Form will show #Error.

    Still cannot get the logic how to prevent this because user will not enter both date at the same time.

  11. #11
    Flekrad is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    18
    Sorry , this seems beyond my access knowledge, will look though and see if i can find anything, but i think we are in need of the more knowledgable folks here on this one..

  12. #12
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    your Dlookup shoud look like this:

    =DLookUp("[TotalWorkday]","qry3","[PID] ='" & [PID] & "'")

    coz PID is a TextField.

    Attached you sample modified.

  13. #13
    leanne is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    maximus, thank you for helping me solve the problem.
    That is really what I want!

    Now if I don't put in the end date, there is no query. And the txtWorkdays will be blank and will not show #Error

    Thank you!

  14. #14
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Please mark the thread solved

  15. #15
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    mark the thread solved please. This will help others with the same problem find answer quickly.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-18-2010, 12:05 PM
  2. Display Query Results on a Form
    By P5C768 in forum Queries
    Replies: 5
    Last Post: 05-04-2010, 11:04 AM
  3. How do I determine a SQL query result?
    By Trainman in forum Database Design
    Replies: 1
    Last Post: 10-15-2009, 04:49 AM
  4. display query results in a form
    By P5C768 in forum Queries
    Replies: 3
    Last Post: 08-14-2009, 03:02 PM
  5. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 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