467,169 Members | 950 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,169 developers. It's quick & easy.

Help - Updateable query adds new row with missing criteria fields

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

Nov 13 '05 #1
  • viewed: 1477
Share:
2 Replies
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
"JM" <jm***********@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
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

Nov 13 '05 #2
JM


tina wrote:
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


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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Marie | last post: by
1 post views Thread by Ivan Carey | last post: by
10 posts views Thread by motessa | last post: by
1 post views Thread by melody.charlesworth@eu.sony.com | last post: by
47 posts views Thread by Jo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.