NotInList Event Question

[wilderfan]

I have created a simple database with only 2 tables: tblCorps and tblEmployees.

Each table has an autonumber primary key.

The primary key of tblCorps appears as a foreign key in tblEmployees.

There is a form frmEmployees where employees can be added. The form has a combo box which lists the available corporations which an employee might work for. The fomatting of the combo box involves two columns: the primary key of tblCorps (which is the bound column) plus CorpName. However, only the CorpName is visible to the database user. (The autonumber primary key would mean nothing to the end user).

I want the end user to be able to add a new CorpName. The frmCorps should then open up, with the new CorpName already appearing in the appropriate textbox of frmCorps. The end user can then fill in the remaining field of frmCorps. Ideally, frmCorps should then close and the user should return to the original form (that is frmEmployees).

I have attached some jpg files to help explain this db.

Thanks for any advice.

 

[boblarson]

If you are using Access 2007, you don't need any code to do this. All you need is the form that adds the new item and then you set, in the combo's "List Items Edit Form" under the data tab in the properties. Then Access 2007 takes care of it all for you.

 

[wilderfan]

We're still using Access 2003 where I work. (However, I created this small db on my home pc, using ACCESS 2007).

 

[RuralGuy]

In order for the frmEmployees to act properly you need to stop the code in that form while the frmCorps is open. You can do this by using the acDialog argument of the OpenForm command. That means code *after* the OpenForm command will not run until you close or make invisible the frmCorps form. In order to pass the NewData to the correct control you will need code in the OnLoad event of the frmCorps form.

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      '-- Being passed a new Corp Name
      Me.txtCorpName = OpenArgs
      Me.txtHQLocation.SetFocus
   End If
End Sub

 

[RuralGuy]

Of course you would remove those two lines from the 1st form but leave the Response = acDataErrAdded line.

 

[wilderfan]

Thanks, RuralGuy. It works now.

One final question:

Is there a way to close the frmCorps dialog box after assigning a value to txtHQLocation?

Normally, I would insert an AfterUpdate event for that textbox, but I wouldn't necessarily want the form to close automatically if the end user had accessed frmCorps directly. That is, I would only want automatic closure of frmCorps if it had been opened from the NotInList event of frmEmployees.

Should I use an If block to check for text in the textbox? ' Not sure if that's the best solution.

 

[RuralGuy]

Use the same:
If Not IsNull(Me.OpenArgs) Then
...as the test maybe.

 

[wilderfan]

I inserted that If block into the AfterUpdate event of the textbox txtHQLocation. frmCorps now closes automatically, but only when it was accessed from the NotInList event of frmEmployees.

Many thanks.

 

[RuralGuy]

Glad I could help. Merry Christmas.

 

[wilderfan]

Re: NotInList Event Question - Glitch Has Come to Light

A glitch has developed in closing the dialog box (for frmCorps).

If the user neglects to provide an entry for txtHQLocation, then the form doesn't close on the txtHQLocation AfterUpdate event.

So I tried using the OnExit event with this textbox and now the dialog box for frmCorps won't close.

I get a run time error #2585.

My objective is to have the dialog box close automatically after the user has exited the last textbox on the form (whether the user has input text or not).

Thanks in advance for any assistance.

 

[RuralGuy]

How many enabled controls are on the frmCorps form? Is there any other control on the form to accept the focus after the txtHQLocation control?

 

[wilderfan]

There are 3 controls on frmCorps:

txtCorpID (bound to CorpID in tblCorps); this is an autonumber & PK

txtCorpName (bound to CorpName in tblCorps); and

txtHQLocation (bound to HQLocation in tblCorps).


So in answer to your second question is no. There are no other controls after txtHQLocation on frmCorps.

 

[RuralGuy]

I'll be honest, I would think moving your code to the OnExit event of that control would have worked. Try moving it to the LostFocus event.

 

[wilderfan]

Here is a zip of the file.

Just checked the on exit event again. It definitely is resulting in an error.

 

[wilderfan]

The LostFocus event on txtHQLocation doesn't work either.

Still getting run time error #2585.

 

[boblarson]

Your problem is the

code which closes the form. Get rid of the in the ON Exit, and put a close button on the form.

 

[wilderfan]

Is there a way to have to form close automatically when the user tabs out of the last control on the form?

 

[boblarson]

Is there a way to have to form close automatically when the user tabs out of the last control on the form?

Put the close button on the form and set the button's CLICK event to do the

DoCmd.Close acForm, Me.Name, acSaveNo

(exactly as written - no changes)

And then in the got Focus event of the button put:

Me.YourButtonNameHere_Click

(changing YourButtonNameHere to whatever your command button name is)
and that should do it.

 

[boblarson]

Sorry, that won't do it. I just tested. Instead of tabbing out, they will just need to close the form with the X on the top right of the form.

 

[RuralGuy]

Try this one.