vba vs macro??

T

tmcavilee

Thank you in advance to anyone who can offer some assistance. I have a
database with a main table and buttons on that main table that open 4
additional tables. I need to have the customer id# update from the
main form when the additional forms are opened and at this point i'm
stumped as to how to achieve this result. Do I program a macro?? write
some code?? or what? Please help!!
 
B

BruceM

What do you mean when you say you have buttons on a table? Sounds like you
made a form based on the table. If the other forms are based on tables that
contain data related in some way to the main table's data, and if you have
that relationship properly set up and defined, you should be able to base
the main form on the main table, and subforms on the related tables, without
the need to copy CustomerID from one to the other. You will need to
describe the database, and the real-world situation, in more detail before a
meaningful response is possible.
 
T

tami

What do you mean when you say you have buttons on a table? Sounds like you
made a form based on the table. If the other forms are based on tables that
contain data related in some way to the main table's data, and if you have
that relationship properly set up and defined, you should be able to base
the main form on the main table, and subforms on the related tables, without
the need to copy CustomerID from one to the other. You will need to
describe the database, and the real-world situation, in more detail before a
meaningful response is possible.






- Show quoted text -
Click to expand...

sorry, yes I have a main "form" with 4 buttons linking to related
forms. I have designated a relationship between them but when I
navigate from the main form to the additional forms the customer id
does not follow me. Make sense??? It is a customer database. On the
main form I have the customer information, and the linking forms
contain financing information, park model information, marketing
information, and customer follow up information. really the only link
between them will be the customer id# but it needs to be constant
throughout the record.
 
B

BruceM

tami said:
sorry, yes I have a main "form" with 4 buttons linking to related
forms. I have designated a relationship between them but when I
navigate from the main form to the additional forms the customer id
does not follow me. Make sense??? It is a customer database. On the
main form I have the customer information, and the linking forms
contain financing information, park model information, marketing
information, and customer follow up information. really the only link
between them will be the customer id# but it needs to be constant
throughout the record.
Click to expand...

You should have a table structure something like this:

tblCustomer
CustomerID (primary key, or PK)
CustName
CustAddress
etc.

tblFinancing
FinanceID (PK)
CustomerID (foreign key, or FK)
FinanceText
etc.

There will also be tblParkModel, tblMarketingInfo, and tblFollowUp, set up
similarly to tblFinancing. Considering just tblFinancing for now, use the
Relationships window to create a relationship between CustomerID in
tblCustomer and CustomerID in tblFinancing. Click Enforce Referential
Integrity.

Build a form (frmCustomer) based on tblCustomer, and another (fsubFinancing)
based on tblFinancing. With frmCustomer open in design view, drag the icon
for fsubFinancing onto it. Click the subform control once to select it, and
click View > Properties. The property sheet will say subform/subreport at
the top. If not, click elsewhere on the form, then click the subform again
as described. On the property sheet's Data tab, be sure that both Link
Field properties (Parent and Child) show CustomerID. If they do not, click
the Link Child Fields (or Link Parent Fields) property and click the three
dots on the right. It will probably suggest the correct fields.

Repeat for the other three linked forms. This assumes that all Financing
information, etc. is contained in a single table. If there are to be
multiple items related to Financing (a series of payments, for instance)
there should be a FinancingDetails table. But for now, the above should
establish the relationships and the linked fields, so that all you need to
do is type information into the form and subform.
 
T

tami

You should have a table structure something like this:

tblCustomer
CustomerID (primary key, or PK)
CustName
CustAddress
etc.

tblFinancing
FinanceID (PK)
CustomerID (foreign key, or FK)
FinanceText
etc.

There will also be tblParkModel, tblMarketingInfo, and tblFollowUp, set up
similarly to tblFinancing. Considering just tblFinancing for now, use the
Relationships window to create a relationship between CustomerID in
tblCustomer and CustomerID in tblFinancing. Click Enforce Referential
Integrity.

Build a form (frmCustomer) based on tblCustomer, and another (fsubFinancing)
based on tblFinancing. With frmCustomer open in design view, drag the icon
for fsubFinancing onto it. Click the subform control once to select it, and
click View > Properties. The property sheet will say subform/subreport at
the top. If not, click elsewhere on the form, then click the subform again
as described. On the property sheet's Data tab, be sure that both Link
Field properties (Parent and Child) show CustomerID. If they do not, click
the Link Child Fields (or Link Parent Fields) property and click the three
dots on the right. It will probably suggest the correct fields.

Repeat for the other three linked forms. This assumes that all Financing
information, etc. is contained in a single table. If there are to be
multiple items related to Financing (a series of payments, for instance)
there should be a FinancingDetails table. But for now, the above should
establish the relationships and the linked fields, so that all you need to
do is type information into the form and subform.- Hide quoted text -

- Show quoted text -
Click to expand...

It lists it as a form - not a subform - did I do something wrong?????
What do I need to change?
 
B

BruceM

tami said:
It lists it as a form - not a subform - did I do something wrong?????
What do I need to change?
Click to expand...
I expect you'll be just fine. A subform is a form contained in a subform
control on another form. A subform control is a sort of box on one form for
holding another form. When you drag the form's icon onto the main form it
is automatically "installed" in a subform control. There are other ways to
do that, by the way. You could add the subform control from the toolbox
while the wizard button is highlighted, and a wizard will attempt to guide
you through the process. I haven't used the wizard, so I couldn't say how
well it works. You could also use the toolbox, but skip the wizard. In any
case, the form that you use as a subform is still just a form, and will be
displayed as such in the database window. There is no separate listing of
subforms.
 
Top