Query in subform hangs after new record created

[DougW via AccessMonster.com]

Good morning.

I continue to work on my first Access DB in many years, and am working my way
through many little issues. I hope one of you can help me with the latest
puzzle.

I have a form bound to a table. I use the form to add new records. I also
have included a query in a subform to display what has already been entered.
There are two list boxes on the form. The selections from these two list
boxes are bound to the fields in the table. The form is set with the
following properties:

Allow filters: Yes
Allow Edits: No
Allow Deletions: Yes
Allow Additions: Yes
Data Entry: Yes
Recordset Type: Dynaset

I have hidden the record selectors on the form. My intention is that the
form is intended for adding new records, and defaults to opening in the next
(new) record. I added an "add record" button to add the new record when the
user selects it. That seems to be working okay (changing "data entry" to
"yes" was necessary for it to work this way).

The query has criteria based on the selected fields in the open form.
I have created a macro to requery the control that contains the subform/query.

The macro is triggered by the "on click" event in either listbox.
As the user clicks around in the list boxes, the query in the subform
dynamically updates.
The query is showing the user records that have already been entered, that
use the values he has selected in the listbox. It's to give the user
awareness of what has already been entered, before he goes ahead and creates
what could be a redundant record. (In some cases a redundant record is okay,
so I can't use the "no duplicates" property in my table to prevent this case.)


Everything seems to be working fine, until the user goes ahead and adds a new
record.
After the new record has been added, and continuing to work in the same open
form, the query stops refreshing dynamically. It kind of "freezes".
The query will blank out when any other selection is made from the listbox.
If I reselect the choice from the listbox from the record I just added, that
query will re-display with the values from the record just added.

As a test, I opened the same query separately, in its own window. When I
have added a record from my form, and my subform query is "frozen", the
separate query will update to the current selected values from the form
listboxes (I do have to toggle the separate query to design view and back to
see the update).

Closing my form and re-opening will reset things so it behaves as I want.
However I would like to be able to continue working in the form after
multiple new records are added.

I hope that wasn't too rambling. Any ideas or tips on what I might be doing
wrong, or how I might get the form to behave as I would like? I would
appreciate any suggestions.

thanks,
Doug

 

[Mark A. Sam]

Hello Doug,

If I'm correct, you are doing all of this to prevent a redundant record
except in certain cases? I think that instead of leaving it to the user to
decided this, you need to take care of that in programming. Just off the
top of my head, using an unbound form to enter new records is a good way to
approach it. The user would enter the data, then when he clicks a Save
button, the code would look for an existing record, based on the criteria
you specify. You could also decide at that time, if a redundant record is
acceptable. If the criteria don't allow for a new record, you can give the
user a message then exit the procedure.

I don't know how difficult this would be using macros, becuase I don't use
them, but that is the way I would approach it. You may be able to find an
example using VBA and modify it.

God Bless,

Mark A. Sam

 

[DougW via AccessMonster.com]

Hi Mark,

Thanks for the response.
Preventing a redundant record isn't really my main goal. I agree with your
comment that it would be better handled in programming. There is value to
me in having a dynamic query that will filter out and display the related
records when the user is clicking on the list boxes but before they add the
new record - to show them "this is what has already been entered". The data
entry will be sporadic and could be weeks between updates.

I'm just frustrated in that I seem to be close to a solution that will work
for me, and I'm just lacking knowledge of how the query refreshes and what
might cause it to hang after the record addition.
I'm guessing there is some property related to the form or my query that
needs to be adjusted to make it work as I want. My low level of expertise
in Access is just enough to get myself in trouble...

I appreciate the reply and am interested in any other thoughts you may have
on it.

Best regards,
Doug

Hello Doug,

If I'm correct, you are doing all of this to prevent a redundant record
except in certain cases? I think that instead of leaving it to the user to
decided this, you need to take care of that in programming. Just off the
top of my head, using an unbound form to enter new records is a good way to
approach it. The user would enter the data, then when he clicks a Save
button, the code would look for an existing record, based on the criteria
you specify. You could also decide at that time, if a redundant record is
acceptable. If the criteria don't allow for a new record, you can give the
user a message then exit the procedure.

I don't know how difficult this would be using macros, becuase I don't use
them, but that is the way I would approach it. You may be able to find an
example using VBA and modify it.

God Bless,

Mark A. Sam

Good morning.

[quoted text clipped - 72 lines]

thanks,
Doug

 

[Mark A. Sam]

Doug,

I am having a difficult time envisioning what you are doing, so bear with
me. On the click event macro, try saving the record as the first action.
If not try refreshing the form. If not then try requerying the main and
subforms. At least it is something to try.

God Bless,

Mark

Hi Mark,

Thanks for the response.
Preventing a redundant record isn't really my main goal. I agree with
your
comment that it would be better handled in programming. There is value
to
me in having a dynamic query that will filter out and display the related
records when the user is clicking on the list boxes but before they add
the
new record - to show them "this is what has already been entered". The
data
entry will be sporadic and could be weeks between updates.

I'm just frustrated in that I seem to be close to a solution that will
work
for me, and I'm just lacking knowledge of how the query refreshes and what
might cause it to hang after the record addition.
I'm guessing there is some property related to the form or my query that
needs to be adjusted to make it work as I want. My low level of
expertise
in Access is just enough to get myself in trouble...

I appreciate the reply and am interested in any other thoughts you may
have
on it.

Best regards,
Doug

Hello Doug,

If I'm correct, you are doing all of this to prevent a redundant record
except in certain cases? I think that instead of leaving it to the user
to
decided this, you need to take care of that in programming. Just off the
top of my head, using an unbound form to enter new records is a good way
to
approach it. The user would enter the data, then when he clicks a Save
button, the code would look for an existing record, based on the criteria
you specify. You could also decide at that time, if a redundant record is
acceptable. If the criteria don't allow for a new record, you can give
the
user a message then exit the procedure.

I don't know how difficult this would be using macros, becuase I don't use
them, but that is the way I would approach it. You may be able to find an
example using VBA and modify it.

God Bless,

Mark A. Sam

Good morning.

[quoted text clipped - 72 lines]

thanks,
Doug