Data Misnatch in NotInLIst Event

[RuralGuy]

Is it working? Did you change the AfterUpdate code?

 

[CSCS]

sorry, same thing.

Why do you think it keeps giving me the Couldn't Locate message?

 

[RuralGuy]

Did you change the AfterUpdate code?

 

[CSCS]

yes this is my new one:

Private Sub Combo48_AfterUpdate()
DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Auto] = " & Me.Combo48.Column(0)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me.Combo48.Column(0) & "]"
End If
End Sub


It still gives me the Could not locate message

 

[RuralGuy]

Is this the message you get?
Could not locate [1234567] Or whatever number
Is there a '1234567' [Auto] in the RecordSet for the Form?

The ComboBox is looking in the STUDENT table. What is the Record Source for the form?

 

[CSCS]

When I enter a new ID in the combo box the NotInList event kicks in and it asks whether I want to add it to the list, I say yes and it takes me to the add form where I add the related information (the system gives it an auto number, for example 49)

I go back to the combo box when I try to choose the ID that I have just entered I get the message (Could not Locate [49])

I checked the table and it's there.

Here is the form record source:

SELECT GUARDIAN_PARENT.*, STUDENT.*, ACAD_PLAN.* FROM ACAD_PLAN INNER JOIN (STUDENT INNER JOIN (GUARDIAN_PARENT INNER JOIN Student_Parent ON GUARDIAN_PARENT.GP_Auto=Student_Parent.GP_Auto) ON STUDENT.Auto=Student_Parent.Auto) ON ACAD_PLAN.PLAN_CODE=STUDENT.PLAN_CODE;

Where ACAD_PLAN contains the academic information (ignore it)
and student_parent connect the student with the guardian_parent.

 

[RuralGuy]

It looks like [Auto] is ambiguous in this case. Try this change in AfterUpdate:

Me.RecordsetClone.FindFirst "([STUDENT].[Auto]) = " & Me.Combo48.Column(0)

 

[CSCS]

ok I changed the code

now whenever I come back from the "add form" to the combo box in the original form I get a code error when I say debug it takes me to AfterUpdate Code and hightlight: DoCmd.Requery

There is one thing I noticed:
my relationship is as follow:

Parent: has an auto number as primary key
Student: has an auto number as primary key

and these 2 primary keys are foreign keys in student_parent table which also has an auto number as a primary key

when I start the form first tab is parent information i enter them normally and an auto number is given for the parent.
I go to the student tab, when I enter a new ID for the student I go to the add form and enter all the related information I come back none of these information (including the auto number) is transferred to the original form (so the relationship between parent and student through student_parent is not established)

However if I go manually to the student table and take the auto number of te student I just created in the add form and then go to the student_parent table and join him (manually) with a parent. Now If I go to the form I can choose him from the combo box.

i.e. I can choose IDs from the combo box only if they are related through the student_parent
and they will be related if the student auto number is transferred from the "add form" to the original form.

 

[RuralGuy]

Let's put some error handling in the AfterUpdate code so we can see what is happening:

Private Sub Combo48_AfterUpdate()
[B]On Error GoTo Err_Combo48_AfterUpdate[/B]
DoCmd.Requery ' Get any changes to the table first.
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "([STUDENT].[Auto]) = " & Me.Combo48.Column(0)
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me.Combo48.Column(0) & "]"
End If
[B]Exit_Combo48_AfterUpdate:
   Exit Sub
Err_Combo48_AfterUpdate:
   MsgBox "Error No:    " & Err.Number & vbCr & _
          "Description: " & Err.Description
   Resume Exit_Combo48_AfterUpdate[/B]
End Sub

 

[CSCS]

Same thing.

I got the Could not Locate message.

 

[ShaneMan]

Hey CSCS,

Just a shot, but what if you took the Requery out of the After Update of the cboBox, then have the [AddForm] that's being called, Requery the cboBox at Close time. This way the cboBox has been requeried before you make any choices in it and it's not happening at the After Update event.

It's just a thought and it's the way I do mine, with no problems, so I hope this helps.

Shane

 

[CSCS]

you mean requery it when clicking the close button of the "add form" ?

I'd really appreciate it if you could a attached a sample of your system (without any data). I just want to take a look at how you did it. I know there is something wrong with mine I just don't know what!

 

[RuralGuy]

What changed? Before you put the error handling code in, you were getting an error on DoCmd.Requery. Now we're back to Could not find [nnnn]? What is going on?

 

[RuralGuy]

How about strippimg out any sensitive data, Tools>Database Utilities - Compact and Repair, zip up what is left and post it for us to review. It is a little too difficult to determine what is going on from here!

 

[CSCS]

Rural,

I tried to do it for the parent instead of the student, but same thing

I'm attaching a sample of the database, The form "GUARDIAN_PARENT4"
it has 2 tabs, first one for student and the second one for the parent.

In the parent tab, I put the combo box in which the IDs are, try to add an ID and it will be added but when you come back to the form you cannot choose it.

Please note the relationship between the student and the parent through student_parent table.

Can you please try to add a student and his parent and check the codes?

your help is highly appreciated!

 

[RuralGuy]

CSCS,

In design mode go to the Record Source of the GARDIAN_PARENT4 form and invoke the builder, "..." on the right of the row. View what records are returned for that Record Source. None.

That's why it keeps coming back with "Could not locate [nnn]". I'll see if I can figure out what is happening and why no records.

 

[CSCS]

Rural,

I'm getting desprate about thing combo box thing and I'm really on a deadline.

so I'm trying another thing and I need your help.

I'll put student information and parent information in 2 separate form.

First the student information is entered normally and then a button is clicked to take the user to the parent form to enter the parent informarion.

My problem is I need to carry the Student_Auto (which is the primary key) to the second form to make the link between the student and the parent.

and do I need to carry the Student_Parent_Auto (which is the primary key of the table that link the student and the parent).

How can I do that? i.e. how can I link the student with the parent when they are in 2 different forms.

Thanks alot for all your help!

CS.

 

[RuralGuy]

The OpenArgs parameter.

 

[CSCS]

where and how? Can you please explain I didn't do something like this before.

 

[RuralGuy]

Code like this will go under your "AddParent" button:

Private Sub cmdParent_Click()
    Dim stDocName As String
    stDocName = "frmNewParent"
    DoCmd.OpenForm stDocName, , , , , acDialog, Me.txtStudentID
End Sub

...and code like this goes in the Open event of the NewParent form:

Private Sub Form_Open(Cancel As Integer)
[COLOR=Red][B]Dim Args As Variant[/B][/COLOR]
If Not IsNull(Me.OpenArgs) Then
    '-- Form is being opened with a Student ID Number
    Args = Split(Me.OpenArgs, ";")
    Me.txtStudentID = Args(0)
End If
End Sub