Loop Error (1 Viewer)

Weekleyba · Sep 20, 2017

I have a small database that has subforms.
In the subform, I have a couple of comboboxes where one depends on the value choose from the other. Therefore, I have a requery combobox event on the independent one, so when I choose from dropdown on the first, my selection on the 2nd combobox will change.
However, when I select a new record from the main parent form, the second combobox will be blank, if there is a different value in the independent combobox described above.
So, I place a RefreshRecord on the subform and all works great until, I need to enter a new record. Then I get the "Macros can only be called 19 times" error.
So how do I fix this?
I'm still a novice at this so, not at all good with VBA.
Any help would be appreciated.
Thanks,
Brad

static · Sep 20, 2017

I had to google refreshrecord.

'In a client database, the RefreshRecord method is equivalent to the Refresh method of the Form object. In a web database, see the RefreshRecord macro action.'

It seems to be a macro command, so I'm unsure if you are using a macro or VBA or VBA generated from a macro, or what.

In any case, the fact that you have controls dependent on other controls being refreshed possibly suggests some kind of recursion (never ending loop).

Weekleyba · Sep 22, 2017

I'm still not able to figure this one out.
Attached is a very small sample database I put together to illustrate my problem.
What I want is to be able to input data and have comboboxes update info based on the entries, also be able to go between each record in each form (ProjectF, ContractF, ContractLineItemF) and for all the fields to display.
I'm primarily using RefreshRecord and Requery in the database but running in to loop issues and controls not showing data.
Please help....

Pat Hartman · Sep 22, 2017

Remove the refresh macro.
Also, your criteria in the queries is backward which makes the queries strange to read in QBE view.

SELECT CANT.CANID, CANT.CAN, [Forms]![ProjectF].[Form]![cboLocation] AS Expr1
FROM CANT
WHERE ((([Forms]![ProjectF].[Form]![cboLocation])=[CANT].[LocationID]));

Should be

SELECT CANT.CANID, CANT.CAN, [Forms]![ProjectF].[Form]![cboLocation] AS Expr1
FROM CANT
WHERE [CANT].[LocationID] = [Forms]![ProjectF].[Form]![cboLocation] ;

Weekleyba · Sep 25, 2017

Pat,
I corrected the query and removed the Refresh.
But with the Refresh removed, when you select different records in either subform, some of the data does not appear. (i.e. ContractNum in ContractF and BAP in ContractLineItemF)
It screams to me, please Refresh me, but when added, I get the error.
What am I doing wrong here?
How do I get rid of the loop error and still see all the data on ALL subforms?

Pat Hartman · Sep 25, 2017

All the foreign keys should be defined as required and their defaults should be Null rather than zero.

I tried rebuilding the query but that didn't work. I think the form may be corrupted. Sometimes you can fix this by creating a new empty database and importing all the objects. If that doesn't work, you might have to rebuild the forms manually. You can use copy and paste to get the control objects and then again to get the code behind the form.

Weekleyba · Sep 25, 2017

I think I figured it out.
I removed all the Refresh Record events and went strictly with VBA Current Requeries for the comboboxes that needed them.
I had to do this for all forms since you can look at different records on each form, the event needed to be trigger whenever you went to a new record on any of the three forms.
So here’s what I ended up doing and it seems to work. See attached.
Made similar changes on my real database and so far it passes my tests.
Crossing fingers….
Thank you for looking at this.
Brad

Pat Hartman · Sep 25, 2017

That's fine but make sure you actually need to requery the combos. Access should be doing that for you when focus moves to a new record.

Weekleyba · Sep 25, 2017

I'm not sure why, but without the requerys, the combos do not update. This little sample database that I had attached, I made just to illustrate the problem, so I don't this it is corrupted but, I'm just not smart enough to figure that one out.
Thanks for your help.

yodalearn13 · Sep 28, 2017

I think you need to requery the combos. Access should be doing that for you when focus moves to a new record.

Loop Error (1 Viewer)

Weekleyba

Registered User.

static

Registered User.

Weekleyba

Registered User.

Attachments

Pat Hartman

Super Moderator

Weekleyba

Registered User.

Pat Hartman

Super Moderator

Weekleyba

Registered User.

Attachments

Pat Hartman

Super Moderator

Weekleyba

Registered User.

yodalearn13

New member

Similar threads

Users who are viewing this thread