1 fild autofill with the value of 2nd field in a different table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with two sub forms. I am trying to take the value inputed from
one field in table a and have it autofill into another field in a different
table. I have tried using a macro where I did a set value from table a to
table b and I have also tried an Event procedure doing the same thing. I have
put the macro and the envent procedure into the properties of the field in
table b. I put the macro and then tried the event procedure in the Event
Property control of OnClick which I assume means that when I click on the
field in table b, the update from table a should occur.

I don't know VBA very well. I am using the Online help from Access 2003 to
come up with the code.
 
Hi Brenda, can you explain what you are trying to do in more detail? For
example you talk about forms/2 sub forms but you are trying to move data
between tables?

Can I guess you entering a value in SubFormA and you want it to appear in
SubFormB?
 
I have a form for input with 2 sub forms. In one sub form I have a field that
autonumbers. I have in the second subform, the same field name (foreign key)
that is in another table.I want to move the autonumber that was incremented
in the first subform into the second subform that will enter that auto number
into the second table. The first field (data type autonumber) is in table a
and the foreign key (data type number) is in a different table. I need to
keep the numbers synchronized between the two tables. The two tables have a
one to one relationship.
So yes, I want to move data from table a (the generated autonumber) to table
b as a regular number. The reason why I can't just generate another
autonumber is that table b has fewer records (a subset of table a) than table
a.

I am guessing that I should be able to do this at the form level. I looked
at the MS help "Base the value of a control on another control" which
describes the process to set a value in a field based on a field in another
table. My thought was that if the value is set into the foreign key that it
would be placed into the table.

Now if I am wrong, that wouldn't surprise me. Amongst my books I don't see
a way to generate a autonumber in one table and place that number in another
table as a different data type.

I appreciate any insight you can give me. This is my first time doing this
and I need to fiind a solution if there is one.

Thanks!
 
I think there is a worked example of this in NWind that matches what you are
after.

Have a look at a form called "Customer Orders"

In early versions of Access, it worked using a hidded text box on the main
form that referenced the Autonumber on the 1st sub. It was then used as the
Master Link to the 2nd sub form. Just checked a recent copy and they bypass
the hidden field and in the 2nd subform the MasterLink is set to "[Customer
Orders Subform1].Form![OrderID]"
 
Back
Top