Which event do I use when a record can't be found with key matching fields

[Drayno241]

I'm working in access 2002. I have three tables :
1- District Data (Student ID, name, grade, etc)
2- Rosters (RRec ID,Campus, Teacher ID)
3- Students on Roster(SRec ID, RRec ID, Student ID)

There's a 1 to many relationship between Rosters and Students on Roster. I created a form and subform to populate the Rosters and Students on Roster.

The Students on Roster subform simply accepts the Student ID's (datasheet format) and if the student is found in the District Data table it displays their data on the screen. I created a relationship between District Data and Students on roster based on Student ID.

I did not use a combo box on purpose, since I want to make sure the data entry clerk verifies the data associated with the student id.

My problem is that when the student is not on the District Data table (cannot find record in the table District Data with key matching field(s) Student ID). I want to launch a data entry screen (another form in dialog mode) to collect the data for the District Data table and pass it back to the original form for display. What event do I use?

I have found quite a bit using combo boxes and the NotInList event, but that will allow for error since it prefills the student id field.

I am a VB newbie. :-( Any help will be greatly appreciated.

[NeoPa]

What are you using to determine if there is a matching record in the [District Data] table (DLookup; Linked fields; Within the RecordSource query)?

[Drayno241]

What are you using to determine if there is a matching record in the [District Data] table (DLookup; Linked fields; Within the RecordSource query)?

I don't know how to do DLookup. I have a relationship between District Data and Students in Roster based on Student ID. I built Form1 using the Student ID field in Students in Roster and pulled the corresponding fields from District Data for that Student ID. When it can't find the corresponding record in District Data (error - cannot find record in the table District data with key matching field(s) Student ID), I want to launch the new Form2 to give them an opportunity to add it and go back to the original Form1 to continue entering more students.

[NeoPa]

I get what you want, but until I understand how you're getting that error message I can't tell you how to do it. I get that it's not DLookup(), but what is it?

[Drayno241]

I get what you want, but until I understand how you're getting that error message I can't tell you how to do it. I get that it's not DLookup(), but what is it?

The forms record source is

SELECT Students on Roster.StudentID, District Data.First, District Data.Last, District Data.Grade, Students on Roster.RostertID FROM District Data INNER JOIN Students on Roster ON District Data.StudentID=Students on Roster.StudentID;

I'm thinking I have setup the form wrong from the get go. It did get the form to display how I want, but it's limiting me to what I can do. I'm thinking a join is not made for lookups. I'm looking at MS office to see how DLookup works. Any suggestions will be APPRECIATED. I'm a self taught Access user with NO VB experience.

[Drayno241]

As I'm researching I found that what I'm doing is Autolookup. I am bypassing using a query, but the results are the same.

[Drayno241]

I get what you want, but until I understand how you're getting that error message I can't tell you how to do it. I get that it's not DLookup(), but what is it?

I'm doing an Autolookup between the joined tables.

Good news! Your comment of Dlookup allowed me to do a validation on the student ID field and send a nice message that my user will understand when a student ID is not in the District table.

Is there a way to launch an event after this validation to open Form2 so they can add the student data and return to Form1?

[NeoPa]

I'll have to get to this tomorrow now probably but :

1. The SQL you posted looks all wrong to me. I can't see how it can work at all. Did you Copy / Paste it in?
2. I'm glad the DLookup() bit has helped.
3. You seem to be going off at a tangent before your basic issue is resolved. That is a recipe for confusion (Mine if not yours).

[Drayno241]

I'll have to get to this tomorrow now probably but :

1. The SQL you posted looks all wrong to me. I can't see how it can work at all. Did you Copy / Paste it in?
2. I'm glad the DLookup() bit has helped.
3. You seem to be going off at a tangent before your basic issue is resolved. That is a recipe for confusion (Mine if not yours).

This is what's in the record source of my form (I did paste this). When you double click on the SQL bulder it it shows you a query with the district table and students on roster tables joined with all the fields.

SELECT StudentsonRosterTBL.StudentID,
       DistrictTBL.First,
       DistrictTBL.Last,
       DistrictTBL.Grade,
       StudentsonRosterTBL.RostertID
FROM DistrictTBL INNER JOIN StudentsonRosterTBL
  ON DistrictTBL.StudentID=StudentsonRosterTBL.StudentID;

[NeoPa]

Notice that none of the table names have embedded spaces in this version ;)

[NeoPa]

I should say that this does answer my question (I think) but I need to find some time later to revisit this and think it through properly.

[NeoPa]

