Need Help With Access, Code Is Inconsistent?

I dont get any errors with this code. Let me explain what im doing:
T1 = temparary table (not really temparary but the values just change so often it might as well be)
this table has field names: partname, order, maxorder, etc. stuff like that
pretty much its a table that has records for each part name.

T2 = main database
this table holds the order for all parts for a particular time and day

T1 is the one that people are familiar with, where as T2 's set up is something that they would not be, so for clarity we use T1 for displaying etc.

now my problem is, i try to transfer data from one table to another but it works sometimes and doesnt work others, i dont get any errors and i have option explicit on as well. if someone could look at this code and tell me if there are any problems that you see, the quicker the response the better.

technical specs:
Access 2000
Windows 2000

Code:
Private Sub insertRecMainDB(ByVal typeOfOrder As String)
'This sub will insert a new record of type O (Ordered) and put in all values from the table Order
'Also it will make a duplicate record of type R (Received) and put in same values so when later
'if there are no changes (meaning all product was received) no entry has to occur, however if you
'are missing a part then you can enter how many on the other form.

Dim yesOrNo As String
Dim tempSQL As String
Dim rec As New ADODB.Recordset
Dim orderAmountRec As New ADODB.Recordset
Dim partNameRec As New ADODB.Recordset
Dim numOfRecords As Integer
Dim cnt As Integer

'Checks the type of order and either checks or does not check it in the DB
If typeOfOrder = "O" Then
yesOrNo = "yes"
Else
yesOrNo = "no"
End If

'Create the SQL statements to update MainDB
tempSQL = "INSERT INTO [MainDB] ([OrderNumber],[DateTime],[OrderedReceived],[TypeOfOrder]) VALUES (" + CStr(txtOrderNumber.Value) + ",'" + CStr(lblDateTime.Caption) + "'," + yesOrNo + ",'" + typeOfOrder + "')"
CurrentDb.Execute (tempSQL)

'Counts the number of parts we have to order, saves it in numOfRecords
rec.Open ("SELECT* FROM [Order]"), conn, adOpenStatic, adLockReadOnly
numOfRecords = rec.RecordCount
rec.Close
'Loop to transfer data from the table Order to the table MainDB
For cnt = 1 To numOfRecords

'Gets the name of each part
tempSQL = "SELECT [Description] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";"
partNameRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly

'Gets the amount ordered of that part
tempSQL = "SELECT [MaterialOrdered] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";"
orderAmountRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly

'Updates that part name with the ordered amount
tempSQL = "UPDATE [MainDB] SET [" + CStr(partNameRec.Fields(0)) + "]=" + CStr(orderAmountRec.Fields(0)) + " WHERE [OrderNumber]=" + CStr(txtOrderNumber.Value) + " AND [DateTime]='" + CStr(lblDateTime.Caption) + "' AND [TypeOfOrder]='" + typeOfOrder + "';"
CurrentDb.Execute (tempSQL)

partNameRec.Close
orderAmountRec.Close
Next
End Sub