Copy Control

[Newbie23]

Hi All,

I'm trying to develop a control button on my form 'frm_initialPro perty' that will copy a contact address (5 fields) from my tbl_customer table to my tbl_properties table.

The form shows a contact address for a customer that was input on a previous form. I would like a button to copy this data into the property address fields on the same the form. I have used the following code: (however I get an error 'You can't assign a value to this object')

    Me.Dyn_Addln1.Value = Me.PAddresLn1.Value
    Me.Dyn_Addln2.Value = Me.PAddresLn2.Value
    Me.Dyn_Addln3.Value = Me.PAddresLn3.Value
    Me.Dyn_Addln4.Value = Me.PAddresLn4.Value
    Me.Dyn_Postcode.Value = Me.PostCode.Value

Me.Dyn* being the contact address fields, Me.P* being the property address fields.

Any Ideas?

Many Thanks

[RuralGuy]

All you should need to do *at most* is put the PrimaryKey for this customer in the tbl_customer table in the Property table as a ForeignKey.

[NeoPa]

Sometimes (generally for auditing purposes) it is necessary actually to copy information. This is rare however. For simple access to the data a link is generally better. Please read Normalisation and Table structures for a fuller explanation.

If you could perhaps explain where that leaves you, we can continue if necessary.

[Newbie23]

Hi There,

I have normalised my data to 3rd NF. However I need to copy this data to a temp table that will be used for reporting. Any help with how I can create a copy control button would be great.

Thanks
Jane

[RuralGuy]

What are your plans for the temp table that can not be accomplished with a query?

[Newbie23]

I know copying the data isnt the best way to do it.. but for how big the database will be and what it will be used for it's really not an issue. I just need to know how to create a copy control?
Cheers

[NeoPa]

Jane, there are various ways.

Let's start back at your posted code. You don't say which line the error occurred on. That might help.

Your explanation also implies copying from one form to another, whereas your code indicates copying between controls on the same form. Your latest post implies it's copying between table data that's required.

I'm very unclear on exactly what you're after here. Perhaps if you continued your first method of using names for all the objects we could get a better understanding of what we're dealing with (I find that often helps to avoid confusion).

[Newbie23]

Hi,
There is one form used to input property information, when this is loaded it populates the contact address on the form. On the same form I have to add details of any properties the customer lets. I would like a control on the form that will copy the contact address to the property address. this will be used rarely, however it will make the interface a little more user friendly.

The only error I get is what I stated before but it doesnt tell me at what line it is failing!!

I know there are better ways of doing it but for how much the database will be used it's really not worth changing the design now.

Many Thanks
Jane

[NeoPa]

Ah, that seems clearer Jane.

I can't imagine that error message ever occurs without it being specific to an individual line, but you are possibly not seeing it due to selecting the abort option. If you select to debug instead, you should see one line highlighted in yellow. This is the line we're interested in.

It may help you generally to have a browse through Debugging in VBA.

I should repeat, that I see nothing wrong with your current code. When we know which object is complaining, we can look at some of the properties in more detail to see if any are set in such a way that might explain this error.

[Newbie23]

Ok well... I managed to get the debugging working and it highlighted the very first line of code. After reading the code myself I thought it would make more sense to swop the values round, so the code now looks like this:

    Me.AddressLn1 = Me.Dyn_Addln1.Value
    Me.AddressLn2 = Me.Dyn_Addln2.Value
    Me.AddressLn3 = Me.Dyn_Addln3.Value
    Me.AddressLn4 = Me.Dyn_Addln4.Value
    Me.PostCode = Me.Dyn_Postcode.Value

I dont get any errors now, however, only the very last line works (i.e. only the postcode value is copied to the other txt box. Any Ideas?

Many Thanks
Jane

[RuralGuy]

Jane,
You may be dealing with some corruption. Try importing everything into a fresh .MDB and then Compact and Repair. Then try it again.

[NeoPa]

Jane,

I noticed some of the control names were spelt differently between versions. This leads me to believe you may have a simpler compilation issue.

Please see quoted post (from a similar situation).

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

[Newbie23]

Hi,
I've compiled my project as suggested, it has sorted one or two little querks I had (so thanks for that) but nothing with regards to this problem. I also tried exporteing to a new database as suggested I got the same problem. I'm stumped, I dont get any errors but it will only copy accross the last value. Any other ideas are more than welcome.

Cheers
Jane

[NeoPa]

OK. Try posting in the whole routine that is at issue.

The name can tell us things like what control is involved etc. Don't forget the [ CODE ] tags ;)

[RuralGuy]

I think it might be time to try and post a sample of your db that demonstrates the issue. I, for one, am out of guesses.