Insert into to update table froma a form help

[Guest]

Due to my limited SQL/Access knowledge, I am having trouble updating a table
from a form. I have a database named BIO ID, and I would like to update the
table named MOVES with information entered from a form I created. The table
MOVES has the following fields: MOVENUM (autonumber), BIO ID (number),
LOCATION (text), MOVE DATE (date/time), INITIALS (txt).

The form pulls BIO ID from another table (named BIO ID), Location is chosen
from a list box populated from another table (named Location), Move Date
being current date, and initials being current user. My SQL statement looks
like this:

DoCmd.RunSQL "INSERT INTO [BIO ID!MOVES]([BIO ID], [LOCATION], [MOVE DATE],
[INITIALS]) VALUES [BIO ID!BIO ID], FORM!LIST43, FORM!TEXT41, FORM!TEXT29"

Any assistance would be greatly appreciated. Thanks.

 

[John Vinson]

Due to my limited SQL/Access knowledge, I am having trouble updating a table
from a form. I have a database named BIO ID, and I would like to update the
table named MOVES with information entered from a form I created. The table
MOVES has the following fields: MOVENUM (autonumber), BIO ID (number),
LOCATION (text), MOVE DATE (date/time), INITIALS (txt).

The form pulls BIO ID from another table (named BIO ID), Location is chosen
from a list box populated from another table (named Location), Move Date
being current date, and initials being current user. My SQL statement looks
like this:

DoCmd.RunSQL "INSERT INTO [BIO ID!MOVES]([BIO ID], [LOCATION], [MOVE DATE],
[INITIALS]) VALUES [BIO ID!BIO ID], FORM!LIST43, FORM!TEXT41, FORM!TEXT29"

Any assistance would be greatly appreciated. Thanks.

Well, NUMEROUS problems here - starting with the fact that you don't
need any code at all to update a table using a Form, if you simply
bind the form to the table (or to an updateable query based on the
table). Have you chosen to reject that option?

That said, the syntax of the INSERT SQL statement is

INSERT INTO tablename
(field, field, field)
VALUES
(value, value, value)

You're using [BIO ID!MOVES] as your table name, and I doubt that you
have a table of that name; I think you want just MOVES there.
Secondly, you can't just reference a table and field name to get the
value.

Referencing a form control uses the syntax

[Forms]![NameOfTheForm]![NameOfTheControl]

rather than FORM!controlname.

Note that the name of the database file itself (the .mdb file) is
irrelevant and is not used in any context. I think you're perhaps
bringing in the ways that some other program works and inappropriately
applying them to Access!

John W. Vinson[MVP]