 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Form in macro to not open if query result is empty recordset
Greetings again, thank you all for looking...here is the situation... I am using a make table query to pull data from a linked table through an ODBC connection. I have a second table stored within the tool called vendors that has a list of the vendors as well as their internal contact/vendor manager. What happens is the query goes out and grabs all of the data from the linked table within the specified time frame, matches the internal contact (if there is one) with a right join and then creates a table which I use for future queries. So I had to ask myself...how do I handle linked data that comes in and the vendor isn't on my vendor table? After the data is copied to the local tool I do a query that looks for null contacts. I created a form with 2 combo boxes, the first displays all of the vendors without a contact listed (where the contact is null) the second combo box is a list of all of the potential contacts. There is a command button that runs a macro with 2 queries in it...teh first is an update query that will update the contact in the table with copied data. The second does an append to the contact table for future use. Here is where I need help (sorry this took so long). The above process is actually only part of the full process. After pulling the data comes the verification and after that there is a review...what I would like is if the query looking for null values in the contact field comes back with an empty record set (meaning all of the vendors had contacts) I would like to skip the validation form from appearing and go straight to the review. I am thiking that I have to convert my macro to code and add an if statement if the recordset is eof...but honestly I am not certain of the best way to do it or if I am even on the right track. Any suggestions about any step of the process is more than welcome. The goal is to have a tool that other users can use without me having to go in and make changes behind the scenes constantly. Thank you again for all of your help. -John Hi John, rather than running a bunch of macros, it would be best to convert the process to VBA. Forget about the forms for now, focus on your data. Once you import the Vendor list, do you wish to automatically add new vendors? you can convert your MakeTable query to code using the following: DoCmd.OpenQuery "YourQueryName" If not all the vendors will be in your vendor list and you have RI (Referential Integrity) enforced, you will first need to add the new vendors before you append records to the vendors table. How is your database set up? read this: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal * (: have an awesome day :) * > Greetings again, thank you all for looking...here is the situation... > [quoted text clipped - 34 lines] > > -John Thank you for your response. We made a concious decision to avoid VBA as much as possible in this tool because of past experiences. This tool is basically a redisign of a previous tool that was written solely in vba. What happened is that there was one original tool that had been created by a contractor. The contractor left and an internal person began building tools based upon the code in the original tool. The first tool was sufficient, but written by someone who did not have a complete understanding of the data, btw this is a reporting tool pulling data from a sql server, I have been trying to push for an online tool with no luck so far. Imagine someone who doesn't know VBA very well trying to create a tool off someone elses code. As you can guess the tools became overly complex and difficult to manage. Frankly the tool is a mess. It took me 2 weeks of stepping through the tool to understand the logic involved and why it kept crashing. Another consideration is that vba will run because of an event...so if code is evoked before teh previous code (generally a query) has completed it tends to make the tool bomb out...having the queries run in a macro means that one will not run until the previous query has completed. So here is where I come in, I am trying to simplify the tool and improve the logic as well as document the tool and make it easy to modify once I have left the group. For a not so technical person...modying a query in query designer or changing a macro is much easier than trying to learn and manipulate vba code. Part of what I want to ensure is that the tool is, from a technical aspect, maintenance free. I do not want to get a call 3 months from now when they want to make a change it. So I am trying to be careful and think of everything that could cause them to call me. I guess the best anaolgy I can give relates to cars...if you look at the engine bay of an American car built pre 1980 you will see LOTS of space. This space makes it easy for the mechanic to get at problems and try to fix things. If you look at the engine bay of current cars you will see that they have jammed everyting in so tight that teh average person cannot get in there. I want to build this group a 1970 Cadillac Fleetwood. I want to make it easy for them to manipulate and update the tool without having to spend a great deal of time reading code or learning VBA. Sorry if this was more information than you needed, I am just looking to prevent an unnecessary step if possible. Than ks again for looking =) -john > Hi John, > [quoted text clipped - 63 lines] > > > > -John Hi John, "having the queries run in a macro means that one will not run until the previous query has completed." code is no different ... but sometimes you need to refresh the tabledefs and/or a DoEvents between steps to make sure that a subsequent step sees the current changes done by a previous step -- that needs to be done with VBA. I can appreciate the frustration of dealing with poorly written code. The same problem applies to macros though. If several people will be working with your process, whether it is VBA or macros, it is especially important to add comments. Please re-read my previous post and answer the questions I asked <smile> Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > Thank you for your response. We made a concious decision to avoid VBA as > much as possible in this tool because of past experiences. This tool is [quoted text clipped - 107 lines] >>> >>> -John Thank you for the response. I hope I get all of the questions, I only saw 2. The answer to the first question about do I want the tool to automatically add vendors. The answer to that is a partial yes...I have it setup so that if a new vendor is recognized it prompts the user to basically makea decision about the vendor and then it is added. I cannot skip the step where the user assigns the contact to the vendor. The reason behind teh question is if the recognition process does not find any new vendors the user is still presented with the form for assigning a contact. If no new vendors are recognized I want to avoid that step. The second question asked how my database is setup...I am not sure what you mean by this. Are you looking for relationship information, table/querie structure? As far as the design goes...the tool is nearly finished and I am strictly working on user enhancements to make the tool more friendly. I will admit that code and sql often improve performance...but in this instance what is important is leaving something that can be maintained by this group. Regarding the macro/code statement...imagine a choose your own ending book...that is very similar to this tool. What that means is there is interactivity with the user while they are diving to get their data. To avoid over zelous users who try to go to teh next page before finishing the current page, the old tool is littered with time validations (speed bumps). One of the biggest complaints I received was that users would click the next option too early and the tol would bomb out. I could easily add speed bumps or code to disable the mouse buttons...problem is...too much code. I have about 5 lines of code in the tool right now, 4 lines are me.recalc. It is important that I stay away from VBA unless absolutely necessary. I do not believe that I can accomplish what I am looking to do (avoid opening a form for an empty dataset) without vba. Thanks again -john > Hi John, > [quoted text clipped - 135 lines] > >>> > >>> -John Hi John, "Are you looking for relationship information, table/querie structure?" not queries because they do not hold data -- information on what your tables contain and how your tables are related ... for instance, you are making a table (that I assume is a temporary table). I assume you have a separate table for vendors and table(s) with related records. Once you make the temporary table (if that is what it is), is there Referential Integrity on the relationship between Vendors and the related tables? Are you adding just the new records if the user wants them? Are you updating records that are already there? what tables are in the database? what is the purpose of the database? "I do not believe that I can accomplish what I am looking to do (avoid opening a form for an empty dataset) without vba." In many cases, it is necessary to use VBA. It should not be something to fear. Using VBA is how to truly unleash the power of Access. The main thing is to make sure you add comments (what sections do, what forms/controls call the code, assumptions, etc), so another programmer down the road will understand what is being done. Also, indent code, declare variables at the top of each procedure, write modular routines but not so cryptic that the code is hard to follow ... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > Thank you for the response. I hope I get all of the questions, I only saw 2. > The answer to the first question about do I want the tool to automatically [quoted text clipped - 169 lines] >>>>> >>>>> -John |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.