Update a Lookup table

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

Geez, I'm so frustrated I could spit! I have a form with
a combo box to select a name from a table. I have
managed to get the right event procedure working so that
I can enter a new name if it is not in the list, but when
I return to my input form the underlying table has not
been updated so I'm stuck in this "Name is not on the
list" thingy. I have looked at all sorts of messages
about this, but I cannot seem to get the right syntax or
put it in the right place!
Main form: TicketSales
Combo box on Main form: GetName
If a new name is entered then my proc opens another form
(Name) which is based on a query (SortName) which is
based on a table called Name.
So how do I get the Name table requeried or whatever so
that the new name is now present when I return to the
main form?

TIA,
Sandra
 
Make sure that your Name form uses an updateable record source. Have you
tried to just open the Name form and add a new name? Does it go into the table?
If the table does in fact get updated but you still get the "Name is not
on the list" in the TicketSales form, simply requery the TicketSales
form in the AfterUpdate event of the Name form.

Pavel
 
You need to requery the combo box. When the new form opens, TicketSales
losses its focus, when you return, it regains the focus. So you can put
Me!GetName.Requery in the OnGotFocus event of the form TicketSales.

Kelvin
 
Thanks, Kelvin, now I see how that works. I guess I'm
asking for the moon LOL but it seems to me that once you
have added the new name and come back into the main form
the user shouldn't have to go through the whole process
again and scroll down to the bottom to find the new
name. Is there a way to sort when the requery is done,
or better yet have the new value passed on to the combo
box when you re-enter? Just seems redundant to me...
I was a Paradox programmer in the old days and I'm
terribly rusty about all this, but I do remember how I
thought things otta work :-)!

Thanks for your help- Sandra
 
Instead of performing the requery in the focus event like I mentioned
earlier, modify your code to follow the sequence below.

1) Perform the check.

2) Requery the combo box on main form.
Form!TicketSales.Requery

3) Set the value of ID into combo box of main form. Since the box has
laready been requeried, this value should be valid.
Form!TicketSales!GetName = Form!Name!NameOfIdField

4) Close popup form.
DoCmd.Close acForm, "Name"

You should be returned to TicketSales and the correct ID in GetName.

Kelvin
 
Back
Top