Humm....

[opiate]

Is this to long to be a Sub? I'm only half done.... It's just a bunch of data manipulation. I would hate to get this all done and it would not work, I'd jump out the 13th floor window at my office....

Any Suggestions?

'On Error Resume Next 'Vb Runtime Error Object
On Error GoTo Handler
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim rsCon As ADODB.Recordset
Dim objBkm As String

prgBar.Visible = True
Label5.Visible = True
Shape5.Visible = True

Set rs = New ADODB.Recordset
rs.Open "TMP_PD_DATA", ObjData.OraConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

If rs.BOF Then
rs.MoveFirst
Else
rs.MoveFirst
End If

'fields for TMP_PD_DATA
Dim strEXT_AGT_CD
Dim strREP_FST_NAM
Dim strREP_MID_NAM
Dim strREP_LST_NAM
Dim strREP_MLG_ADR_LIN_0
Dim strREP_MLG_ADR_LIN_1
Dim strREP_MLG_ADR_LIN_2
Dim strREP_MLG_CTY
Dim strREP_MLG_STA_CD
Dim strREP_MLG_ZIP_CD
Dim strREP_MLG_ZP4_CD
Dim strREP_BUS_ADR_LIN_0
Dim strREP_BUS_ADR_LIN_1
Dim strREP_BUS_ADR_LIN_2
Dim strREP_BUS_CTY
Dim strREP_BUS_STA_CD
Dim strREP_BUS_ZIP_CD
Dim strREP_BUS_ZP4_CD
Dim strREP_BUS_PHN
Dim strREP_HME_PHN
Dim strREP_FAX_ADR
Dim strREP_EMAIL_ADR
Dim strINS_CTR_DATE
Dim strREP_TYP_CD
Dim strAGC_NAME
Dim strAGC_CD
Dim strSUB_AGC_CD
Dim strPAYEE_CD


Dim strNow 'date for all inserts
Dim strNowTemp 'convert strNow to Oracle PM or AM date format
Dim strOraDFormat 'convertion date format for PM OR AM
Dim sSQLAcc As String 'Account Insert Statement

Dim sSQLAccRef As String 'Account reference id's
Dim sSQLAExpan As String 'Account expantion table
Dim sSQLAExpanRef As String 'Account reference id's for expantion table
Dim sSQLAdds As String 'Shipping Address Insert Statement
Dim sSQLAddp As String 'Primary Address Insert Statement
Dim sSQLSelCon 'Select Contacts where the AGC_CD and SUB_AGC_CD = the account ref table
Dim strPostalCodes As String 'Format the 2 Oracle Shipping Postal codes into one
Dim strPostalCodep As String 'Format the 2 Oracle Primary Postal codes into one
Dim strAccount 'saleslogix account name string
Dim nErrorNum 'error number for error handler loop
Dim strAccountid As String 'Accountid as SalesLogix table id's
Dim strAddressidP As String 'AddressidP as SalesLogix Primary address table id
Dim strAddressidS As String 'AddressidS as SalesLogix Shipping address table id
Dim strShippingid As String 'Shipping as SalesLogix table id's
Dim strUserid As String 'Userid as SalesLogix table id's
Dim i As Long 'Contact For...Next Statment counter
Dim sContacid As String 'Contactid


strNow = Format$(Now, "mm/dd/yyyy hh:nn:ss AM/PM") 'nn may cause an error, will validate error
strNowTemp = Mid(strNow, 21, 23)

If strNowTemp = "PM" Then
strOraDFormat = "MM-DD-YYYY HH12:MI:SS PM"
Else
strOraDFormat = "MM-DD-YYYY HH12:MI:SS AM"
End If

sSQLSelCon = "SELECT * FROM TMP_PD_REF WHERE AGC_CD = '" & strAGC_CD & "' AND SUB_AGC_CD = '" & strSUB_AGC_CD & "'"

Do Until rs.EOF
If rs.Fields("REP_TYP_CD") = "NMO" Or rs.Fields("REP_TYP_CD") = "RMO" Or rs.Fields("REP_TYP_CD") = "EMO" Then

prgBar.Value = (rs.AbsolutePosition / rs.RecordCount) * 100
'strAccountid = slapi_DBCreateIDFor("ACCOUNT")
'strAddressidP = slapi_DBCreateIDFor("ADDRESS")
'strAddressidS = slapi_DBCreateIDFor("ADDRESS")
strShippingid = strAddressid
strUserid = "N82VV0000063"

