Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling a var from a sub (access2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>
    I've declared a variable in a function, e.g: Dim stringFund As String = soros
    How can I use this as a critetia for a query? Isn't it Functionname([Fund])
    Tried this but it wont work. What am I doing wrong?
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    not exactly sure of your question but this may help:
    Dim strString as String
    Dim strSQL as string
    Dim intNumber as Integer

    intNumber = 8
    strString = "John"

    strSQL = "Select Name, Age from People Where Name = " & strString & " and Age = " & intNumber

    msgbox strSQL

    running this will produce a messgae box with a correctly formatted SQL Query. the ampersand '&' concatenates the text between the double quotes and the variable values as assigned in the previous lines.

    HTH

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    Hello FatherJack.
    What I want to do is do something like below
    Sub SQL()
    Dim strString As String
    Dim strSQL As String
    strString = "Soros"
    strSQL = "Select Fund FROM qryRecipients WHERE Fund = " & strString
    MsgBox strSQL
    End Sub

    The above code doesn't do anything, its displaying the strSQL line in the message box.

    to filter the query which am using as a source for a mail merged document. I want this code to filter the query and merge only the record which contains the word soros in the fund feild.
    Can this be done?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    ok, i understand what you are after now, the msgbox line was just to show the 'compiled' SQL string.

    Is the value of strString always going to be "soros" ? If so then this may as well be a query in the database rather than created via VBA.

    To allow a maimerge from Access , I would personally , create a report that uses your specified query as its recordsource and then use the access toolbar to link to word and carry out a mail merge. You will need to build the report 'freehand' and then in the Report_Open event set the reportname.recordsource = strSQL.

    Have you thought of running the merge from word ? the mail merge process has quite a good filter gui and will permanently attach to a database so users can open a word document and get merged details without having to have access to the database ?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a var from a sub (access2000)

    Hi,
    I have a couple of questions:
    1. When you say that you have different codes for each report, how different are they? Are they basically the same code but with a different fund name and perhaps a different query name? If so, you should be able to create some generic code and simply pass the fund name and query name as parameters to it (e.g. something like OutputReport("SOROS", "qryReportData") )
    2. Do you run a report for every Recipient in the table? If so, I suspect your best bet would be to open a recordset of all the recipients, then loop through it, running the code for each recipient's fund.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    Hi FatherJack.
    I have few reports that Im creating using word and excel and I have different codes for each report (one report for each fund type) and when there are no records to be created the code opens a fax cover which is merged to a query called "qryRecepient" in this query I have the recepients addresses with the fund name. At the moment this document merges to all the addresses on the table, what I was hoping to do is control this by a filter from access coz Im opening the document using a switchboard.

    the following is a sample code of a report that I create in Excel which is for fund Soros:
    <pre>Sub SFM()
    Dim strFileName As String, strMsg As String, vResult As Variant
    'On Error GoTo ExportSFMReport_Err
    Dim rst As DAO.Recordset, db As DAO.Database
    'Turn System warnings off
    DoCmd.SetWarnings False
    'Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] " _
    & "WITH OWNERACCESS OPTION;", 0
    'Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", _
    acNormal, acEdit
    'Turn System warnings on.
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSFMReportSource")
    If rst.BOF And rst.EOF Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1Fax.doc"
    Exit Sub

    Else
    'Export records to spreadsheet and open it
    strFileName = "S:SRI_WORK_AREADOCUME~1" & "BCP" _
    & Format(Now, "DDMMYY") & ".xls"
    vResult = Dir(strFileName)
    If vResult <> "" Then
    vResult = MsgBox("File " & strFileName & _
    " already exists, Would you like to overwrite that file?", vbYesNo)
    If vResult = vbYes Then
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", _
    acFormatXLS, strFileName, True
    Else
    strFileName = "S:SRI_WORK_AREADOCUME~1" _
    & InputBox("File " & strFileName & " already exists," _
    & Chr(10) & "Please enter another filename not including " _
    & Chr(34) & ".xls" & Chr(34) & ": ") & ".xls"
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", _
    acFormatXLS, strFileName, True
    End If
    'Display message
    Beep
    MsgBox "Data has been exported successfully.", _
    vbInformation, "Export Confirmation"
    Else
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", _
    acFormatXLS, strFileName, True
    End If
    'Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] " _
    & "WITH _ OWNERACCESS OPTION;", 0
    Set rst = Nothing
    Set db = Nothing
    AppActivate "Microsoft Excel"
    End If
    End Sub
    </pre>

    The part where I've bolded is the part am talking about. I have four more codes like this meaning there are four diffrent receipeints.

    Edited to eliminate horizontal scrolling--Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    The reports are based on a different table, The fax is opened when there are no records for that fund on that day. All the recepient addresses are in the same table and yes the codes are similar but are opening diffrent documnets which are formatted differently from one another.

    Im sure it would help someone who could code in vb but that some one is not me. You have 2 tell me exactly what to do. I really don't know what I have to do.

    Everytime I run this code I have to set the value of the criteria feild to whatever fund is on this code to filter the query, coz I haven't merged the document using the vb codes, I've merged it and am using the code JUST to open the document.
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    I

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a var from a sub (access2000)

    Hi,
    Without knowing all the details of your documents, tables and queries, it's pretty much impossible to write the correct code for you. However, assuming that you use the same fax document for all funds (if you don't there's not a lot of point in having a mail merge document) you could add a field to your recipients table to use as an indicator for whether they should be included in the merge (a simple yes/no field should do it.) Then in your code, rather than opening the document for each recipient, you simply set the yes/no field for each to the relevant value. You can then open the fax document at the end of the code and print a copy for each recipient. (You will need to change the query that the document is linked to so that it only retrieves records where the check field =Yes)
    I hope this helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    Sounds good Rory, But how do I set the ye/no feild value using the code?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a var from a sub (access2000)

    Code would be something like:
    Set rstRecipients = db.Openrecordset("tblRecipients", dbopendynaset)
    with rstRecipients
    .Movefirst
    .findfirst "[Fund] = '" & strFundName & "'"
    .edit
    !Merge = True
    .update
    end with
    set rstRecipients = Nothing
    where obviously you would have to Dim rstRecipients as a DAO.Recordset and you would also need a strFundName variable. I've assumed that your fund field is called Fund and that your Yes/No field is called Merge.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a var from a sub (access2000)

    I forgot to mention that if you added a field for queryname and default Excel filename to your recipients table, you could loop through the recipients table calling a generic procedure (or function) to do your export, passing it the name of the query to run and the file to export to, which would make your code tidier and easier to maintain. (You may want to add some more/different fields to the recipients table, those were the only details I could remember from previous threads)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    Below is the SQL code of the Recipients query:
    SELECT Recipients.Fund, Recipients.[Account Name], Recipients.[Account Number], Recipients.Company, Recipients.[Contact Name], Recipients.Place, Recipients.Tel, Recipients.Fax, Recipients.RE, Recipients.Merge
    FROM Recipients
    WHERE (((Recipients.Merge)=Yes));

    Below is the vb code:
    <pre>Sub SQL()
    Dim rstRecipients As a DAO.Recordset
    Dim strFund As String
    Set rstRecipients = db.OpenRecordset("Recipients", dbOpenDynaset)
    With rstRecipients
    .MoveFirst
    .FindFirst "[Fund] = '" & strFund & "'"
    .Edit
    !Merge = True
    .Update
    End With
    Set rstRecipients = Nothing
    End Sub
    </pre>


    There is a error on the line where I

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a var from a sub (access2000)

    rstRecipient is a variable. The error is because the line should read:
    Dim rstRecipients As DAO.Recordset
    I suspect that I said you needed to Dim rstRecipients as a DAO.Recordset, which is half-code, half-English! Sorry about that!
    Oh yes, you need to assign strFund a string value as well.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a var from a sub (access2000)

    Rory, Is this how the vb code is supposed to be? I think am doing something wrong coz when I try to run this program it gives me an error message: THERE WAS AN ERROR EXECUTING THE COMMAND
    <pre>Sub SFM()
    Dim strFileName As String, strMsg As String, vResult As Variant
    Dim rstRecipients As DAO.Recordset
    Dim strFund As String

    Dim rst As DAO.Recordset, db As DAO.Database
    'Turn System warnings off
    DoCmd.SetWarnings False
    'Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    'Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    'Turn System warnings on.
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSFMReportSource")
    If rst.BOF And rst.EOF Then
    vResult = MsgBox("There are no records. Would you like to send a fax?", vbQuestion + vbYesNo)
    If vResult = vbYes Then
    Set rst = Nothing
    Set db = Nothing
    'set value to merge
    Set rstRecipients = db.OpenRecordset("Recipients", dbOpenDynaset)
    With rstRecipients
    .MoveFirst
    .FindFirst "[Fund] = '" & strFund & "'"
    .Edit
    !Merge = True
    .Update
    End With
    Set rstRecipients = Nothing

    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1Fax.doc"
    Exit Sub
    Else
    Exit Sub
    End If
    Else
    'Export records to spreadsheet and open it
    strFileName = "S:SRI_WORK_AREADOCUME~1" & "BCP" & Format(Now, "DDMMYY") & ".xls"
    vResult = Dir(strFileName)
    If vResult <> "" Then
    vResult = MsgBox("File " & strFileName & _
    " already exists, Would you like to overwrite that file?", vbYesNo)
    If vResult = vbYes Then
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, strFileName, True
    Else
    strFileName = "S:SRI_WORK_AREADOCUME~1" _
    & InputBox("File " & strFileName & " already exists," _
    & Chr(10) & "Please enter another filename not including " _
    & Chr(34) & ".xls" & Chr(34) & ": ") & ".xls"
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, strFileName, True
    End If
    'Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"
    Else
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, strFileName, True
    End If
    'Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    Set rst = Nothing
    Set db = Nothing
    AppActivate "Microsoft Excel"
    End If
    End Sub
    </pre>


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •