custom INSERT INTO TABLE statement for ACCESS 2003

[Belinda]

I am trying to insert a "new" record into my table. I want to know how to
take the user input from the test fields on the form and combine them to
INSERT INTO MYTABLE. Currently the fields are bound to MYTABLE.
The user also wanted dropdown lists to minimize type errors so I created a
few “selectDistinctQueries†from MYTABLE to provide this functionality.
However, the lists are not allowing the selection, and/or new entries. LIMIT
TO LIST is NO.
Any help/code on these issues is welcome.

Thanks in advance, Belinda

 

[John W. Vinson]

I am trying to insert a "new" record into my table. I want to know how to
take the user input from the test fields on the form and combine them to
INSERT INTO MYTABLE. Currently the fields are bound to MYTABLE.
The user also wanted dropdown lists to minimize type errors so I created a
few “selectDistinctQueries” from MYTABLE to provide this functionality.
However, the lists are not allowing the selection, and/or new entries. LIMIT
TO LIST is NO.
Any help/code on these issues is welcome.

Thanks in advance, Belinda

If you have a bound form, you should not need any code nor any append query! A
Form bound to a table lets you just type data; as soon as you move off the
record it saves it to disk.

What is your form's Recordsource property?
How about its Allow Additions and Allow Edits (both should be Yes)?
What are the RowSource properties of a couple of these combos? Post the SQL.
What specific error message are you getting?

 

[Belinda]

Hi John
I hope I can answer this adequately…I’m new to this and I experimented with
trying to get custom distinct dropdown lists/menus so thier control source is
to the field names of the table, but the row source on them are from the
distinctQueries, and the row source types are Table/Query. The other fields
on the form that require unique input ( that is not like to be duplicated)
are listed as the regular control source, as the field names in the table
with no row source that I can see on the properties menu.
If I am going about this all wrong, just let me know ASAP. I really don’t
want to waste anymore time on this. I know that the last record can be made
to add a new record but I wanted a little more customization to this and
that’s why I wanted the drop down menus. In my own attempt to remedy this I
am in the process of combining all my selectDistinct Queries into a new query
in hope that I will them be able to create the form off that and then update
the table with the new record. Is that feasible? If you know of how to select
user text input to write the new record, please let me know the easiest,
fastest way, in terms a novice like me can grasp. Thanks a Million John!

-- Belinda

 

[John W. Vinson]

Hi John
I hope I can answer this adequately…I’m new to this and I experimented with
trying to get custom distinct dropdown lists/menus so thier control source is
to the field names of the table, but the row source on them are from the
distinctQueries, and the row source types are Table/Query. The other fields
on the form that require unique input ( that is not like to be duplicated)
are listed as the regular control source, as the field names in the table
with no row source that I can see on the properties menu.

If a field isn't being selected from a list (either a list of values or a
query/table) and instead just being typed in, your form should use a Textbox
control bound to the table field, not a combo box. Otherwise you're doing
fine.

If I am going about this all wrong, just let me know ASAP. I really don’t
want to waste anymore time on this. I know that the last record can be made
to add a new record but I wanted a little more customization to this and
that’s why I wanted the drop down menus. In my own attempt to remedy this I
am in the process of combining all my selectDistinct Queries into a new query
in hope that I will them be able to create the form off that and then update
the table with the new record. Is that feasible? If you know of how to select
user text input to write the new record, please let me know the easiest,
fastest way, in terms a novice like me can grasp. Thanks a Million John!

You're making a simple job very much more difficult!

You don't need ANY CODE AT ALL.
You don't need to append records to the table.
You don't need to combine your queries.

Access does all of this *FOR YOU AUTOMATICALLY*.

A bound form... adds records to the table. All by itself, with no programming
needed from you.

Take a look at Crystal's video or one of the other tutorials here and start
from scratch. It's not as hard as you're making it!
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Belinda]

John
It'll take me a time to look through the resources you referenced. Thank you
very much! I'll let you know how I do...