have a combo box to loose one of its list items after selection

[jaymin]

Hi All,


Hope someone can help, I have a form with three cascading combo boxs and a subform,

When the third combo box is opened it will have a list of questions to be answered one by one when the focus is transfered to the subform.

When the focus is transfered back to the third combo box and opened up, the list of questions there will now be less one, the question that was selected previously.
This should be able to continue till all questions are selected and answered and the combo box list is empty.


There is a simular post form June 7 https://www.accessforums.net/forms/ch...box-25322.html but I do not know how to transpose the code.

Jaymin

[jaymin]

Hope someone can help, to explain further.
when you select an option from the third combo box this takes you to the subform where you enter the data needed, then when you return to the third combo box there will be one less option to choose from, the one that you previously selected has been removed from the combo filter, this keeps happining till you either finish asseccing the combo box or you close the form, when you reopen the form all of the options are viewable again.
I have attached the data base to view, open the frnMainForm, it is the option from the third combo box called Questions that I want this to work, I am after a rowsource SQL to filter this combo,
Jaymin

portfilio_A.mdb

[June7]

Who is answering questions? I don't see that documented in tblAnswers.

Why isn't AnsDate a Date/Time field type?

Why not use DefaultValue property to populate AnsDate for new record instead of VBA code?

Combobox properties:

cboName
SELECT tblStaff.IDStaff, [Surname] & ", " & [FirstName] AS FullName FROM tblStaff ORDER BY Surname, FirstName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

cboPortfolio
SELECT tblPortfilio.IDPortfilio, tblPortfilioName.PortfilioName, tblPortfilio.IDStaff FROM tblPortfilioName RIGHT JOIN tblPortfilio ON tblPortfilioName.IDPortfilioName = tblPortfilio.IDPortfilioName WHERE (((tblPortfilio.IDStaff)=[Forms]![frmMainForm]![cboName])) ORDER BY PortfilioName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

cboQuestion
SELECT tblQuestions.IDQuestions, tblQuestions.Questions FROM tblQuestions LEFT JOIN tblAnswers ON tblQuestions.IDQuestions = tblAnswers.IDQuestions WHERE (((tblQuestions.IDPortfilio)=[forms]![frmMainForm]![cboPortfilio]) AND ((tblQuestions.IDAnswers) Is Null) AND ((tblAnswers.AnsDate)<>Month(Date()) & ", " & Year(Date())));
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

[jaymin]

Hi June7,
Thank you for your reply,

I have entered your mods, all works except for the third combo box which when i open the combo box it is empty no data in it.

Why isn't AnsDate a Date/Time field type?

The AnsDate should have been a Date/Time field in the table tblAnswers, i have changed it now.

Why not use DefaultValue property to populate AnsDate for new record instead of VBA code?

Before today was not able to do AnsDate under the the defaultvalue property, have played around and got it to work, thanks for that. I usally find VBA easy to understand.

Who is answering questions? I don't see that documented in tblAnswers.

Not sure what you are asking here IDQuestions, but that is selected by the first combo box, this selects the name of the the portfilio holder, this filters the second combo box with a list of portfilios that is allocated to them, which then filters the third combo box with the list of questions that they have to answer each month for that portfilio, the data is saved in the subform text box answer.

[June7]

Never mind question about documenting who is answering.


Remove IDAnswers from tblQuestions.


Also, bind the subform to tblAnswers instead of the query.


cboQuestion was showing items for me last night, however, the RowSource still isn't quite right.

SELECT tblQuestions.IDQuestions, tblQuestions.Questions
FROM tblAnswers RIGHT JOIN tblQuestions ON tblAnswers.IDQuestions = tblQuestions.IDQuestions
WHERE (((tblQuestions.IDPortfilio)=[Forms]![frmMainForm]![cboPortfilio]) AND ((tblAnswers.IDAnswers) Is Null) AND ((tblAnswers.AnsDate)<>Month(Date()) & ", " & Year(Date()) Or (tblAnswers.AnsDate) Is Null));

If you changed AnsDate field type, be sure to adjust cboQuestion RowSource.


BTW, isn't 'portfilio' a misspelling of 'portfolio'?

[jaymin]

Thanks June7,
I get data in the third combo box now except, after you select it and answer the question and then return to the third combo box that question is still visible, where as you should not see it.

bind the subform to tblAnswers instead of the query.

Done

Remove IDAnswers from tblQuestions.

Done

If you changed AnsDate field type, be sure to adjust cboQuestion RowSource.
AnsDate a Date/Time field type

changed tblAnswer AnsDate default value to "Month(Date()) & ", " & Year(Date())"

