Copy and Paste Buttons

[Pauly78]

I'm trying to make a copy button and a paste button to reproduce some of the details from an existing record to a new record so i dont have to fill in the fields again if it is he same person, i have got it to work great provided there is some text in the field, i get a null error if one f the fields is null, can anyone tell me if theres something i need to put for it to ignore empty fields. The VBA code i have at present is this...
This code for the copy button....

Private Sub CopyRecord_Click()
Me.FirstName.Tag = Me.FirstName
Me.LastName.Tag = Me.LastName
Me.HouseNumber.Tag = Me.HouseNumber
Me.PostCode.Tag = Me.PostCode
Me.Telephone.Tag = Me.Telephone
Me.Mobile.Tag = Me.Mobile
Me.Email.Tag = Me.Email

This code for the paste new record button..

Private Sub PasteRecord_Click()
DoCmd.GoToRecord , , acNewRec
Me.FirstName = Me.FirstName.Tag
Me.LastName = Me.LastName.Tag
Me.HouseNumber = Me.HouseNumber.Tag
Me.PostCode = Me.PostCode.Tag
Me.Telephone = Me.Telephone.Tag
Me.Mobile = Me.Mobile.Tag
Me.Email = Me.Email.Tag
End Sub

any help greatly appreciated. Thanks

 

[sneuberg]

You can use nz which will put an empty string in the tag, so on the paste side you can use iif to insert a null. You don't want to get empty strings in your data. So on the copy it's

Me.LastName.Tag = Nz(Me.LastName)

and for paste

Me.LastName = IIf(Len(Me.LastName.Tag) > 0, Me.LastName.Tag, Null)

There's probably something terribly wrong with your table structure if you are doing this. You shouldn't be duplicating information like this. If you would share your structure with us, we could give you some recommendations,

 

[Pauly78]

Im a watch and clock repairer with a store, and I've built a database to keep all customers info. I have a table with most of the info (my main table) customers name address etc, and also the job info, eg the item they have brought in and what is required, cost etc, then I have another table which I call comments - i figured out how to make a relationship to the main table so I can store information about contact with the customer so my employees know what's going on with each job of they ever contact us. Now, I realise now that I should have tried to make a table to keep the customers items stored rather than have it on the same table as the customer info, I've already started to use this database and have about 170 entires now, some duplicated because the customer has brought more than one item or is a repeat customer, but because the Job Ref is essentially the Customer ID this is the only way I know how to do it. Can I split the main table for customer items to be on a separate table with IDs for job references and the main table I'd can be customer ID? I hope this makes sense...

 

[sneuberg]

Can I split the main table for customer items to be on a separate table with IDs for job references and the main table I'd can be customer ID? I hope this makes sense...

Yes I think you have the right idea. You can split the existing table with some make table queries. For the Customer table you will want to put the DISTINCT key word in the SQL http://www.techonthenet.com/sql/distinct.php so that you can combine the existing duplicates.

You will need to include the primary key of the Customer table in the Job References table as a foreign key. When you are done creating the tables add relationship between them.

Of course make a backup of your database before you do any of this.

 

[Pauly78]

Thanks for that, ok, instead of splitting the table i just created a new one with the item details, so i now have my main database with customer info unque customer id etc, then i created a new table for the customers items. I created a field called customers and made this the foreign key. I have checked enforce referential integrity, cascade update related fields and cascade delete related records. and join type is option 3.
Now when i try to create a new record in my jobs form i get the error 'you cannot add or change a record because a related record is required in table 'Maindatabase'

Would this be because the form ive created is not a subform? i did have it as a subform originally, but it was too big so made it open as a new tab instead and its not working now.

 

[sneuberg]

Normally this is set up with the data from the child table, i.e., the table with the customer items, in a subform. I'll assume your customer table is called Maindatabase and let's say the customer item table is call CustomerItem. If you don’t already have one create a form that’s bound to the Maindatabase and make some space in it for a subform. Then create a form bound to the CustomerItem table. This will be used for a subform. At this time you will want to decide if you want a single form presentation or continuous forms. For the later choose a tabular layout. When you are finish with this, insert a subform into your Maindatabase form. When the wizard prompts you, give it the form you made for the CustomerItem table. The wizard will prompt you for link fields. Normally its guess for these is right as it goes by the relationships you established so this should go smoothly. You should end up with a form that allow you to enter the customer information and the customer items without this problem of a related record. The linked fields handle that.
Let me know if you have any problems.