Solved

Needs help in function

Posted on 2007-03-31
17
230 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 119

Accepted Solution

by:
Rey Obrero 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
 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 400 total points
ID: 18828909
no need for the names parameter..
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

12 Experts available now in Live!

Get 1:1 Help Now