Adding a record with NotInList property

[mrmetal]

Hello everybody,

I am a teacher who is trying to develop a simple database for my classroom book collection. I have an unbound combobox on my main form that allows me to search for titles. What I would like to do is to be able to type a title in this box, and if there isn't a match, have the database take my directly to a new record.

I've tried coding and doing it in a macro and I get the same result - an error message that says "You cannot move to the specified record." What's worse is that this message loops - I press enter and it appears again. I have to close out through my task manager.

Has anybody run into anything like this before? And is there a simple solution that I am overlooking?

- Ed

 

[KenReay]

See Forms forum FQ section

Combobox - Add New Item to List faq702-4283

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mrmetal]

Thanks for the response KenReay,

I tried everything in the FAQ, but I'm still getting the same loop. I get the message box, but when I click yes, the code to open the form doesn't work. Any other ideas?

- Ed

 

[Eupher]

Hi, Ed,

Could you post the code you are using? Maybe we can see what's going astray...

Ken S.

 

[mrmetal]

I'm going crazy trying to solve this one. It seems as though it should a simple thing to do. Here's what I'm using in the NotInList procedure:

Private Sub FindTitle_NotInList(NewData As String, Response As Integer)

If MsgBox("This book is not in database. Do you wish to add it?", vbYesNo) = vbYes Then
AddRecord_Click

Else
Exit Sub

End If

End Sub


And here's what I'm using for the AddRecord_click procedure:
Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

DoCmd.GoToRecord , , acNewRec

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click

End Sub

I appreciate any help you can give me.

- Ed

 

[Eupher]

Hi, Ed,

You're using your combo as both a lookup AND as a bound object on the same table? Looks to me like you've set up a circular reference. Here's what I *think* is happening...

1) You make an entry in the combo that's not in the list, so the NotInList event procedure fires - but the record is now dirty, because you're editing a bound control...
2) If user says "yes" to the MsgBox prompt, the code tries to move to a new record - which fires the whole BeforeUpdate, AfterUpdate sequence (navigating away from the current record WRITES changes to the table - and the combo is BOUND) - AND because the entry is still not in the list, fires the NotInList event again.
3) So you're stuck in a loop - the code can't navigate to a new record, because it first has to un-dirty the form (i.e. write changes to the table); but it can't write the changes, because the item isn't in the list yet, so we're back to NotInList again...

Standard way of handling this is to have your lookup combo be based on a table different from the recordsource of your form. Then when NotInList fires and user says "yes," open the data entry form for the lookup table, then requery the original combo when lookup table form closes.

Ken S.

 

[mrmetal]

Thanks Ken,

I think the combo is just a look-up. It doesn't store any information in any table. When I'm in Design View, it says "Unbound" in the combo box. But the row source is coming from the main form, of which the title is a piece, so maybe that's where it becomes bound?

I am a novice, so this is very confusing for me. Again, I appreciate your help. I'll play with a lookup table for the combo and see if that helps.

Any other thoughts, let me know. Thank you all.

-Ed

 

[KenReay]

Hi

Your code is nothing like that in the FAQ

see below

Adding new entries to a Combo Box based on a query or table.

Say you have a combo box with entries taken from a table or query, where you wish to limit the user to choosing entries from the list, but allow the user the option to add new entries to the list.

Assumptions:

the combo box to be added to is called cboLastName.

The form to Maintain the table (or query) on which cboLastName is based is called frmPeople.

For cboLastName,

LimitToList : Yes

In the NotInList Event put code so:


Private Sub cboLastName_NotInList(NewData As String, Response As Integer)
If MsgBox("Add to List?", vbYesNo, "Warning") = vbYes Then
DoCmd.OpenForm "frmPeople", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If
End Sub


Ie ask the user if they want to make a new entry

If yes, open the appropriate form, as a modal form, in Add record mode, and pass the entered data (NewData) to the form frmPeople

On returning from the form frmPeople, inform combo box that the data was added.

For the Form frmPeople

In the Form Open Event extract the data passes using the OpenArgs property:


Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
Else
cmdAddNew_Click
Lastname = Me.OpenArgs
End If
End Sub


Ie Extract the passed data from the ‘Sending’ form

Make a new record (using standard wizard code as shown below), and populate the new data


Private Sub cmdAddNew_Click()
On Error GoTo Err_cmdAddNew_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddNew_Click:
Exit Sub

Err_cmdAddNew_Click:
MsgBox Err.Description
Resume Exit_cmdAddNew_Click

End Sub


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Eupher]

Hi, mrmetal,

Bound or unbound, you're in a loop. You can't navigate to a new record without first clearing the NotInList situation. Ken Reay's solution should work...

Ken S.