Pulling info into a form based on account number

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

Guest

I have a master database of all of our subscribers with account number as the
key. What I am wanting to do is track customer response to marketing
campaigns. I have another table set up with Account Number, Date and Campaign
Code. What I'd like to do is have a form that all you do is enter the account
number and all of the customers relevant information from the master database
(address, phone, etc.) populates the form. Each customer can respond to
multiple offers so the account number may occur more than once in the second
table. I'm sure that I can do this, I just don't know where to start.
 
tblSubscribers (parent table) has a one-to-many relationship with tblOffers
(child table). one subscriber may respond to many offers. in tblSubscribers,
the AccountNumber field is the primary key field. in tblOffers, the
AccountNumber field is the foreign key field. (you *cannot* use
AccountNumber as the primary key of tblOffers, of course.) make sure you set
the relationship between the tables in the Relationships window, on the
AccountNumber field which is in both tables, and enforce referential
integrity.

create a form bound to tblSubscribers (frmSubscribers), set to Single Form
view; and another form bound to tblOffers (frmOffers), you can set the view
to Continuous Forms, Datasheet, or Single Form - whatever suits you. in
frmSubscribers, add a subform control. set the control's SourceObject
property to frmOffers, and the ChildLinkFields and MasterLinkFields
properties to AccountNumber.

add an unbound combo box to the mainform's header to "find" a record by
entering an AccountNumber. the easiest way to do this is by using the combo
box wizard to create the combo box.

now you can open frmSubscribers, find any existing account record by
entering the AccountNumber in the combo box, and enter as many Offers as you
want for that account, in the subform.

hth
 
Back
Top