Refresh Combo Box Selection Data

Hi Experts,

I have two Forms.
An Address Data form maintains Address Data and is associated with an Address Table with a field called ADDRESS_KEY.

A Member Data form maintains Member Data and is associated with a Member Table that includes a Member Address Key field called ADDRESS_KEY.  This field uses a Combo Box to lookup / assign the desired ADDRESS_KEY from the Address Table.
This works without any problem.

On the Member Data form there is a Button to open the Address Data form to add new addresses.

The problem I have is that after adding a new address in the Address Data form and saving it, when I go to the Member Data ADDRESS_KEY Combo Box I can't 'see' the new Address Key to select it unless I close and re-open the Member Data form.  I don't want to do this.

How can I make the newly added Address available immediately in the Member Data Address Key Combo Box?

Thanks.
Bob Collison.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
for all the forms that open the Address master maintenance form, use this code format

this is called from form

"[F-10-010 - Member Master Maintenance Form - Maxi Data]"


DoCmd.OpenForm stDocName_F03100, , , stLinkCriteria_F03100, acFormAdd,,Me.name & "|" & "HOME_ADDRESS_KEY"

Open in new window



the following codes will be placed in the Address Master Maintenance form code module

Option Compare Database
Dim strForm as string, strControl as string  ' place this at the top of the module

Open in new window

'-----------------------------------

now place this codes in the load event of the form

private sub form_load()

if me.openargs & ""<> "" then

  strForm=split(Me.openargs,"|")(0)
  strControl =split(Me.openargs,"|")(1)

end if

end sub

Open in new window

' in the unload event

private sub Form_unload(cancel as integer)

Forms(strForm)(strControl).requery

end sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
..after adding a new address in the Address Data form and saving it, you requery the combo box in the Member Data form

Forms![Member Data]![ADDRESS_KEY Combo Box Name].requery


change name of form and combo box accordingly
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Thanks Capricorn1,
I have added the 'Requery' to be triggered with the GotFocus Event for the Member Data form Address Key Combo Box.  i.e. After returning from setting up the new Address in the Address Data form.  It works but only if I move off the Member Data form and come back to it.
Where should I be placing the 'Requery' code?
Thanks.  Bob C.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
Where should I be placing the 'Requery' code?

i already mentioned above, right after you added and save the address in the Address form.

place the requery in the event where you saved the address
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
The opening of the Address Form is done via a Button unrelated to the Member Data ADDRESS_KEY field.  So as you last suggested I added the 'Requery' in the Address Form as an After Update Event.  I then close the Address Form.  It doesn't work.

So at this point I have tried the above plus a Requery in the Member Data form on the Member Data ADDRESS_KEY field which is actually called SYS_MEMBER_ADDRESS_KEY via the GotFocus Event.  The Member Data form is actually a Sub-Form.

The code is as follows.
Private Sub SYS_HOME_ADDRESS_KEY_GotFocus()
On Error GoTo Err_SYS_HOME_ADDRESS_KEY_GotFocus
Forms![F-19-020 - SC - SYS Member Reconciliation Form]![F-19-024 - SC - SYS Member Reconciliation SubForm-F]![SYS_HOME_ADDRESS_KEY].Requery

Exit_SYS_HOME_ADDRESS_KEY_GotFocus:
    Exit Sub
Err_SYS_HOME_ADDRESS_KEY_GotFocus:
    MsgBox Err.DESCRIPTION
    Resume Exit_SYS_HOME_ADDRESS_KEY_GotFocus
End Sub
Thanks.  Bob C.
0
 
Rey Obrero (Capricorn1)Commented:
change this

Forms![F-19-020 - SC - SYS Member Reconciliation Form]![F-19-024 - SC - SYS Member Reconciliation SubForm-F]![SYS_HOME_ADDRESS_KEY].Requery

with

Forms![F-19-020 - SC - SYS Member Reconciliation Form]![F-19-024 - SC - SYS Member Reconciliation SubForm-F].Form![SYS_HOME_ADDRESS_KEY].Requery


and place the codes in the Address Form, use the  After Update event you mentioned abvoe


.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1
I have:
- Done the Requery in the Address Data Form After Update Event.
- Changed the SYS_HOME_ADDRESS_KEY_GotFocus to what you provided above.

When I return to the Member Data form after closing the Address Data form the cursor is loaded to the SYS_HOME_ADDRESS_KEY field.  If I click the Combo Box Selection Button at the end of the Combo Box, the Added Address is not displayed in the list.  If I click anywhere else on one of the forms, click on the SYS_HOME_ADDRESS_KEY Combo Box and the Combo Box Selection Button at the end of the box, the Added Address is displayed in the list.

It appears that when I return from the Address Data form and the cursor is loaded to the SYS_HOME_ADDRESS_KEY field and even if I click on it the GotFocus Event is not being fired.

