Combo-box ceases to update form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Been battling with for some days and then latterly battling with how I can
properly describe the situation.

Well, here goes.........

The application stores details of Community Education programs and the
problem is with the Enrolment form (fEnrolments) which has a sub-form
(sfEnrolments3). The sub-form is data sheet view and the link is in OnLoad
event of fEnrolments
Set Me.Recordset = Me.sfEnrolments3.Form.Recordset

Everything works fine and the SQL for the Record Source is

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee
FROM (((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID;

In order to display the Program Name and Start Date in sfEnrolments3 I add
the tables tMstrProgram and tMstrProgramName and the Record Source SQL becomes

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee,
tMstrProgram.FundingID, tMstrProgramName.Description AS ProgDesc,
tMstrProgram.StartDate
FROM ((((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID) LEFT JOIN
(tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID =
tMstrProgramName.ProgNameID) ON tEnrolment.ProgramID = tMstrProgram.ProgramID;

The problem then arises that when I select the Program combo-box on
fEnrolments and try to change the selection - the bell sounds, the combo-box
does not close and therefore the record is not updated.

Both the form and sub-form definitely have the same SQL in Record Source.

I hope that this is not too vague.
 
Graham
From the look of your RecordSource statements, it looks like you are trying
to display "descriptions" of the various fields in tEnrollment. I would
remove all the LEFT JOINS. Then on the main form, use a pull-down (comboBox)
for the fields in tEnrollment. For example, make a combobox with
MaritalStatusID as the bound column (control Source) and use the
tmstrMaritalStatus for the Row Source. Make sure your bound column is
MaritalStatusID but display the description in the combo.

You generally don't want the subform to have the same RecordSource. That
negates the need for a subform. THe subform is generally linked on one of the
keys that the subform and form share. This is placed in the properties of the
subform so there shouldn't be a need to do anything on the OnLoad event.

Hope this gets you started.

--
sam


Graham said:
Been battling with for some days and then latterly battling with how I can
properly describe the situation.

Well, here goes.........

The application stores details of Community Education programs and the
problem is with the Enrolment form (fEnrolments) which has a sub-form
(sfEnrolments3). The sub-form is data sheet view and the link is in OnLoad
event of fEnrolments
Set Me.Recordset = Me.sfEnrolments3.Form.Recordset

Everything works fine and the SQL for the Record Source is

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee
FROM (((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID;

In order to display the Program Name and Start Date in sfEnrolments3 I add
the tables tMstrProgram and tMstrProgramName and the Record Source SQL becomes

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee,
tMstrProgram.FundingID, tMstrProgramName.Description AS ProgDesc,
tMstrProgram.StartDate
FROM ((((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID) LEFT JOIN
(tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID =
tMstrProgramName.ProgNameID) ON tEnrolment.ProgramID = tMstrProgram.ProgramID;

The problem then arises that when I select the Program combo-box on
fEnrolments and try to change the selection - the bell sounds, the combo-box
does not close and therefore the record is not updated.

Both the form and sub-form definitely have the same SQL in Record Source.

I hope that this is not too vague.
Click to expand...
 
Hi Sam

Maybe, I should have included more detail in the original post, but I was
also trying to not overcomplicate matters.

The sub-form is data sheet view and therefore displays all records and the
code in OnLoad synchronises the main form record when a record in the sub-fom
is clicked.

I am already using combos in the main form. The reason for the
"descriptions" is that it is appropriate to display descriptions in the
sub-form rather than ID's.

My experience has been that with this sort of linking any mismatch between
the SQL in form and sub-form brings grief. But then, my Access experience is
limited - my first language is Progress.
--
Graham


smk23 said:
Graham
From the look of your RecordSource statements, it looks like you are trying
to display "descriptions" of the various fields in tEnrollment. I would
remove all the LEFT JOINS. Then on the main form, use a pull-down (comboBox)
for the fields in tEnrollment. For example, make a combobox with
MaritalStatusID as the bound column (control Source) and use the
tmstrMaritalStatus for the Row Source. Make sure your bound column is
MaritalStatusID but display the description in the combo.

You generally don't want the subform to have the same RecordSource. That
negates the need for a subform. THe subform is generally linked on one of the
keys that the subform and form share. This is placed in the properties of the
subform so there shouldn't be a need to do anything on the OnLoad event.

Hope this gets you started.

--
sam


Graham said:
Been battling with for some days and then latterly battling with how I can
properly describe the situation.

Well, here goes.........

The application stores details of Community Education programs and the
problem is with the Enrolment form (fEnrolments) which has a sub-form
(sfEnrolments3). The sub-form is data sheet view and the link is in OnLoad
event of fEnrolments
Set Me.Recordset = Me.sfEnrolments3.Form.Recordset

Everything works fine and the SQL for the Record Source is

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee
FROM (((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID;

In order to display the Program Name and Start Date in sfEnrolments3 I add
the tables tMstrProgram and tMstrProgramName and the Record Source SQL becomes

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee,
tMstrProgram.FundingID, tMstrProgramName.Description AS ProgDesc,
tMstrProgram.StartDate
FROM ((((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID) LEFT JOIN
(tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID =
tMstrProgramName.ProgNameID) ON tEnrolment.ProgramID = tMstrProgram.ProgramID;

The problem then arises that when I select the Program combo-box on
fEnrolments and try to change the selection - the bell sounds, the combo-box
does not close and therefore the record is not updated.

Both the form and sub-form definitely have the same SQL in Record Source.

I hope that this is not too vague.
Click to expand...
Click to expand...
 
Sam

Just been doing what I should have done at the outset - cutting out the
padding.

Also, have commented out the link to the sub-form - so now it is just
fEnrolments.

The SQL is now
SELECT tEnrolment.*, tMstrProgram.FundingID, tMstrProgram.StartDate
FROM tEnrolment LEFT JOIN tMstrProgram ON tEnrolment.ProgramID =
tMstrProgram.ProgramID;
and the combo-box issue is still there.

If I delete tMstrProgram and then join to any other table that was in the
initial SQL, everything is fine.

So it seems that the problem is something to do with the table tMstrProgram.
But, I am at a loss to understand what.

While, in the current minimilist state it is not necessary to have any
tables joined - it is necessary for a "real world" application (as described
in my previous post)

--
Graham


smk23 said:
Graham
From the look of your RecordSource statements, it looks like you are trying
to display "descriptions" of the various fields in tEnrollment. I would
remove all the LEFT JOINS. Then on the main form, use a pull-down (comboBox)
for the fields in tEnrollment. For example, make a combobox with
MaritalStatusID as the bound column (control Source) and use the
tmstrMaritalStatus for the Row Source. Make sure your bound column is
MaritalStatusID but display the description in the combo.

You generally don't want the subform to have the same RecordSource. That
negates the need for a subform. THe subform is generally linked on one of the
keys that the subform and form share. This is placed in the properties of the
subform so there shouldn't be a need to do anything on the OnLoad event.

Hope this gets you started.

--
sam


Graham said:
Been battling with for some days and then latterly battling with how I can
properly describe the situation.

Well, here goes.........

The application stores details of Community Education programs and the
problem is with the Enrolment form (fEnrolments) which has a sub-form
(sfEnrolments3). The sub-form is data sheet view and the link is in OnLoad
event of fEnrolments
Set Me.Recordset = Me.sfEnrolments3.Form.Recordset

Everything works fine and the SQL for the Record Source is

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee
FROM (((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID;

In order to display the Program Name and Start Date in sfEnrolments3 I add
the tables tMstrProgram and tMstrProgramName and the Record Source SQL becomes

SELECT tEnrolment.*, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrSource.Description AS Source,
tMstrStaff.FirstName+" "+tMstrStaff.LastName AS StaffName,
[tMstrProgramName.Description] & " : " & [StartDate] AS Program,
[tEnrolment.FirstName] & " " & [tEnrolment.LastName] AS Enrollee,
tMstrProgram.FundingID, tMstrProgramName.Description AS ProgDesc,
tMstrProgram.StartDate
FROM ((((tEnrolment LEFT JOIN tMstrStaff ON tEnrolment.StaffID =
tMstrStaff.StaffID) LEFT JOIN tMstrSource ON tEnrolment.SourceID =
tMstrSource.SourceID) LEFT JOIN tMstrMaritalStatus ON
tEnrolment.MaritalStatusID = tMstrMaritalStatus.MaritalStatusID) LEFT JOIN
tMstrCultural ON tEnrolment.CulturalID = tMstrCultural.CulturalID) LEFT JOIN
(tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID =
tMstrProgramName.ProgNameID) ON tEnrolment.ProgramID = tMstrProgram.ProgramID;

The problem then arises that when I select the Program combo-box on
fEnrolments and try to change the selection - the bell sounds, the combo-box
does not close and therefore the record is not updated.

Both the form and sub-form definitely have the same SQL in Record Source.

I hope that this is not too vague.
Click to expand...
Click to expand...
 
Back
Top