Form requery is refusing to cooperate! Help

[fraser_lindsay]

Hi,

I have a form with a subform. On the subform I have a combo. In the not in list of that combo I trigger a second form as popup dialog to enter a new combo option, along with some linked fields.

When the user clicks the 'close' command button it closes that form and returns them to the original form which is still open.

I woudl like th combo to be requeried so the new item added now appears without having to close and reload the form.

I already have this in operation on other forms, although in a slightly different setup. I use this code:

Dim frm As Form 'This code requeries and refreshes all open forms in the current project
For Each frm In Application.Forms
If IsLoaded(frm.Name) = True Then
' requery and then refresh the form
frm.Requery
frm.Refresh
End If
Next frm

That doesn't work for some reason so I have tried lots of different combination and locations for the code yet none seem to be able to requery the subform on the main form while open and I cannot see why.


My most recent attempt is this different code on the close button:

Private Sub cmdClose_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord

Me.Requery
Me.Refresh


DoCmd.Close

Form![frmHazards_Subform].Requery


End Sub

Can anyone help please?

 

[wiklendt]

Hi, try this:

Private Sub cmdClose_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord

Me.Requery
Me.Refresh


DoCmd.Close

Form![COLOR=Red][frmYourMainForm]![/COLOR][frmHazards_Subform].[COLOR=Red]Form.[/COLOR]Requery


End Sub

this link may help in future: subform syntaxhttp://www.access-programmers.co.uk/forums/showthread.php?t=102826

 

[fraser_lindsay]

Hi,

Thanks.

I also tried referencing the exact control an dthat doesn't work.

Form![frmJSA]![frmHazards_Subform].Form![Hazard_Name].Requery

I am getting the same error for both attempts. 2457 I think it was - can't find the object blah blah. I have double checked my names etc and they are correct.

I'm stumped.

 

[fraser_lindsay]

ah, fixed it.

I moved my code from above to the 'on close' event of the popup form.

Seem to have missed that one. Doh.

Thanks for your help though, that link is useful too.

 

[fraser_lindsay]

Actually, maybe I was a bit premature. It works now if I only have the subform loaded sperately - it refreshes and I can pick it.

However if I try it from the main form for some reason the subform isn't updating.

Hmmmm

 

[fraser_lindsay]

I'm replying to myself which is quite sad. Anyway, anyone who is remotely interested I have fixed it.

Needed to reference and requery the exact control on the subform in the 'On Current' event of the main form i.e. when the main form is re-queried from closing the popup form this must trigger 'on current' which in turn refreshes that control on the subform.

 

[wiklendt]

Hi,

Thanks.

I also tried referencing the exact control an dthat doesn't work.

Form![frmJSA]![frmHazards_Subform].Form![Hazard_Name].Requery

I am getting the same error for both attempts. 2457 I think it was - can't find the object blah blah. I have double checked my names etc and they are correct.

I'm stumped.

try without the Hazard_Name part... and have another look at that link i sent - i can never remember when it's appropriate to use a period and when i should use an exclamation point...

Form![frmJSA]![frmHazards_Subform].Form!Requery

in order, can you tell me what all your relevant forms/controls are called? that is:

1) your main form
2) your subform CONTROL
3) your subform
.
.
is your subform just a sub of the main, or a sub of another sub?

as a last resort, could you post your db (or a sanitized version of it) - it could always be soemthing no so obvious as a name/spelling...

hang in there, we'll get to the bottom of this!

 

[fraser_lindsay]

Yes, you are correct - part of the problem was the name of the subform on the main form. I had recently changed the name of the subform to a more suitable DB compatible name. But as this wasn't replicated through everything I had to track down the changes in code and controls. I was missing an underscore, which is why I think early attempts could not find the object.

Plus I put the requery code in the 'on close' event and added a requery event for that speciifc ocntrol on the 'con current' event of the parent form.

It's all working now, but thanks for your help. The links/syntax are an excellent reference. Thanks

 

[wiklendt]

Yes, you are correct - part of the problem was the name of the subform on the main form. I had recently changed the name of the subform to a more suitable DB compatible name. But as this wasn't replicated through everything I had to track down the changes in code and controls.

ah! that's the single most annoying thing in access! yes, happens to me from time to time also.

The links/syntax are an excellent reference. Thanks

yeah, that's one document i always have on hand (funny, you'd think i'd have memorised it by now!) - probably the single most helpful resource in the universe as far as subforms goes.

 

[dfenton]

This has nothing to do with your problem, but I just thought I'd suggest that this code:

