Code for Updating a Lookup List Instantly

[manix]

Hi All,

I have looked through various threads and have not found an answer to this one:

I have a couple of fields on a form that take their values from a separate table. The fields are locations. If the user cannot find the location they need in the lookup, they can click a button to add their own. Thus a new form is launched that can be completed to add to the table that contains the values for the lookup.

I have designed it this way because the likelyhood is others will want to use these locations in the future and therefore we have a complete and growing list. Now this works well, accept the list is not updated instantly and so the user cannot select the location they have just added.

I suppose there are two options:

1. Some code that updates the lookup list, the second the table (containing the look up data) is updated.

OR

2. Some code that automatically enters the users addtion into the field on the original form.

I would prefer 1. as it would be easier to manage (I think! )

Can anyone offer their input?

Thanks,

 

[ShaneMan]

Hey Manix,

I'm assuming location is a cboBox on the main form. You could use the Close event of the second form (the add form) to requery the cboBox on the first form and this should put the new location that has been added to the list.

HTH,
Shane

Hi All,

I have looked through various threads and have not found an answer to this one:

I have a couple of fields on a form that take their values from a separate table. The fields are locations. If the user cannot find the location they need in the lookup, they can click a button to add their own. Thus a new form is launched that can be completed to add to the table that contains the values for the lookup.

I have designed it this way because the likelyhood is others will want to use these locations in the future and therefore we have a complete and growing list. Now this works well, accept the list is not updated instantly and so the user cannot select the location they have just added.

I suppose there are two options:

1. Some code that updates the lookup list, the second the table (containing the look up data) is updated.

OR

2. Some code that automatically enters the users addtion into the field on the original form.

I would prefer 1. as it would be easier to manage (I think! )

Can anyone offer their input?

Thanks,

 

[manix]

Hey Manix,

I'm assuming location is a cboBox on the main form. You could use the Close event of the second form (the add form) to requery the cboBox on the first form and this should put the new location that has been added to the list.

HTH,
Shane

Hi Shane,

Thanks, I hadn't thought of it like that. I will give it a try. I am just learning about queries so this should be a good exercise. I will definately let you know how it works!

Thanks,

 

[ShaneMan]

Your welcome. Hope all goes well with your project. Let us know if we can help out any further.

Shane

Hi Shane,

Thanks, I hadn't thought of it like that. I will give it a try. I am just learning about queries so this should be a good exercise. I will definately let you know how it works!

Thanks,

 

[manix]

Umm just had a quick fiddle and I am not sure how to do what you suggest.

I can see that you can create a Macro for the On Close event, that has the requery function, but I cannot see how to direct the requery to the cmbboxes that I need it to requery!? I do not understand what the control name is?

Sorry I have no idea how to requery the cmbboxes. I do know how to relate the procedure to the Onclose part of the second form but that's about it!

 

[ShaneMan]

Ok, let's forget the Macro part cause it will better for you in the long run that you learn how to do VBA. In the second forms OnClose Event try something like this:

Forms![FormNameWhereThecboBoxIs]![cboBoxName].Requery

fill in the name of the form where I have "FormNameWhereThecboBoxIs" and the cboBoxName where I have "cboBoxName" and see if that does not add the new data to the cboBox.

HTH,
Shane

Umm just had a quick fiddle and I am not sure how to do what you suggest.

I can see that you can create a Macro for the On Close event, that has the requery function, but I cannot see how to direct the requery to the cmbboxes that I need it to requery!? I do not understand what the control name is?

Sorry I have no idea how to requery the cmbboxes. I do know how to relate the procedure to the Onclose part of the second form but that's about it!

 

[manix]

I am trying and have used that code:

Forms![frm Tool Data]![Tool Location].Requery

but now I keep getting the following error code:

Run Time Error 438:

Object doesn't support this property or method

 

[ShaneMan]

What type of control is [Tool Location]? Is it a combo Box? [frm Tool Data] is the name of the form where Tool Location is, right? Also, it's not a good idea to name your controls, forms and tables with spaces in them.

I am trying and have used that code:

Forms![frm Tool Data]![Tool Location].Requery

but now I keep getting the following error code:

Run Time Error 438:

Object doesn't support this property or method

 

[manix]

What type of control is [Tool Location]? Is it a combo Box? [frm Tool Data] is the name of the form where Tool Location is, right? Also, it's not a good idea to name your controls, forms and tables with spaces in them.

Hi Shane, thanks for the quick response. It turns out that the actual control was still called Combo58! so I changed this to Tool_Location and that requery now works a treat!!!!

Thanks so much for your input, I have learned something new!

Thanks for the tip about the naming convention. I thought this was BAD actually after I started this DB. Don't really wanna go chnaging all the names now though, as this may cause me even more headaches! But I will keep it in mind for future projects.

Thanks again, you're a legend!

 

[ShaneMan]

Your welcome and glad I could help. Also, just an FYI. Renaming your controls would not be as hard as you may imagine. You can go into any module and do Ctrl+F and this will bring up the Find dialog box. In that box you can type the name of the wrongly named control and then Click the Replace button and type in the new name for the control and then click Replace to change each instance one at a time or click Replace All to change every instance of the wrongly named control.

Hope you finish your day good,
Shane

Hi Shane, thanks for the quick response. It turns out that the actual control was still called Combo58! so I changed this to Tool_Location and that requery now works a treat!!!!

Thanks so much for your input, I have learned something new!

Thanks for the tip about the naming convention. I thought this was BAD actually after I started this DB. Don't really wanna go chnaging all the names now though, as this may cause me even more headaches! But I will keep it in mind for future projects.

Thanks again, you're a legend!