Refreshing Combo Box

[JK]

Another one I cannot figure out

When I add a record that affect the list in a combo box, understandably that
the combo box is not updated until I manually refresh it in some way,
usually by sending the F9 key. Is there a way to automate it?

Example:
Form Invoice (invoice header - name, address, date etc)
Subform Invoice Details
Product_ID (Combo), Quantity, Price, Line Total (calculated) etc.

On occasions a product has to be created during invoicing. a Double click on
the combo box (or by other means) will open the Product Setup Form and a new
product is added. when the Product form is closed the new product does not
appear in the Product_ID list (in the Invoice Details) unless the F9 key is
press.

I tried OnGotFocus event (Requery) but that would only work if I get out of
the combo box and come back into it. I suspect that I'm using the wrong
event /Action but can't figure out the right way

Any Idea?

Regards/JK

 

[Guest]

from Hong Kong, RainboxSix

you can use AfterInsert event to requery your sub-form or combox
this event will activate after a new record was added.

"JK" 來函：

 

[JK]

Thanks Rainbow

If I read you correctly, I can Requery the Invoke Details from the Product
Setup form on the AfterInsert event asf:

Forms("Invoice")("Invoice Details")("Product_ID").Requery

However, The Product Setup form may be opened independently of the Invoice
form (Invoice From is not open) for general maintenance of products, in
which case I will get an error. This means that I have test that Invoice
form is open first and then Requery it only if its open.

This approach is not always feasible as other forms may also open the
Products forms which make programming somewhat cumbersome. Bearing in mind
that this was only an example - I have the same problem with other forms
such as adding Entities (Customers,Suppliers,Institutions etc) when
invoicing or adding suppliers invoices, Posting to Journals when I need to
create an Entity on the run.

Ideally I prefer to Requery or take another appropriate action within the
Invoice / Invoice details (on this example) if at all possible.
I hope this make sense.

Regards

 

[j_beverly]

(Caution - amateur opinion)
I'm not clear on all the details, but maybe you could set the After
Insert event on your Product Setup Form to something like :

Forms!FormInvoiceDetails!ComboProductID.RowSource =
"SELECT....(repeat you rowsource query here) FROM [TableName];"

(use the correct form/field/table names, of course)


Jeff Beverly
=========

 

[JK]

Thanks Jeff

1. Your reference "Forms!FormInvoiceDetails!ComboProductID.RowSource" is
incorrect syntax because the main form has to be specified, the correct
syntax :

Forms!Invoice!InvoiceDetails!ComboProductID.RowSource = etc which is an
alternative to my pervious reference.

2. The problem is that, if I use that approach, the form "Invoice" and its
subform "InvoiceDetails" must be open when a new product is added in the
Form "Product" which is not alway the case when the user add product. If
product is added when the Invoice form is not open the user will get an
error.

I am looking for some way of refreshing the combo box in the invoice
subform. If that is not possible I may have to "tell" the Product form how
it was opened using OpenArgs or adding a hidden text box say, OpenBy, and
post the form name (Invoice) into it when I open it in order to test how it
was open and then refresh the Combo box OnClose event of the Product Form -
I'm trying, if possible to avoid that.

Regards/JK

(Caution - amateur opinion)
I'm not clear on all the details, but maybe you could set the After
Insert event on your Product Setup Form to something like :

Forms!FormInvoiceDetails!ComboProductID.RowSource =
"SELECT....(repeat you rowsource query here) FROM [TableName];"

(use the correct form/field/table names, of course)


Jeff Beverly
=========

Thanks Rainbow

If I read you correctly, I can Requery the Invoke Details from the Product
Setup form on the AfterInsert event asf:

Forms("Invoice")("Invoice Details")("Product_ID").Requery

However, The Product Setup form may be opened independently of the Invoice
form (Invoice From is not open) for general maintenance of products, in
which case I will get an error. This means that I have test that Invoice
form is open first and then Requery it only if its open.

This approach is not always feasible as other forms may also open the
Products forms which make programming somewhat cumbersome. Bearing in mind
that this was only an example - I have the same problem with other forms
such as adding Entities (Customers,Suppliers,Institutions etc) when
invoicing or adding suppliers invoices, Posting to Journals when I need to
create an Entity on the run.

Ideally I prefer to Requery or take another appropriate action within the
Invoice / Invoice details (on this example) if at all possible.
I hope this make sense.

Regards

 

[j_beverly]

2. The problem is that, if I use that approach, the form "Invoice" and its
subform "InvoiceDetails" must be open when a new product is added in the
Form "Product" which is not alway the case when the user add product. If
product is added when the Invoice form is not open the user will get an
error.

Okay, 1 more idea. Could you use this approach?

If CurrentProject.AllForms{FormName).Isloaded Then

Forms!Invoice!InvoiceDetails!ComboProductID.RowSource = ...

End if

 

[JK]

Excellent Jeff, works perfectly. Goes to show, you learn something every
day, I can use that for other purposes
FYG, as the row source does not change a Requery is sufficient.

Thanks a lot/JK