Subform Issues!

[MimiSD]

Currently I have a many to many relationship set up between two tables,
tblSchool(primary key: SchoolCode) and tblIssue (primary key:IssueID). I
have a form set up that would allow all the information about a new issue to
be entered into the main form and then the school(s) that experiencing that
issue would be listed below in the subform.
Everything is working fine unless a school that is not currently in the
database reports an issue. When I go to the subform to enter the information
of a new school it gives me an error"The Microsoft Jet Database engine cannot
find a record in the table 'tblSchool' ". How do I work around this so that
a new school can be entered in the subform

 

[JPC]

Is there a finite list of schools that could report issues to you? If so,
whether they currently have issues or not, make entries for all of them in
your tblSchool.

Otherwise, in your database, you need to design a way to check for schools
already listed, and enter new schools into your table, before you log in
issues.

 

[MimiSD]

There is not really a finite number of schools that could report issues.
There is constantly being new schools added to our list. So it would be
difficult to get all the schools into the table.

Is there not a way for the school to be added within the current form?
Because there will be times when a representative who is using the database
may not know if the school has been added or not. Essentially, I would like
it to work in a way that if the schools information does not populate in the
other fields that the representative can just add it at that time.

 

[SusanV]

Yes , use the NotInList event. This page has all the details and sample code
from Dev Ashish:
http://www.mvps.org/access/forms/frm0015.htm

 

[MimiSD]

That code looks like it is going to do exactly what I want. I have a error
or two to deal with now. When i compile this code i get an error on the line
that reads

Dim db As DAO.Database

The error states "Compile Error: User-defined type not defined"
How do i fix that error?

 

[SusanV]

In the VBE, tools menu>> references, set a reference for DAO Object
Library - by default Access only includes ADO references.

 

[MimiSD]

OK, I must be totally missing something. Sorry I am pretty new to Access but
this code still does not appear to be working.

Private Sub cboCode_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 School Code " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add a new school?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link 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("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SchoolCode = NewData
rs.Update

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

End If

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

I still recieve the error saying that "The Microsoft Jet Database engine
cannot
find a record in the table 'tblSchool' with matching key field(s)
'SchoolCode'".