strREP_FST_NAM = rs.Fields("REP_FST_NAM")
strREP_MID_NAM = rs.Fields("REP_MID_NAM")
strREP_LST_NAM = rs.Fields("REP_LST_NAM")
strREP_BUS_PHN = rs.Fields("REP_BUS_PHN")
strREP_HME_PHN = rs.Fields("REP_HME_PHN")
strREP_FAX_ADR = rs.Fields("REP_FAX_ADR")
strREP_EMAIL_ADR = rs.Fields("REP_EMAIL_ADR")
strREP_TYP_CD = rs.Fields("REP_TYP_CD")

If strREP_FST_NAM = "" And strREP_MID_NAM = "" Then
strAccount = UCase(strREP_MID_NAM)
Else
strAccount = UCase(strREP_FST_NAM & " " & strREP_MID_NAM & " " & strREP_LST_NAM)
End If


sSQLAcc = "INSERT INTO ACCOUNT (ACCOUNTID, SECCODEIED, ACCOUNTMANAGERID, ADDRESSID, SHIPPINGID, ACCOUNT, TYPE, " & _
"MAINPHONE, ALTERNATEPHONE, FAX, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES (" & _
"'" & strAccountid & "', 'F82VV0000042' ,'NN6BP0268763','" & strAddressidP & "', '" & strAddressidS & "'," & _
"'" & strAccount & "', '" & strREP_TYP_CD & "', '" & strREP_BUS_PHN & "', '" & strREP_HME_PHN & "'," & _
"'" & strREP_FAX_ADR & "', '" & strUserid & "', '" & strUserid & "', TO_DATE ('" & strNow & "', '" & strOraDFormat & "'), " & _
"TO_DATE ('" & strNow & "', '" & strOraDFormat & "')"


a.Write "ACCOUNT INSERT"
a.WriteBlankLines 1
a.Write sSQLAcc
a.WriteBlankLines 2

strAGC_CD = rs.Fields("AGC_CD")
strSUB_AGC_CD = rs.Fields("SUB_AGC_CD")
strPAYEE_CD = rs.Fields("PAYEE_CD")
strINS_CTR_DATE = rs.Fields("INS_CTR_DATE")
strEXT_AGT_CD = rs.Fields("EXT_AGT_CD")

sSQLExpan = "INSERT INTO FIXED_ACC_PD (ACCOUNTID, AGC_CD, SUB_AGC_CD, PAYEE_CD, INS_CTR_DATE, EXT_AGT_CD, CREATEUSER, " & _
"MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAccountid & "', '" & strAGC_CD & "', '" & strSUB_AGC_CD & "'," & _
"'" & strPAYEE_CD & "', '" & strEXT_AGT_CD & "', '" & strINS_CTR_DATE & "','" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE ('" & strNow & "', '" & strOraDFormat & "'), TO_DATE ('" & strNow & "', '" & strOraDFormat & "')"



a.Write "ACCOUNT.FIXED_ACC_PD INSERT"
a.WriteBlankLines 1
a.Write sSQLCon
a.WriteBlankLines 2

strREP_MLG_ADR_LIN_0 = rs.Fields("REP_MLG_ADR_LIN_0")
strREP_MLG_ADR_LIN_1 = rs.Fields("REP_MLG_ADR_LIN_1")
strREP_MLG_ADR_LIN_2 = rs.Fields("REP_MLG_ADR_LIN_2")
strREP_MLG_CTY = rs.Fields("REP_MLG_CTY")
strREP_MLG_STA_CD = rs.Fields("REP_MLG_STA_CD")
strREP_MLG_ZIP_CD = rs.Fields("REP_MLG_ZIP_CD")
strREP_MLG_ZP4_CD = rs.Fields("REP_MLG_ZP4_CD")

If strREP_MLG_ZP4_CD = "" Then
strPostalCodes = strREP_MLG_ZIP_CD
Else
strPostalCodes = strREP_MLG_ZIP_CD & "-" & strREP_MLG_ZP4_CD
End If

sSQLAdds = "INSERT INTO ADDRESS (ADDRESSID, ENTITYID, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, POSTALCODE, ISPRIMARY, ISMAILING, " & _
"DESCRIPTION, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAddressidS & "', '" & strAccountid & "'," & _
"'" & strREP_MLG_ADR_LIN_1 & "', '" & strREP_MLG_ADR_LIN_2 & "', '" & strREP_MLG_ADR_LIN_0 & "', '" & strREP_MLG_CTY & "', '" & strREP_MLG_STA_CD & "'," & _
"'" & strPostalCodes & "', 'F', 'T', 'Shipping', '" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE('" & strNow & "', '" & strOraDFormat & "'), TO_DATE('" & strNow & "', '" & strOraDFormat & "')"

a.Write "ACCOUNT.ADDRESS (SHIPPING) INSERT"
a.WriteBlankLines 1
a.Write sSQLAdds
a.WriteBlankLines 2

