Combo Box source based on other field

[SwingXH]

I have a table with fields like AYesNo, BYesNo, CYesNO.
And these field names are the source of a combo box. My objective is that if AYesNo=Yes and other two are NO, then the list of combox box is AYesNo only. If they are all Yes, then list them all.
Is that possible?
Thanks,

SwingXH

 

[PHV]

Take a look at the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SwingXH]

The problem is that I have about 10 YesNo fields, too complex to use IIF.

 

[TheAceMan1]

How are ya SwingXH . . . . .

If ya wanna give it a shot in code, put the following in the forms module:

[blue]Public Sub cbPack()
   Dim Pack As String
   
   If Me.AYesNo Then Pack = Pack & "AYesNo;"
   If Me.BYesNo Then Pack = Pack & "BYesNo;"
   If Me.CYesNo Then Pack = Pack & "CYesNo;"
   
   Me![purple][b]YourComboBoxName[/b][/purple].RecordSource = Pack
      
End Sub[/blue]

Then in each of the corresponding controls [blue]AfterUpdate[/blue] event, call the routine above:

[blue]   Call cbPack[/blue]

See Ya! . . . . . .

 

[SwingXH]

Aceman, "The record source AYesNo that you specified on this form does not exist" when I click the combo box.

Private Sub Form_Load()
Call cbPack
End Sub

Public Sub cbPack()
Dim Pack As String

If Me.AYesNo Then Pack = Pack & "AYesNo;"
If Me.BYesNo Then Pack = Pack & "BYesNo;"
If Me.CYesNo Then Pack = Pack & "CYesNo;"

Me.Combo5.RowSource = Pack

End Sub


Also, Me.Combo5.RecordSource did not work.
For the last one, do I have to remove ";", is there any function to get rid of the last character in a string?

 

[TheAceMan1]

SwingXH . . . .

[purple]Cancel the code above, somehow you posted just before me.[/purple]

How are you determining which record to use as the criteria?

See Ya! . . . . . .

 

[SwingXH]

I have comboID on a form to control the record on this form.
Thanks,

SwingXH

 

[TheAceMan1]

SwingXH . . . . .

Just making sure I understand you . . . .

Are you saying, the list will consist of fieldnames, inclusion in the list according to yes/no . . . one fieldname per row?

Are you saying, the list is dependant on record selection in the form?

See Ya! . . . . . .

 

[SwingXH]

Yes, AceMan1. I have several YesNo fileds in the main table. Each of them will have a sub table with same structure, if it is Yes.

For example
main table
ID AYesNo BYesNo CYesNo
1 Yes Yes No
2 No Yes Yes

sub table
ID ID1 Name Age Address
1 1 AYesNo 20 NYC
1 2 BYesNo 30 LA
2 2 BYesNo 40 DET
2 3 CYesNo 50 SA


I have a comboID in the main form to control the main table record to show on the main form. For example, when it is 1, show the first row of data in the main table. This was done.

Then since this record give me AYesNo=Yes and BYesNo=Yes, I want another combo box comboID1 to have a list of AYesNo and BYesNo. CYesNo is not in the list since CYesNo=No.

When the user pick the item from the comboID1, I want a sub form to show the sub table with corresponding record. If AYesNo is selected then show
ID ID1 Name Age Address
1 1 AYesNo 20 NYC

If BYesNo is selected, then show
1 2 BYesNo 30 LA


I hope this is clear.
Thanks a lot!
SwingXH

 

[SwingXH]

Any body can help?

 

[TheAceMan1]

SwingXH . . . .

I'm so sorry. I must have somehow deleted a post notice from you or did'nt get it.

[blue]I have several YesNo fileds in the main table. [purple]Each of them will have a sub table[/purple] with same structure, if it is Yes.[/blue]

This is certainly not a Relational Database. Fields do not have subTables! You need to reconsider your table structure. If you don't, your gonna have trouble all the way down the line. Besides . . . . . I'm not gonna help someone travel the wrong path. You should check out the following:

[blue]Normalizing Tables[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

[blue]Table Relationships[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

Get to know the above well and it will clear the field for ya.

Now, put the following in the forms module:

[blue]Public Sub cbPack()
   Dim Pack As String
   
   If Me.AYesNo Then Pack = Pack & "AYesNo"
   If Me.BYesNo Then Pack = Pack & ";BYesNo"
   If Me.CYesNo Then Pack = Pack & ";CYesNo"
                  [green]'
         'One line per field
                  '[/green]
   If Me.?YesNo Then Pack = Pack & ";?YesNo"
   
   Me![purple][b]YourComboBoxName[/b][/purple].[blue][b]RowSource[/b][/blue] = Pack
      
End Sub[/blue]

Then, put the following call in the [blue]On Current[/blue] event of the form ([blue]as you change records the combobox should autoupdate[/blue]):

[blue]  Call cbPack[/blue]

See Ya! . . . . . .