Do Loop Needed?

  • Thread starter Thread starter Wayne Emminizer
  • Start date Start date
W

Wayne Emminizer

I have 35,000 records with 10 columns of specifications.
For each specification there is an abbreviation. For
example Fillet is Flt. I have written the code to grab
those abbreviations and concatenate them into one
description field (NewItemName). This works fine. My
problem is that it is pulling the abbreviations for the
current record in the form and updating all of
the "NewItemName" fields to this same description. How
do I make it check the specs for each record and update
that records NewItemName value to reflect the specs of
that record only. The code is below:

DoCmd.RunSQL "UPDATE tblItemMaster SET NewItemName='" &
sqlString & "'" & "WHERE [ManagementGrp]='01';"

The concatenated name is called "sqlString". For testing
purposes I am working on this in a group at a time
(ManagementGrp).

Any ideas would be greatly appreciated....thanks
..
 
What I do is create a new table that is a copy of the first, but with an AutoNumber (LongInteger) field called SortKey
Run an AppendQuery to put your data into the new table. Then rename your tables appropriately

My code usually look like this

' declare variable
Dim lngMin As Lon
Dim lngMax As Lon
Dim Counter As Lon
Dim strNIN As String
' NIN for NewItemNam
Dim strSQL As Strin

lngMin = DMin("[SortKey]","tblMyTable"
lngMax = DMax("[SortKey]","tblMyTable"

lngCounter = lngMi
For lngCounter = lngMin To lngMa
' ENTER ANY DATA VERIFICATIONS HER
' (I use DLookUps, where SortKey=lngCounter, for this
' THEN, IF APPROPRIAT
' ENTER ANY DATA UPDATES HER
' Use strNIN to build the new item name the
' Use strSQL to build the string for updating the new item name where SortKey=lngCounte
' Execute strSQL using the RunSQL comman
Nex

It will probably take a few minutes to run (even with only 35000 records) but you should be able to accomplish what you want

Hope this helps

Howard Brod


----- Wayne Emminizer wrote: ----

I have 35,000 records with 10 columns of specifications.
For each specification there is an abbreviation. For
example Fillet is Flt. I have written the code to grab
those abbreviations and concatenate them into one
description field (NewItemName). This works fine. My
problem is that it is pulling the abbreviations for the
current record in the form and updating all of
the "NewItemName" fields to this same description. How
do I make it check the specs for each record and update
that records NewItemName value to reflect the specs of
that record only. The code is below

DoCmd.RunSQL "UPDATE tblItemMaster SET NewItemName='" &
sqlString & "'" & "WHERE [ManagementGrp]='01';

The concatenated name is called "sqlString". For testing
purposes I am working on this in a group at a time
(ManagementGrp).

Any ideas would be greatly appreciated....thank
 
Thanks for the suggestions....I can't do it that way
because the there are multiple tables involved and there
are relationships and on top of that it's a replicated
database....but....your idea led me down a similar path.
I have the code to build the new name built as a function
ready to apply to a record. I should be able to use that
For Next logic to apply that function to each record but
first I need to be able to identify individual records on
a form. At the bottom the record indicators tell me
which record of how many I am on and I can get a count of
the records using the DCOUNT formula in vba....how can I
identify record 5 for example of the 500 filtered records
without identifying the actual item code which in the
case is not unique?
-----Original Message-----
What I do is create a new table that is a copy of the
Click to expand...
first, but with an AutoNumber (LongInteger) field called
SortKey.
Run an AppendQuery to put your data into the new table.
Click to expand...
Then rename your tables appropriately.
My code usually look like this:

' declare variables
Dim lngMin As Long
Dim lngMax As Long
Dim Counter As Long
Dim strNIN As String
' NIN for NewItemName
Dim strSQL As String

lngMin = DMin("[SortKey]","tblMyTable")
lngMax = DMax("[SortKey]","tblMyTable")

lngCounter = lngMin
For lngCounter = lngMin To lngMax
' ENTER ANY DATA VERIFICATIONS HERE
' (I use DLookUps, where SortKey=lngCounter, for this)
' THEN, IF APPROPRIATE
' ENTER ANY DATA UPDATES HERE
' Use strNIN to build the new item name then
' Use strSQL to build the string for updating the new
Click to expand...
item name where SortKey=lngCounter
' Execute strSQL using the RunSQL command
Next

It will probably take a few minutes to run (even with
Click to expand...
only 35000 records) but you should be able to accomplish
what you want.
Hope this helps!

Howard Brody



----- Wayne Emminizer wrote: -----

I have 35,000 records with 10 columns of specifications.
For each specification there is an abbreviation. For
example Fillet is Flt. I have written the code to grab
those abbreviations and concatenate them into one
description field (NewItemName). This works fine. My
problem is that it is pulling the abbreviations for the
current record in the form and updating all of
the "NewItemName" fields to this same description. How
do I make it check the specs for each record and update
that records NewItemName value to reflect the specs of
that record only. The code is below:

DoCmd.RunSQL "UPDATE tblItemMaster SET NewItemName='" &
sqlString & "'" & "WHERE [ManagementGrp]='01';"

The concatenated name is called "sqlString". For testing
purposes I am working on this in a group at a time
(ManagementGrp).

Any ideas would be greatly appreciated....thanks
..



.
Click to expand...
 
Back
Top