Data Misnatch in NotInLIst Event

[CSCS]

OK here is what I have:

Table Name: Student
Fields: Auto (Primary Key)
MOI (number)
Name (Text)

I have 2 forms:
1) Student ,which contains student info I have created a combo box in this form for MOI, so whenever I type an MOI ID that have been previously entered I get the student Name, if it's not previously entered I used the NotInList Event, that asks the user if he wants to add the new value in the list if Yes, it will forward him to the second form

2) student_info, which is an entry form the user enters the name of the student and go back to the student form.

The following is the NotInList event code:

Private Sub Combo10_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 student_info form in data entry
' mode as a dialog form, passing the new MOI in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in student_info form's Form_Load event
' procedure.
DoCmd.OpenForm "student_info", , , , acAdd, acDialog, NewData
End If

' Look for the student the user created in the student_info form.
Result = DLookup("[MOI]", "student", _
"[MOI]='" & 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 customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If


End Sub


The code is working perfectly except for one problem:

When I'm in student_info form to enter the name of new student and I want to go back to the Student Form I have this error message:

data type mismatch in criteria expression


I figured maybe because I'm passing a number field and in the DLookup criteria it assigns the MOI (which is number) to NewData which is String as follow:

DLookup("[MOI]", "student", _
"[MOI]='" & NewData & "'")

Attach it the database for better understanding of my prolem.
Any help will be very much appreicated!

Thanks,
CS.

 

[RuralGuy]

DLookup("[MOI]", "student", "[MOI]=" & NewData & ")

 

[CSCS]

I tried this but it didn't work, however I tried DLookup("[MOI]", "student", "[MOI]=" & CSng(NewData))
and it worked.

Thanks

 

[CSCS]

I'm sorry I need one more thing regarding this topic.

whenever I enter a new ID it asks me whether I want to add it to the combo list and if I say yes the student_info form opens and i get to enter the name.
When I go back to the first form the name should be displayed automatically (since I just entered it in the student_info) form. However it doesn't. I had to close the student form and open it again and choose the ID that I have entered and then th ename will be displayed.

How can I solve it?

Many thanks and best regards,
CS.

 

[RuralGuy]

Post your AfterUpdate code for the ComboBox.

 

[CSCS]

here it is

Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MOI] = " & Str(Nz(Me![Combo12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[RuralGuy]

My recommendation for the AfterUpdate code:

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

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

That should show you what is happening.

 

[CSCS]

Rural you are a genius !!!!!

Thanks alot! IT WORKED!

 

[RuralGuy]

You're welcome and thanks for posting back with your success.

 

[CSCS]

Hi, It's me again!

The combo box worked perfectly with the code you gave. However I tried the same thing on a form that has multiple tabs, but it didn't work.

Here's what I did:

I have a form that has multiple tabs. In the last tab (which contains student information: Auto, MOI, Name) I have created a combo box with the same code to do the same thing (the user type an ID in the lcombo box if it's there it'll display all the related information, if it's not there it'll ask the user if he wants to add it to the list and take his to the add form)

Even when I tried to add a combo box in a tab the third option when creating a combo box (find a record on my form based on the value i selected in my combo box) is not showing !
it doesn't work on a tab?
Any idea way?

Any help will very appreciated

Thanks and regards,
CS.

 

[RuralGuy]

Even when I tried to add a combo box in a tab the third option when creating a combo box (find a record on my form based on the value i selected in my combo box) is not showing !
it doesn't work on a tab?
Any idea way?

I don't really understand what you mean here. Did you create a ComboBox and then you can't find it on the tab element?

 

[CSCS]

ok, I'll explain.

my form has a query behind it which consist of three tables related to each other. (Parent Information, School Information and Student Information)
I have 3 tabs in my form, Parent information are in the first tab, scool information in the second tab and student information in the third tab.

Now, in my third tab I need a combo box which contains IDs,so whenever I choose an ID, all the related student information will be displayed. to do that, when creating a combo box using a wizard I have to choose the third option which is "find a record on my form based on the value i selected in my combo box".

The problem is, when a form have mutiple tabs this option doesn't show.

Any idea why, or do you have a work around to solve this problem?

Thanks,
CS,

 

[RuralGuy]

I think I understand. Initially put the ComboBox anywhere but on the tab. When it is defined and working then you can cut and paste it to your tab.

 

[CSCS]

I tried it, it doesn't work.

I even tried another work around solution, I've created a simple student form with a combo box (the option appeard) and then pasted it on the tab. but for some reason the combo box becomes inactive on the tab (when i choose an ID the related information does not appear)

 

[RuralGuy]

Make sure the NotInList and AfterUpdate events have "[Event Procedure]" next to them on the event tab. If the ComboBox were inactive you could not choose an ID. It just needs some code associated with the two events mentioned.

 

[CSCS]

The NotInList event is working perfectly, because I've been able to add an ID which is not in list, but I think the problem is with the AfterUpdate event. When I try to choose an ID (which is already in the list) I get the following message (Could not Locate)

Here is my AfterUpdate code:

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

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[GP_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

Regards,
CS.

 

[RuralGuy]

Is it looking for the correct data? Post the SQL Row Source for this ComboBox.

 

[CSCS]

here it is:

SELECT STUDENT.Auto, STUDENT.MOI FROM STUDENT;

 

[RuralGuy]

Do you believe there should be a [GP_Auto] = STUDENT.Auto ? Have you looked in the table?

 

[CSCS]

I'm sorry it's just Auto
the GP came there by mistake