strREP_BUS_ADR_LIN_0 = rs.Fields("REP_BUS_ADR_LIN_O")
strREP_BUS_ADR_LIN_1 = rs.Fields("REP_BUS_ADR_LIN_1")
strREP_BUS_ADR_LIN_2 = rs.Fields("REP_BUS_ADR_LIN_2")
strREP_BUS_CTY = rs.Fields("REP_BUS_CTY")
strREP_BUS_STA_CD = rs.Fields("REP_BUS_STA_CD")
strREP_BUS_ZIP_CD = rs.Fields("REP_BUS_ZIP_CD")
strREP_BUS_ZP4_CD = rs.Fields("REP_BUS_ZP4_CD")

If strREP_BUS_ZP4_CD = "" Then
strPostalCodep = strREP_BUS_ZIP_CD
Else
strPostalCodep = strREP_BUS_ZIP_CD & "-" & strREP_BUS_ZP4_CD
End If

sSQLAddp = "INSERT INTO ADDRESS (ADDRESSID, ENTITYID, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, POSTALCODE, ISPRIMARY, ISMAILING, " & _
"DESCRIPTION, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAddressidP & "', '" & strAccountid & "'," & _
"'" & strREP_BUS_ADR_LIN_1 & "', '" & strREP_BUS_ADR_LIN_2 & "', '" & strREP_BUS_ADR_LIN_0 & "', '" & strREP_BUS_CTY & "', '" & strREP_BUS_STA_CD & "'," & _
"'" & strPostalCodep & "', 'T', 'F', 'Primary', '" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE('" & strNow & "', '" & strOraDFormat & "'), TO_DATE('" & strNow & "', '" & strOraDFormat & "')"

a.Write "ACCOUNT.ADDRESS (PRIMARY) INSERT"
a.WriteBlankLines 1
a.Write sSQLAddp
a.WriteBlankLines 2


rsCon.Open sSQLSelCon, ObjData.OraConn, adOpenForwardOnly, adLockReadOnly, adCmdText

'***************************Contact Records from TMP_PD_DATA
For i = 1 To rsCon.RecordCount Step 1



sSQLConIns = "INSERT INTO CONTACT (CONTACTID, TYPE, ACCOUNTID, ACCOUNT, ISPRIMARY, LASTNAME, FIRSTNAME, LASTNAME, " & _
"MIDDLENAME, ADDRESSID, SHIPPINGID, WORKPHONE, HOMEPHONE, FAX, EMAIL, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER, " * _
"CREATEDATE, MODIFYDATE) VALUES ('"







Next
rsCon.Close
'****************************Contact Records from TMP_PD_DATA


End If 'NMO,RMO,EMO if statement in 1st line of DO loop

rs.MoveNext
Loop
Handler:
If slapi_LogixErrors Then
strslapi_ErrCds = slapi_LogixErrorCode 'Public variable in SlgxApi.bas
strslapi_ErrTxt = slapi_LogixErrorText 'Public variable in SlgxApi.bas
frmErrors.Show
Exit Sub
ElseIf Err.Number <> 0 Then
nErr = Err.Number 'Error Number as public string
sErrSorc = Err.Source 'Error Source as public string
sErrDescr = Err.Description 'Error Description as public string
frmErrors.Show
Exit Sub
End If



a.Close
Set a = Nothing
Set fs = Nothing


End Sub

[opiate]

Sorry, not all the SQL statments pasted right! <IMG SRC="smilies/cool.gif" border="0">

[antonye]

<center>
<table border="0" width="90%" bgcolor="#000000">
<tr>
<td bgcolor="#FFFFFF" align="center" valign="center">
<font size="+2" color="#ff000">Come to WinDrivers</font>

<font color="#0000ff">...and get people to proof read your code for free!!!



<IMG SRC="smilies/biggrin.gif" border="0"> <IMG SRC="smilies/biggrin.gif" border="0"> <IMG SRC="smilies/biggrin.gif" border="0"> <IMG SRC="smilies/biggrin.gif" border="0"> <IMG SRC="smilies/biggrin.gif" border="0">
</td></tr>
</table>
</center>

[opiate]

HAHAHAHA............


Are there any limitations for a sub being so long?

Are there any kind of memory buffers I could use?

[antonye]

Originally posted by opiate:
Are there any limitations for a sub being so long?

No, but you might want to try and split your code out into separate subs or functions to make it more readable.

You may even find that there's code in there you're using a couple of times, or may be able to use again if you split it out.

It will make it easier to read, debug when you find problems or change in the future.

Don't forget to comment your code well as it's always a nightmare to go back to a large piece of code a year later and try to remember what it does!

[JungleMan1]

No, I don't think there is a limit. As a matter of fact I have heard that most games are one big giant function that loops. But then again this is VB.

But you may want to slice it up anyway. <IMG SRC="smilies/smile.gif" border="0">