Form Updates

[Paul Cooke]

Firstly apologies if this is posted in the wrong thread but I am not sure what area is right !

I have a form called 'Consumables' and within that a Subform called 'TreatmentConsumablesSubForm'. The purpose of this form is to record any consumables used in a specific treatment of a patient.

On the Subform I have controls related to my 'ConsumableProducts' Table
using the fk ConsumableID. I have a cbo called 'Product Code' If I type a legitimate product code into the cbo all is well.

if I type a code that does not exist the NotInList event triggers and a new form opens called 'ConsumableProductsNew'

On loading this form automically picks up a reference prefix and number (from a table called ReferenceNumber) the user enters the product and saves the form via a cmd button and all is well here to!

My problem is I need to know how to update the 'ConsumableProducts' Table
ProductCode field with the new reference prefix and number from the new product entered so when the user goes back to the original Consumables form they can then enter the new number into the product code control and it will come up. At the moment when try to enter the new product code it does noot come up but if i check the table it is showing in there.

I am assuming I need to requery the table or form but not sure how ?

any help will be gratefully recieved

Many thanks

 

[boblarson]

Which version of Access are you using?

If 2007 or 2010 you can do all of this without code.

If 2003 or previous then we have to check your code to see what you are missing - you probably don't have the
acDataErrAdded
in there which Access then knows to update the combo.

 

[Paul Cooke]

hi Bob thanks for the reply I am using 2010 but the DB is 2007 and you so right I have forgot to add the response !! duh !!

thanks again

 

[boblarson]

hi Bob thanks for the reply I am using 2010 but the DB is 2007 and you so right I have forgot to add the response !! duh !!

thanks again

Just for your future reference, with 2007 or above you don't need any code at all for this. All you need is to

1. Have a form which you would use to add the values.

2. While in Design View on the form, select the combo box to which you will want to add values.

3. Set the ListItemsEditForm property, on the DATA tab of the properties dialog, to the form you have from step 1.

4. And that's it. Limit to List has to be Yes but that's all - no code whatsoever.

 

[Paul Cooke]

firstly many thanks again for the reply and information - which has actually helped me on another problem ! but with this one it won't work because of the way the other controls are formatted.

So what I have now is an open form "consumables" with the subform "treatmentCoonsumablesSubForm". I enter a 'New' value in the combo box "cboConsumableID" the NotInList event triggers and a new form "New Product" opens (the "consumables" is still open as other data may have to be added after completing the NotInlist event).

I fill in the two text controls on the "New Product" form and save it. this form closes and I am back with the original form "consumables" ready to enter another item - this is where I stll have a problem...

Even though the New Product code and item are in the table (ConsumableProducts) when I type this code into the "cboConsumableID" on the original form it does not appear - If I close this form and reopen it the new code then works.

It is as if I need to refresh the open form somehow once the NotInList event has finished

Hopefully that makes sense !!

again any advice will be gratefully recieved thanks

 

[boblarson]

Where is the combo box located? Is it on the main form or subform?

 

[Paul Cooke]

it is on the Subform and is a bound control

 

[boblarson]

You could add a requery to the code that adds the value (I'm assuming that this code is in the combo's Not In List event for the combo on the subform):

Me.ComboNameHere.Requery

 

[Paul Cooke]

thanks again Bob I have tried the requery option on various events of the subform but it does not work.

I am assuming it wont work on the NotInlist event of the combo either as this is coded to open another form and the control that needs to be requeried is not on this form its on the Subform thats open in the background.

The issue is definately something to do with a requery or refresh on the combo though as the new data is in the table, as soon as the NotInlist form is saved and closed - its just not showing up in the combo unless I close that form and reopen it - which I can't do as I will then not have any data already entered in saved or related to the correct record

any further suggestions will be most welcome !!

I am currentyl looking at re paint?? any idea on this as I have never used it before?

Thanks again

 

[boblarson]

thanks again Bob I have tried the requery option on various events of the subform but it does not work.

I am assuming it wont work on the NotInlist event of the combo either as this is coded to open another form and the control that needs to be requeried is not on this form its on the Subform thats open in the background.

Oh, sorry I misunderstood. If it is on a different form then you would need (still in the Not in List event at the end):

Forms!YourFormNameHere.ComboNameHere.Requery

If on a subform on another form then

Forms!YourFormNameHere.YourSubformControlNameHere.Form.ComboBoxNameHere.Requery

in that case you would make sure to use the subform control name (name of the control which houses the subform on the parent form) instead of using the subform name unless the subform name and subform control name is the same.

And the .Form. part needs to be there as is.

And I forgot to add - if it is on the parent form of the subform then it would be:

Me.Parent.ComboNameHere.Requery

 

[Paul Cooke]

Ahhhh thanks Bob it now seems to be working although It wouldn't work on the NotInList event - I'm assuming it is because that event had completed as soon as the new form had opened so until I had entered some data in that form and saved it, there was nothing for the combo to be re-queried on

So I simply put your code in the on click event of the Save command button and voila !!

as below

Private Sub cmdSaveClose_Click()
Forms!Consumables.TreatmentConsumablesSubform.Form.cboConsumableID.Requery
DoCmd.Close acForm, "ConsumableProductsNew", acSaveYes
End Sub

Thank you so much for your help and patience, much appriciated - I may even get to bed before midnoght now !!

Many thanks