Having Trouble with Bound Form

  • Thread starter Thread starter epigram
  • Start date Start date
E

epigram

I've got a form (in an app that I inherited) that has several
controls, and a subform. The main form is bound to a query. This
query can return more than one row. There are controls on the form
that are bound to columns in the query. All of this makes sense. Now
for the part(s) I'm having difficulty with.

1) The query that is bound to the main form always returns less
records than the number of records indicated in the main form's
navigation button area. I can achieve this latter number, in a test
query, if I eliminate a search condition from the WHERE clause. I
don't know if this is related to my main issue (number 2). It just
makes me think there is some 'magic' going on here that I'm not aware
of.

2) The issue that I'm having is that the query on the main form
returns more than one row. There is a bound control on the main form
that is bound to a column in that query. The issue is that whenever
that control is updated (via code in the form) and the record that I'm
currently viewing gets saved, ALL rows in that query get updated. In
other words the column, that is represented by the control on the main
form, gets updated for all rows that were returned in the query bound
to the main form.

I know that a form, usually, is bound (one-to-one) with a table/query.
I also know that you can hand-code a lot of operations using SQL
within a form as well. What I don't understand is how Access' "auto
save" type functionality can update all the rows in the main form's
query without me being able to see some SQL statements inside the
form. I assume that this is not Access doing this automagically and
there is some hand-coding going on somewhere in the form. I simply
cannot locate it.

Are there any hooks or procedures I should be looking for in the
code/database that would help me figure out where these rows are being
updated? I keep expecting to see some code in the main form like an
_OnSave method that would contain the SQL I'm expecting to find.
Could it be a macro in a table somewhere that would act as a trigger
that when a certain column is udpated, it would updated other columns
based upon some logic?

Any help would be greatly appreciated.

Thanks,

Tim
 
Tim,

There are a myriad of ways in which this could happen, so it's just a
matter of trying to track down what is happening. You are right in
thinking to look at the form first. Have a look at all the macros and
code associated with all of the form and the controls therein. Sorry I
can't be specific, for obvious reasons. What, for example, is the code
by which "that control is updated"?
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


epigram said:
I've got a form (in an app that I inherited) that has several
controls, and a subform. The main form is bound to a query. This
query can return more than one row. There are controls on the form
that are bound to columns in the query. All of this makes sense. Now
for the part(s) I'm having difficulty with.

1) The query that is bound to the main form always returns less
records than the number of records indicated in the main form's
navigation button area. I can achieve this latter number, in a test
query, if I eliminate a search condition from the WHERE clause. I
don't know if this is related to my main issue (number 2). It just
makes me think there is some 'magic' going on here that I'm not aware
of.
Click to expand...
If the Query is the RecordSource of the Form and there is nothing in the
Form's Filter Property, the number of rows returned by the Query matches the
RecordCount in the NavigationBar. Check the NavigationBar and see if there
is a phrased "(filtered)" after the RecordCount.




2) The issue that I'm having is that the query on the main form
returns more than one row. There is a bound control on the main form
that is bound to a column in that query. The issue is that whenever
that control is updated (via code in the form) and the record that I'm
currently viewing gets saved, ALL rows in that query get updated. In
other words the column, that is represented by the control on the main
form, gets updated for all rows that were returned in the query bound
to the main form.
Click to expand...
It sounds like your code is doing the update of ALL rows, not by the Access
Form's processing. I am yet to observe this behaviour from Access Forms.



I know that a form, usually, is bound (one-to-one) with a table/query.
I also know that you can hand-code a lot of operations using SQL
within a form as well. What I don't understand is how Access' "auto
save" type functionality can update all the rows in the main form's
query without me being able to see some SQL statements inside the
form. I assume that this is not Access doing this automagically and
there is some hand-coding going on somewhere in the form. I simply
cannot locate it.
Click to expand...
I guess the automatic save process (and other Form processes) are built in
as "features" and therefore the internal processing (SQL statements in your
terms) are hidden from us. If an airplane pilot wants to increase the lift
at the same throttle, he adjust some control to move the flaps down but he
doesn't need to know the electrical signals, etc... that sends his
mechanical adjustments of the control to the motor to move the flaps down.



Are there any hooks or procedures I should be looking for in the
code/database that would help me figure out where these rows are being
updated?
Click to expand...
Different Events of the Form or the Controls on the Form