Thanks.  Bob C.
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of your db..
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
This is a major application and therefore very large (over 200 meg).  I will have to create a cut down version containing only the critical code / data which may take me a couple of days.
Thanks.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
I have prepared a DB just for this issue.  Here is how to use it.
1.      Open it up and it will default to the Member Master Maintenance Form.  There are 3 Members in the database to play with.
2.      Select the Home Info Tab.  Note that the Address field is blank as no address has been assigned.  In actual fact there are no Addresses in the Address Master Table.
3.      Click the F-03-100 - Address Master Maintenance Form button in the bottom right.  The form to setup an address displays.  The Address Format defaults to CDN01-Canadian Urban.
4.      Tab to the Country field.  It defaults to CDN-Canadian.
5.      Tab to the Postal Code field.  Enter L7P 3M3.
6.      Tab to the Street Number field and enter a number.
7.      Tab to the Street Name field and enter a Street Name.
8.      Tab to the Street Type field and select a Street Type.
9      Tab to the Municipality field and enter a City.
10.      Tab to the Region field and select a Region (Province).  e.g. ON
11      Tab to save the record.
12.      Close the form.  The Member Maintenance Form re-displays on the Home Info Tab.
13.      Try to lookup the new address and select it.  The lookup is blank.  It needs to display and be selectable.
Thanks.  Bob Collison.
Address-Master.mdb
0
 
Rey Obrero (Capricorn1)Commented:
before we proceed,
you are opening the form "F-03-100 - Address Master Maintenance Form" to add new address, is this correct?

if the intention is to add new address, change this line


DoCmd.OpenForm stDocName_F03100, , , stLinkCriteria_F03100


with


DoCmd.OpenForm stDocName_F03100, , , stLinkCriteria_F03100, acFormAdd


try this and post back.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
You are correct.  I am opening the F-03-100 - Address Master Maintenance Form to add a new address.

I changed the code to that you suggested and I still get the same result.  i.e.  The Combo Box still doesn't display the new entry.

Thanks.  Bob Collison.
0
 
Rey Obrero (Capricorn1)Commented:
in the db you uploaded,
the name of the form that opens is
"F-10-010 - Member Master Maintenance Form - Maxi Data"

and the name of the combo box is
"HOME_ADDRESS_KEY"

and the combo box is in the main form mentioned above "F-10-010 - Member Master Maintenance Form - Maxi Data"

so to requery the combo box what you need is

Forms![F-10-010 - Member Master Maintenance Form - Maxi Data]![HOME_ADDRESS_KEY],Requery



.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
OK I need to put the Requery on 'some' Property of the Combo Box but which one?  Is it an Event for the Combo Box and if so which Event?"  Is it on some other Property?.
Thanks.  Bob Collison.
0
 
Rey Obrero (Capricorn1)Commented:
<OK I need to put the Requery on 'some' Property of the Combo Box but which one?  >

not in any event of the combo box,

place the codes in the "F-03-100 - Address Master Maintenance Form"
after you saved the record..

try using the Unload event of form "F-03-100 - Address Master Maintenance Form"

private sub Form_unload(cancel as integer)

Forms![F-10-010 - Member Master Maintenance Form - Maxi Data]![HOME_ADDRESS_KEY].Requery

end sub
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
The latest code above works!
However since it refers to a specific Parent Form [F-10-010 - Member Master Maintenance Form - Maxi Data] and Field [HOME_ADDRESS_KEY] it means that I have to have a unique version of the Child Form [F-03-100 - Address Master Maintenance Form] for each Parent Form.  Obviously not desirable since the Child Form is called from many different Parent Forms.

Is there a way to make the Parent Form Name / Field Name a variable or at least identify the Parent Form to the Child Form so that I can use something like a Case Statement in the Child Form to determine the Requery statement to use.  As you can see all of my forms have unique identifiers.  e.g. F10010 / F03100.

Thanks.  Bob Collison.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
This looks good.  I'll try to do this this weekend and let you know the results.
Thanks.
Bob Collison
0
 
Bob CollisonSystem ArchitectAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Bob_Collison's comment #a39670678

for the following reason:

This solution is simple to understand and works perfectly.  I did remove the Option to open the Called Form in Add Mode so that I could also review / delete an existing entry or delete.
Excellent Work / Solution!
0
 
Rey Obrero (Capricorn1)Commented:
.



Are you sure you want to ACCEPT your post as the solution to your problem?



.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Capricorn1,
At times I have problems with the EE interface functionality (I don't find it intuitive) and therefore don't do things correctly.  Particularly with the question closing and awarding of points.

Sorry for the incorrect update.

My intent was to:
- Advise that YOUR Work / Solution was EXCELLENT!
- Award you the maximum points (500).
- Simply note that I made a slight change in the Child Form Open Mode.
Please let me know if you need me to do anything to have this question closed and your points awarded?
Thanks. Bob Collison.
0
 
Rey Obrero (Capricorn1)Commented:
you can create a request to close the question and select the comment #a39661329 as the solution.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
I can't figure out how to close this question and award all the points to Capricorn1!
0
 
Rey Obrero (Capricorn1)Commented:
click on the Request Attention above this page,  below your original post
0
 
Bob CollisonSystem ArchitectAuthor Commented:
This solution is simple to understand and works perfectly.  I did remove the Option to open the Called Form in Add Mode so that I could also review / delete an existing entry or delete.
Excellent Work / Solution!

I can't figure out how to assign the points and close the ticket.  the interface is confusing.

Please close and assign all of the points (500) to Capricorn1 for a job exceedingly well dine.
Thanks. Bob Collison
0
 
Bob CollisonSystem ArchitectAuthor Commented:
As mentioned previously.
This was an excellent solution.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.