Save value into new record in table 2

[NeilT123]

I have a main form frmIFFertStocks and subform FrmIfSfFertStocks linked by master/child field FarmAccountNumber.

On the main form I have an unbound combobox called cbCroppingYear.
I use the combobox to select the cropping year and this updates the data in the subform using the following query

SELECT tblfertstocks.FertStocksIndex, tblfertstocks.CroppingYear, tblfertstocks.FarmAccountNumber, tblfertstocks.ProductIndex, tblfertstocks.Stocks, tblfertstocks.Purchases, tblfertstocks.StockComment, tblfertstocks.Cost, tblfertstocks.PurchaseComment
FROM tblfertstocks
WHERE (((tblfertstocks.CroppingYear)=[forms]![frmIfFertStocks]![cbcroppingyear]));

The form is great at displaying existing records but if I try and add a new record via the form then the CroppingYear is not added to the underlying table.

Is it possible to use code to grab the value of croppingYear from the combobox and use it when a new record is added?

Also if there are any suggestions on streamlining the query or forms then please say.

Thank you for any help

Neil

 

[hondaman2003]

Do you have a field on your form for the cropping year other than this combo box?

If not, since the combo box is not bound, then nothing on the form is adding data to that table which would be exactly your problem.

 

[TheAceMan1]

How are ya NeilT123 . . .

You do have [blue]CroppingYear[/blue] in the rowsource of the subform ...

[blue]SELECT tblfertstocks.FertStocksIndex, tblfertstocks.[purple]CroppingYear[/purple], ...[/blue]

Did you forget to put [blue]CroppingYear[/blue] on the subform for editing or what?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[NeilT123]

Thank you for the responses. CroppingYear is on the subform but I don't want it to be.

I think if you have already selected CroppingYear by the combobox on the main form then it looks untidy and a bit amateurish to have CroppingYear showing on the subform and to have to manually enter the CroppingYear when creating a new record.

I would like to have CroppingYear invisible on the subform and a piece of code which selects the value in the Combobox or rowsource and enters it into CroppingYear for the new record.

Is this possible?

 

[hondaman2003]

You could add croppingyear to the subforma and hide it. But first, make it's default value point to the cropping year combo box.

Do you know how to do that?

 

[MajP]

You do not have to show the cropping year in the subform but you do have to include it as the rowsource of the subform. If it was me I would do this without code and link the subform to the main form by the farmAccountNumber and by the cbCroppingYear

linkMasterFields:farmAccountNumber;cbCroppingYear
linkChildFields:farmAccountNumber;croppingYear

no need for code or filters.

 

[NeilT123]

Thank you for the responses which started a different line of thought. I spent a couple of hours trying a few ideas and ended up changing the query behind the mainform to

SELECT tblSLTFarm.FarmAccountNumber, tblSLTFarm.AccountName, [forms]![frmIfFertStocks]![cbcroppingyear] AS CroppingYear
FROM tblSLTFarm;

and then used the
linkMasterFields:farmAccountNumber;CroppingYear
linkChildFields:farmAccountNumber;croppingYear

to select the relevant records. Easy when you know how.
A couple of stars as thanks

 

[NeilT123]

Sorry MajP I did not read your thread properly, I missed the cb before the CroppingYear in the link master fields.

I didn't know that you could link straight into the combobox, that is really easy and straightforward and it simplifies things, which I like.

Thanks

Neil