Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Needs help in function

Hi: In this method i open a text file and read the social numbers one by one from their and then save into a table. Is it possible to change the program with 4 arguments function which receive below 4 arguments and then pass into the function and return the result thru VBA code

SSNumber=website.SSN
Name=website.Name
Service_Date=website.Death
Birth_Date=website.Birth

For eg:

deceasedprocessintoSQL(SSNumber, Name, Serivice_Date, Birth_Date)
deceasedprocessintoSQL("001228164","BEAUREGARD","","")


Result
------
001228164, BEAUREGARD, 001-22-8164, GERARD G BEAUREGARD

Thanks.

Private Sub deceasedprocessintoSQL()
Dim social As String, WShell As Object, vPath As String, vFile
Set WShell = CreateObject("wscript.shell")
vPath = WShell.SpecialFolders("MyDocuments") & "\"
vFile = vPath & "Deceased-Information-" & Format$(Now, "yyyymmdd-hhmmss") & ".txt"
Dim ssnFile As String, xLine
Dim Name1 As String, Name2 As String, Name As String
ssnFile = vPath & "ssn.txt"
Open ssnFile For Input As #2
Do While Not EOF(2)
    Line Input #2, xLine
    social = Trim(xLine)
    Open vFile For Append As #1
    Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & social)
    Close #1
Loop
Close #2
Set WShell = Nothing

Dim j, vArr, rs As DAO.Recordset, nLine
Dim DBConn
Dim oConn
.....
Dim sSQL As String, ssNumber, ssSQLNumber, wLine
Dim rs1
Dim findSSNumber As Boolean
Open vFile For Input As #1
Do While Not EOF(1)
    Line Input #1, xLine
    k = k + 1

    If InStr(xLine, "ssdi.cgi?letter=y&lastname") > 0 Then
        nLine = Mid(xLine, InStr(xLine, "ssdi.cgi?letter=y&lastname") + 18, 110)
        vArr = Split(nLine, "&")
        Name1 = Split(vArr(1), "=")(1)
        Name2 = Split(vArr(0), "=")(1)
        Name = Name1 & " " & Name2
        Birth = Replace(Split(vArr(3), "=")(1), "+", "-")
        Death = Replace(Split(vArr(4), "=")(1), "+", "-")
        ssNumber = Replace(Split(vArr(2), "=")(1), "+", "-")
        ssNumber = Replace(ssNumber, "-", "")
        sSQL = "insert into tblinfo(Name,Birth,Death,ssn) values('" & Name & "', '" & Birth & "', '" & Death & "','" & ssNumber & "')"
        oConn.Execute (sSQL)

    End If
Loop
Close #1
Set oConn = Nothing
Set rs = Nothing
End Sub

Function PostURL(sURL, aPostData)
   sURL = CStr(sURL)
   Dim oXml
   Set oXml = CreateObject("Microsoft.XMLHTTP")
   oXml.Open "POST", sURL, False
   oXml.setRequestHeader "Content-Type", "application/ x-www-form- urlencoded"
   oXml.Send aPostData
   PostURL = oXml.responseText
   Set oXml = Nothing
End Function
0
mustish1
Asked:
mustish1
  • 8
  • 7
  • 2
10 Solutions
 
Rey Obrero (Capricorn1)Commented:
what exactly do you want to do?
0
 
davidraresCommented:
Hi mustish1,

I suppose you have an mdb file and using MSA 2003.

You need to make the following changes:

 - make a function Test in one VBA module:
                  Public Function test()

                           Dim str As String
                           str = Command           ' this will give you the command line parameters
                           ' next parse the cmd line and get you parameters
                           ' call your deceasedprocessintoSQL function with parameters parsed from cmd line
     
                  End Function

 - make a macro called AutoExec with one line:

                   RunCode with FunctionName -> Test()

 - start the application with the following command line:

      "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" path_to_mdb_application /cmd a b c
       
       where a, b, c are you parameters and can be as many as they want.

Good luck,
Dave
 

0
 
mustish1Author Commented:
I need to create a function with 4 arguments, instead of pulling social numbers from the text file and then run the program and fetch information from the web site. I mean i pass 4 arguments in the function: Social Number, Name, Birth Death and it return row against that argument from the web site. But all 4 parameters are optional i mean either i pass only socil with birth date or social with name or any combination or only social number in the function

Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
davidraresCommented:
Hi mustish,

in my comment above I explained how can you pass command line arguments to a function in a MSA application.

Do you need help also with modifying the function from no-arguments to 4-arguments?

Dave
0
 
Rey Obrero (Capricorn1)Commented:


Sub deceasedprocessintoSQL(Optional SocialNumber As String, _
Optional Name As String, Optional Birth As String, Optional Death As String)
Dim WShell As Object, vPath As String, vFile

