Command buttons - Access 2003

M

Mary Ann

I have a database which has a contacts table and a
companies table with a one to many relationship between
contacts and companies. The database has been used so
far to send out mailshots to prospective subscribers to a
magazine.

I have a contacts form which includes a combo box to show
the contact's company. The combo box record source is a
query containing the company name and some address
fields - this is necessary because there are some
companies with the same name but different addresses.

I also have a companies form with a sub form containing
contact details.

My problem arises when I need to change the contact's
company. A contact phones to tell me they have changed
companies, I go to the contacts form, find the contact
and select the new company from the company combo list.
This is fine when the company already exists.

When the company does not exist yet, I need to create a
new company when the person is on the phone and then
update their record. I created a command button on the
contacts form, using the Wizard, which opens the
companies form so I can create a new company. I then
close the companies form and am returned to the contacts
form showing the correct person, but the companies combo
does not include the new company. I have tried including
a command button on the contacts form which "Refreshes
form data" but this does not make any difference.

If I close the contacts form and reopen it the newly
created company is present in the list - but I don't want
to have to do this as I am then not looking at the
correct contact record.

If anyone can help I would be most grateful. I am a
strongly competent user of Access but I don't write
Visual Basic.

Regards

Mary Ann
 
N

Nikos Yannacopoulos

Mary Ann,

I'll assume the name cboCompanies for the combo on your Contacts form, and
you'll have to change to the actual name. All you need to do is add a single
line of code to the form's On Got Focus event:

Me.cboCompanies.Requery

so when you close the Companies form and focus is set back to the Contacts
form, the combo is requeried and the newly added record is read.

HTH,
Nikos
 
M

Mary Ann

Nikos - Thank you very much for your reply - but it still
doesn't work for me! I named my companies Combo on the
contacts form to the name you used, just to make it
straightforward. Then I selected the form (top left tiny
box) and in Properties on the Event tab on the On Got
Focus line typed
Me.cboCompanies.Requery
I then closed and saved the form.

I went through the process I previously described to add
a new company and it still wasn't in the Combo list. I
tried clicking on the form background before selecting
the combo drop down as well as going straight to the
combo.

Do I have to do anything special to give the form focus?

Any thoughts you have about what is not going right would
be greatly appreciated

Thanks and ever hopeful

Mary Ann
 
N

Nikos Yannacopoulos

Mary Ann,

Sorry about this, there are two problems:

First, wrong event: use event On Activate instead; I tested it, and found
out that Got Focus doesn't fire like I thought it did - which is actually
why you did not get an error message having typed the line of code in the
event property itself! This is the second problem, I should have been more
detailed in my directions:
The line of code I gave you is not to be typed in the event property itself,
it must be typed in the code procedure of the event. To do that, put the
cursor in the On Activate property's box and click on the tiny button with
the three dots on the right. Select Code Builder; you will be transferred to
the VB editor screen, and the cursor will be between two lines that look
something like:

Private Sub Form_Activate()

End Sub

Type/paste the code in between these lines, and close the VB editor window
to return to your form. Don't forget to clear the wrong entry from the On
Got Focus property. It should work now.

Hope it works this time,
Nikos
 
Top