(Post #7)
Good news! Your comment of Dlookup allowed me to do a validation on the student ID field and send a nice message that my user will understand when a student ID is not in the District table.

from this, I'm not sure you still need an answer, but from the RecordSource query you posted, it seems that students that don't have an entry in your DistrictTBL table do not even appear on your form (at all).

[Drayno241]

from this, I'm not sure you still need an answer, but from the RecordSource query you posted, it seems that students that don't have an entry in your DistrictTBL table do not even appear on your form (at all).

Correct, it's how I want it. The user is entering student ids, that if on the district table, will display their data.

I have gotten alot further on my design. I have a Not On List event that catches when not on the district table. I have it launching my Add Student form passing the student id for the user to keyin the student data into the district table and returning to my original form.

My current problem is that if they neglect to keyin the student data, my if null clause is not working when it returns to the original form. It works if they use the escape key to exit.

I'm guessing that since I'm passing the student id, it's added automatically. How do I handle this?.

I've pasted my NOT on List code:

Private Sub StudentID_NotInList(NewData As String, Response As Integer)
    Dim Result
    Dim Msg As String
    Dim CR As String
 
    CR = Chr$(13)
 
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
 
    'Ask the user if he or she wishes to add the new Student.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
        ' If the user chose Yes, start the Add Student Form in data entry
        ' mode as a dialog form, passing the new Student ID in
        ' NewData to the OpenForm method's OpenArgs argument. The
        ' OpenArgs argument is used in Add Student form's Form_Load event
        ' procedure.
        DoCmd.OpenForm "01 add student ", , , , acAdd, acDialog, NewData
    End If
 
    ' Look for the student the user created in the add student form.
 
    Result = DLookup("studentid", "districtTbl", NewData)
 
    If IsNull(Result) Then
        ' If the student was not created, set the Response argument
        ' to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again!"
    Else
        ' If the student was created, set the Response argument to
        ' indicate that new data is being added.
        Response = acDataErrAdded
    End If
End Sub

[NeoPa]

1. Please remember to enclose your code in the 'Code' tags and lay it out so that it's readable if you expect us to go through it for you. A bunch of code formatted as text is really not intelligible.
2. VBA has built-in constants for <CR> (vbCR), <LF> (vbLF) & <CR><LF> (vbCRLF) for your use.
3. Can you explain more clearly the circumstances under which you have problems. If you have a ListBox or ComboBox on your form we need to know about it and the details of what's in it etc.
4. Do you know how to trace through the VBA code in debug mode?

[NeoPa]

BTW well done on the progress you've made already. Clearly not someone who needs your hand held at every step :)

[Drayno241]

1. Please remember to enclose your code in the 'Code' tags and lay it out so that it's readable if you expect us to go through it for you. A bunch of code formatted as text is really not intelligible.
2. VBA has built-in constants for <CR> (vbCR), <LF> (vbLF) & <CR><LF> (vbCRLF) for your use.
3. Can you explain more clearly the circumstances under which you have problems. If you have a ListBox or ComboBox on your form we need to know about it and the details of what's in it etc.
4. Do you know how to trace through the VBA code in debug mode?

All those constants are foreign to me, so I do not know how to format my code so you can read it. My apologies. :-(
Let me try to explain better.

What I now have is a Form1 that has a subform embedded in it. The subform collects [studentid ] in a combo box. The combo box is has the Limit to List = yes on the Data tab of the studentid field. If the student is in the District Table list, the asociated data displays. If not, I have an ON NOT in List Event that opens an Add Student form (passing the studentid) to add the student to the District table. My problem is that if the Add Student form is closed by the user w/o entering the data, the studentid gets added with blank data.

I THINK this is happening because I'm passing the student id to the Add Student form and it gets added on the load. I've determined that the only time it does not get added is if I hit the ESC key to exit out of the form.

The Before Update event looks like something I could use, but how?

BTW - I'm just amazed how much I've coded w/o any experience in VB. Your website is awesome!!! Thanks for the encouragement. :-)

[NeoPa]

I'm finding your explanation quite confusing I'm afraid.
Let me look at it again later to see if a second look can help.

BTW. Displaying the code in readable format simply means to break the lines where necessary so that the reader doesn't need to scroll left and right to see what's there (See Post #9).
vbCR is a Carriage Return and is predefined in VBA so you don't need to create your own versions. Likewise vbLF is a Line Feed and vbCRLF is a Carriage Return / Line feed sequence - typically used in Windows text files etc to split one line from another.

[Drayno241]

Okay, I've resolved this initail problem. I'm going to open a new thread to look at a new issue. Thanks for your help. :-)

[NeoPa]

I'm glad you got there Drayno - although I'm not sure I was much help with this one :(