ASP.NET, SQL Server, WPF, C#, VB.NET

I have an update query that combines the data of 4 fields from the current 
 
table into one when the form is closed. Right now it runs an update on every 
 
record in the table.  What is the criteria I can use to update only the 
 
current record displayed in the form??

"msnews.microsoft.com" <breichenbach @ istate DOT com> 
 
wrote: 
 
You need to use the record's primary key field and the value 
 
of that field in the current record. If the PK field is a 
 
numeric type field: 
 
strSQL = strSQL & "WHERE PKfield = " & Me.PKfield 
 
If the PK field is a Text field: 
 
strSQL = strSQL & "WHERE PKfield = """ & Me.PKfield & """" 
 
-- 
 
Marsh 
 
MVP [MS Access]

Thank you very much for your help...  But I'm not too bright.. 
 
I thought this was something to be added to my query, but that didn't work. 
 
PK is numeric, (autoincrement). 
 
Should this be added to the VBA? 
 
I'm so sorry, I'm sure you thought this would be a simple answer for you.. 
 
I don't understand all of your solution 
 
I thought you were telling me to use my SQL string plus a where clause on 
 
the PKfield and the Me.PKfield. 
 
Me. if I remember right is the active whatever...  I'll keep trying... 
 
This is what I added to the end of my query> 
 
WHERE [inventorycounter] = Me.inventorycounter; 
 
and this is the VBA that runs on form close.   This form is the edit 
 
record form opened from the display form(which it is link to on the 
 
PK field, and only one record can be editied at at time. 
 
Private Sub Save_Click() 
 
Dim stDocName As String 
 
On Error GoTo Err_Save_Click 
 
stDocName = "qry_Equip_ID_Update" 
 
DoCmd.OpenQuery stDocName, , acEdit 
 
DoCmd.close 
 
Exit_Save_Click: 
 
Exit Sub 
 
Err_Save_Click: 
 
MsgBox Err.description 
 
Resume Exit_Save_Click 
 
End Sub 
 
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
 
news:3b1oa2hbr3cmvs81keui73m5l2j7qel1i1@4ax.com...

Somehow, I had the impression that you were constructing the 
 
SQL statement in VBA. 
 
If you are using a saved query, then I have to question why 
 
you are updating a record to what seems to be a calculated 
 
value.  Generally, this is a bad idea and a violation of the 
 
rules of Database Normalization.  In other words, it's sort 
 
of a spreadsheet way of thinking that can lead you down a 
 
perilous path to a train wreck.  Please post a Copy/Paste of 
 
your query's SQL statement so I can see what's going on 
 
along with an explanation of why you think you need to do 
 
this. 
 
To answer your specific question, Me is a form module's way 
 
of referring to itself.  It is not valid in a query.  The 
 
WHERE clause would be more like: 
 
WHERE inventorycounter = Forms!nameofform.inventorycounter 
 
-- 
 
Marsh 
 
MVP [MS Access] 
 
"msnews.microsoft.com"  wrote:

There is no real explanation, other then my lack of thorough knowledge.. 
 
Here is my qry 
 
UPDATE tblLocation RIGHT JOIN ((tblCategory RIGHT JOIN (tbl_Department RIGHT 
 
JOIN (tblInventory LEFT JOIN tbl_Function ON tblInventory.Function_ID = 
 
tbl_Function.Function_ID) ON tbl_Department.Dept_ID = 
 
tblInventory.Department_ID) ON tblCategory.category = tblInventory.Category) 
 
LEFT JOIN tblLeaseInformation ON tblInventory.InventoryCounter = 
 
tblLeaseInformation.[inventory counter]) ON tblLocation.Location = 
 
tblInventory.Branch SET tblInventory.Equipment_ID = 
 
[Location_ID]+[Department_ID]+IIf(Len([category_ID])<2,'0'+CStr([category_ID]),CStr([category_ID]))+IIf(Len([InventoryCounter])<6,'00'+CStr([inventorycounter]),CStr([inventorycounter])); 
 
While I am aware of some of the rules of database normalization I am not 
 
familiar with them all. 
 
As for the field, it is used to consolidate 4 other fields for record 
 
keeping and label printing.  The request was made to create a database that 
 
would print labels with a bar code that contained this ID type field which 
 
would provide 4 key elements to the items location, purpose, department, and 
 
Database ID.  There is so much more to be done, but I have it almost 
 
useable.  It is split into front end back end files and runs from the WS's 
 
where it is copied down to if the network version is newer.  The backend 
 
file may eventually be converted to a SQL. 
 
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
 
news:087oa2log2rjl74b5fism8c980f7ajb0jt@4ax.com...

Well, one of the rules is to never store values that can be 
 
calculated from other fields.  It's possible for them to get 
 
out of sync with the individual values and it's usually 
 
faster to recalculate a value than save and load it along 
 
with the longer records needed to keep the additional field. 
 
Rather than updating records with this value, you could do a 
 
DLookup on a SELECT query that calculates this combined 
 
information in a report or form text box.  If you need the 
 
calculated value for a lot of records, you could join this 
 
to whatever other data you are retrieving. 
 
I don't fully understand the expression you are using to 
 
calculate the combined value.  You are using +, but it 
 
appears that you are concatenating the individual items. 
 
While + will do that for text strings, it also propagates 
 
Null.  Maybe that's what you want, but if not, use & 
 
instead. 
 
I can't be sure what you are doing here, but the part with 
 
IIf(Len([category_ID])<2,'0'+CStr([category_ID]),CStr([category_ID])) 
 
looks like it could be simplified to 
 
Format(category_ID, "00") 
 
and the other IIf to 
 
Format(InventoryCounter, "0000000") 
 
-- 
 
Marsh 
 
MVP [MS Access] 
 
"msnews.microsoft.com"  wrote: