AC2000/ pass parameter to second form. opinions?

[ms]

i'm hoping for some guidance on the best practice for the
following situation:

i have a form with a combobox. one column (bound) has an
id, the other has a project name. rowsource is based on
query.

when a user selects a project, i want to open another form
allowing them to edit the other fields in the database for
this project based on its id.

i want the form to be unbound to the record, requiring a
submit button be pressed before the record is updated.
what i don't know is the best way to pass the id from the
first form to the open event of the second form so i can
run my sql and then populate the various text fields w/
the right data.

is it possible to use the criteria when opening the form?
ie
stLinkCriteria = "Project_ID=" & parProject
DoCmd.OpenForm stDocName, , , stLinkCriteria

if so, how is this retreived in the second form?

or do i have to refer back to the first form from the
second like:
mySQLParameter = Forms!frmOne!Project_List.Value

this just seems messy to me. would prefer it me more 'self
contained' if that makes sense.

any help/thoughts/direction appreciated. thanks.
ms

 

[Albert D. Kallal]

i want the form to be unbound to the record, requiring a
submit button be pressed before the record is updated.

That above is your problem. You are trying to re-invent the wheel, and as a
result are going to write tons of code here. (who is paying for this
project?).

I would slightly change your UI, and simply add a cancel button, and a "save
and close" button. I would either remove the "X" in the upper right hand
corner, or better yet have that X perform the same function as save and
close.

Really, the save should be implied for your users. I mean, can you imagine
turning the key in your car, and then a little box comes up and says do you
want to start the car?

How about putting a document into a file cabinet, and then the file cabinet
stops, and says do you want to file the document? (it just does not make
sense). If the user modified the record, then likely they wanted to. If they
make a mistake, then teach them to go edit->undo like good software has. The
fact that word bugs me all do to save a document is really dumb.

You can certainly give the user a save and close button. You can also give
the user a cancel button to NOT save the changes (but simply giving the user
a un-do button is the best).

Further, a bound form has all kinds of great verification events such as
before insert, before update, after update. There is a zillion events that
you can use with a abound form, and to try and code/replace those events is
a real waste of time. Further, you can prevent the record from being saved
if you wish anyway, but I think that is a backwards approach.

What happens if the user modify the record, and then try to close the form
without a save? (you then ask the stupid question do you wan to save?).

If you must, and fell the need to use up valuable coding time, then you can
simply use the openargs parameter of the open form, and pass the id that
way.

docmd.OpenForm "yourform",,,,,,me.id

Then, in the forms on-load event, you can go

msgbox "id passed = " me.openargs

or do i have to refer back to the first form from the
second like:
mySQLParameter = Forms!frmOne!Project_List.Value

The above is also not too bad either

this just seems messy to me. would prefer it me more 'self
contained' if that makes sense.

Well, you are making a mess by using a un-bound form when you don't need to.
You can eliminate 99% of your code, and get better usability with a bound
form.

 

[ms]

thanks for your reply. i originally felt i needed it to be
unbound, not because i was concerned about their wanting
to save the record, but i needed the opportunity to verify
the data before it was saved. i've never used the "before
insert, before update, after update events" that you
mentioned (fairly new to access/vba). will definitely look
into them now.

thanks.
ms

 

[Albert D. Kallal]

don't let me be too hard on your either.

Un-bound forms certainly have their place. Also, many applications do,
should, and will have a explicit save button.

However, to cancel, or close the form without updating, you can go:

me.Undo
docmd.Close

The above code will close the form without a update.

You can very much contorl the behavour of your form, even when they are
bound.

 

[Nikolai Novikov]

However, to cancel, or close the form without updating, you can go:

me.Undo
docmd.Close

is there any way to limit the auto commitment on navigate?

i.e. I can hide record navigators, but if user pushes Pg-Dn button he'll be
transferred to the next record or if only one record is selected as
recordset for the form - to the new record, in this case the current record
will be save anyway.

please advise

 

[ms]

thanks again.
to confirm, if i want to add a 'close w/out saving' button
and do me.undo it will undo all changes that were made,
even if multiple fields were edited or will it only undo
the last change that was made?

 

[Albert D. Kallal]

Most of my forms have the navigation buttons remove. I also set the allow
additions to false (and that effectively kills the page/down and page/up). I
also always set the forms "cycle" property to current record, so when the
tab is hit on the last field, it cycles around back up to the top.

doing the above do works very well. the vast majority of my forms DO NOT
have navigation buttons, and the above disables the mouse wheel, and page
up/down etc.

 

[Albert D. Kallal]

It will undo all changes made to the record before you came in to that
record.

Yes, all fields will be un-done.

 

[ms]

once again, thanks for all your time and help. final
question:

i've now made a bound form that's opened to the correct
record for editing using the openform wherecondition.

i'd now like to make a button to add a new record. is it
possible to use the same bound form (so i don't have to
maintain 2 forms) but by not passing it a condition open
as a new record? or possibly by using an openarg that's
used in the form_open.

currently, when i remove the condition, it seems to set
the values to the first record in the table it's bound to.

how do i tell it i want a new record?
thanks again. you've saved me countless hours already.
ms

 

[ms]

nevermind. think i figured it out

If Me.OpenArgs = "ADD=Y" Then
DoCmd.GoToRecord , , acNewRec
End If

 

[Ragnar Midtskogen]

It is my experience that to be sure to cancel everything entered you need to
use Me.Undo twice.
This is because Access forms have two layers of buffers, the control buffers
and the record buffer. When data has been entered in a control it goes into
the control buffer.
If you move to the next control, the data for the last control is copied to
the current record buffer.
If you instead hit the ESC button (Or select Edit/Undo), Access will
discard the contents of the current control buffer, replacing it with data
from the current record buffer.
Finally, if you move to a new record, the record buffer is copied to the
underlying table, and the record buffer and the control buffers are fillled
with data from the new table record..
If you instead hit ESC a second time, Access discards the contents of the
current record buffer, replacing it with the content of the table record. If
you were entering a new record which had not been saved to the table yet,
all the buffers would be cleared, in effect cancelling the new record.
If you need to know more details, take a look at one of the Access
Developer's Handbook editions by Getz, Litwin and Gilbert. The Access 2000
Developers Handbook's ISBN Number is 0-7821-2370-8.

Ragnar

 

[Albert D. Kallal]

Yes, the me.undo will dump the changes made to the record.