Form bound to table

[dhstein]

I used the form wizard to create a form to update records in a table. I have
several questions:

1) I have 3 Yes/No fields - two of them show up as input boxes where you
can type yes or no - and one is a checkbox - how do I convert the checkbox to
the other form?

2) I have 2 columns of fields. The controls in each column move together.
Is there a way to break apart these controls so I can move them wherever I
want?

3) I put in a button to have the user hit "Update" after he has corrected
any data. I discover that the data gets updated automatically. I don't want
that - is there a way to disable it and just do an update when he clicks
"Update" ?

 

[Allen Browne]

I'm assuming you use Access 2007.

Re #1: In form design view:
a) Select and delete the existing control.
b) Click the combo (or whatever you want) in the Controls chunk of the
ribbon (on the Design tab.)
c) Click the field you want in the Field List (pane at right, in the same
space as the Properties box.)

Re #2, see:
http://office.microsoft.com/en-us/access/HA102868491033.aspx

Re #3, the best solution is to get used to the idea that Access saves
automatically as needed. There are so many things that can trigger the
record being saved, e.g. tabbing past the last control, using the navigation
buttons, using the menus, the toolbars, or the ribbon, applying a filter,
changing the sort order, reassigning the RecordSource, pressing a shortcut
key (Shift+Enter), closing the form, closing Access, performing a requery,
etc.

Whatever you need to do before the record saved, do in the BeforeUpdate
event of the form (not controls.) Access fires this event just before the
record is saved.

If you really must save only through your own button, you can create a
form-level boolean variable, set it to True in the button's Click event, and
test and reset it in Form_BeforeUpdate. If the variable is not set, the save
happened in some other way, so cancel the Form_BeforeUpdate event.

 

[Tom van Stiphout]

On Mon, 16 Feb 2009 17:32:18 -0800, dhstein

Re 1: Not sure why this happened - you can inspect the details of
those three fields and probably figure it out.
Simply delete the control, put a checkbox control in its place and set
the ControlSource property to the field name.

Re 2: Yes. Click on the plus-in-a-box control in the upperright corner
of the group to select the group, then right-click on one of the
controls and select Layout > Remove.

Re 3: Remove the button and let Access do its thing. Especially when
you are new to it. If you want a confirmation, in the
Form_BeforeUpdate event write:
dim intAnswer as Integer
intAnswer=Msgbox("Wanna save?", vbYesNo or vbQuestion)
if intAnswer=vbNo then Cancel = True

That last line cancels the BeforeUpdate event, effectively stopping
the Save.

-Tom.
Microsoft Access MVP

 

[dhstein]

Allen and Tom,

First of all thanks a million for all the great information you, as well
as others, provide. I wanted to thank Allen especially, because this form
that I'm creating uses the excellent routine FindAsUType. It works great,
but since I have only a somewhat hazy understanding of the exact workings (I
have tried to read the code - and I've learned a lot just from that effort)
I'm reluctant to make too many serious changes to this form. So the checkbox
issue for example - I didn't want to delete the control and make a new one,
but I'll try that anyway. Actually, I worked on some changes last night
(Eastern US here) and have become a little more comfortable with what I can
do. There are fields on this form that need special processing, so what I've
done for now is lock them down and provide a separate button to handle those
fields. I used FindAsUType so the user could find records easily and as I
said it works great. The one issue is that a lot of the records have similar
names so if we search for widgets there might be "very shiny widgets" and
"not so very shiny widgets" and unless the user knows the exact name he might
get the wrong one. So I was thinking about creating a query with a string
matching function. Then I could use that in a combo box to filter records
and show all the ones that match - and the user could then select that one.
I will try this - unless either of you have a better idea. Again thanks for
all the excellent advice - it helped me go from being a complete database
novice to a good beginner in 3 months.

 

[Allen Browne]

No worries.

The FindAsUType stuff is fairly involved: mostly so that it can cope with
whatever form you add it to without needing you to modify it. It therefore
does quite a lot of work figuring out which fields it can filter on, and
what name the user knows them by, what their data type is, and so on.
Consequently, most people use it as plug'n'play code without needing to pull
it apart.

If it has helped you learn, that's great news.

For anyone else who wonders what we are talking about:
http://allenbrowne.com/AppFindAsUType.html