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

    Code help (Access2000)

    I've got the following code in a module which outputs data records to Excel and as you can see, it opens a word document when there are no records to output. The word document is a mail merged fax cover, which is merged with a table called Recepient, which is in the database that has the following module

    What I was wondering is: if I could merge a certain address to the document, This module is for a fund called soros and in the receipient table I have a feild with fund names, I could declare the fund name somewhere in the module and compare it with the fund name in the table and select the record with the fund name and merge it to word.

    It sounds easy but there is only one problem: I don't know how to program with Visual Basic <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Would somebody do the honour of helping me out, please?

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Sub BCPAmendReport()
    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 "AppendToSFMAmend", 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
    Exit Sub
    Else
    'Export records to spreadsheet and open it
    strFileName = "S:SRI_WORK_AREADOCUME~1" & "BCP" & Format(Now, "DDMMYY") & " amend" & ".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
    End If
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, strFileName, True
    'Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"
    '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

    Somebody PLEASE <img src=/S/help.gif border=0 alt=help width=23 height=15> me....

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

    Re: Code help (Access2000)

    Is there anyway I could declare a variable ( eg: Soros) on the above code and use that variable as a criteria of a field (Fund) for a query? Doesn't anybody know how this could be done? If anybody knows, PLEASE PLEASE let me know.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code help (Access2000)

    I'm sorry, but I don't understand what you're asking. You have several issues that seem to be only loosely related, and most of us simply don't have the time to plow through a good-sized block of code trying to figure out what you're attempting to do that you want to do differently.

    You say it "it opens a word document when there are no records to output". Do you want it to do that or not? It isn't really logical to open a Word merge document if there are no records to merge.

    You say "if I could merge a certain address to the document". Is there any reason why you can't, or is this an address you want to merge with there are *no records*?

    If you want to merge a specific record to Word, you can query down to that record, either in Access or in Word. What you want to do may be clear to you, but it isn't clear to us.
    Charlotte

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

    Re: Code help (Access2000)

    I am creating a report in Excel, which is sent to the suppliers everyday, if there are no orders for the day then we send a fax to inform the supplier that there are no orders (the merged document is the fax). We have many suppliers one for each type of order. This code creates reports for orders type: SOROS. I have a table with all the suppliers details. What I was hoping to do was to have a constant type of variable in this code e.g: Fund = SOROS and filter the query which is merged to the fax with this. But the problem is that I don't know how coz I can't code in visual basic.

    I want to filter the query using this method because I'm ONLY using a single query and a general fax cover which is merged to this query, to send to all the suppliers.

    There is nothing wrong with the code I've posted. The reason I've posted it is because I wanted to add the constant to that peice of code and I thought it'd help u guys to make sense of what am trying to say.
    Thanx.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code help (Access2000)

    Didn't you get an answer to your post in Excel? I thought you had and that this must be a different problem. Please remember to mention in a post that you have also posted the question in another forum. That way, there is less likelihood of multiple threads trying to address the same problem when it's already been solved.
    Charlotte

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

    Re: Code help (Access2000)

    This is a total different question charlotte. and YES, I did get an answer for the question I asked in the Excel forum.

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

    Re: Code help (Access2000)

    Will I be able to write a Function which would contain all the subs which I use to create & open the reports, and at the start of the function I could have a variable called Fund and assign the appropriate value to the Fund in each sub. Then call this function in the query (Recipient) Fund field criteria?
    The code would look like this:

    Function Reports()
    Dim strFund As String

    Sub SFM()
    Fund=Soros

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code help (Access2000)

    NO! Functions do not *contain* other routines. Functions and Subs are routines in themselves. They can call other functions or subs, but they don't contain them.

    You would declare an input argument for the function and then call the appropriate sub based on the value of the argument. However, if you want the function to return a value, then probably the subs will have to return values as well, which means they too should be functions and not subs.
    Charlotte

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

    Re: Code help (Access2000)

    Maybe I've used the wrong word there. What I want to do is to have a program with sub programs in it. The program would have a variable and each subprogram could assing a value to that variable.
    Do I make sense now?

  10. #10
    BAM
    Guest

    Re: Code help (Access2000)

    <<It sounds easy but there is only one problem: I don't know how to program with Visual Basic
    Would somebody do the honour of helping me out, please?>>

    I don't have any offerings on your code, but as a friendly suggestion <img src=/S/smile.gif border=0 alt=smile width=15 height=15>, if you are going to use VB/VBA you need to know more about the programming language.

    VBA is a language. Just as French, English, Spanish, etc, are languages. If you go to another country you need to be able to speak their language so you don't go to a restaurant and end up ordering "pieds bouillis de porcs" (boiled pigs feet) the same goes for VBA. In VBA you could severely corrupt files or wipe out valuable data if you don't know what you are doing.

    You might want to check with some local colleges and see if they offer courses (many of them have night classes you can attend) or perhaps purchase a computer manual that will assist you. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ~~~~~~~~~~~~~~
    Cheers! <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code help (Access2000)

    Only if you're thinking of an application as a program. Code is stored in modules, and you can have multiple routines in a module, but there is no way to run a module because it isn't anything except a container. You have to call a function or sub and allow it to call other routines as I suggested before.

    The kind of programming that's done with VBA in the Windows environment is broken up into routines (subs and functions) that you can mix and match. Forms and reports have their own modules that are a special kind of class module, so they contain multiple subs and functions for their exclusive use, but there still isn't any way to embed one routine inside another. What you do instead is build a basic routine that accepts an argument like your Fund name and then calls other routines to do whatever needs to be done.

    I'm not sure if that answers your question but if not, then keep explaining what you're trying to accomplish (rather than how you want to do it, which may not be possible), and we'll try to help you arrive at a solution.
    Charlotte

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

    Re: Code help (Access2000)

    What else do u guys want me to explain? I tried my best. I simply want to set value to a variable in the code and use it to filter a query, the only thing is that I use this query for all the reports and each report is created using different codes similar to this, I could set value in each code but I won't be able to use these as criteria coz there would be more than one criteria in the criteria line.

    I wish I had time to learn visual basic but I don't so I might as well leave it as it is.
    Thank you for all those who tried to help me.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code help (Access2000)

    We're trying to help, but there seems to be a communication problem. I've suggested the same thing a couple of times and you haven't explained why that suggestion doesn't work for you.
    <hr>set value to a variable in the code and use it to filter a query<hr>
    That statement isn't very specific. You can't use a variable in a query, only in code. I'm not sure if you want to create the query in code, pass the parameters from code to the query or something else entirely. Are you trying to use the fund name to export records only for that fund? Your code simply does a blanket export from a particular table and you populate that table using
    <hr>DoCmd.OpenQuery "AppendToSFMAmend", acNormal, acEdit<hr>
    If you're asking how to populate that table with the records for a single fund, then the answer is that without seeing the SQL for the query, I can't tell.
    <hr>I could set value in each code but I won't be able to use these as criteria coz there would be more than one criteria in the criteria line<hr>
    This is where I get totally confused. You want to use a variable to limit a recordset but you're saying that you need multiple values? Which one is it? In any event, you would have to modify your query to accept parameters and then pass the parameters in code or from the user interface, but I'm not sure that's what you actually want to do.

    The most difficult part of programming and troubleshooting is getting a clear understanding of what is actually going on and what *should* be going on. When you work face-to-face with someone, it isn't hard to point to something and say "is this what you want to do", but when we're trying to do the same thing here in the Lounge the only option is to keep asking questions until we can understand what is being asked for. The process is as frustrating for us as for you because we want to help, but we can't figure out the help you need.
    Charlotte

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

    Re: Code help (Access2000)

    DoCmd.OpenQuery "AppendToSFMAmend", acNormal, acEdit
    this is a query which append data to a table from a general table, this has nothing to do with what Im trying to do so PLEASE forget that part.

    set value to a variable in the code and use it to filter a query
    I could set value in each code but I won't be able to use these as criteria coz there would be more than one criteria in the criteria line

    Like I said I could set value to the above code and use it in the criteria feild of the query and Im sure it'll work but that means I have to create one query for each fund based on the same table. If I have to do that, I might as forget about this and set the criteria of the query to a certain fund.

    Read the following scenario, you might understand what Im trying to do:
    Lets say I have a store and I have many suppliers. I get supplied drinks from a certain supplier and frozen food from another and so on. I have all of the suppliers details including their address and what they supply in a table.
    Lets say the above code is to create a report for the drinks supply: When I have orders I create the report and send it to the supplier (I'm doing this part (NO PROBLEM)) but when I don't have any orders I send them a fax cover (This is where the problem is) I've merged this fax cover with a query created using The table which contains suppliers details(RECIPIENT). I have different codes to create a report for each supplier coz they want it in a certain format. so what I was hoping to do was to have a program with sub programs in it. the sub programs would be the codes to produce each reports. I want to have a variable in the program called FUND and set the variable to different values in each sub program, for example: in the above code the variable would be FUND=Drinks and use this variable to filter the Recipient query so that the Fax cover document would merge to the appropriate address rather than merging to all addresses in the Recipient table.

    Hope I made sense. Does this help? <img src=/S/help.gif border=0 alt=help width=23 height=15>

  15. #15
    New Lounger
    Join Date
    Jul 2001
    Location
    Scottsdale, Arizona, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (Access2000)

    Let's see, if I understand you correctly, you're trying to use a variable to programmatically determine a criteria for one field of a query. Here's how I would go about doing it:

    Make your query a parameter query by defining a parameter in the Query, Parameters menu item. Put that parameter's name in the criteria area of the field you'd like to filter.

    Then, in your subroutine's code, you can set your 'Fund' variable equal to whatever criteria you want (e.g. Fund="Drinks"), and then set the query's parameter equal to the variable (e.g. Query.Parameters![Parameter Name] = Fund).

    Look up 'Parameter Object' in Access help for more information.

    Hope this helps -
    <img src=/S/king.gif border=0 alt=king width=21 height=22> Eric

Posting Permissions

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