Solved

Needs help in function

Posted on 2007-03-31
17
252 Views
Last Modified: 2012-06-27
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
Comment
Question by:mustish1
  • 8
  • 7
  • 2
17 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828813
what exactly do you want to do?
0
 
LVL 2

Assisted Solution

by:davidrares
davidrares earned 100 total points
ID: 18828814
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
 

Author Comment

by:mustish1
ID: 18828831
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Assisted Solution

by:davidrares
davidrares earned 100 total points
ID: 18828852
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828853


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
 

Author Comment

by:mustish1
ID: 18828899
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
 

Author Comment

by:mustish1
ID: 18828905
>Do you need help also with modifying the function from no-arguments to 4-arguments?
Yes thanks.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828908
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828909
no need for the names parameter..
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828916
the sub will just be

Sub deceasedprocessintoSQL(SocialNumber As String)
0
 

Author Comment

by:mustish1
ID: 18828920
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
 

Author Comment

by:mustish1
ID: 18828939
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828960
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18828962
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
 

Author Comment

by:mustish1
ID: 18828988
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 18829141
you can not pass the whole name, just the lastname or socialnumber
0
 

Author Comment

by:mustish1
ID: 18829154
Wheni  try to call it gives syntax error: Expected function or variable
a = deceasedprocessintoSQL("", "BEAUREGARD")

Thanks.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now