Complicated (maybe) form question

[Matt]

I have a question about a problem I have been having with one on my
database's forms.

My form consists of one main form with two subforms. Subform1 is a
continuous form which lists the names of individuals. Subform2 is a
single form that lists details about the indivduals in subform1 (phone
numbers, addresses, etc.) Both subforms contain information from the
same table, in this case tbl_People. For background information,
tbl_People has a one-to-many relationship with tbl_Assignments, which
tracks a person's assignments.

Now I have the two forms set up so that I click on a record in the
subform1 and that individual's information appears on subform2.
Thanks to Treeless on UtterAccess, I was able to accomplish this by
creating an unbound text box in the main form that contains the value
of the primary key from subform1. The primary key in subform2 links
back to the text box in the main form.

Everything was working fine, until I added some VBA code to subform2.
I added some DCOUNT statements that use the primary key of tbl_People
to search another table, tbl_Assignments, for matching records. Now,
whenever I switch records by clicking on a record in subform1, Access
freezes up. The lower left hand corner of the screen reads
"Calculating..." and the mouse pointer turns into an hour glass, and
the program stops responding.

Once this problem is solved, my database will be more or less
complete. Is there anyone who knows what I might be wrong?

Thanks.

 

[alicia]

when you say "clicking on a record in subform1" what does
that part of the code look like? is it an on_click event?

 

[Matt]

Yeah, I have a command button lying over top each record in the
subform1. The button is transparent so it doesn't appear on the
screen, but you can still click it.

Private Sub Record_Select_Button_Click()

DoCmd.RunCommand acCmdSelectRecord

End Sub

 

[Matt]

Ok, I have diagnosed what has been giving me problems with my database
all along. By changing my DCOUNT statements to DLOOKUPs, I have
stopped the infinite loops.

Now there is a new problem. In my OnCurrent event for subform2, I
have my form query tables to see if certain records exist. This is
necessary because I have certain fields set up to grey out
automatically when there is no record to display. If the record
exists, a checkbox is checked and the field is enabled. If the record
does not exist, the checkbox is blank and the field is disabled.

However, everytime a record is saved on subform2, subform1 re-queries,
and that causes the OnCurrent event to run again. Since no records
are added until I fill out the field that is enabled when the checkbox
is checked, the checkbox unchecks itself as the OnCurrent event can't
find the record I hadn't yet had a chance to create.

This is from the Accsess help files:

"Microsoft Access automatically saves the record you are adding or
editing as soon as you move the insertion point to a different record.

To explicitly save the data in a record while you are editing it,
click Save Record on the Records menu."

Is there anyway to disable Access from automatically saving records
when you move the insertion point?