Solved

Needs help in function

Posted on 2007-03-31
17
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

730 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