Unable to refresh/requery subform using VBA

[togo]

Hi. I spent most of the day on this problem. There are 2 levels of forms, the main parent form is called INSPECTION. Then there are two subforms: TESTRESULTS and addTESTRESULTS.

the form TESTRESULTS is linked to the TESTRESULTS query "QTESTRESULTS2". This query looks at two hidden fields on INSPECTION called FILE and REPORT NO. The query finds all fields inside of TESTRESULTS that match the two criteria. This works fine. The form TESTRESULTS is linked to the QTESTRESULTS2 query as direct data source link. This works fine.



a temporary table has been setup to append test results to TESTRESULT table. This works fine. What doesn't work fine is updating the subform TESTRESULTS when needed. I want it so that the welder enters his information, clicks save, the info gets appended to TESTRESULTS table and instantly updates on the form for them to verify.

[inside subroutine for saving a record] all efforts at accessing this form have failed. I've tried !form!subform!requery, it has failed, I've tried making dummy fields inside the form and accessing them and that has failed, I've tried .setfocus on the form and that has failed, each time the compiler tells me 'unknown field', when in fact I'm trying to hit the form itself.

Its worth mentioning that I've set events [on this form for any kind of update event including mouse click] to Me.requery for this form when you click on it, it updates, but I need to automate this somehow, and the form just seems untouchable via VBA. Any help is hugely appreciated.

[June7]

You mention subform addTestResults in the first paragraph but then say nothing about it elsewhere. How is this form involved in the process? Why do you have a temp table and an APPEND query? Why is data entry not direct to TestResults table?

Suggest you give subform container control a name different from the object it holds, like ctrResults. Then:
Form_Inventory.ctrResults.Requery

Show the procedure code or provide db for analysis.

[togo]

there are many reports tied to each job number. There are many job numbers so that there can be many duplicate reports. I decided to use an append query to form the database and each report is entered into a temporary table and when the save report button is pushed, that temporary table gets appended to the real report table.

The same exact thing happens with the item list for the same reasons. A list of items per report, there may be duplicate item numbers for multiple reports.

For the items table there is a form linked to a query so that when a new item is entered the inspector can tell what has been entered and has not. When hitting the ''save item'' button, it goes to this sub:

Private Sub Command61_Click()
DoCmd.GoToRecord , , acNewRec
Dim t As Object
Set t = CurrentDb
Set t1 = t.openrecordset("tempTestResults")
t1.EDIT
t1("FILE").Value = job1
t1("Report No").Value = report1
t1.Update
DoCmd.OpenQuery ("TESTRESULTAppendQuery")
DoCmd.Close acForm, "testresults"
'Forms![inspection]![testresults].Form.SetFocus
'DoCmd.SelectObject acForm, "testresults", True
'DoCmd.BrowseTo acBrowseToForm, "testresults", "inspection.testresults", "item #"
'DoCmd.SetFocus "!forms!inspection!testresults"
'!forms!inspection!testresults!testresultactivate
'DoCmd.SelectObject acForm, "testresults"
'DoCmd.GoToControl "testresults"
'DoCmd.GoToRecord , , acNewRec
End Sub

the last few lines which have been remarked out were various efforts to update the form in some way, all of them unsuccessful, one of them references a dummy text box that was put into the form in another effort to activate the form somehow. job1 and report1 are linked to hidden fields on the subtable (addTESTRESULT) which are in turn linked to their parent fields.

if a single .requery command was able to get through to the form, my problem would be solved I think

ps. the form is set to activate on any event. Clicking on it, for example, updates it, if only Access could be fooled into thinking the form was being clicked on automatically.

[June7]

I still don't understand why you want this intermediate step of entering record to a 'temp' table and then copying it to 'real' table. I have never set up a db like this. With properly structured form/subform this should be unnecessary.

Want to provide db for analysis? Follow instructions at bottom of my post.

[togo]

Certainly. Thanks for your time.

[June7]

I see you have tables tempTestResults and TempReport and virtually identical TESTRESULTS and EXAMINATIONREPORT. Only the primary key fields are different

I presume each job can be associated with only one client but can each client have more than one job?

The EXAMINATIONREPORT is associated with JOBNUMBER. What is the relationship - 1 to 1, 1 to many, many to many? A form/subform arrangement should probably be:
Main form bound to CQIJobs (why is this a linked spreadsheet) and subform bound to EXAMINATIONREPORT. Master/Child links properties of the subform container control will synchronize associated records. Data entry in subform will automatically save the JOBNUMBER as foreign key in report record.

Same concept for associated EXAMINATIONREPORT and TESTRESULTS. What is the relationship of these tables - 1 to 1, 1 to many, many to many?

[togo]

many to many. I initially thought based on the paper form it would be job number, then subreports, and for each subreport items. While this is the organization of hierarchy, the way the numbering is used there could be many duplicates, they switched the numbering on me midway through the project. Ended up not being able to organize it in a way that Access likes, now just tabulating the job number and report number for reach report and item individually.

I've been sinking a huge amount of time into this. Can someone look at this updated version of the database and tell me why when hitting "save and clear report" the form containing the list of items doesn't refresh with the blank qTESTresults2 query? And sometimes the form does not update when saving items, and Access has to be saved and closed in order to wake it up so it updates when new items are added.

thanks for input. And sorry for the messy code, haven't gotten around to cleaning it up.

[June7]

Sorry, the data structure, form arrangements, and code are so bizarre and convoluted, I don't even want to try understanding.

A many-to-many relationship requires 3 tables for proper normalization. I can't even figure out if your data structure is normalized.

[togo]

Fair enough. I don't have time to sort it out at the moment, it does work, and the only issue is that the form "TESTRESULTS" won't refresh itself consistently. I was using Form_TESTRESULTS.Requery to refresh it.

but if you use it like a normal end user, and open up the tables and queries in the background normally, they update properly and the data goes where it needs to go as you enter it into the forms and press buttons.

[togo]

Oh. I forgot to tell you where to start :/ to test it out just double click on inspection form.

[June7]

The was about the only thing I figured out. After look at structure and code, threw up my hands and yelled 'uncle'.

[togo]

I ended up just starting from scratch and it turned out fine.