How do I requery a subform after an append query?

[Big Pat]

Hi,

I have a form with STAFF and a subform with SKILLS. When I add a new STAFF record and update its [Job] field, I want to run an append query, to put required skills into a table and immediately update the display of the subform.

I tried two methods:

1. A macro that does "Save" and then "Open append query"

This does the first two parts as the tables are being updated correctly....but how would I then redisplay the subform? I tried Repaint on the subform, but it told me it wasn't open, then I tried Repaint on the main form but it appeared to have no effect.


2. Doing it via code.

I suspect this may me the better way as at least I know how to use the Requery statement (if almost no other code!!)

But what's the syntax for running an append query? The code I came up with (in my hamfisted way) is as follows:

Private Sub Job_AfterUpdate()
DoCmd.Save
DoCmd.OpenQuery(qryAppendRequiredSkills,acViewNormal,,)
Me.frmStaffSkills.Requery
End Sub

But the OpenQuery line is giving me an error message. Compile error. Expected: expression

I don't know what it needs!


I don't mind whether I end up using method one or two, but as you can see I'm stuck on both.

Thanks for any help,

Pat

 

[MStef]

Try this:
[Forms]![MainFormName]![SubFormName].Requery

 

[Big Pat]

Thanks, but where?

Try this:
[Forms]![MainFormName]![SubFormName].Requery

Would I put this statement into the macro? If so how?

Remember I ALREADY have a working Requery statement, so if I'm going to use code instead of a macro, then it's the DoCmd.OpenQuery(qryAppend..etc) part that I'm stuck on.

Thanks,

Pat

 

[MStef]

Instead of your codeMe.frmStaffSkills.Requery

 

[Big Pat]

I have tried that without success. My code now reads:

Private Sub Job_AfterUpdate()
DoCmd.Save
DoCmd.OpenQuery(qryAppendRequiredSkills,acViewNormal,,) << === error is here
[Forms]![frmStaffAddNew]![frmStaffSkills].Requery
End Sub

But I think you misuderstood my question. The error message is NOT on the Requery line. It is on the DoCmd.OpenQuery line.

Pat

 

[MStef]

DoCmd.OpenQuery "QueryName", acNormal, acEdit

 

[Big Pat]

No,

still not working.

Private Sub Job_AfterUpdate()
DoCmd.Save
DoCmd.OpenQuery(qryAppendRequiredSkills,acNormal,acEdit)
[Forms]![frmStaffAddNew]![frmStaffSkills].Requery
End Sub

Could it be an error with brackets or quotes?

 

[MStef]

try this:
DoCmd.OpenQuery "QueryName", acNormal, acEdit
instead of
DoCmd.OpenQuery(qryAppendRequiredSkills,acNormal,a cEdit)

 

[Rich]

If the skills are displayed/entered in the relevant subform why are you using an Append query?

 

[Big Pat]

If the skills are displayed/entered in the relevant subform why are you using an Append query?

My tables are STAFF, JOBS, SKILLS, JOBSKILLS and STAFFSKILLS. Basically, every JOB has a set of required SKILLS but the STAFF employee may or may not have some or more of the skills.

So, when I add a new person to frmSTAFF, I need records to be appended to JOBSKILLS to say that this person *needs* those skills. Then, immediately after that, I need the subform to update and display that along with other fileds in the table so that I can tick to say whether that JOBSKILL is actually present.

I got some help from DocMan over the weekend in this post
http://www.access-programmers.co.uk/forums/showthread.php?t=128268

There's also a fuller explanation of what I'm trying to achieve overall.

Thanks to all for your help so far.

 

[Rich]

Then job skills should be a subform of Staff, if the skill is present or not then why do you need a checkbox?

 

[Big Pat]

JOBSKILLS *is* a subform of STAFF. The way I need it to work is as follows:

User adds new member of staff, enters name, dept. etc. then selects a value for [Job] e.g. "Radiologist".

Subform should populate immediately, showing a continuous list of the REQUIRED skils for a radiologist. The user should not have to select them as it may be thousands of skills over dozens of jobs. This is where I think I need the append query. At least that's my understanding of what The Doc Man said in that other post and I think I was coming to that conclusion myself.

Then the user will just need to scroll down the form, ticking the skills that this person actually has and leaving unticked those that have not yet been gained.

E.g.

Citizen.....John Q......Radiologist

subform displays

CTscan tick
MRIscan (no tick)
Plain film tick
Interpretation tick

etc.

 

[Soma_rich]

I had this same problem, in my case it was not the code that was wrong but when it was being called.

Make sure you requery the subform onenter that way assuming the append happends from a control outside the subform when the user enters the subform it is requeried.

 

[Big Pat]

Thanks soma_rich,

I'll give that a go. You could be onto something there!

Pat

 

[gemma-the-husky]

even if the query opens, the code in the calling form will continue to run, and therefore the requery will take place BEFORE you close/modify the underlying data.

you need to wait until the query is closed before requerying


- not sure how to add a link in here, but look at my entry in code repository -"waiting for an object to close"