Enable based on continuous form combo

[Jupie23]

I have a form with a continuous subform that contains a combobox. I currently have code to enable some fields on the main form if a certain selection is made in the combobox. This is working, however, if a selection is made in a second combobox, it then closes the fields needed for the first combo. Is it possible to have some kind of OR statement so the user can use as many comboboxes as they want to, and all applicable fields will enable?

Thank you!

 

[pbaldy]

Sure, might help to see your code and how you want to adapt it.

If Something OR SomethingElse Then

Depending on the desired logic, a Select/Case block may be appropriate too.

 

[Jupie23]

This is what I currently have that only does one at a time:

Private Sub Document_Needed_AfterUpdate()
If Me.Document_Needed = 8 Then
Me.Parent!AlternateName.Enabled = True
Me.Parent!AlternateName2.Enabled = True
Else: Me.Parent!AlternateName.Enabled = False
Me.Parent!AlternateName2.Enabled = False
End If
If Me.Document_Needed = 7 Then
Me.Parent!Customer2.Enabled = True
Me.Parent!County.Enabled = True
Me.Parent!TitlingState.Enabled = True
Me.Parent!Model.Enabled = True
Me.Parent!Color.Enabled = True
Else: Me.Parent!Customer2.Enabled = False
Me.Parent!County.Enabled = False
Me.Parent!TitlingState.Enabled = False
Me.Parent!Model.Enabled = False
Me.Parent!Color.Enabled = False
End If
End Sub

 

[pbaldy]

In words, what is your desired result?

 

[Jupie23]

When they select something in the combo in the subform, it adds another combo below it so they can select as many items as they want. I want them to be able to select for example, 3 items in a row, and then go over to the fields in the main form and have the necessary fields for all 3 items be enabled.

Currently if you select an item, those fields open, but when you select the next combo, the first ones disable and the second ones enable. I would like all to stay open so they can be completed as a group. I have no preference on logic, as long as it works! Thank you!

 

[Mark_]

Odd... just had this discussion in another thread...

What you may want to do is have your PARENT form check CHILD records to see if they meet the criteria then set controls as needed. MajP posted a good sample.

 

[Jupie23]

Mark, thank you for sharing that. I have to admit, it's a little over my head though and I'm not sure where to start. Are you able to provide me a bit more direction?

 

[MajP]

I have to same I am very confused, and not certain the thread Mark posted is what you are saying. Can you post a screen shot? Can you be more precise? You use terms like first combo, second combo, first ones, second ones. I have no idea which controls those are. It sounds like me you want to unlock some controls on the main form, but somehow they get locked after getting unlocked.

 

[MajP]

That should read "it sounds to me like you want to ... " not "It sounds like me you want"
That is an embarrassing typo.

 

[MajP]

As presented your logic did not make sense with the two "elses"
Here is my interpretation and I added a variable and a with/end with to shorten the code

Private Sub Document_Needed_AfterUpdate()
   dim frm as access.form
   set frm = me.Parent

   'Disable all if not 7 or 8
   with frm
    .Customer2.Enabled = False
    .County.Enabled = False
    .TitlingState.Enabled = False
    .Model.Enabled = False
    .Color.Enabled = False
    .AlternateName.Enabled = False
    .AlternateName2.Enabled = False
   end with
   'enable when 7 0r 8
  
  If Me.Document_Needed = 8 Then
    frm.AlternateName.Enabled = True
    frm.AlternateName2.Enabled = True
  end if
  
  if me.document_Needed = 7 then
    with frm
      .Customer2.Enabled = True
      .County.Enabled = True
      .TitlingState.Enabled = True
      .Model.Enabled = True
      .Color.Enabled = True
    end with 
 end if 
End Sub

 

[Jupie23]

It sounds like me you want to unlock some controls on the main form, but somehow they get locked after getting unlocked.

