Updating a field on a form
John F
Sorry this may seem like a wimp of a question, but I'm new to using Access
The project I am developing has two main screen, the first holds company
information and at the bottom of the form has a tab box with myriads of
company information, plus a tab box containing all of the employees for that
organisation. When you double click one of these names, a second form is
shown with the contact person layout - with me so far?
On this second form I have another tab box with several areas of
information - one holds a sub form that has details of their transactions.
My client wants to quickly and simply identify the total sales by client and
by company and therefore, rather than asking him to write queries with lots
of tables, I felt it would probably be easier to de-normalise the data and
store the total of sales on both the contact person and organisation table.
My problem comes in trying to get these fields to update consistently.
In the subform that holds the transactions at customer level, I have a
computed field (I think that's what you would call them, Sorry I only know
PowerBuilder terminology) which is at the bottom of the transactions sub
form which is ' =sum(price_paid)'
This field however does not seem to update quickly when I change data in one
of the fields (for instance if I overwrite the discount rate or the price
paid). I've looked at adding Me!Repaint to the beforeUpdate code but this
does not seem to be doing the job.
How can I make it update consistently.
Secondly, probably because I know no better way, I have a hidden field at
the top of the contact person form which is bound to the transaction value
column on the contact person table.
To update this, I have the code Parent!TransactionValue = Me!sum_trans in
several of the events in the subform - unfortunately it always seems to be
one update behind and I can't get it to carry this out effectively - is
there a better way of doing this.
Finally, before I go off and try and buy myself a good book, assuming that
my organisation form is still open (It will always be when the contact
person form is open because it shuts contact person when it closes), I
presume I can use the same method to update a field in this form - after
running a query to grab all of the transactions of all contact persons at
that organisation - but in what event should I run the code and is it
possible to code in some thing along the lines of
Form!Organisation!transaction_value = DoCmdSQL("SELECT paid_price from
sales_transactions a, contacts b where a.person_id = b.person_id and
b.org_id = " & Me!Person_id)
Regards
John
David Jessee
For the part about the person wanting to see the sales informatiln by
employee and company, the simplest, and coicidentally most impressive, way to
do this would be to create a botton, ent (double-click) or something that
pulled up a "sales history" form that was based on a cross-tab of the data.
Then I saw that you were considering de-normalizind your data, I felt a
little faint :) this would provide a way around that. On a side note, it
always helps to create a number of queries for no other use than for
reporting. In the past, I've created "report Queries" that are not even
usedby my application. That way, once its compiled the user can use some
reporting software (I recommend Crystal) to write reports on the database.
It's also easier to send a crystal file to them that they just have to open,
as opposed to importing a report into the application, then providing the
user some methos of accessing that report.
I know that there was another question you asked, but I'm on a freind's WebTV
and it truncated the initial message so I'm not sure what the other part was.
Happy developing,
David Jessee
Sent via Deja.com http://www.deja.com/
Before you buy.