Missing code somewhere

[Guest]

Hello. Ill try to outline the structure of my form first

Tables:
T_Names, includes fields: Stud_ID, Stud_Nam
T_Car, includes fields: Stud_ID, Licence No., Make, Model, et

I have a main form with a subform within it. The main form basically consists of an unbound combo box which searches for Stud_Name from the T_Names table, and i have an After Update function which generates the corresponding Stud_ID record for that Stud_Name in a text box called Stud_ID in the main form, and also brings up that record in the subform. The purpose of the subform is to update data in the T_Car table. My code for the AfterUpdate function is (the name of the Stud_Name combo box is Combo17)

Private Sub Combo17_AfterUpdate(
' Find the record that matches the control
Dim rs As Objec

Set rs = Me.Recordset.Clon
rs.FindFirst "[Stud_ID] = '" & Me![Combo17] & "'
If Not rs.EOF Then Me.Bookmark = rs.Bookmar
End Su

Sometimes it is necessary to add data to a record that is not found in the Stud_Names field, so i have created a function that allows the user to update the T_Names table when they double click the combo box. Here is the code for that

Private Sub Combo17_DblClick(Cancel As Integer
Dim LinkCriteria As Strin
LinkCriteria = "[Stud_Name] = '" & Me!Combo17.Text & "'
DoCmd.OpenForm "F_MaintainNames", , , LinkCriteri
End Su

The afterupdate function works fine when a name is selected from the list. The doubleclick function also works well in adding the name to the T_Names table and the combo box, but the original after update function (the one that would populate the text box in the main field and the subform) doesn't work when i select the newly added name from the combo box. I have noticed that if i exit the form and reenter it and look up the name that i just added it works fine. What is the missing code to make the afterupdate work once i have selected the newly added name from the combo box and where does it go? Am i right in thinking along 'requery' lines?
Thanks for spending the time reading and hope a speedy answer is on its way!

 

[Niklas Östergren]

Hi!

What´s missing is "DoCmd.Requery in your combobox AfterUpdate_Event! This
will run refresh the underlying recordset and show you all records not just
the records read in before you entered the new name.

I´m thinking of soemthing else her actually. I don´t know if I have missed
something in your explanation but it seams thatwhat your´doing here could be
done with a bound form (main form) which is bound to T_Names and your
subform bound to T_Car.

If you have the relationship between these two tables set up right and the
subform is properly linked to the mainform ("Linked from" =
[T_Names].[Stud_ID] and "Linked to" = [T_Car].[Stud_ID]) then should the
subform show all records that are linked to the record with the same
Student_ID without any VBA code in the combobox AfterUpdate_Event.

I asume that the combobox is in my example bound to [T_Names].[Stud_ID] but
using Student:ID as the bound field but having column with set to 0 and
showing Stud_Name in second column with column with set to > 0.

Ther´s a wizard that can help you out with this just make sure that the
wizard is activated (looks like magic sticks with stars sprkling) when you
add a new combobox to your main form.

I
Like I said in the begining I don´´t know if this is what you need but I
hope it will help you out with a little!?

// Niklas

Hello. Ill try to outline the structure of my form first.

Tables:
T_Names, includes fields: Stud_ID, Stud_Name
T_Car, includes fields: Stud_ID, Licence No., Make, Model, etc

I have a main form with a subform within it. The main form basically

consists of an unbound combo box which searches for Stud_Name from the
T_Names table, and i have an After Update function which generates the
corresponding Stud_ID record for that Stud_Name in a text box called Stud_ID
in the main form, and also brings up that record in the subform. The
purpose of the subform is to update data in the T_Car table. My code for
the AfterUpdate function is (the name of the Stud_Name combo box is
Combo17):

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Stud_ID] = '" & Me![Combo17] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Sometimes it is necessary to add data to a record that is not found in the

Stud_Names field, so i have created a function that allows the user to
update the T_Names table when they double click the combo box. Here is the
code for that:

Private Sub Combo17_DblClick(Cancel As Integer)
Dim LinkCriteria As String
LinkCriteria = "[Stud_Name] = '" & Me!Combo17.Text & "'"
DoCmd.OpenForm "F_MaintainNames", , , LinkCriteria
End Sub

The afterupdate function works fine when a name is selected from the list.

The doubleclick function also works well in adding the name to the T_Names
table and the combo box, but the original after update function (the one
that would populate the text box in the main field and the subform) doesn't
work when i select the newly added name from the combo box. I have noticed
that if i exit the form and reenter it and look up the name that i just
added it works fine. What is the missing code to make the afterupdate work
once i have selected the newly added name from the combo box and where does
it go? Am i right in thinking along 'requery' lines?

Thanks for spending the time reading and hope a speedy answer is on its

way!