INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Need help updating a table with additional data

## Need help updating a table with additional data

(OP)
I have some code that creates records in a table
based upon a part number that is manually typed into
a form (newpn). I have a large list of numbers that I need to
be placed into this table by performing the same calculation
on them instead of typing one number at a time into the form.

Below is my code which takes a part number and strips
out all dashes (pn_nodash) and then  that incorporates
a look up table and calculates a type of checksum based
upon the contents of the string of the part number.

Is there an easy way to take the part number from a
table and create the whole list at once instead of
one at a time like it is done in the code below?

thanks

#### CODE

Dim x As Integer
Dim y As Integer
Dim HIBC As String
Dim partnum As String
Dim totals As Integer
Dim slen As Integer
Dim digit As String
Dim HIBCvals(43) As String
Dim remaindr As Integer
Dim pn_nodash As String
Dim varX As Variant

HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
HIBCvals(5) = "5"
HIBCvals(6) = "6"
HIBCvals(7) = "7"
HIBCvals(8) = "8"
HIBCvals(9) = "9"
HIBCvals(10) = "A"
HIBCvals(11) = "B"
HIBCvals(12) = "C"
HIBCvals(13) = "D"
HIBCvals(14) = "E"
HIBCvals(15) = "F"
HIBCvals(16) = "G"
HIBCvals(17) = "H"
HIBCvals(18) = "I"
HIBCvals(19) = "J"
HIBCvals(20) = "K"
HIBCvals(21) = "L"
HIBCvals(22) = "M"
HIBCvals(23) = "N"
HIBCvals(24) = "O"
HIBCvals(25) = "P"
HIBCvals(26) = "Q"
HIBCvals(27) = "R"
HIBCvals(28) = "S"
HIBCvals(29) = "T"
HIBCvals(30) = "U"
HIBCvals(31) = "V"
HIBCvals(32) = "W"
HIBCvals(33) = "X"
HIBCvals(34) = "Y"
HIBCvals(35) = "Z"
HIBCvals(36) = "-"
HIBCvals(37) = "."
HIBCvals(38) = " "
HIBCvals(39) = "$" HIBCvals(40) = "/" HIBCvals(41) = "+" HIBCvals(42) = "%" pn_nodash = Replace(newpn, "-", "") Me.nodash = pn_nodash totals = 78 ' 78 = +m258 slen = Len(pn_nodash) For x = 1 To slen Step 1 digit = Mid(pn_nodash, x, 1) For y = 0 To 42 Step 1 If HIBCvals(y) = digit Then totals = totals + y y = 42 End If Next y Next x remaindr = totals Mod 43 digit = HIBCvals(remaindr) HIBC = "+M258" & pn_nodash & "0" & digit ### RE: Need help updating a table with additional data If all what you do is take newpn and calculate HIBC, why don't you crate a little Function that takes newpn and returns HIBC? #### CODE --> Not Tested Option Explicit Dim x As Integer Dim y As Integer Dim HIBC As String Dim partnum As String Dim totals As Integer Dim slen As Integer Dim digit As String Dim HIBCvals(43) As String Dim remaindr As Integer Dim pn_nodash As String Dim varX As Variant Private Sub CommandButton1_Click() Dim i As Integer For i = 0 To 9 HIBCvals(i) = i Next i For i = 10 To 35 HIBCvals(i) = Chr(i + 55) Next i HIBCvals(36) = "-" HIBCvals(37) = "." HIBCvals(38) = " " HIBCvals(39) = "$"
HIBCvals(40) = "/"
HIBCvals(41) = "+"
HIBCvals(42) = "%"

For i = 1 to rstSomeRecord.RecordCount
MsgBox FigureHIBC(rstSomeRecord.newpn)
rstSomeRecord.MoveNext
Next i

End Sub

Private Function FigureHIBC(strNewPN As String) As String

pn_nodash = Replace(strNewPN, "-", "")

Me.nodash = pn_nodash

totals = 78                     ' 78 = +m258
slen = Len(pn_nodash)
For x = 1 To slen Step 1
digit = Mid(pn_nodash, x, 1)
For y = 0 To 42 Step 1
If HIBCvals(y) = digit Then
totals = totals + y
y = 42
End If
Next y
Next x

remaindr = totals Mod 43
digit = HIBCvals(remaindr)

