Copying a value in one field to a field in another form 1

[drazeni]

I have a stock entry form that accepts products, if the product code entered by the user is not in the product table, it will execute the notinlist event and open up the product details form to add the new product.

I am having difficulty in copying the new product code (which doesn't exist in the current table) to the new product form to be accepted as the new product code. Can anyone help me in this respect.

Thanks.

 

[JGillespie]

If you mean that you want the value that caused the notinlist event to be automatically entered in the products form when it is opened try this:

have the NotInList event open the products form in add mode (form's DataEntry property set to true)

use the NewData argument of the NotInList event to set the corresponding fields value in the products table. Be careful if your combo box has more than one column, the value you see on screen is not necessarily what is being stored by the combo box. If this is the case you may need to run a DLookUp function to return the visible value of the combo box from the stored one.

Save the new record

Requery the original combo box when you exit the products form and set it's value to the same as the form you have just closed.

Hope it helps!!

 

[drazeni]

Thanks JGillespie, although I understand what you're telling me and I wasn't sure whether I should be using the NewData argument, you have cleared it up somewhat.

But understanding the passing of arguments was never my strong point in VBA. Could you be more specific as to the command on how to pass the value?

Is the argument "NewData" automatically populated with that fields value? If so then would it be just a matter of saying "Let [product_code] = NewData" on the new form?

Much appreciated.

 

[JGillespie]

Yes, you would set the [product_code] field's value from within the code that calls and opens the products form with a statement like:

forms!productform.product_code.text = NewData

Make sure the products form is open before setting this value though.

Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[drazeni]

I got it working. The brain teaser I have now is to requery the original form once the popup (new products) form is closed. Which event do I use to requery? According to the MS-Access help, if a popup form is called (docmd.openform) from the original form, the original form appears not to lose focus.

Thanks again.

 

[drazeni]

Hi Jamie, excuse me if it appears that I'm having a conversation with myself..... hehe

Please ignore my previous post because I have solved the problem.

I used the close event on the new product form to requery and thereafter also pasted the "NewData" value into the original field of the combo box.

Thank you very much, you've been helpful.

Regards.