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

  • Thread starter Thread starter Don
  • Start date Start date
D

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
 
You could have the field linked to the entry in one form,
then based on the "After update" event update the other field.

--Doug
 
-----Original Message-----
Don said:
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?
Click to expand...

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

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