SELECT tblQuestions.IDQuestions, tblQuestions.Questions
FROM tblAnswers RIGHT JOIN tblQuestions ON tblAnswers.IDQuestions = tblQuestions.IDQuestions
WHERE (((tblQuestions.IDPortfilio)=[Forms]![frmMainForm]![cboPortfilio]) AND ((tblAnswers.IDAnswers) Is Null) AND ((tblAnswers.AnsDate)<>Month(Date()) & ", " & Year(Date()) Or (tblAnswers.AnsDate) Is Null));

placed this in the rowsource of the cboQuestion

Combobox properties:

cboName
SELECT tblStaff.IDStaff, [Surname] & ", " & [FirstName] AS FullName FROM tblStaff ORDER BY Surname, FirstName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

cboPortfolio
SELECT tblPortfilio.IDPortfilio, tblPortfilioName.PortfilioName, tblPortfilio.IDStaff FROM tblPortfilioName RIGHT JOIN tblPortfilio ON tblPortfilioName.IDPortfilioName = tblPortfilio.IDPortfilioName WHERE (((tblPortfilio.IDStaff)=[Forms]![frmMainForm]![cboName])) ORDER BY PortfilioName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

Done

[June7]

The combo needs to be Requeried after record is committed so the RowSource will update. Try code in the form AfterUpdate event procedure:

Me.cboQuestion.Requery

Did you change AnsDate field to date/time type? If so the DefaultValue should be Date() and The RowSource query would use Date() as criteria.

[jaymin]

Hi June7

The combo needs to be Requeried after record is committed so the RowSource will update. Try code in the form AfterUpdate event procedure:

Me.cboQuestion.Requery

Have done that still getting all the questons, the selected question is still visible

Did you change AnsDate field to date/time type? If so the DefaultValue should be Date() and The RowSource query would use Date() as criteria.

I changed the AnsDate field in the table tblAnswer to date/time, is it the defaultvalue in the table tblAnswer be date(), the rowsource criteria ansDate is <>Day(Date()) & ", " & MonthName(Month(Date())) & ", " & Year(Date()) Or Is Null, or should that be just Date()

[June7]

Just Date().

Sorry, the syntax for code in subform would be: Me.Parent.cboQuestion.Requery.

However, the subform record is not saving the QuestionID of the question selected in the combobox, it is saving the QuestionID of the main form record, which never changes. Apparently all the bookmark code isn't working. I thought it was because the searches use names, not IDs, but even after fixing that it isn't working. Mysterious.

An alternative is not to use a form/subform. Comboboxes and button would be in form Header section. Bind cboQuestions to QuestionID. Copy the subform controls into Detail section, delete the subform. Try Continuous form view, you might like.

Form RecordSource:
SELECT tblAnswers.*, tblQuestions.Questions FROM tblQuestions RIGHT JOIN tblAnswers ON tblQuestions.IDQuestions = tblAnswers.IDQuestions;

Have a textbox in the Detail section to display the question but set that box as Locked Yes, TabStop No.

Code behind form:

Option Compare Database
Option Explicit

Private Sub cboName_AfterUpdate()
    Me.cboPortfilio.Requery
End Sub

Private Sub cboPortfilio_AfterUpdate()
    Me.cboQuestion.Requery
End Sub

Private Sub Form_AfterUpdate()
    Me.cboName = Null
    Me.cboPortfilio = Null
    Me.cboQuestion.Requery
End Sub

Private Sub btnClose_Click()
    DoCmd.Close
End Sub

cboPortfilio RowSource:
SELECT tblPortfilio.IDPortfilio, tblPortfilioName.PortfilioName FROM tblPortfilioName RIGHT JOIN tblPortfilio ON tblPortfilioName.IDPortfilioName=tblPortfilio.IDPo rtfilioName WHERE (((tblPortfilio.IDStaff)=[cboName])) ORDER BY PortfilioName;

cboQuestions RowSource:
SELECT tblQuestions.IDQuestions, tblQuestions.Questions FROM tblAnswers RIGHT JOIN tblQuestions ON tblAnswers.IDQuestions=tblQuestions.IDQuestions WHERE (((tblQuestions.IDPortfilio)=[cboPortfilio]) And ((tblAnswers.IDAnswers) Is Null) And ((CDate(NZ([AnsDate],#12/31/2999#)))<>Date()));

Make sure you are on a new record row before selecting a question in the combobox. You will notice that the bound combobox will not show the question for existing records because the RowSource is a query with a lookup alias and that value is filtered out of the RowSource list. That's the reason for textbox to show the question.

Use debug techniques to analyse problems. Refer to link at bottom of my post for guidelines.

[jaymin]

Hi June7,
Sorry for the delay, work commitments, but all right now
Jaymin