Solved

Access Front End with SQL Back End, Commit record changes

Posted on 2013-05-29
4
1,049 Views
Last Modified: 2013-06-05
I have linked my SQL tables to the Access Front End using DSN-less. Now I am having an issue with getting SQL to commit record changes. One would think that when you move from one record to the next the change would be committed. And, that might be the case. However, I have a computed field in the SQL table (price*quantity) I created a text box that points to that field in the form. If I change the either the price or the quantity, the computed field does not change. I can move to other records. I can change other records. The field value does not change in any of the records. However, if I close the form and open it, all changes are accepted, recorded and the computed field is correct. Anyone have an idea why this is happening and what I can do to correct the error. I believe I could use the "dirty" function to write the change. However, there are some "before update" events that would get fired by this and I would have to modify those.
0
Comment
Question by:rodneygray
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39206434
When you say that the computed field does not change, do you mean that you do not see it change in your Access form, or that you can access the database using another tool (eg SSMS) and see that it has not yet changed?

If the former then you should refresh your data and redisplay it on the form. If the latter then you are missing something in your code in your Access form that will post the data explicitly to the database.

hth

Mike
0
 
LVL 1

Author Comment

by:rodneygray
ID: 39208830
Mike,
The fields in the table are being updated. Updates are not showing up on form.

I have a computed field in the SQL table that multiplies quantity * price. This is a persisted field. If I change quantity or price, the change is written to the table. However, it does not show up in the form.

To make this happen, I added a "me.refresh" command on the after update event for the fields quantity and price. This did resolve the computed field issue. However, it lead to another issue as I was using a "beforeupdate" event to error other fields. I was able to code around this issue by getting creative.

Now there is another issue. I am totaling the computed field "totalprice" on a subform. Main form is the invoice number, customer, etc. Refresh would not update the total for the computed field. Next, I added a "refreshall" button (using wizard which created an embedded macro) to the subform. This did not update the total. Then, I added a "refreshall" button (wizard) to the main form. This did update the totals.

Maybe I am going about this the wrong way. However, things that worked without a hitch on Access database are now a little screwy with SQL as the backend.

Any information will be greatly appreciated.
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 39209681
Try designing the back-end of the form around the dataflow. What I mean is that your form open should be like this:

open form hidden
run query to get data
populate table on form
make form visible

When your user changes the data then he or she will do some action to show that the change is complete. For example, maybe they are altering three numbers on a row. After each number you know entry of that number is complete because the move off the field. However, you know that row-entry is complete when they go to another row, click on another form, close the form, clock on a button on the form, ....
When this happens:

run update query to send data to database
run select query to retrieve updated data from database
populate table on form

This will ensure that you get updates to things like fields calculated in the database, and also any alterations that other people may have made.

BTW, the action you should take when the user starts a new row is t insert a new record (blank) in the database and show that on the form. All your tables should have primary keys, which you don't (normally) have to show to the user.

I'm sorry I can't offer code, but it's a number of years since I did this sort of programming. However, I did it in several different systems and the event sequence is exactly the same.

hth

Mike
0
 
LVL 1

Author Closing Comment

by:rodneygray
ID: 39224061
Thanks for the assistance you offered. I resolved the issue by performing a me.refesh after each field modified in the afterupdate event. This certainly and continues to be a learning process. When adding a record, I have the user enter record key and click the add button. Then I add the record to the database (no other fields populated). Then, I open the form in edit mode. If the user wants to keep the record, they must populate required fields and save record. If they do not want to keep the record, there is a delete button.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now