Form Event confusion

[Guest]

Hello

Access 2003 - Intermediate user (below basic VBA coding knowledge).

I've used the occasional event buttons to use the built in commands the
button wizard helps design and assign some macros. I have an issue and know
what I'm doing wrong but don't know how to fix it.

The desired outcome is when the user clicks no (using the on click event), I
want the query to restart. What happens is the query starts, and the user
enters the required parameters, but it won't finish because (Sorry, I'm
typing the error from memory), I get a locked table error, saying the table
is in use by another person, process.

Which is true because the form in question here is based off the same table.
This form is a summary form that essentially reports a total and confirms a
change the user is about to make. If he selects no, he's redirected to start
over again by beginning the query all over.

So I modified the onclick event to execute a do.cmd.close form first, and
that didn't work. I tried putting the do.cmd close form on the exit event,
but that didn't work either.

I'm thinking I need to redirect the form to a macro, where the macro would
close the form, then restart the query, and I'll try that today, but I'm
thinking it won't work either because the macro is still running because of
the form which is still related to the table which is somehow still be used
by the form process....I'm looped and it's all so confusing to me.

Have mercy,
Thanks
Laura

 

[Nikos Yannacopoulos]

Laura,

Can you please help us picture the situation a little bit better?

What is the form's recordsource? If not a single table, post the query SQL;
What does the query mentioned do? Post its SQL also;
Post your existing code behind the command button's Click event, and
tell us which line it errs on, and what the exact error message is.

Have faith!

Nikos

 

[Guest]

Nikos, explanations are inline. Thankyou for your post.

What is the form's recordsource?

souce is tblBatchAudit

If not a single table, post the query SQL;

the record souce is a single table.

What does the query mentioned do?

Its' a make table query. Essentially the query asks the user to select
what kinds of records to change, (ie, supplies, travel, contracts) and then
asks the user to enter a number (in decimal form) to indicate by how much the
user should increase or decrease those records by.

Post its SQL also;

SELECT [enter % change as decimal]*[funded] AS change, qryOCSums.OCCode,
qryOCSums.OCDescription, qryOCSums.PROGRAM, qryOCSums.tblPriBand_ID,
qryOCSums.Source, qryOCSums.order, qryOCSums.Command1, qryOCSums.command2,
qryOCSums.ObjectClass, qryOCSums.PD, qryOCSums.AcTitle, qryOCSums.MDEP,
qryOCSums.SAG, qryOCSums.tblCmd_ID, qryOCSums.tblObjClass_ID,
qryOCSums.function, qryOCSums.tblFunction_ID, qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID,AS rmks INTO tblBatchAudit
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));

Post your existing code behind the command button's Click event, and
tell us which line it errs on, and what the exact error message is.

Private Sub cmdNoqryBatch1_Click()
On Error GoTo Err_cmdNoqryBatch1_Click

Dim stDocName As String

stDocName = "qryBatchStep1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdNoqryBatch1_Click:
Exit Sub

Err_cmdNoqryBatch1_Click:
MsgBox Err.Description
Resume Exit_cmdNoqryBatch1_Click

End Sub

There is no error line, just a pop up window which reads: "The database
engine oculd not lock table 'tblBatchAudit' because it is already in use by
another person or process"


Laura

 

[Guest]

Nikos, problem solved. Modified the queries and got it to work with a macro.
Yipppeee!

Laura

 

[Nikos Yannacopoulos]

Nikos, problem solved. Modified the queries and got it to work with a macro.
Yipppeee!

Good! It always feels better to crack it yourself, doesn't it?

Anyway, seeing your second detailed post, it is evident that the problem
was that you were trying to essentially overwrite a table, while it was
open by the form. Not sure what you did, but one way around it would
have been to temporarily "disconnect" the form from the underlying table
while it is open, re-create the table and re-connect it. To that effect,
the code behind the button would be modified to something like:

Private Sub cmdNoqryBatch1_Click()
On Error GoTo Err_cmdNoqryBatch1_Click

Dim stDocName As String

Me.RecordSource = ""

stDocName = "qryBatchStep1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Ne.RecordSource = "tblBatchAudit"

Exit_cmdNoqryBatch1_Click:
Exit Sub

Err_cmdNoqryBatch1_Click:
MsgBox Err.Description
Resume Exit_cmdNoqryBatch1_Click

End Sub

Furthermore, since qryBatchStep1 is an action query, it is more
efficiently run in this fashion:

CurrentDb.Execute "qryBatchStep1", dbFailOnError

instead of the OpenQuery Mmethod of DoCmd (nobody said macro conversion
to code is optimal!). The gains include no flashing query window, if you
get that, and no confirmation dialog - it just runs in a flash.

The next step would normally be to use a form for the user to enter the
parameters in - better yet select from combo or single / multi-select
listboxes... that takes you to the next level of VBA utilization for a
professional looking user interface. Interested?

Of course, all of that is just theory on your original question... now
the hard part: why create / delete / recreate the table? Use the query
as the form's recordsource instead. Any particular reason why you can't?
Any measures against bloat?

HTH,
Nikos

 

[Guest]

Is bloat inefficiency?

I do love access, it's like a giant puzzle with more than one solution. My
frustration arises because I don't the know the most efficient solution and I
don't work with access daily, so I end up re-learning everything I've
forgotten before I can move on. Add to the fact that I just do not have the
TIME to learn code and I know it limits my ability....rrrrg. Baby steps.

