stop subform refresh!

[tech.rawsteak]

i have a form that takes in orders by means of a subform datasheet.
the order subform takes in an item name, unit qty, cost, and total
cost which is unit qty * cost. the subform and the order form are
linked via the order number.

the problem is when you enter an item for the first time in the order
subform and then go to add another item. the item disappears from the
orderform, but only visually. the item still appears on the "items
ordered" table with the order numbers intact, and any subsequent
additions to the order are added to the table as well, but except for
the first order that is "deleted," all the orders appear on the order
form.

so then my question becomes, how do i get the subform to not refresh?
if the data is being saved, is there a way to requery the datasheet
subform and bring back all the rows with the same order number and
then continue to accept new item orders?

 

[Allen Browne]

That's not the default behavior. If you have a subform in datasheet or
continuous view, and it is tall enough to display multiple records, the
first record should not disappear when you enter the 2nd record.

So, there must be something triggering this behavior. Do you have any code
in the events of the subform or its controls? Try copying all the code out
to Notepad, save the subform, and see if it still happens. If not, you can
start pasting half the code back at a time, until you identify the culprit.

 

[tech.rawsteak]

That's not the default behavior. If you have a subform in datasheet or
continuous view, and it is tall enough to display multiple records, the
first record should not disappear when you enter the 2nd record.

So, there must be something triggering this behavior. Do you have any code
in the events of the subform or its controls? Try copying all the code out
to Notepad, save the subform, and see if it still happens. If not, you can
start pasting half the code back at a time, until you identify the culprit.

that solved the problem... but now i have to find a way to update my
fields, like if someone changes the initial quantity or price, to
reflect those changes in the total field.... but thanks for letting me
know where the problem originated from!

 

[Allen Browne]

Good: you identified the cause. Now you can put the rest of the code back
in, and try to find an alternative for the problem part.

You have fields Qty, Cost, and Total, and you want the Total to always be
Qty * Cost. There's an incredibly easy way to do that, and it can never go
wrong:
1. Delete the Total field from your table.
2. Create a query.
3. In the query, enter this expression into a blank column in the Field row:
Total: [Qty] * [Cost]
4. Add the other fields to the query output grid too.
5. Save the query.
6. Change the RecordSource property of your form to the query instead of the
table.

You can use this query anywhere you would have used your table. You need no
code to keep this up to date, and you never have to worry about whether it
is right.

One of the basic rules of data normalization is to never store dependent
data (like the Total), but to have the database calculate it for you. It is
a key concept to maintaining the integrity of your data.

 

[tech.rawsteak]

Good: you identified the cause. Now you can put the rest of the code back
in, and try to find an alternative for the problem part.

You have fields Qty, Cost, and Total, and you want the Total to always be
Qty * Cost. There's an incredibly easy way to do that, and it can never go
wrong:
1. Delete the Total field from your table.
2. Create a query.
3. In the query, enter this expression into a blank column in the Field row:
Total: [Qty] * [Cost]
4. Add the other fields to the query output grid too.
5. Save the query.
6. Change the RecordSource property of your form to the query instead of the
table.

You can use this query anywhere you would have used your table. You need no
code to keep this up to date, and you never have to worry about whether it
is right.

One of the basic rules of data normalization is to never store dependent
data (like the Total), but to have the database calculate it for you. It is
a key concept to maintaining the integrity of your data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

that solved the problem... but now i have to find a way to update my
fields, like if someone changes the initial quantity or price, to
reflect those changes in the total field.... but thanks for letting me
know where the problem originated from!

It seems i have a lot to learn about database programming. Thank you
very much for all your help. If I could trouble you for a little
while more, is it faster for MS Access to use code written for each
form that is open, or have a single unified source of code that all
the forms refer to? I've learned that creating a function that many
forms can use will reduce the initial size of the database, but then
in the long run, the size of the database will depend a lot more on
the amount of records stored than the code itself. In contrast, if a
form has to run back and forth to a module of code, won't it slow down
data processing, especially if people are accessing the database off
of a server? I admit I am a little new to databases (ok, I'm an
intern), but I have a fairly good background in programming, so I'm
just not familiar with how MS Access works internally. Thanks again
for all your help!

 

[Allen Browne]

Use the form's own module for code that applies to this form only.
Use a standard module for code that you can reuse across multiple forms.