Recordset errors

[BillyH1892]

Hi,

Can you please help as this is driving me crazy. :banghead:

At work we have a parts master database, its very crude and poorly designed. We have recently added a new table that uses a six character / digit primary key to return a calculation value. The six character primary key consists of a category code (3 characters), a life cycle code, a PMC value and a fluctuation code, all 1 character for each. The parts master table lists these four columns, what i want to do is run a query to first return the matching part number, category, LCC, PMC and fluctuation code, put these values into a Recordset, iterate through the Recordset and concatenate the category, LCC, PMC and fluctuation codes as one value.
I want to then put the part number and the newly created category code into a temporary table for further processing

Working from home I made mock up tables, wrote the code and it works fine. The problem is that when i created the module on the database at work it throws an error. It is exactly the same code using the same tables but the error occurs every-time. I step through the code and the error always occurs after the line
Set rst = db.OpenRecordset(sqlString, dbOpenDynaset) highlighted in red
Why does this happen? The database is old and has a .mdb extension but i have simulated this at home and on my machine the code works fine.

Could someone please help?

Sub ConcatenateValuesAndPlaceInTempTable()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim partNumber As String
Dim category As String
Dim lifeCycleCode As String
Dim pmc As String
Dim fluctuation As String
Dim sqlString As String
Dim concatenatedString As String

On Error GoTo ErrorHandler:

sqlString = "SELECT P.PART_NO, P.CATEGORY, P.LIFE_CYCLE_CODE, P.PMC, P.FLUCTUATION" & _
"FROM TBL_PART_UTILITY U INNER JOIN dbo_NEP_PARTS_MASTER P ON P.PART_NO = U.PART_NO"

Set db = CurrentDb
Set rst = db.OpenRecordset(sqlString, dbOpenDynaset)

Do While Not rst.EOF
partNumber = rst.Fields(0)
category = rst.Fields(1)
lifeCycleCode = rst.Fields(2)
pmc = rst.Fields(3)
fluctuation = rst.Fields(4)

concatenatedString = category & lifeCycleCode & pmc & fluctuation

Debug.Print partNumber & " " & concatenatedString

db.Execute "INSERT INTO tbl_DECISION_TEMP (PART_NO, CAT_VALUE) VALUES ('" & partNumber & "','" & concatenatedString & "')"

rst.MoveNext
Loop

rst.Close
db.Close

Exit Sub

ErrorHandler:
MsgBox "An error occurred when concatenating values"
Resume Next

End Sub

 

[MarkK]

We have recently added a new table that uses a six character / digit primary key to return a calculation value. The six character primary key consists of a category code (3 characters), a life cycle code, a PMC value and a fluctuation code, all 1 character for each. The parts master table lists these four columns, what i want to do is run a query to first return the matching part number, category, LCC, PMC and fluctuation code, put these values into a Recordset, iterate through the Recordset and concatenate the category, LCC, PMC and fluctuation codes as one value.

IMO, this is a very bad idea. This is like trying to build a roof first and then building a house underneath it, cart before horse, tail wagging dog, and so on, and so on.

I recommend you search this site, or use Google for "disadvantages of compound keys" or "meaningless vs meaningful key," before you bash your head too much longer.

Check out the Autonumber that Access provides for a primary key. Only create these fancy human-readable codes at retrieval time. Never save one in a table.

Hope this saves you hours and hours and hours,

 

[WayneRyan]

Billy,

You need a space before the FROM.

Wayne