Form in macro to not open if query result is empty recordset

[H0MELY]

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

 

[strive4peace]

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
*

 

[H0MELY]

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

 

[strive4peace]

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
*

 

[H0MELY]

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

 

[strive4peace]

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
*