Copying a field contents from one table to another

[gjameson]

Let me start off saying I am a newbie with Access. I know a little Access and
VB. Here is what I am trying to accomplish.

I have a customer table with all of my data. There is a unique ID # for each
customer.
I have created a new table for some other data(ledger). A form for that has
customer data ID, address etc and a subform from ledger table. The new table
will have multiple entries for each customer sorted by the ID. Problem I can
not get by is when I try to create a new record for ledger table I want to
copy the ID # from form to the new record form. I have been able to get it to
display but not save to the new table. (=Forms![All Maint]!ID) All other data
entered into new ledger form is save into ledger table with the exception of
the ID#, which is blank. Can some one offer any assisatance?

 

[Ed Robichaud]

Design your tblLedger to have its own primary key, include the CustID as a
foreign key (dupes allowed), then set the relationship (1 customer to Many
ledger records). A typical form design for this would be Customer record on
the main form and related Ledger records in the subform.
-Ed

 

[gjameson via AccessMonster.com]

Sorry I di not get back sooner. I have been looking into relationships and
the foreign key. I beleive I have almost everything worked out but I still
can not get one field copied to another field/table. Is there anyway youi
could send me a simple example. I have been looking at one in my book but it
is real complicated. What I am trying to do should be pretty straight forward.


Gerald

Design your tblLedger to have its own primary key, include the CustID as a
foreign key (dupes allowed), then set the relationship (1 customer to Many
ledger records). A typical form design for this would be Customer record on
the main form and related Ledger records in the subform.
-Ed

Let me start off saying I am a newbie with Access. I know a little Access
and

[quoted text clipped - 17 lines]

of
the ID#, which is blank. Can some one offer any assisatance?

 

[Ed Robichaud]

Your tables should look something like:

tblCustomer
CustID -autonumber (or other unique value) primary key
LastName - text
FirstName
etc.

tblLedger
LedgerID -autonumber (or other unique value) primary key
CustID -long integer (to match the autonumber in tblCustomer,
otherwise same field type) foreign key duplicates allowed
LedgerDate
LedgerAmt
etc.

After creating (or converting/editing those tables), click on "Tools -
Relationships" and create a 1-many relation between the two tables. Do so
by dragging the [CustID] of one table onto the [CustID] of the other table.

Create a single record type form to show Customer data. Create a datasheet
style form to show Ledger data. Use the Toolbox wizard to add a subform to
your Customer form and select the Ledger form as the subform source. Follow
the prompts and select the Master-Child link of CustID -> CustID.

Now, when you add records to the Ledger subforms, they'll be related to just
the selected Customer. Use a query of both the Customer + Ledger tables
(previously defined relationship will already be there) as a record source
for reports. Group on CustID to show related ledger records for each
customer.

Look at the Northwinds sample database for more examples. Good luck!
-Ed

Sorry I di not get back sooner. I have been looking into relationships and
the foreign key. I beleive I have almost everything worked out but I still
can not get one field copied to another field/table. Is there anyway youi
could send me a simple example. I have been looking at one in my book but
it
is real complicated. What I am trying to do should be pretty straight
forward.


Gerald

Design your tblLedger to have its own primary key, include the CustID as a
foreign key (dupes allowed), then set the relationship (1 customer to Many
ledger records). A typical form design for this would be Customer record
on
the main form and related Ledger records in the subform.
-Ed

Let me start off saying I am a newbie with Access. I know a little
Access
and

[quoted text clipped - 17 lines]

of
the ID#, which is blank. Can some one offer any assisatance?