Yes, you are correct. I will attach a screen shot. The comboboxes under Documents Needed (cboDocNeeded) is a continuous subform that saves to a Document Needed table, which is related to my main table. The main table contains the fields on the right for Customer Information. Not all fields are needed there unless they pick a certain document. In my example/screen shot, say I choose "Highlighted sections in enclosed doc." in cboDocNeeded. Then I need Customer 2 to open (and stay open). Then I choose Third Party Security Agreement, and I need the 2 Alternate name fields to open, but also Customer 2 to stay open.

Currently if I select "Highlighted sections...", the Customer 2 opens with the code I posted earlier, but as soon as I make the next combo selection, Customer 2 closes, and only Alternate Name opens. I would like to be able to make all of the combobox selections at once, and then go over to the right and fill out all of the applicable fields. Does that explain it better?

 

[Jupie23]

Thanks MajP, I just saw your response came when I was typing that. I will give that a shot!

 

[Jupie23]

I still end up with the same issue. Is what I want to do possible with a continuous form?

 

[pbaldy]

Could the questions on the left be selected from a multiselect listbox instead of a series of combos?

 

[Jupie23]

Sure, if a listbox would work better, that would be fine. I have seen some code to add the listbox selections to a table on the form's after insert event, but I didn't know if it would work for me here. In this form, they fill out all the fields and then there's a print preview and print button they use to open/print the reports, so I when I tried it, I had issues with the record not being saved yet.

 

[MajP]

Instead of checking the value of the combo you would need to check the underlying table on the after update. I do not know where the document_Needed is being stored. I assume you are putting in a document_Needed ID and another foreign key relating to the parent record.
So in the after update you use a dcount to see if the table contains a 7 for that parent PK. Set your control states. Then you do the same for 8 and set your control states.

Something like

if dcount ("*","childTable", "documentID = 7 and someForeingKey = " Me.SomeID) > 0 then 
  enable 7 controls
end if

Do the same for 8.

 

[pbaldy]

I don't really understand your flow, but you can loop the subform's recordset, or the underlying table (which would be how I'd do it). Maybe code that disables all the fields, then loops the questions and enables the appropriate ones. Is there a table that associates questions with fields that need to be enabled? I'm guessing you can't be sure what order the user will select documents needed.

 

[Jupie23]

Hope this helps. I have the following:

tblExceptions: main table with imported data from report that shows what each account is missing and additional info the user will add.

tblDocNeeded: Is a list of all potential documents. Combobox rowsource is based on this.
ID
Document
Instructions
Notary Yes/No

tblLetter: subform with combobox previously mentioned saves document needed here
ID (pk)
ExceptionID(fk) : related to tblExceptions
DocumentNeeded (number field= ID from tblDocNeeded)

There is no table that associates the document with the fields it needs. There are 7 fields that need to enable based on the document selected (all are seen in the original code I posted). I do like the looks of a listbox better, but clearly I don't know what I'm doing so it seemed too difficult.

 

[MajP]

something like

Private Sub Document_Needed_AfterUpdate()
   dim frm as access.form
   set frm = me.Parent

   'Disable all if not 7 or 8
   with frm
    .Customer2.Enabled = False
    .County.Enabled = False
    .TitlingState.Enabled = False
    .Model.Enabled = False
    .Color.Enabled = False
    .AlternateName.Enabled = False
    .AlternateName2.Enabled = False
   end with
   'enable when 7 0r 8
  
  If dcount("*","tblLetter","DocumentNeeded = 8 and ExceptionID = " & me.parent.ExceptionID) > 0 Then
    frm.AlternateName.Enabled = True
    frm.AlternateName2.Enabled = True
  end if
  
 If dcount("*","tblLetter","DocumentNeeded = 7 and ExceptionID = " & me.parent.ExceptionID) > 0 Then
    with frm
      .Customer2.Enabled = True
      .County.Enabled = True
      .TitlingState.Enabled = True
      .Model.Enabled = True
      .Color.Enabled = True
    end with 
 end if 
End Sub

 

[Jupie23]

Ok MajP, I tried that and it appears that the fields aren't opening until I select another combo below it. For example: I select POA. Nothing happens. I pick another document in the 2nd combobox, and then the POA fields open. I make a selection in the 3rd combo, and the fields enable for the 2nd combo selection.