M
mattc66 via AccessMonster.com
It think the issue is with the below criteria. The issue happens with the
PO_NO has a letter with the number. If it's just a number it works fine. Any
suggestion.
stLinkCriteria = "[PO_NO]=" & "'" & SID & "'"
Private Sub LookUpPO_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
'Added to Update Vendor Data
Dim rs3 As Recordset
Dim rs4 As Recordset
'Dim rs5 As Recordset
Dim varReturn As Variant
Me.Filter = ""
If Me.Dirty Then
Me.Dirty = False
End If
Set rsc = Me.RecordsetClone
Set db = CurrentDb
SID = Me.LookUpPO.Value
stLinkCriteria = "[PO_NO]=" & "'" & SID & "'"
Set rs1 = db.OpenRecordset("select * from PODET_C1 where PO_NO = '" +
CStr(SID) & "';")
Set rs2 = db.OpenRecordset("select * from tblPurchaseOrderDetail_C1 where
PO_NO = '" + CStr(SID) & "';")
Set rs3 = db.OpenRecordset("ICSTOCK_C1")
Set rs4 = db.OpenRecordset("qryPurchaseOrderVendorItemStp2_C1")
Set rs5 = Forms![frmPurchaseOrder_C1]![sfrmPurchaseOrderDetail_C1].Form.
RecordsetClone
'Check table for for item number.
If Me.LookUpPO.ListIndex = -1 Then
'Data is not in the list
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Me.LookUpPO.SetFocus
Else
'Message box warning that part number doesn't exsist.
MsgBox "PO Number" _
& SID & " is not valid PO number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the PO is still not found" _
& vbCr & vbCr & "I am not sure!", vbExclamation _
, "PO NOT FOUND"
Me.LookUpPO.SetFocus
Exit Sub
End If
End If
If Me.LookUpPO.Value = True Then
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) = 0 Then
DoCmd.GoToRecord , , acNewRec
'This is suposed to move curser to the subform.
Me.PO_NO = Me.LookUpPO.Column(0)
Me.PO_STAT = Me.LookUpPO.Column(1)
Me.VEN_NO = Me.LookUpPO.Column(2)
Me.SHIP_VIA = Me.LookUpPO.Column(3)
Me.BUYER = Me.LookUpPO.Column(4)
Me.INV_NO = Me.LookUpPO.Column(5)
Me.ORDER_DATE = Me.LookUpPO.Column(6)
Me.PO_TAX_FLG = Me.LookUpPO.Column(7)
Me.CONF_COMMENT = Me.LookUpPO.Column(8)
Me.FOB = Me.LookUpPO.Column(9)
Me.NO_DET_LINES = Me.LookUpPO.Column(10)
Me.PO_SHIP_ADD_ID = Me.LookUpPO.Column(11)
Me.Refresh
rs1.MoveFirst
While Not rs1.EOF
With rs2
rs2.AddNew
rs2.Fields("PO_NO") = SID
rs2.Fields("PO_LINE_NO") = rs1.Fields("PO_LINE_NO")
rs2.Fields("DET_STAT") = Nz(rs1.Fields("DET_STAT"), "") ' helps
to deal with nulls in bundle table
rs2.Fields("ITEM") = Nz(rs1.Fields("ITEM"), "") ' it says if
there's a null, use this
rs2.Fields("DESC") = Nz(rs1.Fields("DESC"), "") '<--in this case,
a blank
rs2.Fields("EXP_COST") = Nz(rs1.Fields("EXP_COST"), 0)
rs2.Fields("LANDED_CST") = Nz(rs1.Fields("EXP_COST"), 0)
rs2.Fields("ACT_COST") = Nz(rs1.Fields("ACT_COST"), 0)
rs2.Fields("QTY_ORD") = Nz(rs1.Fields("QTY_ORD"), 0)
rs2.Fields("QTY_RCVD") = Nz(rs1.Fields("QTY_RCVD"), "0")
rs2.Fields("UOM") = Nz(rs1.Fields("UOM"), "")
rs2.Fields("CONV_FACTOR") = Nz(rs1.Fields("CONV_FACTOR"), "0")
rs2.Fields("ORG_DATE_EXP") = Nz(rs1.Fields("ORG_DATE_EXP"), "")
rs2.Fields("DATE_SHIP") = Nz(rs1.Fields("ORG_DATE_EXP") + -5, "")
rs2.Update
End With
rs1.MoveNext
Wend
'Added to update Long Description and Vendor Part#
If rs5.RecordCount > 0 And rs3.RecordCount > 0 Then
rs5.MoveFirst
Do While Not rs5.EOF
rs3.FindFirst "Item = """ & rs5!Item & """"
If Not rs3.NoMatch Then
rs5.Edit
rs5![DESC] = Nz(rs3![DESCRIPTION], 0)
rs5![V_ITEM] = Nz(rs4![V_ITEM], "")
'rs![AvgCost] = Nz(rs1![AVG_COST], 0)
'rs![AR_CODE] = Nz(rs1![AR_CODE], "")
'rs![AR_Matrix] = Me.AR_Matrix
'rs![WHSE] = Me.WHSE
'rs![Company] = Me.Company
rs5.Update
End If
rs5.MoveNext
Loop
End If
rs3.Close
If rs5.RecordCount > 0 And rs4.RecordCount > 0 Then
rs5.MoveFirst
Do While Not rs5.EOF
rs4.FindFirst "Item = """ & rs5!Item & """"
If Not rs4.NoMatch Then
rs5.Edit
rs5![V_ITEM] = Nz(rs4![V_ITEM], "")
'rs![AvgCost] = Nz(rs1![AVG_COST], 0)
'rs![AR_CODE] = Nz(rs1![AR_CODE], "")
'rs![AR_Matrix] = Me.AR_Matrix
'rs![WHSE] = Me.WHSE
'rs![Company] = Me.Company
rs5.Update
End If
rs5.MoveNext
Loop
End If
rs5.Close
Else
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Me.LookUpPO.SetFocus
End If
End If
End If
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set rs4 = Nothing
Set db = Nothing
Set rsc = Nothing
Me.Refresh
Me.LookUpPO = Null
End Sub
PO_NO has a letter with the number. If it's just a number it works fine. Any
suggestion.
stLinkCriteria = "[PO_NO]=" & "'" & SID & "'"
Private Sub LookUpPO_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
'Added to Update Vendor Data
Dim rs3 As Recordset
Dim rs4 As Recordset
'Dim rs5 As Recordset
Dim varReturn As Variant
Me.Filter = ""
If Me.Dirty Then
Me.Dirty = False
End If
Set rsc = Me.RecordsetClone
Set db = CurrentDb
SID = Me.LookUpPO.Value
stLinkCriteria = "[PO_NO]=" & "'" & SID & "'"
Set rs1 = db.OpenRecordset("select * from PODET_C1 where PO_NO = '" +
CStr(SID) & "';")
Set rs2 = db.OpenRecordset("select * from tblPurchaseOrderDetail_C1 where
PO_NO = '" + CStr(SID) & "';")
Set rs3 = db.OpenRecordset("ICSTOCK_C1")
Set rs4 = db.OpenRecordset("qryPurchaseOrderVendorItemStp2_C1")
Set rs5 = Forms![frmPurchaseOrder_C1]![sfrmPurchaseOrderDetail_C1].Form.
RecordsetClone
'Check table for for item number.
If Me.LookUpPO.ListIndex = -1 Then
'Data is not in the list
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Me.LookUpPO.SetFocus
Else
'Message box warning that part number doesn't exsist.
MsgBox "PO Number" _
& SID & " is not valid PO number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the PO is still not found" _
& vbCr & vbCr & "I am not sure!", vbExclamation _
, "PO NOT FOUND"
Me.LookUpPO.SetFocus
Exit Sub
End If
End If
If Me.LookUpPO.Value = True Then
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) = 0 Then
DoCmd.GoToRecord , , acNewRec
'This is suposed to move curser to the subform.
Me.PO_NO = Me.LookUpPO.Column(0)
Me.PO_STAT = Me.LookUpPO.Column(1)
Me.VEN_NO = Me.LookUpPO.Column(2)
Me.SHIP_VIA = Me.LookUpPO.Column(3)
Me.BUYER = Me.LookUpPO.Column(4)
Me.INV_NO = Me.LookUpPO.Column(5)
Me.ORDER_DATE = Me.LookUpPO.Column(6)
Me.PO_TAX_FLG = Me.LookUpPO.Column(7)
Me.CONF_COMMENT = Me.LookUpPO.Column(8)
Me.FOB = Me.LookUpPO.Column(9)
Me.NO_DET_LINES = Me.LookUpPO.Column(10)
Me.PO_SHIP_ADD_ID = Me.LookUpPO.Column(11)
Me.Refresh
rs1.MoveFirst
While Not rs1.EOF
With rs2
rs2.AddNew
rs2.Fields("PO_NO") = SID
rs2.Fields("PO_LINE_NO") = rs1.Fields("PO_LINE_NO")
rs2.Fields("DET_STAT") = Nz(rs1.Fields("DET_STAT"), "") ' helps
to deal with nulls in bundle table
rs2.Fields("ITEM") = Nz(rs1.Fields("ITEM"), "") ' it says if
there's a null, use this
rs2.Fields("DESC") = Nz(rs1.Fields("DESC"), "") '<--in this case,
a blank
rs2.Fields("EXP_COST") = Nz(rs1.Fields("EXP_COST"), 0)
rs2.Fields("LANDED_CST") = Nz(rs1.Fields("EXP_COST"), 0)
rs2.Fields("ACT_COST") = Nz(rs1.Fields("ACT_COST"), 0)
rs2.Fields("QTY_ORD") = Nz(rs1.Fields("QTY_ORD"), 0)
rs2.Fields("QTY_RCVD") = Nz(rs1.Fields("QTY_RCVD"), "0")
rs2.Fields("UOM") = Nz(rs1.Fields("UOM"), "")
rs2.Fields("CONV_FACTOR") = Nz(rs1.Fields("CONV_FACTOR"), "0")
rs2.Fields("ORG_DATE_EXP") = Nz(rs1.Fields("ORG_DATE_EXP"), "")
rs2.Fields("DATE_SHIP") = Nz(rs1.Fields("ORG_DATE_EXP") + -5, "")
rs2.Update
End With
rs1.MoveNext
Wend
'Added to update Long Description and Vendor Part#
If rs5.RecordCount > 0 And rs3.RecordCount > 0 Then
rs5.MoveFirst
Do While Not rs5.EOF
rs3.FindFirst "Item = """ & rs5!Item & """"
If Not rs3.NoMatch Then
rs5.Edit
rs5![DESC] = Nz(rs3![DESCRIPTION], 0)
rs5![V_ITEM] = Nz(rs4![V_ITEM], "")
'rs![AvgCost] = Nz(rs1![AVG_COST], 0)
'rs![AR_CODE] = Nz(rs1![AR_CODE], "")
'rs![AR_Matrix] = Me.AR_Matrix
'rs![WHSE] = Me.WHSE
'rs![Company] = Me.Company
rs5.Update
End If
rs5.MoveNext
Loop
End If
rs3.Close
If rs5.RecordCount > 0 And rs4.RecordCount > 0 Then
rs5.MoveFirst
Do While Not rs5.EOF
rs4.FindFirst "Item = """ & rs5!Item & """"
If Not rs4.NoMatch Then
rs5.Edit
rs5![V_ITEM] = Nz(rs4![V_ITEM], "")
'rs![AvgCost] = Nz(rs1![AVG_COST], 0)
'rs![AR_CODE] = Nz(rs1![AR_CODE], "")
'rs![AR_Matrix] = Me.AR_Matrix
'rs![WHSE] = Me.WHSE
'rs![Company] = Me.Company
rs5.Update
End If
rs5.MoveNext
Loop
End If
rs5.Close
Else
If DCount("PO_NO", "tblPurchaseOrder_C1", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Me.LookUpPO.SetFocus
End If
End If
End If
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set rs4 = Nothing
Set db = Nothing
Set rsc = Nothing
Me.Refresh
Me.LookUpPO = Null
End Sub