Help with NotinList code

[Tony Williams]

I have two combo boxes on different forms but both called cmbmaincompany.
On form1 the the row source is:
SELECT tblCompany.cmbCompany FROM tblCompany ORDER BY tblCompany.cmbCompany;

On form2 the rowsource is:
SELECT tblCompany.cmbCompany, tblCompany.txtEuroIndicator FROM tblCompany
WHERE (((tblCompany.txtEuroIndicator)=Yes));

I have the Limit toList properties on both forms set to YES and the On
NotinList property has this code:
Private Sub cmbmaincompany_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 on the list of companies." & vbCrLf
& " Do you want to add them to the current List?" & vbCrLf & " Click Yes to
Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompany", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!cmbCompany = NewData
rs.Update

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

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

Form1 works fine and will allow me to add a new company but form2 gives me
the message box to add a new name but then I get the error message "An
error occurred. Please try again."

Is it because of the WHERE clause in the second form and if so what can I do
about it?

Many thanks
Tony

 

[tina]

suggest you put a breakpoint on the first "Else" line in your procedure, and
then step through it, so you can see exactly which line is erring out.

hth

 

[Tony Williams]

SORRY this posted twice. The first post didn't show on any of the news
servers with my news reader yesterday! I keep getting this problem changed
to Microsoft news server but still happens.
Anyway I still have a problem with NotinList
Tony

 

[Tony Williams]

Sorry Tina I'm new at VBA how do I do that?
Tony

suggest you put a breakpoint on the first "Else" line in your procedure, and
then step through it, so you can see exactly which line is erring out.

hth


I

 

[tina]

to add a breakpoint to a line of code: open the form's code module and go
to the line of code you want to "break" on. make sure your I-beam is
anywhere on that line, and press F9 (to remove the breakpoint, press F9
again). this command is also on the menu bar, under Debug.

open your form in form view, and go through the steps that will cause the
NotInList event to fire. when Access gets to the breakpoint, it will stop
the code and open the code window with that line highlighted.

to step through the code line by line, from the breakpoint, press F8 (also
on the menu bar under Debug). each time you press F8, the next line of code
will execute. to resume automatic execution of the code at any time, press
F5 (also found on the menu bar, under Run).

hth

 

[Tony Williams]

Thanks Tina really useful help
Tony

to add a breakpoint to a line of code: open the form's code module and go
to the line of code you want to "break" on. make sure your I-beam is
anywhere on that line, and press F9 (to remove the breakpoint, press F9
again). this command is also on the menu bar, under Debug.

open your form in form view, and go through the steps that will cause the
NotInList event to fire. when Access gets to the breakpoint, it will stop
the code and open the code window with that line highlighted.

to step through the code line by line, from the breakpoint, press F8 (also
on the menu bar under Debug). each time you press F8, the next line of code
will execute. to resume automatic execution of the code at any time, press
F5 (also found on the menu bar, under Run).

hth