[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Needs help in function

Posted on 2007-03-31
17
Medium Priority
?
287 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 1600 total points
ID: 18828813
what exactly do you want to do?
0
 
LVL 2

Assisted Solution

by:davidrares
davidrares earned 400 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Assisted Solution

by:davidrares
davidrares earned 400 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 1600 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 1600 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 1600 total points
ID: 18828909
no need for the names parameter..
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1600 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 1600 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 1600 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 1600 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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