Newbie: Need my combo box to populate another field based on selection

[Beeman]

I am not extremely verse in access so please bear with me. I have created
a database with 3 tables, one for company info, account info, and
transaction info to manage my bills. I setup relationships from tables
"Company Info" -> "Account Info" -> "Transaction Info." I am trying to
create a form using the "transaction info" that I can select the company
name from a combobox and it (keeping the relationships) fills in the account
number from the account field in the "Account Info" table based on that
company in the accounts field in the "transaction info." table (used to keep
track of my bills and payments) If you require more info let me know.

I know this isn't clear but I will appreciate any help.

 

[John Vinson]

I am not extremely verse in access so please bear with me. I have created
a database with 3 tables, one for company info, account info, and
transaction info to manage my bills. I setup relationships from tables
"Company Info" -> "Account Info" -> "Transaction Info." I am trying to
create a form using the "transaction info" that I can select the company
name from a combobox and it (keeping the relationships) fills in the account
number from the account field in the "Account Info" table based on that
company in the accounts field in the "transaction info." table (used to keep
track of my bills and payments) If you require more info let me know.

I know this isn't clear but I will appreciate any help.

Use a Form based on the company table, with a Subform based on the
Account Info table; use the CompanyID (or Account Number? What's the
Primary Key of the Company Info table? Use it...) as the master/child
link field.

Make this subform a Single subform view by selecting its Default View
property; and put a subform *on the subform* for the transaction
table. Use the AccountID as the master/child link field.

Suggestion: it's safest to avoid using blanks or other special
characters in tablenames. I'd be inclined to name the tables
Companies, Accounts, and Transactions (the plural makes it clear that
this is one table for many companies).

John W. Vinson[MVP]

 

[Beeman]

First thanks for the response, and for the advice about spaces, I am going
to change them right now(I have spaces everywhere).

Here's what my relationships look like.
Company:CompanyName [1 --> Many] Account:CompanyName
Transaction:CompanyName [1 --> Many] Account:CompanyName
Transaction:AccountNumber [1 --> Many] Account:AccountNumber

here is the transaction table:
TransactionID
CompanyName
AccountNumber
AmountPaid
Date_Sent
Date_Widthdrawal
Comments
Date_Entered

What am/was hoping to do was to make a form using the Transaction table and
have CompanyName be filled in from a combo-box and when you select the
company name it populates the AccountNumber field in the transaction table
with the account number from the account table. This way I can enter the
company data from a seperate form and then add the account numbers from a
different form, and the working form will be the transaction form. *(is
there a way to make the transaction account field use a combo box just
incase I have a company that I have 2 or more accounts with ex. Verizon, 2
separate lines each with a unique account number * I might be pushing it
)

 

[John Vinson]

First thanks for the response, and for the advice about spaces, I am going
to change them right now(I have spaces everywhere).

Here's what my relationships look like.
Company:CompanyName [1 --> Many] Account:CompanyName
Transaction:CompanyName [1 --> Many] Account:CompanyName
Transaction:AccountNumber [1 --> Many] Account:AccountNumber

here is the transaction table:
TransactionID
CompanyName
AccountNumber
AmountPaid
Date_Sent
Date_Widthdrawal
Comments
Date_Entered

What am/was hoping to do was to make a form using the Transaction table and
have CompanyName be filled in from a combo-box and when you select the
company name it populates the AccountNumber field in the transaction table
with the account number from the account table.

Ummm... WHICH AccountNumber? There's a one to many relationship from
Companies to Accounts. Knowing the CompanyName does not provide the
information which you need.

I'd suggest leaving the CompanyName field OUT of the transaction table
altogether, since it can be determined uniquely from the
AccountNumber.

This way I can enter the
company data from a seperate form and then add the account numbers from a
different form, and the working form will be the transaction form. *(is
there a way to make the transaction account field use a combo box just
incase I have a company that I have 2 or more accounts with ex. Verizon, 2
separate lines each with a unique account number * I might be pushing it
)

Of course the transaction Form can use a combo box, based on the
Accounts table - in fact that's the best way to do it. You can have
the Accounts combo filtered to select only the accounts for that
company; rather than basing the combo box directly on the Accounts
table, base it on a Query using the Form control containing the
companyID or company name as a criterion:

=Forms![MyForm]![CompanyName]


John W. Vinson[MVP]