NotInList adding new entry as a new record each time

[robingSA]

Hi...I have a number of Combo Boxes on a form. If one types a name
which is not in the table, I want it to add the typed, new item. I am
using the following code to do this and it works well, except for one
thing - each new item is added as a new record. I want the new name to
be added after the last entry in the relevant field...is this possible
- preferably by tweaking the code I am using already?

Private Sub Supermarket_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Businesses", dbOpenDynaset)
Rs.AddNew
Rs![Supermarket] = NewData
Rs.Update
Response = acDataErrAdded
End If
End Sub

Thanks...Rob

 

[Douglas J Steele]

I don't understand what you mean by "added after the last entry in the
relevant field".

Your combo box is presumably based on your Businesses table. If what you've
typed isn't already in the Businesses table, you have to add it as a record
to the Businesses table in order to continue.

Are you complaining about where in the list the new data appears? Never make
any assumptions about the order in which data is returned unless you're
using a query with an appropriate ORDER BY clause. If you've based your
combo box on a query with the appropriate ORDER BY clause and the data still
isn't coming back in the order you expect, post back with more details.

 

[robingSA]

Hi Doug,

The Row Source for my combo box is as follows:
SELECT [Businesses].[Supermarket] FROM Businesses;

I don't care where in the list the new entry appears (although
alphabetical would be good). What is happening is this - let's say I
have 4 combo boxes all using the select statement above with different
fields obviously. The Businesses table consists of these 4 fields. If I
enter a new item in Combo box A, it places the new entry in row 1 under
Field Aof the Businesses table. If I then enter a new item in Combo box
B, it places the new entry in row 2 under Field B...and so on... I
would like the entry in box B in this example to go in row 1 as well.
The way it is now, I am getting a lot of blanks in the combo boxes.

Hope this makes it a little clearer!

 

[Douglas J Steele]

So you've got 4 different combo boxes all based on the same Businesses
table. However, in the NotInList code you posted previously, the only value
you're adding to the Businesses table is the Supermarket, so the row
corresponding to that new supermarket is going to have Nulls (or default
values) for all of the other fields.

What you might need to do is rather than opening the recordset and adding
NewData to it is open a maintenance form that will let the user add all of
the fields associated with that supermarket. Open it with the WindowMode set
to acDialog, so that they must deal with the form before they can continue.
Alternatively, prompt them for the other values, and insert all of them into
the table, not just the Supermarket value.