I keep expecting to see some code in the main form like an
_OnSave method that would contain the SQL I'm expecting to find.
Could it be a macro in a table somewhere that would act as a trigger
that when a certain column is udpated, it would updated other columns
based upon some logic?
Click to expand...
Likely to be in your code ...Do you have any code executing an Update Query
/ SQL String in the Form's Module? Any code that traverse an update a
Recordset in the Form's Module? Any code calling a Sub outside the Form's
Module and the called Sub has one of the above 2 actions?
 
Thanks for your response Steve. I've looked through all the code for
the form and I don't see any SQL that is performing the type of update
I'm describing. Maybe there is a saved query that is being used in
the form. As far as the control (which is bound to a date field in
the main form query) being updated, this happens on change events for
other controls. This makes sense since this control holds a
"modified" date for information displayed by the form. So, when a
user modifies anything on the form, this control gets updated by
getting assigned to the current date/time.

I guess I'm really looking for some event in the code that would be
like an OnSave event so I could find the query or SQL (or macro) that
is updating the database. I looked at the events for the form and
through the code itself, and it wasn't apparent that this was
happening, but I'm sure I'm simply missing something. All that
usually happens when something on the form is changed is an assignment
of the current date/time to the control then the event function exits.

I know that Access does a lot of this under the covers when you've got
a bound form, but I would only expect the current 'row' to be updated
not multiple rows. So, that makes me think there is a query/SQL/macro
to achieve this and this is happening as the result of some event. Is
this assumption correct?

Are there events that happen with form that don't show up in the code,
but that may be on a property sheet, etc.? Maybe a function/macro is
being executed there. I know the database gets updated when I move to
the next record, so is there an event for this?

Thanks,

Tim
 
Tim,

When I said "What is the code by which that control is updated?", what I
meant was... can you please tell us what the code is?
 
Steve,

I apologize, but I am clearly misunderstanding your question. Are you
asking me what is the code that is updating the control? If so, this
(text) control (bound to a date column) is updated via an assigment
when other various events take place from other controls on the form.
You'll find the simple statement "txtModified = Now" at the end of
these event functions.

In some of these events, there is SQL code to update various tables,
etc. as I would expect. There is also a deliberate attempt to keep
the Access "framework" from auto-updating in some of these events
because there is code explicitly setting Dirty to False. I'm scouring
the code looking for the update type SQL or update queries, but thus
far I can't find it.

Thanks,

Tim
 
Van,

Thank you for your response. See comments in-line.

Van T. Dinh said:
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



If the Query is the RecordSource of the Form and there is nothing in the
Form's Filter Property, the number of rows returned by the Query matches the
RecordCount in the NavigationBar. Check the NavigationBar and see if there
is a phrased "(filtered)" after the RecordCount.
Click to expand...

The query is the RecordSource of the form (at least in the form's
properties), and there is nothing in the form's filter property. Even
so, there is nothing like "(filtered)" near the NavigationBar at least
during run-time viewing the form. Should I be looking somewhere in
code for this? The number of rows returned by the query bound to the
form does not match the number of rows shown in the navigation bar.
It sounds like your code is doing the update of ALL rows, not by the Access
Form's processing. I am yet to observe this behaviour from Access Forms.
Click to expand...

The form IS somehow updating ALL the rows in the query associated with
the form, but not the query (which I cannot locate) that is returning
the rows counted by the navigation bar.
I guess the automatic save process (and other Form processes) are built in
as "features" and therefore the internal processing (SQL statements in your
terms) are hidden from us. If an airplane pilot wants to increase the lift
at the same throttle, he adjust some control to move the flaps down but he
doesn't need to know the electrical signals, etc... that sends his
mechanical adjustments of the control to the motor to move the flaps down.
Click to expand...

I understand what you mean here, and it's a good point. I don't
really want to know the details for academic purposes though. I'm
trying to understand the details because it appears that is where I
will find how these 'mysterious' updates are happening.
Different Events of the Form or the Controls on the Form




Likely to be in your code ...Do you have any code executing an Update Query
/ SQL String in the Form's Module? Any code that traverse an update a
Recordset in the Form's Module? Any code calling a Sub outside the Form's
Module and the called Sub has one of the above 2 actions?
Click to expand...

I don't see this type of code. That's what is confusing me. I
suspect there IS a query/macro/sub being called like this somewhere in
this code, I just can't find it. I suppose I just need to find the
right event where this code/query exists. I can usually find this
code in the other forms, but in this one it eludes me for some reason.
 
OK. I can't see your database and I never observed this behaviour done
automatically by Access without code.

If the database is small enough, Compact & zip it and post to a Web site so
that we can download and I ( + possibly Steve) will have a look in your
database.
 
Back
Top