Continuous Form Loading Problem

[sexton75 via AccessMonster.com]

I have a continuous form that has a combo box based on another field. That
is not the problem. The weird thing is that when I am in the form adding
data, the values display properly based on their corresponding field.

When I close the form and reopen it, only some of the answers are provided
even though the data is in the table. The code is currently pasted in the
current and open part of the form, but it is not working. Can anyone help?
My code is as follows:

Private Sub Form_Current()
Dim strSQL As String
If IsNull(Me.Answer) Then
Me.Answer.RowSource = "sTbl_responses"
Else
strSQL = "SELECT sTbl_Responses.AnswerID, sTbl_Responses.Answer,
sTbl_Responses.ReponseTypeID_fk FROM sTbl_Responses WHERE (((sTbl_Responses.
ReponseTypeID_fk)=[Forms]![Form1]![ResponseTypeID_fk]));"
Me.Answer.RowSource = strSQL
End If
End Sub

 

[Wolfgang Kais]

Hello sexton75.

I have a continuous form that has a combo box based on another
field. That is not the problem. The weird thing is that when I
am in the form adding data, the values display properly based
on their corresponding field.

When I close the form and reopen it, only some of the answers are
provided even though the data is in the table. The code is currently
pasted in the current and open part of the form, but it is not
working. Can anyone help?
My code is as follows:

Private Sub Form_Current()
Dim strSQL As String
If IsNull(Me.Answer) Then
Me.Answer.RowSource = "sTbl_responses"
Else
strSQL = "SELECT sTbl_Responses.AnswerID, " & _
"sTbl_Responses.Answer, sTbl_Responses.ReponseTypeID_fk FROM" & _
" sTbl_Responses WHERE (((sTbl_Responses.ReponseTypeID_fk)=" & _
"[Forms]![Form1]![ResponseTypeID_fk]));"
Me.Answer.RowSource = strSQL
End If
End Sub

Did you respect Nr. 8 of http://www.mvps.org/access/tencommandments.htm?
The code is called when a record becomes the current record. If an
AnswerID exists in that record, only those Answers appear in the list of
the combo box (yes, not boxes, it's only one box) that have a matching
value in the ReponseTypeID_fk column.
Obviously your continuous form displays records with Answers of
different types and you will have funny effects when switching between
such records.
The problem is that there is only one combo box that is displayed
multiple times and the only difference is in the data bound properties
(like Value). All other properties (like RowSource) are the same for
all appearences of the combo box. If you insist using a continuous
form, you could try to filter the records based on a ResponseType.

 

[sexton75 via AccessMonster.com]

Maybe I wasnt very clear on what I meant. The form works exactly how I want
when I open the form to enter data. The continuous form pulls the correct
responses in the drop down boxes. For instance

One question is: What is the employees title?
This provides the user with the following choices in the combo box ("Sales
Rep, Marketing Rep, Mgr")

A subsequent question is: Who is the employees supervisor?
This provides the user with the options (Supervisor 1, Supervisor 2, etc)

This works great. The problem is when the form is saved, closed, and then
reopended, it only shows choices for the first question and shows the second
combo box blank. The responses are bound to the table which holds the data,
but doesnt display it in the box.

Hello sexton75.

I have a continuous form that has a combo box based on another
field. That is not the problem. The weird thing is that when I

[quoted text clipped - 19 lines]

End If
End Sub

Did you respect Nr. 8 of http://www.mvps.org/access/tencommandments.htm?
The code is called when a record becomes the current record. If an
AnswerID exists in that record, only those Answers appear in the list of
the combo box (yes, not boxes, it's only one box) that have a matching
value in the ReponseTypeID_fk column.
Obviously your continuous form displays records with Answers of
different types and you will have funny effects when switching between
such records.
The problem is that there is only one combo box that is displayed
multiple times and the only difference is in the data bound properties
(like Value). All other properties (like RowSource) are the same for
all appearences of the combo box. If you insist using a continuous
form, you could try to filter the records based on a ResponseType.

 

[Wolfgang Kais]

Hello "sexton75".

I have a continuous form that has a combo box based on another
field. That is not the problem. The weird thing is that when I
[quoted text clipped - 19 lines]
End If
End Sub

Did you respect Nr. 8 of http://www.mvps.org/access/tencommandments.htm?
The code is called when a record becomes the current record. If an
AnswerID exists in that record, only those Answers appear in the
list of the combo box (yes, not boxes, it's only one box) that have
a matching value in the ReponseTypeID_fk column.
Obviously your continuous form displays records with Answers of
different types and you will have funny effects when switching
between such records.
The problem is that there is only one combo box that is displayed
multiple times and the only difference is in the data bound
properties (like Value). All other properties (like RowSource) are
the same for all appearences of the combo box. If you insist using
a continuous form, you could try to filter the records based on a
ResponseType.

Maybe I wasnt very clear on what I meant. The form works exactly
how I want when I open the form to enter data. The continuous form
pulls the correct responses in the drop down boxes.

I think that "entering data" means filling a record that has not yet
an "Answer"? In this case the ComboBox will show all possible answers
to all questions. That is because when you go to a record with no
Answer, the whole table sTbl_responses is made the rowsource of the
ComboBox. When the current record is such a new record, all answers
entered before will show correctly on the continuous form, because
the rowsource of the combo contains them all.

For instance One question is: What is the employees title?
This provides the user with the following choices in the combo box
("Sales Rep, Marketing Rep, Mgr")

This is only true if there is already an answer stored. But if the
combo box in the current record contains only these few entries,
then all other rows also contain just these few entries (stop, stay
on that record!), therefore, I bet that you see answers in the other
records if and only if these records have the same ResponseType.

A subsequent question is: Who is the employees supervisor?
This provides the user with the options (Supervisor 1, ... , etc)

Only true if that is your current record and an answer is already
stored.

This works great. The problem is when the form is saved, closed,
and then reopended, it only shows choices for the first question
and shows the second combo box blank. The responses are bound to
the table which holds the data, but doesnt display it in the box.

Believe me, you already have that problem before closing the form.
Enter the two new records of your example and the click into the
first, click into the second...
You will see an answer only in the "current" record.

I you firtered the records based on a ResponseType, for example you
select a response type in a main form and use a continuous subform
for data entry, you won't have that problem anymore.

Although this won't solve your problem, I hope that I could help
you understanding what the ptoblem is.