Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access Front End with SQL Back End, Commit record changes

Posted on 2013-05-29
4
1,139 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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