Always looking for better ways to improve. Let me play around with your
recommendations and get back to you. I am interested in the multi-select box
form idea, and think I have the tables linked correctly to easily set this
up, so taht I'm not creating list boxes. I have the FK fields in the main
table already set up as lookup fields from the related tables, this should
help right?

As to your question, I have records like the following (fields separated by
a : and not all fields included in the examples)

[unit] [category] [descrption] [account] [program director] [amount]
39th Supplies tool kits 123456 Mr. Kinfe
100
39th travel conference 123456 Mr. Kinfe
50
43d supplies paper 123444 Ms. Smith
45
43d printing pamphlets 123456 Ms. Smith
3
G8 contracts Dorina 123456 Ms. Kinard
5

Mostly changes to records are made individually by inserting a new record.
However sometimes i to apply a % change to a category of records...say
reduce supplies by 10% or increase travel by 20%. For every change though
made, I need a record of that change, I just can't apply the change to the
amt number. I need a tool that will let me go back at the end of the year
and see what changes were made and why. I call this an audit trail.

So since it's possible for the records in the main table to have records
that would look like this:

39th Supplies tool kits 123456 Mr. Kinfe
-10
39th travel conference 123456 Mr. Kinfe
-3
43d supplies paper 123444 Ms. Smith
-2
43d printing pamphlets 123456 Ms. Smith 5
G8 contracts Dorina 123456 Ms. Kinard
15

I have a query that SUMS the amount by category so I would end up with a
record that tells me tool kits in 39th now only cost $90 vs $100.

It's this Summary query that I use as the base for the make table qury.
Although now it's not a make table query. It's an append qry into a table.
My macro uses a del qury to clear the table first. I use this Batchtable as
a source for a form that gives the user a total $ number of the changes they
are about to make and they can confirm or restart the query to make
corrections. If they confirm the batchtable records are then appended into
the maintable and thus I can qury the rmks column for my audit trail.

Make sense? Regret asking?

Have a great day.
Laura

 

[Nikos Yannacopoulos]

Hi Laura,

Pls see my comments in-line.

Rgds,
Nikos

Is bloat inefficiency?

Yes, one form of it. The term refers to the increase of the Access file
size which occurs by adding objects and data and then deleting them, as
a result of Access not automatically relinquishing the disk space they
occupied upon deletion. The more temporary data creation and deletion,
the higher the resulting bloat.

The antidote is the Compact and Repair utility built in Access. This is
something every Access based app administrator has to live with, and
measures are taken to deal with it regularly. In the case of a
monolithic database it's pretty simple really, all it takes is to check
the Compact On Close option under Tools > Options, tab General. In multi
user split apps, though, this will only work on the front end, while
different approaches are available for the back end holding the data. I
have been employing two different techniques, (a) scheduled overnight
compaction through a scheduled job, and (b) ad-hoc compaction triggered
by the front end launch, provided no other user is already connected to
the back end - effectively, first user to open the app every morning
compacts the back end before connecting. They both do the job equally
well, (a) is better for bigger back-ends taking longer to compact, while
(b) is simpler to implement.

It should be evident by now why it's advisable to store all temporary
data in the local front end rather than in the back end.

I do love access, it's like a giant puzzle with more than one solution.

The best user alias I've seen in the NewsGroups is "Access Junkie"... I
suppose it applies to many of us here!

I just do not have the TIME to learn code...

You're not alone in that, I felt the same way, and I probably wans't the
exception to the rule

and I know it limits my ability....

More so than you realize! At some point you finally cross the line, then
you begin to realize, and then there's no turning back.

Always looking for better ways to improve.

Good. You won't escape, trust me.

I am interested in the multi-select box ...

Multi-select -> code. It's a one way street, no alternatives.

I have the FK fields in the main table already set up as lookup fields
from the related tables, this should help right?

Most likely not. look-up fields in tables are a bad idea,IMHO this
option should have been removed many Access versions ago. Have a look at
this:

http://www.mvps.org/access/lookupfields.htm

Mostly changes to records are made individually by inserting a new record.
However sometimes i to apply a % change to a category of records...say
reduce supplies by 10% or increase travel by 20%. For every change though
made, I need a record of that change, I just can't apply the change to the
amt number.

Good thought.

I need a tool that will let me go back at the end of the year
and see what changes were made and why. I call this an audit trail.

Another good thought.

I have a query that SUMS the amount by category so I would end up with a
record that tells me tool kits in 39th now only cost $90 vs $100.

A natural result of your original good thought.

Although now it's not a make table query. It's an append qry into a table.
My macro uses a del qury to clear the table first.

Good. In theory, deleting and recreating the whole table would result in
more bloat vs. this approach, as you would be writing and deleting the
table object definition on top of the data.


So, if I get this right now, the temporary data in the table are the new
records until they are confirmed? That makes sense. My original
impression was you only summed up records already existing in the main
table and stored in the temporary one just so you can display them in
the form, which doesn't make sense.

Make sense? Trying!

Regret asking?

What does it look like?

 

[Guest]

Nikos,

Thanks for the information and validation. I'm having a minor work crisis
that is taking up all my attention right now but intend to take the DB home
over the w/e and consider undoing my lookup fields. Not sure if this
particular DB is worth the effort, but shouldn't be too hard to modify a few
forms and queries if necessary.

I'll repost if I run into complications. This new community rocks!

Laura