Problem with subform

  • Thread starter Thread starter Yossi
  • Start date Start date
Y

Yossi

I have a form Teachers and a subform SubTeachers. I need to display data in
the sub form according to my selection in the combobox on the TEachers Form.
I have coded the following on the combo_change event
but it doesn't refresh the data in the sub form. What am I doing wrong?

<---Code starts Here ---->

Forms!Teachers!SubTeachers.Form.RecordSource = "SELECT Teachers.TeacherID,
Teachers.SchoolID, Teachers.Initials, Teachers.Surname, Teachers.Learners
FROM Teachers where SchoolID = " & ComboSchoolID.Value
Form_SubTeachers.Form.Refresh

<---Code Ends Here ---->



Thanks
 
You need to force the subfrom to requery after you have changed the
RecordSource with:
Forms!Teachers!SubTeachers.Form.Requery

TomU
 
Hi Tom,

Unfortunately this doesn't work. The event occurs. If I run a query on the
table it returns 9 records but on the form it does not populate the
datasheet.
What else should I look for?

Yossi
 
Yossi said:
I have a form Teachers and a subform SubTeachers. I need to display data in
the sub form according to my selection in the combobox on the TEachers Form.
I have coded the following on the combo_change event
but it doesn't refresh the data in the sub form. What am I doing wrong?

<---Code starts Here ---->

Forms!Teachers!SubTeachers.Form.RecordSource = "SELECT Teachers.TeacherID,
Teachers.SchoolID, Teachers.Initials, Teachers.Surname, Teachers.Learners
FROM Teachers where SchoolID = " & ComboSchoolID.Value
Form_SubTeachers.Form.Refresh
Click to expand...


You do not need to Refresh or Requery the subform, setting
the RecordSource property does all that automatically.

There must be something else involved as to why won't it
display the records that match the combo box selection.

Are you sure the subform **control** is named SubTeachers?

Maybe, the subform is trying to display the correct records,
but it takes too much time so the screen is not being
repainted??
 
Hi Marshall,

I tried to remove the form and recreate it. It still does not work. Also it
is only returning under 50 records so it should not be timing out.
What else should I look for to make it work?
Yossi
 
Double check the names and data types of everything,
especially the reference to the combo combo box and the
SchoolID field in the table. Note that the Where clause
specifys that the SchoolID field is a numeric type, if its
really a Text field then the clause should be:

WHERE SchoolID = """ & ComboSchoolID & """"

Also check the assignment statement itself. You posted it
as a single very long line of code. If that's not a
Copy/Paste of the code, then may be something wrong with
that I can't see.

One debugging action you can try, is placing a break point
in the code immediately after you set the record source, the
use the debug window to check the actual SQL statement by
copy/pasting it into a new query and trying to run the query
directly from the query design window.
 
Hi Marshal,

The fields are definitely numeric here is the entire procedure:

Private Sub ComboSchoolID_Change()

Forms!Teachers!SubTeachers.Form.RecordSource = "SELECT
Teachers.TeacherID, Teachers.SchoolID, Teachers.Initials,
Teachers.Surname, Teachers.Learners FROM Teachers where SchoolID = " &
ComboSchoolID.Value

End Sub

I have checked the SQL statement and it is fine. Returns a few records.

Yossi

Marshall Barton said:
Double check the names and data types of everything,
especially the reference to the combo combo box and the
SchoolID field in the table. Note that the Where clause
specifys that the SchoolID field is a numeric type, if its
really a Text field then the clause should be:

WHERE SchoolID = """ & ComboSchoolID & """"

Also check the assignment statement itself. You posted it
as a single very long line of code. If that's not a
Copy/Paste of the code, then may be something wrong with
that I can't see.

One debugging action you can try, is placing a break point
in the code immediately after you set the record source, the
use the debug window to check the actual SQL statement by
copy/pasting it into a new query and trying to run the query
directly from the query design window.
--
Marsh
MVP [MS Access]