FigureHIBC = "+M258" & pn_nodash & "0" & digit

End Function
That's just my guess of how the code may look like.  You may want to move some declaration of variables

Have fun.

---- Andy

### RE: Need help updating a table with additional data

(OP)
Thanks Andy but how can I do the whole list at once instead of entering one part number at a time?

I have over 1500 items to enter and would prefer not to do it manually...

### RE: Need help updating a table with additional data

(OP)
I figured it out...

#### CODE

Dim rstProduct As DAO.Recordset
Dim strMsg As String, strTitle As String, statmsg As String
Dim x As Integer
Dim y As Integer
Dim HIBC As String
Dim partnumb As String
Dim totals As Integer
Dim slen As Integer
Dim digit As String
Dim HIBCvals(43) As String
Dim remaindr As Integer

Set dbs = CurrentDb
Set db = CurrentDb

HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
HIBCvals(5) = "5"
HIBCvals(6) = "6"
HIBCvals(7) = "7"
HIBCvals(8) = "8"
HIBCvals(9) = "9"
HIBCvals(10) = "A"
HIBCvals(11) = "B"
HIBCvals(12) = "C"
HIBCvals(13) = "D"
HIBCvals(14) = "E"
HIBCvals(15) = "F"
HIBCvals(16) = "G"
HIBCvals(17) = "H"
HIBCvals(18) = "I"
HIBCvals(19) = "J"
HIBCvals(20) = "K"
HIBCvals(21) = "L"
HIBCvals(22) = "M"
HIBCvals(23) = "N"
HIBCvals(24) = "O"
HIBCvals(25) = "P"
HIBCvals(26) = "Q"
HIBCvals(27) = "R"
HIBCvals(28) = "S"
HIBCvals(29) = "T"
HIBCvals(30) = "U"
HIBCvals(31) = "V"
HIBCvals(32) = "W"
HIBCvals(33) = "X"
HIBCvals(34) = "Y"
HIBCvals(35) = "Z"
HIBCvals(36) = "-"
HIBCvals(37) = "."
HIBCvals(38) = " "
HIBCvals(39) = "$" HIBCvals(40) = "/" HIBCvals(41) = "+" HIBCvals(42) = "%" strSQL = "SELECT [HIBCtbl-new].[Part], [HIBCtbl-new].[CleanPN], [HIBCtbl-new].[HIBC] FROM [HIBCtbl-new];" Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset) If rstProduct.EOF = False Or rstProduct.BOF = False Then rstProduct.MoveFirst ' Get first record in Table Do While rstProduct.EOF = False cntr = 1 partnum = Trim(rstProduct!CleanPN) totals = 78 ' 78 = +m258 slen = Len(partnum) For x = 1 To slen Step 1 digit = Mid(partnum, x, 1) For y = 0 To 42 Step 1 If HIBCvals(y) = digit Then totals = totals + y y = 42 End If Next y Next x remaindr = totals Mod 43 digit = HIBCvals(remaindr) HIBC = "+M258" & partnum & "0" & digit ' Set the rstProduct!HIBC.Value in the table HIBCtbl-new with calculated value getHIBC With rstProduct .Edit !HIBC = HIBC .Update End With rstProduct.MoveNext ' get next record Loop End Sub ### RE: Need help updating a table with additional data Nice. It is pretty much along the lines of my sollution. And I see you did not like my version: For i = 0 To 9 HIBCvals(i) = i Next i For i = 10 To 35 HIBCvals(i) = Chr(i + 55) Next i HIBCvals(36) = "-" HIBCvals(37) = "." HIBCvals(38) = " " HIBCvals(39) = "$"
HIBCvals(40) = "/"
HIBCvals(41) = "+"
HIBCvals(42) = "%"

HIBCvals(0) = "0"
HIBCvals(1) = "1"
HIBCvals(2) = "2"
HIBCvals(3) = "3"
HIBCvals(4) = "4"
...

but that's OK

Have fun.

---- Andy

### RE: Need help updating a table with additional data

(OP)
Ok Andy- Now I see that and yes that is much cleaner...

I'm sorry I  missed it because I couldn't see how it updated the table with the new values...

### RE: Need help updating a table with additional data

What you might want to consider is using Access. It is an SQL environment good at maintaining Access databases. SQL and the relational model were invented so you didn't have to write code like you've been writing.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!