UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Query, Access 2016    
 
   
catwood
post Jan 22 2019, 04:54 PM
Post#1



Posts: 2
Joined: 26-November 18



I'm a long-time lurker (from about 15 years ago - but I only develop stuff once in a while) and I've learned most of what I know from UtterAccess, many thanks for such a great resource! This is my first post, I can almost always find a solution in the archives here.

But I've been trying to solve a problem for several days (okay, most of two weeks... frown.gif ) that I just can't figure out.

ACCESS 2016
SQL SERVER 2016


Long story short: I have an Access application with a SQL backend. I'm trying to run an update query on a list of parts for a particular assembly. In the parts list, I'm trying to prevent someone from adding the same part twice and I want it to seamlessly update the part count rather than adding multiple instances of that part in the list. When I run the update query, I get not only the updated results but a second set of results inserted as well!

The query runs perfectly within SQL and updates the existing record. It updates only the record referenced and doesn't create a duplicate result. For testing I've made all the values explicit, and emptied the table down to one record:
UNIQUEID ASSEMBLYID PART_NUMBER QTY
761 3129 abc123 1



===========
SQL QUERY:
UPDATE myTable
SET qty = 10
WHERE assemblyID = 3129
AND part_number = 'abc123'


RESULTS:
UNIQUEID ASSEMBLYID PART_NUMBER QTY
761 3129 abc123 10

=============

However, when I run the update query from Access, removing all of the other logic and making the values explicit, the original record updates correctly. But it creates another record too!

====================
ACCESS QUERY:
Private Sub Command593_Click()
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "Update myTable set qty = 10 Where assemblyID = 3129 and part_num = 'abc123';"

With db
Execute strSQL, dbFailOnError

End With
End Sub


RESULTS:
UNIQUEID ASSEMBLYID PART_NUMBER QTY

761 3129 abc123 10
762 3129 abc123 10
====================

Results from Debug.print strSQL:
Update myTable set qty = 10 Where assemblyID = 3129 AND PART_NUM = 'abc123';

=====================

It only runs once. I have the logic for the rest of the problem resolved, but I just can't get around this! It shouldn't be so messy to just update a count, so I must be missing something obvious or I'm not asking the right question when I search through the archives... I'm attempting to remove the duplicates using some other code but there must be a better way.

Any input would be greatly appreciated!

Thank you!
Go to the top of the page
 
catwood
post Jan 22 2019, 06:50 PM
Post#2



Posts: 2
Joined: 26-November 18



** SOLVED **

I think (as usual) that writing everything down helps me make a little more sense of it...

The form I'm inputting the data in must be create a record as soon as the form closes, even though there is no "requery" or "save record" or "go to new record" in the events for the form and all elements of the form.

Because it is "dirty" the data is saved.

I've added
CODE
if me.dirty then
  me.undo
end if


to the beforeUpdate event and the duplication has stopped.


Stupidly simple - once you see it... Now I can get on with putting all my code back together!


As always, thanks to everyone who leaves evidence of their "stupidly simple" moments in the forum and all those who contribute their wisdom to resolve those issues. Sometimes all I have to do is think about how I'd explain a predicament and the types of questions you would ask, and I can sort things out.
Thank you!!
This post has been edited by catwood: Jan 22 2019, 06:59 PM
Go to the top of the page
 
tina t
post Jan 22 2019, 11:14 PM
Post#3



Posts: 5,957
Joined: 11-November 10
From: SoCal, USA


QUOTE
The form I'm inputting the data in must be create a record as soon as the form closes, even though there is no "requery" or "save record" or "go to new record" in the events for the form and all elements of the form.

well, i'll assume that the VBA you posted was simplified, and that you're working with a form that's bound to the table (or a query based on the table), and adding/editing data in controls that are bound to fields in the form's RecordSource. AFAIK, only a bound form can have a record that you "dirty" by adding/editing data.

so it's not really clear what you're doing and why. seems that you're entering data in a new (blank) record in the form, and then using that data to run the Update query. is there a reason you can't just update a record's qty field directly in the form, rather than running an Update query from VBA?

i can think of several options to avoid adding a record via the form, rather than Me.Undo, but what would work best to support your goals depends on what your setup is, and why.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ScottGem
post Jan 24 2019, 08:59 AM
Post#4


UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY


I have to agree with tina. One of the advantages of Access is using Bound forms that handle the I/O between the form and the table. What it sounds like to me is you are using a bound form to retrieve the data, but then the form is creating a record instead of updating the data. Sounds more cumbersome than its worth. Let Access do the heavy lifting for you rather than writing your own code to update the table.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 04:03 PM