Combo/Textbox requery

  • Thread starter Geezer via AccessMonster.com
  • Start date
G

Geezer via AccessMonster.com

This should be simple, but I can't find an answer in previous threads.

I have a form [Select a Patient] with a combobox [Combo10] from which a
patient record is selected. [Combo10] gets it's information from a query to
display names and the patient [ID]. [ID] is the bound column. There are a
number of command buttons that when clicked go to forms bound to specific
tables with various information related to the selected patient.
(Demographic info, history, diet, etc.) I have the AfterUpdate event of
[Combo10] coded to make the command buttons visible or not depending on
whether the selected ID in [Combo10] exists in the various "linked tables"
using DLookup as such:

In [Text66] the Control Source is

=NZ(DLookUp("[ID]","Aspirations_Family_History","[ID] = " & [Forms]![Select a
Patient]![Combo10].[Column](3)),-1)

[Combo10]s AfterUpdate in this case looks at [Text66] and if the value is "-
1" then the command button for [Aspirations_Family_History_Add] form is
visible, but if the value of [Text66] is not "-1" then the command button for
[Aspirations_Family_History_Edit] form becomes visible. This all works fine.
If the Add form command button is visible and clicked a simple Add form opens,
in Dialog Mode, into which only the [ID] is entered so it is added to the
underlying table.

The problem is when I close the [Aspirations_Family_History_Add] form, from
Dialog Mode, I want the Add command button to become invisible and the Edit
command button to become visible, since the ID is now in the
[Aspirations_Family_History] table. (I have verified that the ID is being
added to the table.)

I've tried requerying [Combo10] and [Text66] but that apparently doesn't fire
the AfterUpdate event for [Combo10]. I've tried requerying the entire
[Select a Patient] form, no luck. Any suggestions how I can accomplish my
goal of closing the form [Aspirations_Family_History_Add] thus returning to
[Select a Patient] form and having the command button related to editing
become visible, based on [Text66]s DLookup Control Source returning a non "-
1" for the newly entered record in the [Aspirations_Family_History] table?

I hope this makes sense and thanks ahead of time for any help.
 
J

Jeff Boyce

One approach might be to put a spot of code in the OnClose event of the
opened-form. That code would explicitly point to the [Select a Patient]
form and set the control's visible property. You can explicitly point to a
control on another form using something like:
Forms![Select a Patient]![Aspirations_Family_History_Add].Visible = True
This is untested aircode, though, and you might have to first set the focus
to the form...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Geezer via AccessMonster.com said:
This should be simple, but I can't find an answer in previous threads.

I have a form [Select a Patient] with a combobox [Combo10] from which a
patient record is selected. [Combo10] gets it's information from a query to
display names and the patient [ID]. [ID] is the bound column. There are a
number of command buttons that when clicked go to forms bound to specific
tables with various information related to the selected patient.
(Demographic info, history, diet, etc.) I have the AfterUpdate event of
[Combo10] coded to make the command buttons visible or not depending on
whether the selected ID in [Combo10] exists in the various "linked tables"
using DLookup as such:

In [Text66] the Control Source is

=NZ(DLookUp("[ID]","Aspirations_Family_History","[ID] = " & [Forms]![Select a
Patient]![Combo10].[Column](3)),-1)

[Combo10]s AfterUpdate in this case looks at [Text66] and if the value is "-
1" then the command button for [Aspirations_Family_History_Add] form is
visible, but if the value of [Text66] is not "-1" then the command button for
[Aspirations_Family_History_Edit] form becomes visible. This all works fine.
If the Add form command button is visible and clicked a simple Add form opens,
in Dialog Mode, into which only the [ID] is entered so it is added to the
underlying table.

The problem is when I close the [Aspirations_Family_History_Add] form, from
Dialog Mode, I want the Add command button to become invisible and the Edit
command button to become visible, since the ID is now in the
[Aspirations_Family_History] table. (I have verified that the ID is being
added to the table.)

I've tried requerying [Combo10] and [Text66] but that apparently doesn't fire
the AfterUpdate event for [Combo10]. I've tried requerying the entire
[Select a Patient] form, no luck. Any suggestions how I can accomplish my
goal of closing the form [Aspirations_Family_History_Add] thus returning to
[Select a Patient] form and having the command button related to editing
become visible, based on [Text66]s DLookup Control Source returning a non "-
1" for the newly entered record in the [Aspirations_Family_History] table?

I hope this makes sense and thanks ahead of time for any help.
 
G

Geezer via AccessMonster.com

Thanks Jeff. Trying just to set visibility did not work, but setting
visibility and then setting focus to the button did. This board is a life
saver! Thanks for your help.

Jeff said:
One approach might be to put a spot of code in the OnClose event of the
opened-form. That code would explicitly point to the [Select a Patient]
form and set the control's visible property. You can explicitly point to a
control on another form using something like:
Forms![Select a Patient]![Aspirations_Family_History_Add].Visible = True
This is untested aircode, though, and you might have to first set the focus
to the form...
This should be simple, but I can't find an answer in previous threads.
[quoted text clipped - 36 lines]
I hope this makes sense and thanks ahead of time for any help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top