Can you populate same field in two tables with one entry?

[Don]

Although I may end up designing a multiple page form, I
would like to know if there is a command or macro that
allows an entry which will populate the same field name in
two different tables? I have a field "case number" and I
have linked it on two forms (one opening, the other
closing the case number)and although linked, currently it
requires that the case number be re-entered into the field
within the "closing" table. Any help?
Thanks, Don

 

[Doug Chinnock]

You could have the field linked to the entry in one form,
then based on the "After update" event update the other field.

--Doug

 

[Brian]

Don

It is faster to use a update stored procedure or update
query on form closure.

Brian

 

[Don Marshall]

-----Original Message-----

Although I may end up designing a multiple page form, I
would like to know if there is a command or macro that
allows an entry which will populate the same field name in
two different tables? I have a field "case number" and I
have linked it on two forms (one opening, the other
closing the case number)and although linked, currently it
requires that the case number be re-entered into the field
within the "closing" table. Any help?

Using bound forms, you simply need to refer to the open first form in the
default value property of the [Case Number] text box. I like do it in code
as the second form opens, so I can trap any error that might occur (here's
some aircode):


Sub Form_open()
On Error GoTo Error_Handler
Me.txtCaseNumber.DefaultValue = [Forms]![Other Form]! [txtCaseNumber]
Exit_Here:
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

If you open the second form with criteria that the [Case Number] =
Me.txtCaseNumber, it will open to an existing record, if it exists, and a
new one with the default value, if it does not.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003


Arvin,

Thanks... but (since I'm a newbe) I am still doing
something wrong. You mentioned a "bound" form... I know
how to make subforms -- is this how they are "bound" or is
there another way? Currently I have two forms - Main Case
Information and Invoice/Closing Table Form. I created a
button to open the new form and (using an append query
that, although it works, is less "smooth" as your
technique. Since the two forms are "bound" after an
append, I am assuming that, by linking the two tables
on "Case Number," they are bound. I have tried adding the
code to the Invoice/Closing Case Number field in
BeforeUpdate, AfterUpdate, OnEntry, etc. but the button
that opens this form no longer works (so, obviously, I am
placing the code into the wrong locaton). Thanks for your
help on this, but I also understand it may be too time
consuming for you to "teach" me so I will understand if
you cannot. Don