Queries and forms (2000)

Topic

New Reply

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.SortOrder, qryCriteriaGroupsAndNames.CriteriaName, qryCriteriaGroupsAndNames.tblCriteriaNames.SortOrder
FROM qryCriteriaGroupsAndNames, tblSiteInfo
WHERE (((tblSiteInfo.SiteID)=[Forms]![frmSiteInfoDataEntry]![SiteID]))
ORDER BY qryCriteriaGroupsAndNames.tblCriteriaGroups.SortOrder, qryCriteriaGroupsAndNames.tblCriteriaNames.SortOrder;

(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?
*********
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.

Any help or pointers greatly appreciated.

Darsha

Reply | Quote

Replies

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!

Reply | Quote

Thankyou
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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!!

Darsha

Reply | Quote