NotInList Help

[docmeizie]

Basically what I need to do is properly code a NotInList event procedure using Access 97. What happens is I have a combobox that when you type the name in it matches and goes to that record. Well when there is not a record in the list what I want to do is add a whole new record but apparently from looking at Access help( reads like stereo instructions sometimes) you have to code an If...Then with certain set VBA parameters. Is there a way to over ride this or would I have to use the preset???? If you are wondering what I am talking about, look up the 97 Access example on NotInList.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[PeteJohnston]

Absolutely nothing at all. It was just that the earlier comment about "A combo box displays one column" was a bit misleading, and I did say "On a slightly sideways"



PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[nakedbamboo]

KenReay,
Could you explain your code for the open event on the frmWarehouses form? I am not fluent in VB and am tying to get it to pass the entered data to the popup form and am having trouble. If you could explain these two lines and how to customize them, I would be grateful. Thanks.

cmdNew_Click
strWarehouse = Left(Me.OpenArgs, 2)

 

[KenReay]

Hi

cmdNew_Click is the onclick event of a command button (cmdNew), which is standard wizard code for Add new record, so all it does is add a new record

I took the code from an actual (working) example so

strWarehouse = Left(Me.OpenArgs, 2)

strWareHouse is the bound control which contains the column which was missing from the combo box.

I have the Left() in because the user may eneter more than the allowed number of characters, becuas eteh combo box is not bound the no error will occur, but if you try to save (say) three characters in a two character column you will get an error (you tried to paste more data .etc).

I have also made a FAQ on this subject in the Form Forum under Comboboxes.



Regards

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

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

UK

 

[PeteJohnston]

KenReay
Thanks for the idea. I've got a couple of list boxes which need similar NotInList processing but they are loaded from an SQL statment rather than just a value list so I've used the following variation on your theme

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

    Dim strSQL As String, MaxID As Long
    
    If MsgBox("Add " & NewData & " as a new town?", vbYesNo + vbQuestion, _
            "Town not in list") = vbYes Then
        NewData = UCase(Left(NewData, 1)) & Mid(NewData, 2)
        CurrentDb.Execute "INSERT INTO tblTown (Town) VALUES('" & NewData & "')"
        strSQL = "SELECT * FROM tblTown"
        cboTown.RowSource = strSQL
        Response = acDataErrAdded
    End If

End Sub

tblTown holds town names and has an autonumber key.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[KenReay]

Hi PeteJ

Yes that is OK for single column look up tables, but the example I mention (as a FAQ) is not for combos based on value lists (I very rarely use these, who wants an application that needs a code change to add a new look up value!), the example I give is for look up table based combos, with 'n' columns, so a pop up form is used to capture the 'extra' data over and above that passed in the Newdata parameter.

Regards

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

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

UK

 

[nakedbamboo]

Kenreay,

Thanks for the help. The FAQ did it for me. I was missing the Click sub function.

 

[frankparker4]

i had the same problem, try this:
Dim db As Database
Dim strSQL As String
If vbYes = MsgBox("'" & NewData & "' is not a Recognized Vendor." & vbCrLf & "Do you wish to add it to the Vendors Table?", vbQuestion + vbYesNo, " &quot Then
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO [Use Your Table Name Here] ([Use Field Name Here]) VALUES('" & NewData & "');"
db.Execute strSQL
MsgBox "Added", vbOKOnly, "thanks"
Response = acDataErrAdded
Set db = Nothing
Else
Response = acDataErrContinue
End If
Frank