Can't figure out how create a Data Entry Form

[Mike Webb]

Using Access 2K2
Experience Level: Beginner

Problems: (1) I don't know how to the PK autonumber to advance to the next
available number. (2) I don't know how to save the new entry on my form. (3)
I don't know how to setup my listbox in the subform to allow 2 or more
choices.

Tables behind the form: tblAddressList (contains all info on a Contact);
tblAddressListCategories (contains the category (ies) that a Contact will
fall into - can easily be more than one; for example, the Contact can be a
"major donor", "member of Advisory Board", and belong to "US Fish & Wildlife
Service); tblJoinAddressListCategories (contains PK's of first 2 tables as
FK's).

Form uses tblAddressList, and subform the other 2 tables. (I used the Form
Wizard to create this). I added every field from all 3 tables, and then
made some of them invisible or hid the columns.

The data/row source of the items in the Form are fields in tblAddressList.
The data/row source of the sole visible field in the SubForm is from
tblAddressListCategories.

I am using a Listbox in the subform and it shows all the choices from the
referenced table field. I choose one from the list, but have no way to
choose another.

I made the PK for the table behind the Form visible, but locked it (after I
took a quick look at the Northwinds sample db to see how it was done on one
of their forms.) I then entered some sample data and, for want of a means
to close it, tried to close the form. I got a dialog box telling me "Index
or primary key cannot contain a Null value."

I looked over some websites from the Access MVP site, Googled this newsgroup
using "data entry" as a search term, and looked over Access Help. Couldn't
find what I need to correct the problems and move forward.

Would someone please share their comments or advice on how I should proceed?

Cordially,
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501(c)(3) organization

 

[John Vinson]

Using Access 2K2
Experience Level: Beginner

I think you're making something easy into something difficult.

Problems: (1) I don't know how to the PK autonumber to advance to the next
available number.

If it's an Autonumber, just adding a record to the table advances it;
the autonumber is assigned with the very first keystroke on the new
record.

(2) I don't know how to save the new entry on my form.

Moving to a new record, closing the form, moving to a subform,
selecting Record... Save Current Record, in code...

(3) I don't know how to setup my listbox in the subform to allow 2 or more
choices.

That's a bit harder! You need to set the Listbox's Multiselect
property to "Simple" or "Complex", and then write VBA code to process
the selections. Answers to specific problems interspersed below.

Tables behind the form: tblAddressList (contains all info on a Contact);
tblAddressListCategories (contains the category (ies) that a Contact will
fall into - can easily be more than one; for example, the Contact can be a
"major donor", "member of Advisory Board", and belong to "US Fish & Wildlife
Service); tblJoinAddressListCategories (contains PK's of first 2 tables as
FK's).
Good!

Form uses tblAddressList, and subform the other 2 tables. (I used the Form
Wizard to create this). I added every field from all 3 tables, and then
made some of them invisible or hid the columns.

Actually, the Subform should probably be based only on
tblJoinAddressListCategories; on the subform you could have a Combo
Box bound to the categoryID and based on tblAddressListCategories.

The data/row source of the items in the Form are fields in tblAddressList.
The data/row source of the sole visible field in the SubForm is from
tblAddressListCategories.

That's one problem! There *is no relationship* between
tblAddressListCategories and tblAddressList; updating a field in the
category table isn't going to assign that category to a contact!

I am using a Listbox in the subform and it shows all the choices from the
referenced table field. I choose one from the list, but have no way to
choose another.

I'd suggest instead using a continuous Subform with a combo box. You
can use a listbox, but it requires some VBA code both to populate it
with the selected categories and more VBA code to store selected
categories in your table. I do have some sample code if you would like
to try it out, but it's a MUCH more complex method than the simple
subform!

I made the PK for the table behind the Form visible, but locked it (after I
took a quick look at the Northwinds sample db to see how it was done on one
of their forms.) I then entered some sample data and, for want of a means
to close it, tried to close the form. I got a dialog box telling me "Index
or primary key cannot contain a Null value."

Did you get this after entering data just on the mainform or on the
subform as well?

 

[Mike Webb]

I'll try your suggestions and report back. In answer to your question about
the errors, I got them when entering info on the main form.

Mike