Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries and forms (2000)

    I am developing a database with 2 main tables (tblSiteInfo and tblSiteReviewCriteria) with a one-to-many relationship.
    The database contains information on site where audit are being carried out..
    The table tblSiteInfo stores generate project info such as names and address.
    The table tblSiteReviewCriteria contains commments referring to a fixed set of criteria names (which are also grouped into categories)

    Site information is added by typing on a form (frmSiteInfoDataEntry) containing a tab control with a number of pages.
    One of the pages contained a subform where the Review Criteria infomation is typed.

    I have written a the following query, activated by clicking a button on the form.

    INSERT INTO tblSiteReviewCriteria ( SiteID, CriteriaGroup, GroupOrder, CriteriaName, NameOrder )
    SELECT tblSiteInfo.SiteID, qryCriteriaGroupsAndNames.CriteriaGroup, qryCriteriaGroupsAndNames.tblCriteriaGroups.SortOr der, qryCriteriaGroupsAndNames.CriteriaName, qryCriteriaGroupsAndNames.tblCriteriaNames.SortOrd er
    FROM qryCriteriaGroupsAndNames, tblSiteInfo
    WHERE (((tblSiteInfo.SiteID)=[Forms]![frmSiteInfoDataEntry]![SiteID]))
    ORDER BY qryCriteriaGroupsAndNames.tblCriteriaGroups.SortOr der, qryCriteriaGroupsAndNames.tblCriteriaNames.SortOrd er;

    (The query qryCriteriaGroupsAndNames just selects information from two other tables. Ultimately I'm looking to develop the database futher,
    maybe letting the user choose a subset of values to be appended from a much bigger list)

    This appends 36 rows to the Review Criteria table (i.e 36 criteria are bening assessed for each site)

    Other fields in this table are then updated manually by typing directly onto the subform within the main data entry form.
    *********
    Problem, whilst the query works fine, I don't see the records on the subform unless I move away from the site record and back again.
    Why is this, am i doing something wrong? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    *********
    Secondly, the query needs to be run (and run only once) automatically when a new site record is created.
    I'm guessing this will require a test for a new record and maybe be triggered by an after update event.
    *********
    I'm really a beginner and getting way out of my depth. I think what I'm trying to do in principle is fine but I haven't got a clue how to do it. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>



    Any help or pointers greatly appreciated.



    Darsha

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Q1: I assume that the button_Click event is in VBA code. If true look at the code in the class form and insert a line like this next to the DoCmd.OpenQuery stmt:
    [sbfSiteReviewCriteria].Requery
    Where sbfSiteReviewCriteria is the name on the subform control. To find the name: select the subform control with the form in design mode and look for the Name property.

    Q2: If you are not used to do VBA coding, I think you best stick to what you have and ensure that the new record is saved before the query is run. You cannot link new records in another table to the new SiteInfo record until it has been saved. To save the record you may insert the following code line in button_Click event before the Docmd.OpenQuery stmt:
    DoCmd.Runcommand acCmdSaveRecord

    If this is rubbish to you or I did not seem to understand your problem, yell!

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Thankyou <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I can't believe this bit was so obvious. I had the save record line in but it didn't occur to me I needed to requery the subform.

    My code now reads:
    Private Sub cmdRunQuery_Click()

    'save record first before running query
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    On Error GoTo Err_cmdRunQuery_Click

    Dim stDocName As String

    stDocName = "qryAppendDefaults"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    'requery the subform to display the new records
    [sfrmSiteReviewCriteriaDataEntry].Requery


    Exit_cmdRunQuery_Click:
    Exit Sub

    Err_cmdRunQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunQuery_Click

    End Sub

    Don't suppose you have any suggestions on the real task at hand, which is to run the query a soon as a new record is created. <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Since you are using a tab control it should not cause you a lot of trouble to do what you aim to do.
    Selecting the page having your subform will tricker the AfterInsert event for the form when you have typed your new record..
    Hence, all you have to do is to use the following code for your form

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Thanks, I will try try this but I still need to ensure the query is run only once. Using this code the query could be run again. Somehow I need to check the tables to run the query only where a site has no matching records in the review criteria table. I could use the query wizard to create a query to check for unmatched records (i.e a site with no matching records in the review criteria table). If there are no criteria continue the code and run the query, else exit to sub routine. Trouble is I can't think how to check if the current site is part of the recordset produced by the unmatched query.

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Don' t worry - the query will be run once only for each SiteInfo record in your table. The form's AfterInsert event will be trickered only after a new record has been added.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    I don't believe it! Thankyou.

    I have been working a little with with databases for some time now, taking over from a colleague who has now left. I always seem to think things are more complex than they really are. I will try your suggestion tomorrow and let you know how its goes <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    Excuse my bad spelling. The trick is to spell trigger the correct way.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and forms (2000)

    I figured that was what you meant
    Everything is working perfectly now, thanks for your help. Time to tackle the other 101 improvements I need to make!! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Darsha

Posting Permissions

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