joining 2 fields on a form and populating into a third

[ilkington]

Hi all
Have played around with Access for quite a while.

Even after trawling the net - i cannot find the answer to this.

I know that it is not the ideal thing to do- but please humour me.

I am tryin g to create a product number based on the selected codes

There are 3 fields
Style code - 101, 102, 103
Colour Code - B10, B20, C25
Product Code - To be generated (and saved) - 101B10, 102B20 ..

So I have a form with the 3 fields

The product code uses in its control source =[Style Code] & [Colour Code]

Shows up perfectly but doesn't save

I need it to save and i know it shouldn't be done so please don't explain how i could use it in a report or query - i know - i just need to save the resultant vallue in the Product Code field in the table

Thanks

 

[Isskint]

The way i would do this is to use the AfterUpdate event of the [StyleCode] and [ColourCode] controls. EG Me![Product Code] =Me. [Style Code] & Me.[Colour Code]

This would then update the Product Code anytime the [Style Code] & [Colour Code] are changed. However you would need some checks to ensure a new record is not 'saved' unless both parts have been supplied.

Equally you could do this on the forms BeforeUpdate event.

 

[ilkington]

Thanks for the reply so promptly


I tried what you suggested and it comes back with

The expr after update produced the following resulr - The object doesn't contain the Automation object 'Me."

This is the expression I used

=[Me]![Prod Code]=[Me].[Style Code] & [Me].[Colour Code]

 

[ypma]

Try: Me.Product_Code = Me.Style_Code & Me.Colour_Code



I am uising ac2003

 

[Isskint]

Thanks for the reply so promptly


I tried what you suggested and it comes back with

The expr after update produced the following resulr - The object doesn't contain the Automation object 'Me."

This is the expression I used

=[Me]![Prod Code]=[Me].[Style Code] & [Me].[Colour Code]

Where are you using that code? the way you have typed it suggests you are using it as a control source or possibly as a function in the command buttons property box OnClick.

 

[RainLover]

I would be doing this in the Forms Module.

Private Sub etc

[Me]![Prod Code]=[Me].[Style Code] & [Me].[Colour Code]

End Sub

Note that here is no = sign at the beginning.

= does not mean equals, it means becomes.

 

[ilkington]

I have tried all but by putting in the Me. or Me! in AfterUpdate code it comes up with an error so I did it with only [Prod Code] = [Style Code] & [ Colour Code] and the value appears in Prod Code on the form.

But it doesn't save it
and also when i add a new record that value stays

I would have expected it to save once i added a new record and then clear all data


Thanks for all your patience

 

[ilkington]

Oh by the way - i am using Access 2010

 

[ilkington]

I have tried all but by putting in the Me. or Me! in AfterUpdate code it comes up with an error so I did it with only [Prod Code] = [Style Code] & [ Colour Code] and the value appears in Prod Code on the form.

But it doesn't save it
and also when i add a new record that value stays

I would have expected it to save once i added a new record and then clear all data


Thanks for all your patience

 

[ypma]

I am very new to ac2010 but have produced a demo . in the demo I have entered the code in the after update event of the Syle code field, as suggested by Isskint and the demo works and saves .Is your colour code field code BOUND ?

 

[ilkington]

Cool - thanks -

Please explain 1 thing from your sample db

The one field name in the table is:
prod Code - yet in the subroutine you specify Prod_Code

where is that variaqble (or field name coming from) - I don't see it
or maybe there is an aspect i do not understand

 

[ilkington]

Ooops

I just checked one thing

Even your sample doesn't save into the table - just displays the correct info

I need it to save into the table

 

[ypma]

I cannot explain why domo does not save the data on your PC , as it does on mine.! The demo on the last count had 9 vists ,if someone could confirm whether or not the demo is saving data or not, would be helpful in eliminating the fault .

Regarding the _ i use this to show that there is a space in the field name , you could use [] both seems to work . Not sure as to the merits of one over the other .

Regards

 

[RainLover]

A text box on a form is a Control.

A Control has a name. Call it something applicable.

The information displayed in the control is saved in a Field of a Table. The name of the field should be different to the name of the control.

As an example "Address" would be the name of the field and "txtAddress" would be the name of the Control.

In your case the Control should have in the Properties Box a Control Source. The Control source should be "Address" for the Control "txtAddress"

I am assuming this is not the case in your form.

 

[ypma]

ilkington I fully take on board Rainlover's points and will amend my demo if that would help ?

On my demo are you selecting the save icon before looking at the table ? forgive me if this is an obvious question.

Regards

 

[RainLover]

ilkington I fully take on board Rainlover's points and will amend my demo if that would help ?

On my demo are you selecting the save icon before looking at the table ? forgive me if this is an obvious question.

Regards

Looks like you have the problem in hand.

I will leave you to it.

 

[ilkington]

Thanks isskint, ympa and rain for all the help

I had the same name for control and control source changed it and all is wonderful in the land

Thanks again from Sunny South Africa