Solved

Access Front End with SQL Back End, Commit record changes

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

896 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

14 Experts available now in Live!

Get 1:1 Help Now