How to Handle Null Combo Box Entries in Append Query?

  • Thread starter Thread starter hawk
  • Start date Start date
H

hawk

I have a database all built off one main table of data. New data is
entered through a form with approx. 20 combo boxes, which all link
into the main data table. I am trying to create a "copy" button that
will take an existing entry and create an identical entry at the end
of the table, for easy updating purposes. I've created an append query
that grabs the value from each combo box and appends to the main data
table, but it is getting tripped up when it comes across a blank combo
box (the field for that entry just doesn't contain any data).

How do I go about telling my append query to enter nothing, zero, etc.
when it comes across one of these blank combo boxes?

Thanks in advance
 
Hawk,

Is this main form a bound form? If so, do you have a unique ID field
in your main table that distinguishes one record from another?

If so, you should be able to create a query that looks something like:

INSERT INTO yourTable (fld1, fld2, fld3, ...)
SELECT fld1, fld2, fld3, ...
FROM tblMOD
WHERE yourTable.ID = me.ID

If this ID field is an autonumber field, make sure you keep it out of
both of the field lists above. If it is not an autonumber field, it
might look like:

INSERT INTO yourTable (ID, fld1, fld2, fld3, ...)
SELECT NZ(DMAX("ID", "YourTable"), 0) + 1, fld1, fld2, fld3, ...
FROM tblMOD
WHERE yourTable.ID = me.ID
--
HTH

Dale Fye


I have a database all built off one main table of data. New data is
entered through a form with approx. 20 combo boxes, which all link
into the main data table. I am trying to create a "copy" button that
will take an existing entry and create an identical entry at the end
of the table, for easy updating purposes. I've created an append query
that grabs the value from each combo box and appends to the main data
table, but it is getting tripped up when it comes across a blank combo
box (the field for that entry just doesn't contain any data).

How do I go about telling my append query to enter nothing, zero, etc.
when it comes across one of these blank combo boxes?

Thanks in advance
 
Back
Top