I tried to remove the form and recreate it. It still does not work. Also
it
is only returning under 50 records so it should not be timing out.
What else should I look for to make it work?
Click to expand...
Click to expand...
 
If the (sub)form's record source runs fine from the query
design window, it should also work in the subform.

I don't suppose the subform has its Filter property set to
something that preventing the records from being retreived.

Or maybe(??) the new record source data doesn't match
whatever's specified in the subform control's Link
Master/Child properties.

I hope you can straighten this out or at least come up with
a few more clues, because I'm really running out of ideas
here.
 
Hi Marshal,

I eventually resolved my problem. But only by first recretaing the 2 forms
from scratch. Don't know why the first attempt did not work.

I have another questionif you do not mind? The sub form is a datasheet how
do I know which row in the sheet is selected?

Yossi

Marshall Barton said:
If the (sub)form's record source runs fine from the query
design window, it should also work in the subform.

I don't suppose the subform has its Filter property set to
something that preventing the records from being retreived.

Or maybe(??) the new record source data doesn't match
whatever's specified in the subform control's Link
Master/Child properties.

I hope you can straighten this out or at least come up with
a few more clues, because I'm really running out of ideas
here.
--
Marsh
MVP [MS Access]


The fields are definitely numeric here is the entire procedure:

Private Sub ComboSchoolID_Change()

Forms!Teachers!SubTeachers.Form.RecordSource = "SELECT
Teachers.TeacherID, Teachers.SchoolID, Teachers.Initials,
Teachers.Surname, Teachers.Learners FROM Teachers where SchoolID = " &
ComboSchoolID.Value

End Sub

I have checked the SQL statement and it is fine. Returns a few records.
Click to expand...
Click to expand...
 
Yossi said:
I eventually resolved my problem. But only by first recretaing the 2 forms
from scratch. Don't know why the first attempt did not work.

I have another questionif you do not mind? The sub form is a datasheet how
do I know which row in the sheet is selected?
Click to expand...


The record selector box at the left of the form has an
indicator (small triangle) for the current record. Once you
start to edit the record, the indicator should change to a
pencil that indicates the record is dirty.

If that's not what you were asking or if it's an
insufficient indicator, post back with more details.

I should probably mention that datasheet view is pretty
limitted in this regard. You might want to consider using
continuous view instead.
 
Hi Marshal,
I should probably mention that datasheet view is pretty limitted in this
regard.
Click to expand...
I must use a datasheet as I want a list of all Teachers at a particular
school. The school has been selected in the main form.
What I need to acheive is that when I select a specific teacher in the
subform it must communicate back to a different subform (of the parent) that
that teacher has been selected. Then I will be able display relevant
information about that teacher.

Regards,
Yossi
 
Yossi said:
Hi Marshal,



I must use a datasheet as I want a list of all Teachers at a particular
school. The school has been selected in the main form.
What I need to acheive is that when I select a specific teacher in the
subform it must communicate back to a different subform (of the parent) that
that teacher has been selected. Then I will be able display relevant
information about that teacher.
Click to expand...

When a form is in datasheet view clicking on the record selector will
fire the form's Current event and the form's Click event. The only
difference is the form's current event fire's when the form opens or
when it's recordsource changes. Take your pick.
 
Yossi said:
I must use a datasheet as I want a list of all Teachers at a particular
school. The school has been selected in the main form.
What I need to acheive is that when I select a specific teacher in the
subform it must communicate back to a different subform (of the parent) that
that teacher has been selected. Then I will be able display relevant
information about that teacher.
Click to expand...


Oh OK, in that case add a text box named txtCurrentTeacher
to the mainform's header section. Add a line of code the
the datasheet subform's Current event:

Parent.txtCurrentTeacher = Me.teacherID
Now set the teacher details subform **control** LinkMaster
property to txtCurrentTeacher and LinkChild to teacherID.
Of course you need to change teacherID to whatever name
you're using for the teacher table's primary key **field**.
 
Back
Top