.requery stops working? (1 Viewer)

[AccessChap]

Hi guys, me again...

Help! It seems that I've managed to break another Access command in splitting my database. Essentially I have a form with several subforms and a button that fires off a command from VBA along the lines of OpenCall_OnHold.Requery etc for each of the subforms. The idea is that clicking this button triggers a refresh of the data in each of the subforms called OpenCall_OnHold etc

This worked fine but I have now created a front end database for the users and this option no longer works. I get no error message either so I've no error number to quote I'm afraid. The only thing I have noticed is that if I right-click on the VBA line and select Definition I get the error:

"Cannot jump to 'OpenCall_OnHold' because it is in the library 'C:\Documents and Settings\path\dbname.mdb' which is not currently referenced"

I'm not sure what this means but I can say that the front end copy of the database is indeed held in the path and filename quoted in the error message. If I go back to the original consolidated database then this error does not occur. Any ideas? It's obviously something I've broken by splitting the database but frankly I've no clue how to resolve the problem

I created the front end part of the database by copying sideways the back end, deleting the data tables and linking them back to the back end tables. What I mean is that from the perspective of the front end database the tables being accessed are now remote

All hints and suggestions most welcome!

TIA,

Andy

 

[RuralGuy]

You did not mention what version of Access you are using and on what Operating System. Is your patch level up to date including HotFixes?

 

[gemma-the-husky]

how have you split the database/created the front end

the backend should have just tables - it sounds like your forms are in the backend?

 

[AccessChap]

Hi guys,

The operating system is Windows 2000 and it's Access 2003. All patched up too.

Dave,

The database has been split so that the forms are indeed in the front end. Maybe I'm not referencing someting correctly though? Perhaps I've left a function to execute with a default connection parameter or something?

Thanks,

Andy

 

[RuralGuy]

You do know about the very important HotFix for the SP3 update right?

 

[AccessChap]

Yep, all up to date thanks

 

[RuralGuy]

Do you have any bogus MISSING references? What code is under the Refresh button?

 

[AccessChap]

Hi,

Thanks for the reply. When I drill down into the code and right click on the .requery I am taken into the VB Object Browser. It lists from left to right the forms, then Classes (of which ListBox is selected) and then Members of ListBox (of which Requery is selected). Requery seems to be a system supported command rather than a user-generated one that has been extended to operate on listboxes

Does any of that help or have I just clouded the issue?

Thanks,

Andy

 

[RuralGuy]

From a code window go to Tools>References and see if any existing checked references have MISSING next to them. If so then remember the object and uncheck it. Then thy your system again. Right click the Refresh Button and go to the event tab of the properties sheet and press the "..." button. Post the code here.

 

[AccessChap]

Hi,

Nothing that was checked was flagged as missing so I guess the references are ok. When I click on the .Requery this is the code I see:

Private Sub Button_Update_Click()

' Update list of incomplete security requests
OpenCall_OnHold.Requery
OpenCall_Unix.Requery
OpenCall_Create.Requery
OpenCall_Amend.Requery
OpenCall_Delete.Requery

End Sub

When I right click and select Definition on the .Requery I get no error but if I right click on the OpenCall_OnHold portion of the code and ask for the definition then I get the original error. Any ideas?

Thanks,

Andy

 

[RuralGuy]

That looks like it is pointing to 5 different controls on your form. Is that the case?

 

[AccessChap]

Yep, each line is for a separate control

 

[RuralGuy]

So is there a control named OpenCall_OnHold? What kind of control is it?

 

[AccessChap]

Hi, it's a list box

 

[RuralGuy]

Try adding Me:
Me.OpenCall_OnHold.Requery
Me.OpenCall_Unix.Requery
Me.OpenCall_Create.Requery
Me.OpenCall_Amend.Requery
Me.OpenCall_Delete.Requery

 

[AccessChap]

Thanks for that but no joy unfortunately The listboxes do not refresh and when drilling in to the code I get the same error. Any other ideas? I think you're thinking along the same lines as me (a scope problem) but I'm clueless...

Cheers,

Andy

 

[jubb]

OpenCall_OnHold.Requery etc for each of the subforms. The idea is that clicking this button triggers a refresh of the data in each of the subforms called OpenCall_OnHold etc

it's a list box

Am I right in assuming the listbox controls are contained in subforms on your main form? if so you need to change the me. to the full path to your subform.

Forms!form_name!Control_name.requery

or if you have some vb code on each of your subforms you can use

Form_subformname.controlname.requery

 

[AccessChap]

Am I right in assuming the listbox controls are contained in subforms on your main form? if so you need to change the me. to the full path to your subform.

Forms!form_name!Control_name.requery

or if you have some vb code on each of your subforms you can use

Form_subformname.controlname.requery

Actually no, that was my mistake. There is a subform on the form but the listboxes exist on the main form. I have tried using the Forms!MainFormName!Control.Requery notation but no joy sadly

 

[RuralGuy]

OK, try refreshing the references. Go to the References and uncheck something then exit to the code and go back to the references and check the ltem again. That should force Access to refresh the references.

 

[AccessChap]

Nope, no joy I'm afraid. However..... I haven't been idle and have discovered the function .Refresh. Adding in the line Forms!MainFormName.Refresh seems to achieve what I want. I can always add in another call for the function to the subform if I find that lagging behind. If I go with this as the solution can you see any pitfalls?

Cheers,

Andy