EOF error!!! please help (1 Viewer)

P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
i'm using MS access 2007
i get en error at :
....
Set Temp1_Set = Db.CreateTableDef("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])
If Not Temp1_Set.EOF Then
....

Error sayd:
Compile error:
Method or data member not found
 
DCrake

DCrake

Remembered
Local time
Today, 06:59
Joined
Jun 8, 2005
Messages
8,632
Have you defined your Temp_Set as

Dim Temp_Set as DAO.Recordset
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 07:59
Joined
Nov 3, 2010
Messages
6,142
CreateTableDef?
What are you trying to do here?
 
DCrake

DCrake

Remembered
Local time
Today, 06:59
Joined
Jun 8, 2005
Messages
8,632
Code: 
Set Temp1_Set = Db.[B]CreateTableDef[/B]("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])

Well Spotted

Should be

Code: 
Set Temp1_Set = [B]CurrentDb.OpenRecordset[/B]("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
first of all, Thanks a lot Guys for looking into this

@David: the code is old and it used to give me an error at Recordset .. and after changing it to DAO.Recordset it passes that error .. and stops at EOF!!

@Spikepl: this code runs at form activate action, and what it should do is open some tables and check for new sale orders ... then list copy them into another table
 
DCrake

DCrake

Remembered
Local time
Today, 06:59
Joined
Jun 8, 2005
Messages
8,632
What is your full code and what error is is raising?
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
DCrake said:
What is your full code and what error is is raising?
Click to expand...
Option Compare Database 'Use database order for string comparisons
'Dim Db As Database
Dim New_SO_Set As DAO.Recordset
Dim New_SO_PT_Set As DAO.Recordset
Dim New_Status_Set As DAO.Recordset
----------------------------------------------------------
Private Sub Form_Load()
Dim D As Long
Dim int_qty As Integer
Dim Temp1_Set As DAO.Dynaset
Dim Temp2_Set As DAO.Dynaset
Dim Temp3_Set As DAO.Dynaset
Dim SS As String
Dim PI As String
On Error GoTo Import_Sale_Error
Dim qryInputSalesOrder As QueryDef
Set qryInputSalesOrder = Db.QueryDefs("New_SO_Delete")
qryInputSalesOrder.Execute
Set qryInputSalesOrder = Db.QueryDefs("New_SOP_Delete")
qryInputSalesOrder.Execute
Set qryInputSalesOrder = Db.QueryDefs("Status_Delete")
qryInputSalesOrder.Execute
qryInputSalesOrder.Close
Me.Refresh
DoCmd.Hourglass True
Set New_SO_Set = Db.OpenRecordset("New_SO", DB_OPEN_DYNASET)
Set New_All_PT_Set = Db.OpenRecordset("New_SOP", DB_OPEN_DYNASET)
'Set New_S_PT_Set = Db.OpenRecordset("dbo_v_sfcs_all", DB_OPEN_DYNASET)
Set Split_Order_Set = Db.OpenRecordset("dbo_cws_split_order", DB_OPEN_DYNASET)
Set New_Status_Set = Db.OpenRecordset("Sale_Order_Master_Table", DB_OPEN_DYNASET)
'Set SO_rec = Db.OpenRecordset("Import_SO")
'Set WO_rec = Db.OpenRecordset("Import_WO")
'Set New_S_Set = Db.OpenRecordset("Import_S")
Set Old_SO_unshipped_Set = Db.OpenRecordset("SO Master", DB_OPEN_DYNASET)
Set Old_SO_shipped_Set = Db.OpenRecordset("SO_Master_Shipped", DB_OPEN_DYNASET)
' Mesge parts data dbo_v_sfcs_sop & dbo_sfcs_wop to new_sop
Set New_SO_PT_Set = Db.OpenRecordset("dbo_v_sfcs_sop", DB_OPEN_DYNASET)
Do Until New_SO_PT_Set.EOF
New_All_PT_Set.AddNew
New_All_PT_Set("order_no") = Format(New_SO_PT_Set![Order_No], "00000000")
New_All_PT_Set("part_no") = New_SO_PT_Set![Part_No]
New_All_PT_Set("order_qty") = New_SO_PT_Set![Order_Qty]
New_All_PT_Set("order_type") = New_SO_PT_Set![Order_Type]
New_All_PT_Set("order_line_no") = New_SO_PT_Set![order_line_no]
New_All_PT_Set("kit_line_no") = New_SO_PT_Set![kit_line_no]
New_All_PT_Set("prod_type") = New_SO_PT_Set![prod_type]
New_All_PT_Set.Update
New_SO_PT_Set.MoveNext
Loop
New_SO_PT_Set.Close
Set New_WO_PT_Set = Db.OpenRecordset("dbo_v_sfcs_wop", DB_OPEN_DYNASET)
Do Until New_WO_PT_Set.EOF
New_All_PT_Set.AddNew
New_All_PT_Set("order_no") = Format(New_WO_PT_Set![Order_No], "00000000")
New_All_PT_Set("part_no") = New_WO_PT_Set![Part_No]
New_All_PT_Set("order_qty") = New_WO_PT_Set![Order_Qty]
New_All_PT_Set("order_type") = New_WO_PT_Set![Order_Type]
New_All_PT_Set("order_line_no") = New_WO_PT_Set![order_line_no]
New_All_PT_Set("kit_line_no") = New_WO_PT_Set![kit_line_no]
New_All_PT_Set("prod_type") = New_WO_PT_Set![prod_type]
New_All_PT_Set.Update
New_WO_PT_Set.MoveNext
Loop
New_WO_PT_Set.Close
New_All_PT_Set.MoveFirst
' Merge SO dbo_v_sfcs_so to New_SO
Set SO_rec = Db.OpenRecordset("Import_SO")
If SO_rec.EOF Then
SO_rec.Close
MsgBox "No SO record imported"
Else
SS = ""
PI = ""
Temp_SO = SO_rec![Order_No]
Temp_Loc_No = SO_rec![From_Loc_No]
Temp_order_no = SO_rec![Order_No]
Temp_Order_Type = SO_rec![Order_Type]
Temp_Part_No = SO_rec![Part_No]
Temp_first_name = SO_rec![first_name]
Temp_last_name = SO_rec![last_name]
Temp_order_qty = SO_rec![Order_Qty]
Temp_entry_datetime = SO_rec![entry_datetime]
Temp_pick_date = SO_rec![pick_date]
Temp_Ship_Method = SO_rec![Ship_Method]
Temp_Expected_date = SO_rec![Expected_date]
Temp_ext_ref = SO_rec![ext_ref]
Temp_cust_name = SO_rec![cust_name]
Temp_po_date = SO_rec![PO_Date]
Temp_Cust_SO = SO_rec![Cust_SO]
On Error Resume Next
If Mid(SO_rec![Comment], 1, 3) = "CRD" Or Mid(SO_rec![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(SO_rec![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
Do Until SO_rec.EOF
If SO_rec![Order_No] = Temp_SO Then
Select Case SO_rec![comment_type]
Case "SI"
SS = SS & " " & SO_rec![Comment]
Case "PI"
PI = PI & " " & SO_rec![Comment]
End Select
Else
Temp_SO = SO_rec![Order_No]
New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
SS = ""
PI = ""
Temp_SO = SO_rec![Order_No]
Temp_order_no = SO_rec![Order_No]
Temp_Loc_No = SO_rec![From_Loc_No]
Temp_Order_Type = SO_rec![Order_Type]
Temp_Part_No = SO_rec![Part_No]
Temp_first_name = SO_rec![first_name]
Temp_last_name = SO_rec![last_name]
Temp_order_qty = SO_rec![Order_Qty]
Temp_entry_datetime = SO_rec![entry_datetime]
Temp_pick_date = SO_rec![pick_date]
Temp_Ship_Method = SO_rec![Ship_Method]
Temp_Expected_date = SO_rec![Expected_date]
Temp_ext_ref = SO_rec![ext_ref]
Select Case SO_rec![comment_type]
Case "SI"
SS = SS & " " & SO_rec![Comment]
Case "PI"
PI = PI & " " & SO_rec![Comment]
End Select
Temp_cust_name = SO_rec![cust_name]
Temp_po_date = SO_rec![PO_Date]
Temp_Cust_SO = SO_rec![Cust_SO]
On Error Resume Next
If Mid(SO_rec![Comment], 1, 3) = "CRD" Or Mid(SO_rec![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(SO_rec![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
End If
SO_rec.MoveNext
Loop
SO_rec.Close

New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
End If
' Mesge WO dbo_v_sfcs_wo to New_SO
Set WO_rec = Db.OpenRecordset("Import_WO")
If WO_rec.EOF Then
WO_rec.Close
MsgBox "No WO record imported"
Else
SS = ""
PI = ""
Temp_SO = WO_rec![Order_No]
Temp_order_no = WO_rec![Order_No]
Temp_Loc_No = WO_rec![From_Loc_No]
Temp_Order_Type = WO_rec![Order_Type]
Temp_Part_No = WO_rec![Part_No]
Temp_first_name = WO_rec![first_name]
Temp_last_name = WO_rec![last_name]
Temp_order_qty = WO_rec![Order_Qty]
Temp_entry_datetime = WO_rec![entry_datetime]
Temp_pick_date = WO_rec![pick_date]
Temp_Ship_Method = WO_rec![Ship_Method]
Temp_Expected_date = WO_rec![Expected_date]
Temp_ext_ref = WO_rec![ext_ref]
Temp_cust_name = WO_rec![cust_name]
Temp_po_date = WO_rec![PO_Date]
Temp_Cust_SO = WO_rec![Cust_SO]
On Error Resume Next
If Mid(WO_rec![Comment], 1, 3) = "CRD" Or Mid(WO_rec![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(WO_rec![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
Do Until WO_rec.EOF
If WO_rec![Order_No] = Temp_SO Then
Select Case WO_rec![comment_type]
Case "SI"
SS = SS & " " & WO_rec![Comment]
Case "PI"
PI = PI & " " & WO_rec![Comment]
End Select
Else
Temp_SO = WO_rec![Order_No]
New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
SS = ""
PI = ""
Temp_SO = WO_rec![Order_No]
Temp_order_no = WO_rec![Order_No]
Temp_Loc_No = WO_rec![From_Loc_No]
Temp_Order_Type = WO_rec![Order_Type]
Temp_Part_No = WO_rec![Part_No]
Temp_first_name = WO_rec![first_name]
Temp_last_name = WO_rec![last_name]
Temp_order_qty = WO_rec![Order_Qty]
Temp_entry_datetime = WO_rec![entry_datetime]
Temp_pick_date = WO_rec![pick_date]
Temp_Ship_Method = WO_rec![Ship_Method]
Temp_Expected_date = WO_rec![Expected_date]
Temp_ext_ref = WO_rec![ext_ref]
Select Case WO_rec![comment_type]
Case "SI"
SS = SS & " " & WO_rec![Comment]
Case "PI"
PI = PI & " " & WO_rec![Comment]
End Select
Temp_cust_name = WO_rec![cust_name]
Temp_po_date = WO_rec![PO_Date]
Temp_Cust_SO = WO_rec![Cust_SO]
On Error Resume Next
If Mid(WO_rec![Comment], 1, 3) = "CRD" Or Mid(WO_rec![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(WO_rec![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
End If
WO_rec.MoveNext
Loop
WO_rec.Close
New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
End If
' Mesge reconfiguration orders dbo_v_sfcs_s & dbo_v_sfcs_all to New_SO
Set New_S_Set = Db.OpenRecordset("Import_S")
Do Until New_S_Set.EOF
Set Temp1_Set = Db.CreateDynaset("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])
If Not Temp1_Set.EOF Then
SS = ""
PI = ""
Temp_SO = Temp1_Set![Order_No]
Temp_Loc_No = Temp1_Set![From_Loc_No]
Temp_order_no = Temp1_Set![Order_No]
Temp_Order_Type = Temp1_Set![Order_Type]
Temp_Part_No = Temp1_Set![Part_No]
Temp_first_name = Temp1_Set![first_name]
Temp_last_name = Temp1_Set![last_name]
Temp_order_qty = Temp1_Set![Order_Qty]
Temp_entry_datetime = Temp1_Set![entry_datetime]
Temp_pick_date = Temp1_Set![pick_date]
Temp_Ship_Method = Temp1_Set![Ship_Method]
Temp_Expected_date = Temp1_Set![Expected_date]
Temp_ext_ref = Temp1_Set![ext_ref]
Temp_cust_name = Temp1_Set![cust_name]
Temp_po_date = Temp1_Set![PO_Date]
Temp_Cust_SO = Temp1_Set![Cust_SO]
On Error Resume Next
If Mid(Temp1_Set![Comment], 1, 3) = "CRD" Or Mid(Temp1_Set![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(Temp1_Set![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
Do Until Temp1_Set.EOF
If Temp1_Set![Order_No] = Temp_SO Then
Select Case Temp1_Set![comment_type]
Case "SI"
SS = SS & " " & Temp1_Set![Comment]
Case "PI"
PI = PI & " " & Temp1_Set![Comment]
End Select
Else
Temp_SO = Temp1_Set![Order_No]
New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
SS = ""
PI = ""
Temp_SO = Temp1_Set![Order_No]
Temp_order_no = Temp1_Set![Order_No]
Temp_Loc_No = Temp1_Set![From_Loc_No]
Temp_Order_Type = Temp1_Set![Order_Type]
Temp_Part_No = Temp1_Set![Part_No]
Temp_first_name = Temp1_Set![first_name]
Temp_last_name = Temp1_Set![last_name]
Temp_order_qty = Temp1_Set![Order_Qty]
Temp_entry_datetime = Temp1_Set![entry_datetime]
Temp_pick_date = Temp1_Set![pick_date]
Temp_Ship_Method = Temp1_Set![Ship_Method]
Temp_Expected_date = Temp1_Set![Expected_date]
Temp_ext_ref = Temp1_Set![ext_ref]
Select Case Temp1_Set![comment_type]
Case "SI"
SS = SS & " " & Temp1_Set![Comment]
Case "PI"
PI = PI & " " & Temp1_Set![Comment]
End Select
Temp_cust_name = Temp1_Set![cust_name]
Temp_po_date = Temp1_Set![PO_Date]
Temp_Cust_SO = Temp1_Set![Cust_SO]
On Error Resume Next
If Mid(Temp1_Set![Comment], 1, 3) = "CRD" Or Mid(Temp1_Set![Comment], 1, 3) = "RSD" Then
temp_crd = CDate(Mid(Temp1_Set![Comment], 7, 11))
If Err.Number <> 0 Then
temp_crd = ""
End If
Else
temp_crd = ""
End If
End If
Temp1_Set.MoveNext
Loop
New_SO_Set.AddNew
New_SO_Set("Order No") = Format(Temp_order_no, "00000000")
New_SO_Set("From_Loc_No") = Temp_Loc_No
New_SO_Set("Part Number") = Temp_Part_No
New_SO_Set("Customer") = Temp_cust_name
New_SO_Set("First Number") = Temp_first_name
New_SO_Set("Last Name") = Temp_last_name
New_SO_Set("Qty") = Temp_order_qty
New_SO_Set("Entry Date") = Temp_entry_datetime
New_SO_Set("Picked Date") = Temp_pick_date
New_SO_Set("comments_SI") = SS
New_SO_Set("comments_PI") = PI
New_SO_Set("ship_method") = Temp_Ship_Method
New_SO_Set("order_type") = Temp_Order_Type
New_SO_Set("expected_date") = Temp_Expected_date
New_SO_Set("crd") = temp_crd
New_SO_Set("ext_ref") = Temp_ext_ref
New_SO_Set("cust_so") = Temp_Cust_SO
New_SO_Set("po_date") = Temp_po_date
New_SO_Set.Update
End If
New_S_Set.MoveNext
Loop
New_S_Set.Close
' create New_Status_Set ("Sale_Order_Master_Table")
New_SO_Set.MoveFirst
Do Until New_SO_Set.EOF
C = New_SO_Set![Order No]

Old_SO_unshipped_Set.MoveFirst
Old_SO_unshipped_Set.FindFirst "[Sales_Order] LIKE " & "'" & C & "*'"

If Not Old_SO_unshipped_Set.NoMatch Then
D = Old_SO_unshipped_Set![SO_ID]
Split_Order_Set.FindFirst "[Order_No] =" & C
If Not Split_Order_Set.NoMatch Then
GoTo Cc
Else
GoTo Update_Routine
End If
End If

Old_SO_shipped_Set.MoveFirst
Old_SO_shipped_Set.FindFirst "[Sales_Order] LIKE " & "'" & C & "*'"
If Not Old_SO_shipped_Set.NoMatch Then
D = Old_SO_shipped_Set![SO_ID]
GoTo Warning_Routine
End If

Split_Order_Set.FindFirst "[Order_No] =" & C
If Not Split_Order_Set.NoMatch Then
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "SPLIT"
New_Status_Set.Update
GoTo Cc
End If
''''''''''' Status NEW
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "NEW"
New_Status_Set.Update
GoTo Cc
''''''''''' Status UPDATE
Update_Routine:
Tot = Old_SO_unshipped_Set![Qty]
Old_SO_unshipped_Set.FindNext "[Sales_Order] LIKE " & "'" & C & "*'"
Do While Not Old_SO_unshipped_Set.NoMatch
Tot = Tot + Old_SO_unshipped_Set![Qty]
Old_SO_unshipped_Set.FindNext "[Sales_Order] LIKE " & "'" & C & "*'"
Loop

If (New_SO_Set![Qty] < Tot) Or (Format(New_SO_Set![Picked Date], "m/d/yy") <> Format(Old_SO_unshipped_Set![Picked], "m/d/yy")) Then
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "UPDATE"
New_Status_Set.Update
GoTo Cc
End If
Set Temp3_Set = Db.CreateDynaset("SELECT * FROM [New_SOP] WHERE [order_no]='" & C & "' order by [part_no]")
Set Temp2_Set = Db.CreateDynaset("SELECT * FROM [SO_Parts_List] WHERE [SO_ID]=" & D & " order by [parts]")

If Not Temp3_Set.EOF Then
Temp3_Set.MoveLast
End If
c1 = Temp3_Set.RecordCount

If Not Temp2_Set.EOF Then
Temp2_Set.MoveLast
End If
c2 = Temp2_Set.RecordCount

If c1 <> c2 Then
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "UPDATE"
New_Status_Set.Update
GoTo Cc
End If

If c1 <> 0 Then
Temp3_Set.MoveFirst
End If
If c2 <> 0 Then
Temp2_Set.MoveFirst
End If

Do Until Temp3_Set.EOF
int_qty = Temp3_Set![Order_Qty] / New_SO_Set![Qty]
If (Temp3_Set![Part_No] <> Temp2_Set![Parts]) Or (int_qty <> Temp2_Set![Qty]) Or (New_SO_Set![Ship_Method] <> Old_SO_unshipped_Set![Ship_Method]) Then
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "UPDATE"
New_Status_Set.Update
GoTo Cc
End If
Temp2_Set.MoveNext
Temp3_Set.MoveNext
Loop
GoTo Cc
''''''''''' Status WARNING
Warning_Routine:
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = C
New_Status_Set("Model_No") = New_SO_Set![Part Number]
New_Status_Set("QTY") = New_SO_Set![Qty]
New_Status_Set("Status") = "WARNING"
New_Status_Set.Update
GoTo Cc
Cc: New_SO_Set.MoveNext
Loop
''''''''''' Status DELETE
Old_SO_unshipped_Set.MoveFirst
Do Until Old_SO_unshipped_Set.EOF
If Old_SO_unshipped_Set![Finished] = "" Or IsNull(Old_SO_unshipped_Set![Finished]) Then
New_SO_Set.MoveFirst
If Len(Old_SO_unshipped_Set![Sales_Order]) = 8 Then
VV = Old_SO_unshipped_Set![Sales_Order]
New_SO_Set.FindFirst "[Order No]= " & "'" & VV & "'" 'Old_SO_unshipped_Set![Sales_Order]
If Not New_SO_Set.NoMatch Then
GoTo DD
Else
New_Status_Set.AddNew
New_Status_Set("Sale_Order_No") = Old_SO_unshipped_Set![Sales_Order]
New_Status_Set("Model_No") = Old_SO_unshipped_Set![Model]
New_Status_Set("QTY") = Old_SO_unshipped_Set![Qty]
New_Status_Set("Status") = "DELETE"
New_Status_Set.Update
End If
End If
End If
DD: Old_SO_unshipped_Set.MoveNext
Loop
Finish:
Me.Refresh
DoCmd.Hourglass False
Exit Sub
Import_Sale_Error:
Me.Refresh
MsgBox "Error " & Err & " (" & Error & ") has occurred."
Resume Finish
'DoCmd.Hourglass False
End Sub
 
DCrake

DCrake

Remembered
Local time
Today, 06:59
Joined
Jun 8, 2005
Messages
8,632
You have still got your Db commented out and should be

Dim Db As DAO.Database
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
now i get an error at DAO.Dynaset!!!!
says: Compile error: User-defined type not found
here are the variable i have
Dim D As Long
Dim int_qty As Integer
Dim Temp1_Set As DAO.Dynaset
Dim Temp2_Set As DAO.Dynaset
Dim Temp3_Set As DAO.Dynaset
Dim SS As String
Dim PI As String

NOT DAO.Recordset as i have in the beginning --- sorry for the confusion
 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
And for heaven's sake - if you post code (ESPECIALLY, long code) USE CODE TAGS so it is readable!

 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
Pelerin13 said:
now i get an error at DAO.Dynaset!!!!
says: Compile error: User-defined type not found
here are the variable i have
Dim D As Long
Dim int_qty As Integer
Dim Temp1_Set As DAO.Dynaset
Dim Temp2_Set As DAO.Dynaset
Dim Temp3_Set As DAO.Dynaset
Dim SS As String
Dim PI As String

NOT DAO.Recordset as i have in the beginning --- sorry for the confusion
Click to expand...

Where are you trying to use this? Are you trying in a program that is NOT Microsoft Access? If not, you do not use DAO.Dynaset. It doesn't exist in VBA.
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
thanks BOB ... sorry i'm new to this forum ... will do next time ;)
 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
Pelerin13 said:
it is MS access 2007.
Click to expand...

A few initial things I've noted:

1. Your declarations at the top of the module really should use

Code: 
[B]Private[/B] New_SO_Set As DAO.Recordset
[B]Private[/B] New_SO_PT_Set As DAO.Recordset
[B]Private[/B] New_Status_Set As DAO.Recordset
instead of DIM. Dim may work but outside of a procedure you should use either Private or Public, depending on the scope you want.

2. There is no such thing as DAO.Dynaset in Access VBA. You open a recordset as a dynaset but you don't have a dynaset object (like you do in VB.NET, C#.Net)

3. When opening a DAO recordset, the Argument for Dynaset is:
dbOpenDynaset, not DB_OPEN_DYNASET

4. Instead of using:
Code: 
                SS = ""
                PI = ""

You should use
Code: 
                SS = vbNullString
                PI = vbNullString

5. Be careful about using:

On Error Resume Next

as it can obscure several problems. And if you must, set it back to following an error handler as soon as possible. In fact, I see multiple uses of On Error Resume Next where you don't need to do it because it is already set that way.

6. Try to avoid all of the GoTo xxx code. If you find yourself needing to branch like that, create a new procedure for that part and call it when necessary.
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
thanks BOB,
The declarations part I have it at the top (General declarations) not within the load procedure.
I will make the changes and let you know
Thanks guys
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
error @ .CreateDynaset
Code: 
Set Temp1_Set = Db.CreateDynaset("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])

Compile error:
Function or interface marked as restricted, or the function uses an automation type not supported in visual Basic
 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
Pelerin13 said:
error @ .CreateDynaset
Code: 
Set Temp1_Set = Db.CreateDynaset("SELECT * FROM [Import_ALL] WHERE [order_no]=" & New_S_Set![Order_No])

Compile error:
Function or interface marked as restricted, or the function uses an automation type not supported in visual Basic
Click to expand...

Of course you are going to get that error. I already said, there is no such code in Access VBA. There are no DYNASET objects to be created. There is no CreateDynaset. You use Recordset objects NOT DYNASET OBJECTS. Get it? There is no such thing as a DAO dynaset object in Access VBA.
 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
Pelerin13 said:
thanks BOB,
The declarations part I have it at the top (General declarations) not within the load procedure.
Click to expand...

Yes, that is fine. But you don't use DIM you use either PRIVATE or Public, but not DIM when it is not inside a procedure.
 
P

Pelerin13

Registered User.
Local time
Yesterday, 22:59
Joined
Mar 23, 2011
Messages
56
but the code is working fine (with Dynaset) under access97!!!
 
boblarson

boblarson

Smeghead
Local time
Yesterday, 22:59
Joined
Jan 12, 2001
Messages
32,059
Oh my! :eek: Well, I have to say that is something I have not seen. I guess you can still use it then. Perhaps I can get someone who has still used Access 97 recently to chime in on this thread. That is a 14 year old version of Access and, while it does seem to be a pretty good workhorse, you are not going to be able to run it forever unless you can maintain equipment to run a compatible Operating System on it. It is getting close to where it will not run on a future operating system and so you would not be able to replace any equipment that uses it with any new equipment.

Just an FYI for you.
 
You must log in or register to reply here.

Similar threads

P
Solved Access vba Manage an Attachment field with vba
Replies
8
Views
435
Pat Hartman
P
J
VBA to Display References, Access 2000 through 2019
Replies
12
Views
470
Josef P.
J
I
Solved Error -2146232576 in SHA256: Automation error in ms access
Replies
19
Views
1,226
isladogs
isladogs
EL_Gringo
Solved Set frm = Forms(FormName): Error 438
Replies
11
Views
946
EL_Gringo
EL_Gringo
C
Help managing open connections / "cannot open any more databases" error
Replies
6
Views
689
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom