By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

copying records in a form

P: 25
Hi All.

Was wondering if anyone could help me with this. Currently I have a form that creates customer details (Account Number, Name, Contact Address etc) when I hit the 'Next' cmd button it takes me to a property form. In the property form the user has to add a property address for the customer. The property address may or may not be the same as the contact address.

So currently when I load the property form the account number is carried over and populates the record for the property table. However I would also like the contact address (that was added in the previous form) to appear and the user to be able to populate the property address via a button if the two address are the same.

Hope that makes sense. : )
Any help would be fantastic, thanks all!!
Feb 19 '09 #1
Share this Question
Share on Google+
5 Replies


DonRayner
Expert 100+
P: 489
I hate to tell you this but you're heading down the wrong path with your database. You realy should take a look at Database Normalization and Table Structures before you continue any further.

This will save you many many headaches down the road as you further develop your application.

After you have checked out the link, please post back any questions that you may have and we will do our best to help you along.

Welcome to Bytes

Don
Feb 20 '09 #2

P: 25
Hi,
I have normalised the database and drawn up ERD's. I understand you have to prevent data duplication. However a customer may have many different properties (as well as a contact address). This is a one to many relationship that requires each property to have a seperate record, as this eventually leads to a servicing plan set up on this particular property. The contact address and property address are different data elements.

What I want to do on the form is provide the user with an option to copy the contact address info to the property table if the two are the same address. perhaps by a button?

Jane
Feb 20 '09 #3

DonRayner
Expert 100+
P: 489
Personally I would do this with two tables. One for Customers and one for Addresses. In the Addresses table add a boolean field for Primary Address and link the two tables by adding a Customer Field as a foren key to the addresses table.

Expand|Select|Wrap|Line Numbers
  1. CustomerTable
  2.  
  3. CustomerID.......... Primary Key
  4. CustomerName
  5. CustomerPhone
  6. CustomerFax
  7. CustomerEmail
  8. and so on.
  9.  
  10. Addresses Table
  11.  
  12. AddressID................Primary Key
  13. CustomerID..............Foren Key from Customers Table
  14. AddressLine1
  15. AddressLine2
  16. City
  17. State
  18. PrimaryAddress.......(Boolean yes/no)
But if you still insist on entering the address twice, you can accomplish what you want in VBA by using either a DAO/ADO recordset or Insert Query SQL statement. Either one, if you want help with it you will have to provide us with the Form and associated control names and the field names in the table you want updated.

I do hope that this helps.

Don
Feb 20 '09 #4

P: 25
Hi,
I do have two seperate tables a customer table and a properties table (one to many relationship) as shown below:

tbl_Customers
*CustID
CustName
CustPhone
CustConAddressLine1
CustConAddressLine2
CustConAddressLine3
CustConPostcode
and so on

tbl_Properties
*PropID
CustID
PropAddressLn1
PropAddressLn2
PropAddressLn3
PropPostcode
Tenancy

If I do use the boolean field as suggested and the property address is not the primary address, I would still need to store this somewhere? I cant send landlord information to tenants.

When the user is asked to add a property address the customer record is already saved. I tried to attach the form buts im having problems with this at the minute. The property form shows all fields from tbl_properties (except the PK and FK are greyed out as these are populated automatically). Also I would like to also have on contact address on there to (readonly). I've had play around with both SQL, unbound txt boxes & VB methods for this but cant quite seem to fit the peices together!

Many thanks for the help
Jane
Feb 21 '09 #5

FishVal
Expert 2.5K+
P: 2,653
Hello, Jane.

There is no need to duplicate contact address in properties table as it could be easily fetched dynamically when need in a query like the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. Nz(tbl_Properties.PropAddressLn1, tbl_Customers.CustConAddressLine1),
  3. Nz(tbl_Properties.PropAddressLn2, tbl_Customers.CustConAddressLine2)
  4. Nz(tbl_Properties.PropAddressLn3, tbl_Customers.CustConAddressLine3)
  5. FROM tbl_Customers LEFT JOIN tbl_Properties
  6. ON tbl_Customers.CustID=tbl_Properties.CustID;
  7.  
Feb 21 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.