how to run update query on current record only

[msnews.microsoft.com]

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??

 

[Marshall Barton]

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??

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 & """"

 

[msnews.microsoft.com]

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

strSQL = strSQL & "WHERE PKfield = " & Me.PKfield

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]

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]

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

strSQL = strSQL & "WHERE PKfield = " & Me.PKfield

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" 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 & """"

 

[msnews.microsoft.com]

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.

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]

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

strSQL = strSQL & "WHERE PKfield = " & Me.PKfield

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" wrote

:

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??


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 & """"

 

[Marshall Barton]

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]

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" wrote

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]

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
strSQL = strSQL & "WHERE PKfield = " & Me.PKfield
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" wrote
:

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??


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 & """"

 

[msnews.microsoft.com]

HI,Sorry about the lag in reply, I got that working 2 days ago, and have
been refocased on another project. I understand most of what you said in
your last reply, but have no time right now to work on this any further
right now.
There is still alot I need to do to this DB, and hope to get back to it next
week
for now it serves their purpose. Thanks you again very much!!!

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]

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" wrote

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:
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
strSQL = strSQL & "WHERE PKfield = " & Me.PKfield
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" wrote
:

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??


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 & """"

 

[msnews.microsoft.com]

Hello, I hope that you are still watching this subject .
Can you elaborate on how to do the Dlookup on select qry.....
The >>
IIf(Len([category_ID])<2,'0'+CStr([category_ID]),CStr([category_ID]))
is designed to test the value in the numeric field and add zeros to the
beginning if needed and convert the data to a string so that it can be
combined with the other fields, which is a text field and auto increment
field.

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]

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" wrote

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:
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
strSQL = strSQL & "WHERE PKfield = " & Me.PKfield
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" wrote
:

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??


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 & """"

 

[Marshall Barton]

I think(?) this query will do the calculation that you want
to use:

SELECT tblLocation.Location_ID &
tbl_Department.Department_ID &
Format(tblCategory.Category_ID, "00") &
Format(tblInventory.InventoryCounter, "0000000")
As EquipmentID
FROM 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

(You are joining some tables that don't appear to be
involved in the calculation. If those tables really are not
required, you should get rid of them to speed up the query.)

Now, you have a choice how you apply the criteria to the
query. You can add the WHERE clause we have been
discussing:
WHERE inventorycounter = Forms!nameofform.inventorycounter
to the query and just look up the calculated value in a text
box's control source expression:
=DLookup("EquipmentID", "nameofquery")

Or, you can leave the WHERE clause out of the query and
apply the criteria in the DLookup:

=DLookup("EquipmentID", "nameofquery", "inventorycounter="
& inventorycounter)

Personally, I prefer the latter because it makes the query
independent of the form. BUT, it might(?) have poorer
performance. If there is a significant performance penalty,
then use the former approach.
--
Marsh
MVP [MS Access]

Can you elaborate on how to do the Dlookup on select qry.....
The >>
IIf(Len([category_ID])<2,'0'+CStr([category_ID]),CStr([category_ID]))
is designed to test the value in the numeric field and add zeros to the
beginning if needed and convert the data to a string so that it can be
combined with the other fields, which is a text field and auto increment
field.

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")

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" wrote
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


"msnews.microsoft.com" wrote:
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
strSQL = strSQL & "WHERE PKfield = " & Me.PKfield
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 [snip]

"Marshall Barton" wrote
:

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??


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 & """"