Loops

[tbassngal]

Can someone help me by adding looping code for 3 cboDesc boxes, cboDesc1, cboDesc2, cboDesc3

Private Sub cboApplication_AfterUpdate()
Dim sSQL As String
'MsgBox "The Code is Executing!"

'clear cboDesc1
Me.cboDesc1 = Null

sSQL = "SELECT Desc1 FROM tblApplicationDescription"
sSQL = sSQL & " WHERE [Application] = '" & Me.[cboApplication] & "' "
sSQL = sSQL & " ORDER BY Desc1"

'for debugging
'Debug.Print sSQL

Me.cboDesc1.RowSource = sSQL

'check if txtDesc1 has values
If IsNull(Me.cboDesc1.ItemData(0)) Then
Me.cboDesc1.Enabled = False
Else
Me.cboDesc1.Enabled = True
Me.cboDesc1 = Me.cboDesc1.ItemData(0)

'optional
Me.cboDesc1.SetFocus
Me.cboDesc1.Dropdown
End If

End Sub

 

[MajP]

If I understand this you have a table called
tblApplicationDescription
It has three fields Desc1, Desc2, and Desc3

First, I would guess that this table is not propertly normalized. It looks like you have three fields with the same type of information. I would fix your table structure first. My guess is that DESC (whatever that represents) should be its own table linked to the application table

tblDesc
applicationID (foreign key to application table)
Desc (some field containing data like Tina)

However, assuming you do not fix the data. Here is my guess of what you are asking

Private Sub cboApplication_AfterUpdate()
  Dim sSQL As String
  dim i as integer
  dim ctrl as access.combobox
  for i = 1 to 3
    sSQL = "SELECT Desc" & I & " FROM tblApplicationDescription"
    sSQL = sSQL & " WHERE [Application] = '" & Me.[cboApplication] & "' "
    sSQL = sSQL & " ORDER BY Desc" & I
    'please verify the sql string. You can copy this out of the debug window
    'and then run it through the query editor
    debug.print
    set Ctrl = me.controls("cboDesc"&I)
    ctrl.rowsource = sSql
    'Not sure what you want to happen here
    'If IsNull(ctrl.ItemData(0)) Then
    '   ctrl.Enabled = False
    'Else
    '   ctrl.Enabled = True
    '   ctrl.Dropdown
    ' End if
   next i  
End Sub

 

[TheAceMan1]

How are ya tbassngal . . .

Just presenting another variation:

[blue]Private Sub cboApplication_AfterUpdate()
   Dim SQL As String, [purple][b]ctlName[/b][/purple] As String, x As Integer
   
   For x = 1 To 3
      [green]'                       1           2           3[/green]
      [purple][b]ctlName[/b][/purple] = Choose(x, "cboDesc1", "cboDesc2", "cboDesc3")

      [green]'clear cbo[/green]
      Me([purple][b]ctlName[/b][/purple]) = Null
   
      SQL = "SELECT " & [purple][b]ctlName[/b][/purple] & _
            "FROM tblApplicationDescription " & _
            "WHERE [Application] = '" & Me.[cboApplication] & "' " & _
            "ORDER BY [" & [purple][b]ctlName[/b][/purple] & "];"
            
      [green]'for debugging[/green]
      Debug.Print SQL

      Me([purple][b]ctlName[/b][/purple]).RowSource = SQL

      [green]'check if txtctlName has value[/green]
      If Trim(Me([purple][b]ctlName[/b][/purple]).ItemData(0) & "") = "" Then
         Me([purple][b]ctlName[/b][/purple]).Enabled = False
      Else
         Me([purple][b]ctlName[/b][/purple]).Enabled = True
         Me([purple][b]ctlName[/b][/purple]) = Me([purple][b]ctlName[/b][/purple]).ItemData(0) [green]'Whats the purpose?[/green]
        
         [green]'optional[/green]
         Me([purple][b]ctlName[/b][/purple]).SetFocus
         Me([purple][b]ctlName[/b][/purple]).Dropdown
      End If
   Next

End Sub[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]