Dim frm As Form 
'This code requeries and refreshes all open forms in the current project
For Each frm In Application.Forms
  If IsLoaded(frm.Name) = True Then
     ' requery and then refresh the form
     frm.Requery
     frm.Refresh
  End If
Next frm

Since you're refreshing only open forms, use the collection that includes only open forms:

Dim frm As Form
For Each frm In Forms
   frm.Requery
Next frm
Set frm = Nothing

Also, note that a .Refresh after a .Requery is a waste of effort, as after a requery you've got all the latest data and an immediate refresh won't get you anything you don't already have.

 

[speakers_86]

i can never remember when it's appropriate to use a period and when i should use an exclamation point...

I don't know myself. Is there a good reference for this?

 

[JANR]

I don't know myself. Is there a good reference for this?

Yes here is one: http://blogs.msdn.com/access/archive/2008/05/30/dot-or-bang.aspx

According to this a dot . gives you early binding to controls which resolves at compiling and a Bang ! gives a late binding at runtime.

JR

 

[speakers_86]

So it doesn't matter too much?

 

[JANR]

Not as much accept this:

Also in the case of Forms, if you change the underlying query at runtime - dot references to the old query's fields will fail at runtime since update of the Form's type library cannot happen at runtime.

JR

 

[speakers_86]

From the AWF Website:

Exclamation point or period - which do I use and when?

There is a basic rule to follow here; if you name something yourself then use the exclamation point, if Access has named something then use the period. For example, form names and control names need an exclamation point. Properties need a period.

If you use the Expression Builder, the punctuation is filled in for you when you select a control or property, together with the names of the objects and controls.

 

[fraser_lindsay]

I didn't realise this thread was still going. However, thanks for the extra code pointers and links. Always highly appreciated from you guys. I'd still be staring at crappy access template if it wasn't for helpful people on this forum.

Thanks

 

[speakers_86]

I didn't realise this thread was still going.

Are you kidding. I love talking about this stuff. (insert nerdy smiley face here)

 

[dfenton]

Let me offer a thumbs down on the dot operator for control references. The reason it works is because when you create a control, Access creates a behind-the-scenes wrapper property around your control. That's why you get compile-time checking, because it's actually a hidden property that you're using, not the control itself (as you are with the ! operator).

When this works, it's wonderful.

But when it breaks, you can have major issues because something you didn't create is corrupted.

Thus, for control references in forms and reports, I always use the ! operator. I just don't find the compile-time checking worth the risk because when it goes bad, it can be very hard to fix.

Also, by avoiding the dot operator for controls, there's a much cleaner distinction for what it is used for -- it is for properties and methods. A control is not a property/method of a form, so referring to it as Me.Control is illogical. Also, you have to keep track of where you can't use the dot operator, such as with a recordset: rs!FieldName works while rs.FieldName does not. If you avoid using the dot operator for controls and fields, then you never have to keep this inconsistency sorted out.

 

[speakers_86]

Me.Control is illogical.

This may be a hard habit to break.

 

[wiklendt]

Let me offer a thumbs down on the dot operator for control references. The reason it works is because when you create a control, Access creates a behind-the-scenes wrapper property around your control. That's why you get compile-time checking, because it's actually a hidden property that you're using, not the control itself (as you are with the ! operator).

When this works, it's wonderful.

But when it breaks, you can have major issues because something you didn't create is corrupted.

Thus, for control references in forms and reports, I always use the ! operator. I just don't find the compile-time checking worth the risk because when it goes bad, it can be very hard to fix.

Also, by avoiding the dot operator for controls, there's a much cleaner distinction for what it is used for -- it is for properties and methods. A control is not a property/method of a form, so referring to it as Me.Control is illogical. Also, you have to keep track of where you can't use the dot operator, such as with a recordset: rs!FieldName works while rs.FieldName does not. If you avoid using the dot operator for controls and fields, then you never have to keep this inconsistency sorted out.

as a newbie and someone who like rules as a general thing - would it be possible for me to only ever use the bang, and never a period? or are there instances where i MUST use a period?

and something that recently got me in the jugular a week or so ago - access crutches (a term that took my fancy - coined by boblarson, but he's not the one who bit my head off for this) like lookup fields in tables... it sounds like you're saying Me.Control is a crutch that is better not used (but available via Microsoft to make things 'easy')?

i agree with speakers - it will be a hard habit to break, expecially seeing as, in the VBA editor, if i type "Me.", then access brings up a list of things i can put in after the period, such as a control name.... if i type in "Me!", it doesn't - i would have to remember how i've named/spelt my control AND whether i'm even thinking of the correct form/control...

just as breaking the habit for lookup fields... god i've found that really difficult.