insert query with fields from a form

i am using access 97. I have a form where i enter data,
after i am done entering data i want to be able to hit a
command button and that button fires a query to insert the
datafields of the form into a database. My problem is
that access doesnt like the insert of the form variable in
the insert query's values section. Is there anything i
can do to solve this or a web reference that helps me
along.
thanks

Please post the SQL view of the query so we can see how you're trying to do
what you're doing.

--
Wayne Morgan
Microsoft Access MVP


"doug" wrote in message
...
i am using access 97. I have a form where i enter data,
after i am done entering data i want to be able to hit a
command button and that button fires a query to insert the
datafields of the form into a database. My problem is
that access doesnt like the insert of the form variable in
the insert query's values section. Is there anything i
can do to solve this or a web reference that helps me
along.
thanks

Have you tried using a main/subform or linked form? Searching help for these
topics may shed some light.

CH

"doug" wrote:

i am using access 97. I have a form where i enter data,
after i am done entering data i want to be able to hit a
command button and that button fires a query to insert the
datafields of the form into a database. My problem is
that access doesnt like the insert of the form variable in
the insert query's values section. Is there anything i
can do to solve this or a web reference that helps me
along.
thanks

the query is
INSERT INTO time_log_table ( [task date], [assoc number],
[main items], [sub items], [time], [entry date], notes )
SELECT [Forms]![time_entry_form]!calendar.Value, [Forms]!
[time_entry_form]!txtAssocNumber.Value, dlookup("[main
item desc]", "main_item_table", "[main item code] =
[Forms]![time_entry_form]!cboMainItem.value"), dlookup
("[subitem desc]","[subitems_table]", "[subitem code]=
[Forms]![time_entry_form]!cbosubItemsection.value"),
[Forms]![time_entry_form]!txtHoursonJob.value, format
(now, "mm/dd/yyyy"), [Forms]![time_entry_form]!
txtComments.Value;

any help would be appreciated
-----Original Message-----
Please post the SQL view of the query so we can see how
you're trying to do
what you're doing.

--
Wayne Morgan
Microsoft Access MVP


"doug" wrote in
message
...
i am using access 97. I have a form where i enter data,
after i am done entering data i want to be able to hit a
command button and that button fires a query to insert
the
datafields of the form into a database. My problem is
that access doesnt like the insert of the form variable
in
the insert query's values section. Is there anything i
can do to solve this or a web reference that helps me
along.
thanks



.

I can see one problem right away. In the criteria of your DLookup
statements, you're not concatenating in the value from the form.

For example:
dlookup("[main item desc]", "main_item_table", "[main item code] =
[Forms]![time_entry_form]!cboMainItem.value")

Should be:
DLookup("[main item desc]", "main_item_table", "[main item code] = " &
[Forms]![time_entry_form]!cboMainItem.value)

This would be if the value of cboMainItem is a number. If it is a text
value, you would need:
DLookup("[main item desc]", "main_item_table", "[main item code] = """ &
[Forms]![time_entry_form]!cboMainItem.value & """")

Both DLookup statements need this correction.

For the field "format(now, "mm/dd/yyyy")", I believe you'll need the
parentheses after Now. VBA doesn't require them, but I believer the query
will. Also, Since you're only storing the Date, judging by the format, you
could use Date instead. Using Date, you could probably drop the format
statement also and just use Date.

Format(Date(), "mm/dd/yyyy")
or
Date()

To make it easier to read, you should be able to drop the ".Value" on each
statement. Value is the default property of the control and so is what will
be used if you omit it. However, this shouldn't be causing you a problem.

Are the field types you are inserting into the correct data type for the
data being inserted? This would be set in the table's design view.

--
Wayne Morgan
Microsoft Access MVP


wrote in message
...
the query is
INSERT INTO time_log_table ( [task date], [assoc number],
[main items], [sub items], [time], [entry date], notes )
SELECT [Forms]![time_entry_form]!calendar.Value, [Forms]!
[time_entry_form]!txtAssocNumber.Value, dlookup("[main
item desc]", "main_item_table", "[main item code] =
[Forms]![time_entry_form]!cboMainItem.value"), dlookup
("[subitem desc]","[subitems_table]", "[subitem code]=
[Forms]![time_entry_form]!cbosubItemsection.value"),
[Forms]![time_entry_form]!txtHoursonJob.value, format
(now, "mm/dd/yyyy"), [Forms]![time_entry_form]!
txtComments.Value;

i now have as my query

INSERT INTO time_log_table ( [task date], [assoc number],
[main items], [sub items], [time], [entry date], notes )
SELECT [Forms]![time_entry_form]!calendar.Value, [Forms]!
[time_entry_form]!txtAssocNumber.Value, DLookup("[main
item desc]", "main_item_table", "[main item code] = """ &
[Forms]![time_entry_form]!cboMainItem.value & """"),
DLookup("[sub item desc]", "subitem_table", "[subitem
code] = """ & [Forms]![time_entry_form]!
cbosubItemsection.value & """"), [Forms]![time_entry_form]!
txtHoursonJob.value, format(now(), "mm/dd/yyyy"), [Forms]!
[time_entry_form]!txtComments.Value;

but still no dice. is there still something wrong?
thanks

-----Original Message-----
I can see one problem right away. In the criteria of your
DLookup
statements, you're not concatenating in the value from
the form.

For example:
dlookup("[main item desc]", "main_item_table", "[main
item code] =
[Forms]![time_entry_form]!cboMainItem.value")

Should be:
DLookup("[main item desc]", "main_item_table", "[main
item code] = " &
[Forms]![time_entry_form]!cboMainItem.value)

This would be if the value of cboMainItem is a number. If
it is a text
value, you would need:
DLookup("[main item desc]", "main_item_table", "[main
item code] = """ &
[Forms]![time_entry_form]!cboMainItem.value & """")

Both DLookup statements need this correction.

For the field "format(now, "mm/dd/yyyy")", I believe
you'll need the
parentheses after Now. VBA doesn't require them, but I
believer the query
will. Also, Since you're only storing the Date, judging
by the format, you
could use Date instead. Using Date, you could probably
drop the format
statement also and just use Date.

Format(Date(), "mm/dd/yyyy")
or
Date()

To make it easier to read, you should be able to drop
the ".Value" on each
statement. Value is the default property of the control
and so is what will
be used if you omit it. However, this shouldn't be
causing you a problem.

Are the field types you are inserting into the correct
data type for the
data being inserted? This would be set in the table's
design view.

--
Wayne Morgan
Microsoft Access MVP


wrote in message
...
the query is
INSERT INTO time_log_table ( [task date], [assoc
number],
[main items], [sub items], [time], [entry date], notes )
SELECT [Forms]![time_entry_form]!calendar.Value,
[Forms]!
[time_entry_form]!txtAssocNumber.Value, dlookup("[main
item desc]", "main_item_table", "[main item code] =
[Forms]![time_entry_form]!cboMainItem.value"), dlookup
("[subitem desc]","[subitems_table]", "[subitem code]=
[Forms]![time_entry_form]!cbosubItemsection.value"),
[Forms]![time_entry_form]!txtHoursonJob.value, format
(now, "mm/dd/yyyy"), [Forms]![time_entry_form]!
txtComments.Value;


.

What is the Data Type of each of the fields you are inserting into? You say
it doesn't work, do you get an error? If so, what's the error? Is this a
"stored" query (a query in the query tab of the database window)? If not,
where is it located?

--
Wayne Morgan
MS Access MVP


"doug" wrote in message
...
i now have as my query

INSERT INTO time_log_table ( [task date], [assoc number],
[main items], [sub items], [time], [entry date], notes )
SELECT [Forms]![time_entry_form]!calendar.Value, [Forms]!
[time_entry_form]!txtAssocNumber.Value, DLookup("[main
item desc]", "main_item_table", "[main item code] = """ &
[Forms]![time_entry_form]!cboMainItem.value & """"),
DLookup("[sub item desc]", "subitem_table", "[subitem
code] = """ & [Forms]![time_entry_form]!
cbosubItemsection.value & """"), [Forms]![time_entry_form]!
txtHoursonJob.value, format(now(), "mm/dd/yyyy"), [Forms]!
[time_entry_form]!txtComments.Value;

but still no dice. is there still something wrong?