Solved

Refresh Combo Box Selection Data

Posted on 2013-11-17
26
425 Views
Last Modified: 2013-11-24
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.
0
Comment
Question by:Bob_Collison
  • 14
  • 11
26 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39654553
..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
 

Author Comment

by:Bob_Collison
ID: 39654596
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39654618
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
 

Author Comment

by:Bob_Collison
ID: 39654672
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39654761
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
 

Author Comment

by:Bob_Collison
ID: 39654790
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39654820
upload a copy of your db..
0
 

Author Comment

by:Bob_Collison
ID: 39654923
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
 

Author Comment

by:Bob_Collison
ID: 39655358
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39656477
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
 

Author Comment

by:Bob_Collison
ID: 39657924
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39658303
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Bob_Collison
ID: 39658330
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39658375
<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
 

Author Comment

by:Bob_Collison
ID: 39661227
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39661329
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
 

Author Comment

by:Bob_Collison
ID: 39670678
Hi Capricorn1,
This looks good.  I'll try to do this this weekend and let you know the results.
Thanks.
Bob Collison
0
 

Author Comment

by:Bob_Collison
ID: 39671538
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39671356
.



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



.
0
 

Author Comment

by:Bob_Collison
ID: 39671365
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39671372
you can create a request to close the question and select the comment #a39661329 as the solution.
0
 

Author Comment

by:Bob_Collison
ID: 39671495
I can't figure out how to close this question and award all the points to Capricorn1!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39671510
click on the Request Attention above this page,  below your original post
0
 

Author Comment

by:Bob_Collison
ID: 39671539
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
 

Author Closing Comment

by:Bob_Collison
ID: 39673376
As mentioned previously.
This was an excellent solution.
Thanks.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now