on not in list code prob

[nevstic]

Greetings all
would you kindly take a look at the following code and maybe tell me what is wrong.
I am trying to add a name to the underlying table if what I type is not on the Combo list.

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

appreciate any help on this
Rgds
Nick

 

[ITMannie]

Nick

I tried out your code and it worked fine (after I added an End If at the bottom of the code)

The table I tried it on only had one field (AEName). Do you have any other required fields that need a value when entering new records i.e. a primary key field that is not an AutoNumber.

What errors are you getting with it?

 

[nevstic]

Thanks for your quick reply ITmannie
The strange thing is that I am not getting anything !
There is only one field in the Underlying table and there is no Autonumber on this.
It is just ignoring the code when I leave the Combo box.
baffling for a Sunday eh?

Any other thoughts would be most appreciated

V best rgds
Nick

 

[ITMannie]

Check that the Limit To List property of the combo box is set to Yes

If that doesn't solve it then check that the On Not In List property has [Event Procedure] in it (that can sometimes be missing).

Failing that, set up a breakpoint and go through the code step by step.

Let me know how you get on

Cheers

 

[nevstic]

Hiya ITmannie
the Limit to list was indeed no , so I set it to Yes
and for the first time it has at least responded. Problem
is it has highlighted this piece of code...
Dim db As DAO.Database

and then when I exit I get the error message that what I typed in the Combobox was not on the list.

Just for info

My table name is "Agent" and the field that I am concerned with is called "HNDLG"
and lastly the combobox I left as Combo41

Soory for this ,as you know doubt realise I am pretty new to all this Recordset stuff.
many thanks again
Nick

 

[ITMannie]

Which version of Access are you running?

If it doesn't like the Dim db As DAO.Database line of code then you probably don't have a reference to the DAO object library. You can set this through the Tools...References... menu option. Just click on the Microsoft DAO 3.6 Object Library (that's what it is in my Access 2000 - in previous versions you will probably find the Microsoft DAO 3.51 Object Library).

Alternatively, you could use the ActiveX Data Objects (ADO) (e.g. Dim Rst As ADODB.Recordset) - there are plenty of examples out there for ADO.

Cheers

 

[nevstic]

Hi there
I am using access 97
would this be the prob ?

Rgds
nick

 

[ITMannie]

If you are using Access 97 then you should not have any problem.

What is the error that you get?

 

[nevstic]

It works !
I also added the extra "end if"

Many thanks IT mannie
youve been just great and are most appreciated.
Which part of the World are you in ?
Nick

 

[ITMannie]

Glad to be of help.

I'm in Glasgow, by the way.

Cheers and good luck.

 

[nevstic]

Many thanks ITM
I'm down here in Surrey
used to live on Arran near to you,used to stay
on Holy Island next to Arran.Great fishing !

Have a nice rest of W/e
rgds
Nick