Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Front End with SQL Back End, Commit record changes

Posted on 2013-05-29
4
Medium Priority
?
1,296 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
[X]
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
  • 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

662 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