Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code Help (2003)

    Hi,
    I have the code attached that I am referring to. I believe I am almost there. I basically copied the code from another location in my database that is very similar so I've made some changes, but I'm stuck as to where to go to next.

    Here's what I am trying to do: I have form where the user picks a Vendor from a combo box, they punch in a start date and end date, and the subform will show all the orders for that Vendor during that time period. (that works fine) They will then click a button and I want the orders showing on the subform to Export to an Excel spreadsheet. I have the Excel file BPATemplate.xls created. The overall picture is I want a separate Excel file per Vendor and then a new sheet in that file created every time the user exports. The user will do this once a month. When I run this it gives me the input box and tells me a sheet name already exists (which its not suppose to), i give it a new name, it creates a new sheet but none of my data carries over.
    Any thoughts?
    Thanks,
    Deb

  2. #2
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    I've made some adjustments and have fixed a couple of the problems I was having. Now I just can't seem to pass the data from the subform to the Export file. The new code is also attached.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2003)

    You are trying to export data from the query qryBPACallRegisterExport. Do you get no data at all in the worksheet, or too few records, or too many, or not the ones you want?

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    I get no data at all in the worksheet, but if I run the query I get records ( i have criteria in the query that looks to the form for the answers)

    Deb

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2003)

    Have you tried to single-step through PopulateOrders to see what is happening?

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    It's funny you ask that I just remmed out the line On Error resume next and I get the following message:
    Runtime error 1004

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2003)

    As a test, you could temporarily change that line to something like

    Randomize
    NewSheet.Name = Format(Date, "ddmmmyy") & Chr(Int(65 + 26 * Rnd))

    It will append a random letter to the sheet name, making it less likely that you get the same name twice. You should be able to check whether the correct data are transfered.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    Ok, that helped a little, but I did find out that for some reason it doesn't like my criteria being in the query. I now get the too few paramenters. expected 3 message. So what i did was take out the criteria in my query and it now passes the data. So i need to put the query criteria in my SQL statement. The first part is easy:
    rsSQL = "SELECT * FROM [qryBPACallRegisterExport] WHERE [lngVendorID] = " & lngVendorID --- I did have [Forms]![frmBPACallRegister]![lngVendorID] in my query. when I add this to the SQL statement it works. In my query I also had under the dtmDateOrdered field the following criteria:
    Between [Forms]![frmBPACallRegister]![dtmBPACallRegisterStartDate] And [Forms]![frmBPACallRegister]![dtmBPACallRegisterEndDate] where the start date and end fields are unbound date fields on my main form. I'm not sure how to add this to the SQL statement.
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2003)

    The problem is that DAO operates at a much lower level than Access - it "knows" about tables and queries but not about forms, so it doesn't recognize references to forms in the criteria.

    ... Between [Forms]![frmBPACallRegister]![dtmBPACallRegisterStartDate] And [Forms]![frmBPACallRegister]![dtmBPACallRegisterEndDate]

    becomes

    ... Between #" & [Forms]![frmBPACallRegister]![dtmBPACallRegisterStartDate] & "# And # " & [Forms]![frmBPACallRegister]![dtmBPACallRegisterEndDate] & "#"

    (the dates now become literal values in the SQL string, and hence they must be enclosed in #s)

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    I now have this,
    rsSQL = "SELECT * FROM [qryBPACallRegisterExport] WHERE [lngVendorID] = " & lngVendorID & _
    " Between #" & [Forms]![frmBPACallRegister]![dtmBPACallRegisterStartDate] & "# And # " & [Forms]![frmBPACallRegister]![dtmBPACallRegisterEndDate] & "#"

    but now no data again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2003)

    You have omitted And and the name of the date field.

    rsSQL = "SELECT * FROM [qryBPACallRegisterExport] WHERE [lngVendorID] = " & lngVendorID & _
    " And dtmDateOrdered Between #" & [Forms]![frmBPACallRegister]![dtmBPACallRegisterStartDate] & _
    "# And # " & [Forms]![frmBPACallRegister]![dtmBPACallRegisterEndDate] & "#"

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    I added that and now I get the too few parameters. expected 1 and when i click on debug it highlights this line:
    Set rs = DB.OpenRecordset(rsSQL, dbOpenDynaset) and rs=nothing

    deb

  13. #13
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    Ok, I realized my dtmDateOrdered was misspelled, it's now working. Now I just have that sheet name problem to figure out. But I"m not sure why that won't work because if it's the same sheet name the input box should come up so i can rename it. Not sure why that's not working.
    Deb

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2003)

    Ok, ignore the last post. I un-remmed the On error line and everything now seems to be working correctly.

    THANKS A TON FOR YOUR HELP!!!!!

    Deb

Posting Permissions

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