new to macros/events & need desperate help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SVE - 07 Jun 2005 05:59 GMT

Here's the situation. I have a form called Companies. It has a subform
called Contacts subform. It has a field called Title. This field is a limit
to list, lookup combo box with the data coming from a table called Titles and
has say 3 records: Manager, Superintendent, Foreman.

Here's what I want to do. Whe the user is inputting data and they get to
the Title field in the Contacts subform, let's say they want to type in
"Assistant Manager". Because it's not on the list it won't allow it. That's
OK. What I want to happen though, is a message to appear that asks the user
if they want to add this to the list. If they say yes, then I want the
Titles form to open up and allow the new record to be added. Once it has
been added, it needs to be available in the Contacts subform Title field drop
down list without having to close the main form and reopen it.

I hope I have explained myself clearly. Can anyone tell me step by step how
to create a macro/event to make this happen?

I appreciate any and all help you can give me.

Thanks in advance.

Reply to this Message

JASON SMITH - 07 Jun 2005 20:10 GMT

This might help.
In the event section of the drop down menu you have a "On Not In List".
Here is where you can create or assign a macro to do what you need. Such as
the Open form command. Create the Form with a message stating "Would you
like to add a Contact?". Have two buttons one for OK and one for Cancel.
Assign to each button a macro for the next step. the OK button will open
the add a Contact form and allow them to add the contact. I would suggest
this be a create record form only. Once this is complete you then need to
have button to save and close the add a Contact form. Then attach another
macro to the Contact field event "On Got Focus". This needs to be a macro
that will do a requary on a specific control. I think it's something like
"ctrRequary" The Cancel button is a secondary for the user to get if he so
desires. Attach a macro that close out the current form with no record
saved. Not much hope this helps.

Reply to this Message

JASON SMITH - 07 Jun 2005 20:24 GMT

Here is the requary VB. You just need to fill in the form name twice and
the control name once.

Private Sub form name_GotFocus()
Dim ctlList As Control

' Return Control object pointing to list box.
Set ctlList = [Forms]![form name]![control name]
' Requery source of data for list box.
ctlList.Requery

End Sub

Reply to this Message

SVE - 07 Jun 2005 21:05 GMT

Jason,

Thank you so much for your help with this. I will give a try this evening.

SVE

> Here is the requary VB. You just need to fill in the form name twice and
> the control name once.
[quoted text clipped - 8 lines]
>
> End Sub

Reply to this Message