insert query with fields from a form

D

doug

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
 
W

Wayne Morgan

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

Guest

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
 
W

Wayne Morgan

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") <<
Click to expand...
Click to expand...

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


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;
Click to expand...
 
D

doug

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") <<
Click to expand...
Click to expand...

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


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;
Click to expand...


.
Click to expand...
 
W

Wayne Morgan

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?
 
Top