Help - Updateable query adds new row with missing criteria fields

J

JM

Hello,

I've created a Querydef in a Form_Load() sub. The form is a subform
that no longer has linked child fields. The form is bound to this
querydef. When I open the form, the fields are populated just fine.
However, when I add a new record using the new record control at the
bottom of the subform, it gets added with NULL for the two values that
were in the original WHERE clause.

For example, if my query was "SELECT batch, style, serial_no,
warehouse, color from chair_inventory WHERE batch = 138 and style = 6",
the form is populated. Then, when I add a new record, it gets added
with the serial_no, warehouse, and color I specified, but batch and
style are NULL. How can I make the dynaset pick up the original
criteria and insert them?

Thanks,

JM
 
T

tina

you'll need to capture the criteria and programmatically insert it into the
proper fields in the new record. you can do this on the subform's Insert
event, or BeforeUpdate event, by just setting the value of the fields in
VBA. something like

Me!BatchField = thebatchcriteria
Me!StyleField = the stylecriteria

how you capture the criteria depends on how you fed them to the query in the
first place. if you entered the criteria in controls on another form that
remains open, you can refer to those controls in the code directly, as
Forms!MyCriteriaForm!MyBatchCriteriaControl

if you generated the criteria request in the subform's Load event, it should
be simple enough to save the return values into variables, so they're
available while the subform remains open.

hth
 
J

JM

tina said:
you'll need to capture the criteria and programmatically insert it into the
proper fields in the new record. you can do this on the subform's Insert
event, or BeforeUpdate event, by just setting the value of the fields in
VBA. something like

Me!BatchField = thebatchcriteria
Me!StyleField = the stylecriteria

how you capture the criteria depends on how you fed them to the query in the
first place. if you entered the criteria in controls on another form that
remains open, you can refer to those controls in the code directly, as
Forms!MyCriteriaForm!MyBatchCriteriaControl

if you generated the criteria request in the subform's Load event, it should
be simple enough to save the return values into variables, so they're
available while the subform remains open.

hth
Click to expand...

Tina,

Thanks very much for the suggestion. I'll try it. I neglected to
mention that the table is SQL Server 2000 linked ODBC. When the table
was Access, the application worked as designed. I know the linked
table broke the Dynaset insert, and I wish I knew why. I hate when I
get asked to put a SQL Server back end on an Access front-end. Thanks
again.
 
Top