Setting a value on an open form From a pop up form

[Dinnydannydong]

Hi,

I have a NewOrders form on my database.

If I have a new customer I have created a pop up form and I fill in the new customer details including CustomerID and then I close this form and return to the NewOrders form. What I would like to happen is the new customer be then entered in the CustomerID field on the NewOrders form. This field is set up as Combo Box displaying the Customer name in a query set up in the Order Table design. I hope this makes sense and also thanks in advance for any help

Martin

[jimatqsi]

In the pop up form after you have the customer ID you can put something like

 Forms!orderformname!CustomerID=[popupformscustomeridfield]

You would put that in the Close event of the pop up form, or maybe in the after update event of the customer ID field in the pop up. After you have the ID you just place in on the other form.

Jim

[Dinnydannydong]

Hi, firstly I apologise in the late reply I have been away. I have tried this code and it works as far as when you go back into the main Order Form (NewOrders)it is in the drop down list,so thank you. But I was hoping to have it automatically put in the field so you do not have to scroll for it.

[NeoPa]

For that you should set the value of the ComboBox to the value of the new customer. That will select that value in the list for you.

[Dinnydannydong]

Hi NeoPa,

think we could be on different time zones, but thanks again for your prompt reply. Your solution looks to be the answer but could you go into a bit more detail in how I achieve this. I have to be careful as I will not always have a new customer. Thanks in advance for any help

[zmbd]

OK
The basics:

Say you have the combobox with the
named: ctrlCBOcustomer
rowsource:

SELECT [CustomerID], [CustomerName]
FROM tblCustomer;

The bound column: is #1 ([CustomerID])
The widiths set as: "0, 2"
This way the value from the [CustomerID] is hidden.
Form is named "frm_exampl e"

Once your pop-up form adds the new record to the tblCustomer, you need to:
-get the value for [CustomerID]=new_customer_r ecord:
-Requery the control to add the new customer:
forms!frm_examp le.ctrlCBOcusto mer.requery
(NOTE: Depending on how you have the form designed, you may also have to requery the form... in which case, you will need additional code to reset the form to the correct record or to restore the data entries for the new record.)
-set the value of ctrlCBOcustomer = [CustomerID]=new_customer_r ecord
forms!frm_examp le.ctrlCBOcusto mer.value =new_customer_r ecord

I would also include code to double check that the main form was open etc...

I leave the details for you. Post your code if you have any issues.

[Dinnydannydong]

Hi zmbd,

thank you for jumping in on this enquiry I appreciate your input.
I have wrote the following code from your information and guidance.
forms!NewOrders .CustomerID.req uery

forms!NewOrders .CustomerID.val ue= =new_customer_r ecord

The issues I have now is the place to put it in the properties of the combo box and also in the last line of code I have 2 equals sign and what is new_customer_re cord is that access code.

Thanks for any help warm wishes

Martin

[NeoPa]

TBF I suspect my response was simply filling in a small gap in Jim's fuller answer.

In answer to how you would go about doing what I suggested Z has provided you with the code for that in his latest post.

Good luck :-)

[NeoPa]

Ok. I don't know why, but your latest post wasn't visible when I did my last reply.

Firstly, if you have two equals (=) characters in your code then, and I hope this doesn't sound to obvious but, remove one of them. A VBA assignment line is of the form X = Y.

The reference to new_customer_re cord is simply a reference to the CustomerID value that you've just got from your customer details form (See your own explanation in post #1).

[zmbd]

Thank You Neopa for clarifing things. I had to step out for a meeting first thing this morning (Z_z)

Dinnydannydong
: Yes indeed, the double equal sign was a typo (well, a victim of the ole-cut-n-paste) and is VBA code. I had thought that your pop-up form would have a [Save] button that would run the code to close the form. You would insert the logic I provided within that code. HOWEVER, if you used the wizard in ACC2010 (or have not set the default to VBA in ACC2007) then you will have an embedded Macro language in the button's on-click event. You need to convert that to VBA and then insert the logic into the converted code.

[Dinnydannydong]

Hi,

NeoPa and zmbd, you two are the guys. Thanks for all the advice, I am returning to access after a 4 year break designing websites so still rusty but appreciate all your Information and Guidance and the way you delivered it.

Warm wishes

martin