Set WShell = CreateObject("wscript.shell")
vPath = WShell.SpecialFolders("MyDocuments") & "\"
vFile = vPath & "Deceased-Information-" & Format$(Now, "yyyymmdd-hhmmss") & ".html"
Debug.Print vFile
Open vFile For Output As #1
Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & SocialNumber)
Close #1
Set WShell = Nothing

End Sub

Function PostURL(sURL, aPostData)
   sURL = CStr(sURL)
   Dim oXml
   Set oXml = CreateObject("Microsoft.XMLHTTP")
   oXml.Open "POST", sURL, False
   oXml.setRequestHeader "Content-Type", "application/ x-www-form- urlencoded"
   oXml.Send aPostData
   PostURL = oXml.responseText
   Set oXml = Nothing
End Function
0
 
mustish1Author Commented:
It gives an error: Expected Function or variable on line:
returnresult = deceasedprocessintoSQL("001228164", "GERARD BEAUREGARD")
Also can i print the output on a screen instead of an html file

Thanks.

Private Sub Cmd_Process_Click()
Dim returnresult As String
returnresult = deceasedprocessintoSQL("001228164", "GERARD BEAUREGARD")
End Sub

Sub deceasedprocessintoSQL(Optional SocialNumber As String, _
Optional Name As String, Optional Birth As String, Optional Death As String)
Dim WShell As Object, vPath As String, vFile

Set WShell = CreateObject("wscript.shell")
vPath = WShell.SpecialFolders("MyDocuments") & "\"
vFile = vPath & "Deceased-Information-" & Format$(Now, "yyyymmdd-hhmmss") & ".html"
Debug.Print vFile
Open vFile For Output As #1
Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & SocialNumber)
Close #1
Set WShell = Nothing

End Sub
0
 
mustish1Author Commented:
>Do you need help also with modifying the function from no-arguments to 4-arguments?
Yes thanks.
0
 
Rey Obrero (Capricorn1)Commented:
mustish,
i think it is enough to pass the SocialNumber to the function, since it is a Unique value the returned information  will be more accurate
0
 
Rey Obrero (Capricorn1)Commented:
no need for the names parameter..
0
 
Rey Obrero (Capricorn1)Commented:
the sub will just be

Sub deceasedprocessintoSQL(SocialNumber As String)
0
 
mustish1Author Commented:
Yes but some times i have data contains only birth or death or only name and i have to confirm from the web site are they really deceased or not.

Thanks.
0
 
mustish1Author Commented:
Some of the data which i have are with social and name but some have only names with birth or social with death. Not 100% all data is with the social numbers

Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
ok try this, you can pass SocialNumber or LastName

Open vFile For Output As #1
If Len(SocialNumber) > 0 Then
    Print #1, PostURLa("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & SocialNumber)
    ElseIf Len(LastName) > 0 Then
    Print #1, PostURLa("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "lastname=" & LastName)
End If
Close #1
0
 
Rey Obrero (Capricorn1)Commented:
oops

Open vFile For Output As #1
If Len(SocialNumber) > 0 Then
    Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & SocialNumber)
    ElseIf Len(LastName) > 0 Then
    Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "lastname=" & LastName)
End If
Close #1

0
 
mustish1Author Commented:
I try to pass the name without social but it gives syntax error on line:
returnresult = deceasedprocessintoSQL("", "GERARD BEAUREGARD")
-----------------------------------------------------------------
Private Sub Cmd_Process_Click()
Dim returnresult As String
returnresult = deceasedprocessintoSQL("", "GERARD BEAUREGARD")
End Sub

Sub deceasedprocessintoSQL(Optional SocialNumber As String, _
Optional Name As String, Optional Birth As String, Optional Death As String)
Dim WShell As Object, vPath As String, vFile

Set WShell = CreateObject("wscript.shell")
vPath = WShell.SpecialFolders("MyDocuments") & "\"
vFile = vPath & "Deceased-Information-" & Format$(Now, "yyyymmdd-hhmmss") & ".html"
Debug.Print vFile
Open vFile For Output As #1
If Len(SocialNumber) > 0 Then
    Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "ssn=" & SocialNumber)
    ElseIf Len(LastName) > 0 Then
    Print #1, PostURL("http://ssdi.rootsweb.com/cgi-bin/ssdi.cgi", "lastname=" & LastName)
End If

Close #1
Set WShell = Nothing

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
you can not pass the whole name, just the lastname or socialnumber
0
 
mustish1Author Commented:
Wheni  try to call it gives syntax error: Expected function or variable
a = deceasedprocessintoSQL("", "BEAUREGARD")

Thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now