+ Reply to Thread
Results 1 to 15 of 15

Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

  1. #1
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Dear Experts

    I need vba help on Msaccess form Cmd button retrive data from Start & End date depends upon of Tabular column

    I have set of 3 Table column named Dispatch,Closed,Cancel & One Form

    condition is
    If Select Dispatchcalls Filter Date col is = "Order Relase Date". Col No 26
    If Select ClosedCalls Fitler Date Col is = "ClosedDate". Col No 48
    If Select Cancelcalls Filter Date Col is = "ClosedDate". Col No 38

    Save
    1.If DispatchCalls select in table List and Filter Date in Start&End ..Retrive Data from Table DispatchCalls depends upon OrderrelaseDatecol... Savefile as Dispatch
    2.If ClosedCalls select in table List and Filter Date in Start&End ..Retrive Data from Table ClosedCalls depends upon ClosedDatecol... Savefile as Closed 3.If CancelCalls select in table List and Filter Date in Start&End ..Retrive Data from Table CancelCalls depends upon ClosedDatecol...savefile as Cancel

    Find the attachment


    Refer this code
    http://www.excelforum.com/access-pro...eted-file.html
    Attached Files Attached Files
    Last edited by breadwinner; 05-26-2014 at 09:54 PM. Reason: file attach

  2. #2
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Any experts can Help on this?....

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    The zip file is empty.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Sorry Sir I Uploaded Wrong File , Pls find the attachment of "ONSITEDCC" Thanks in Advance

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Build a query for each of the reports you wish using the appropriate table as your record source. In the criteria for the particular field you wish to filter on, type

    Please Login or Register  to view this content.
    Save your query and name it according to what you have named in your combo box.

    Now create your VBA for exporting your query to Excel.

    Please Login or Register  to view this content.
    Last edited by alansidman; 05-27-2014 at 12:24 AM.

  6. #6
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Dear sir

    can u upload the output file with query and code? I am too Week in Ms-Access ....Please sir

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    No Sir. Strength comes through learning. We are here to help and teach, not do your work for you. If you wish to work in Access, then it is time to learn how to make queries. Here are several links to help you get to the next level of your basic Access development. Queries are easy to learn. Try it and you will find that you can master it in minutes.

    http://www.techonthenet.com/access/queries/index.php

    http://www.accessmvp.com/strive4peace/

    http://www.databasedev.co.uk/queries.html

    http://www.datapigtechnologies.com/AccessMain.htm

  8. #8
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Sir i tried create Query but getting error Object File #3 missing can u help on this

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Post your db with your query. I don't know what that error message means.

  10. #10
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Dear sir

    find the attachment i just tried through your links posted #7 , I didnt know where i did mistake.. pls find attachment and teach me
    Attached Files Attached Files

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    You have placed three tables in one query and have not related the tables to each other. There are no joins. With this type of query you will end up with a cartesian result. I believe that from what you indicated in your first thread you wish to have three different queries. I cannot build them for you, because I don't know what you want as results. Which fields from each table are to be included? Currently, you only have one field selected. What information is needed for each query.

    Now to the meat of your db design. Why do you have three tables? The information in each appears to be the same or very similar except for the status. Why not have one table with a field for status and in that field you can identify Cancelled, Closed or Dispatch. You then only have to query one table and change the status criteria. I urge you to make this change early in the design as it will continue to prove problematic as you get further along in your db.

    Your combo box SQL statement needs to be changed to
    Please Login or Register  to view this content.
    In the properties for the combo box, on the format tab, change column count to 1 and change column widths to 1". On the data tab, your bound column should be 1. This will resolve the error message, but your query would still fail as noted above in the first paragraph.


    Suggest you read the next link carefully. It is on proper set and design of databases.

    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

    If you wish to work effectively with Access, then you need to learn how to design a data base that will be efficient. Access is not as intuitive as Excel. I urge you to get a book and learn step by step. Once you have a good understanding, then you can venture into the world of development.

    Additionally, you need to change the names of the queries in the List to Cancel, Dispatch and Closed. They cannot have the same name as your tables.
    Last edited by alansidman; 05-29-2014 at 12:58 AM. Reason: combo box edits.

  12. #12
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Dear sir
    as u said, I have create Query from Dispatchcalls and assigned field from table then In order release date i kept criteria as

    "Between Forms!Report.text0 and Forms!Report.text2"

    and In cmd9 button i paste your code as your mentioned above.. please help on this find the attachment
    Attached Files Attached Files

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    You have created one query and it works if you run it from the QBE. But, you didn't change the query names as I suggested. You didn't amend the SQL statement for your combo box nor did you change any of the properties I suggested for your combo box, therefore your VBA will fail. It is essential to have the query name be the same in your combo box as the actual query unless you go to one table as I suggested and then you will not need the combo box to name the query to run, but to set the parameter of Closed, Cancelled or Dispatched in your new field (column in your query.)

    To help further on this, you will need to decide
    One table as suggested or staying with three tables as instructions going forward will differ depending on which direction you go. At present with your current db, I can't tell which way you are leaning.

  14. #14
    Forum Contributor
    Join Date
    07-21-2011
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007,2010,2016
    Posts
    695

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Can u tech me step by step ... I could understand step of follow for create query on data design.. but i cant implement on this report

    where i can find the combobox?
    where i amend the sql statement?
    better if u give me out put file i could learn easily... pls sir

  15. #15
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate

    Combo box. change the names of the records in the list table that you created.

    Look at the three videos on this link about combo boxes. You will need to scroll down a bit to see the three http://www.datapigtechnologies.com/AccessMain.htm

    They will show you how to get to the properties section of the combo box and then you will understand what I am referring to.

    I have previously given you the correct SQL statement for the combo box.

    You still have not advised which direction you will be taking in your design. If you go with one table, then the entire combo box situation needs to take a different direction. As I indicated to you earlier, I will not do your work for you. If that is what you want and need, then I urge you to hire a developer or close this thread and post in the commercial services area of the forum which is a pay for services sub forum. As an aside, the proper level of support in the commercial services is probably in the neighborhood of 35 to 50 points to provide you with a workable solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help on Drop down list from data validation depends upon first value select
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-20-2013, 06:09 PM
  2. need excel formula to get result from data table depends upon week,partner,column values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2013, 08:41 AM
  3. Equation that depends on table (not sure how to explain this in a title)
    By adwhitle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2013, 10:15 AM
  4. [SOLVED] I want to fill the table with corresponding data depends on user selection in the drop dow
    By maruthi_ravi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-13-2013, 06:23 AM
  5. Data Collection that depends on various conditions
    By calli in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-27-2008, 10:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1