Simple VBA reference for a control value within a tab control

[goGreen]

Hi,

Well I spent too much time trying to resolve this- but I am just not seeing my mistake. Any assistance you provide would be greatly appreciated.

I have a main form, called Edit_frm_2, with a tab control called edit_control, which has a combo box -called LOB_txt. The LOB_txt, on the second tab of the edit_control, is what I am trying to change its value -based on another field. So here is my code.

Private Sub lob_txt_Click()
Dim new_sql As String

new_sql = " SELECT LA " & _
           " FROM LA_tbl inner join lob_tbl " & _
           " ON LA_tbl.lab_id=lob_tbl.lob_id " & _
           " Where Ucase(lob_tbl.lob)=ucase('" & Forms!Edit_frm_2!LOB_txt.Value & "') " & _
           " ORDER BY BA "

CurrentDb.QueryDefs("select_LA").sql = new_sql
ba_txt.Requery
MsgBox (" LOB_txt : " & LOB_txt.Value)

End Sub

Once I click on lob_txt, the above routine runs but nothing changes in the la_txt field. I opened the select_LA query only to find the 'Forms!Edit_frm_2!LOB_txt.Value' empty.I dont think i should be referencing the tab control.

Thanks in advance.

 

[TheAceMan1]

How are ya goGreen . . .

I can't tell what your trying to do with the code, but:

[blue]You reference a control on a Tab Control as if the Tab Control wasn't there.[/blue]

... and your code should look more like:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, new_sql As String
   
   Set db = CurrentDb
   new_sql = " SELECT LA " & _
             " FROM LA_tbl inner join lob_tbl " & _
             " ON LA_tbl.lab_id=lob_tbl.lob_id " & _
             " Where (lob_tbl.lob = '" & Forms!Edit_frm_2!LOB_txt & "' " & _
             " ORDER BY BA;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   [purple][b]Me.la_txt = rst!la_txt[/b][/purple]
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[goGreen]

Hi AceMan1,

I am relieved to see your post, as I do need help. I was not referencing the tab control at all. Within the sql string, i reference:

Forms!Edit_frm_2!LOB_txt

Where
-edit_frm_2 is the main form
-LOB_txt is the combo box field value within the tab control

The problem is that value is always empty. So my sql query does not perform the filter.


What do you think could be the cause of this- i am sure its simple but that has been my grief.

What i am trying to do is the user chooses a value for LOB_txt combo and based on that value another combo box- ba_txt, displays the appropriate dataset. Thats why I have

CurrentDb.QueryDefs("select_LA").sql = new_sql
ba_txt.Requery

ba_txt has 'select_LA' as the query. So i want to change the sql string of the query and then have the ba_txt display the new result set.

I am not sure what this line does?

Me.la_txt = rst!la_txt

 

[TheAceMan1]

goGreen . . .

Just a heads-up, I detect your having a problem with your naming convention, which could be better ... espcially with your use of the underscore sperator. [blue]cbxLOB[/blue] is more readily recognizable as a combobox than [blue]LOB_txt[/blue].

In your initial post you say:

[blue]The [purple]LOB_txt[/purple] is what I am trying to change based on another field.[/blue]

You go on to say:

[blue]Once I click on [purple]lob_txt[/purple], the above routine runs but nothing changes in the [purple]la_txt[/purple] field.[/blue]

Then in your reply [blue]7 Nov 10 9:43[/blue] you say:

[blue]What i am trying to do is the user chooses a value for [purple]LOB_txt[/purple] combo and based on that value another combo box- [purple]ba_txt[/purple], displays the appropriate dataset.[/blue]

See the confusion!

The 3rd quote makes the most sense and shows you have the [blue]ba_txt[/blue] combo dependent on a selection made with the [blue]LOB_txt[/blue] combo.

To do this you simply need:

[blue]   Dim new_sql As String
   
   new_sql = " SELECT LA " & _
             " FROM LA_tbl inner join lob_tbl " & _
             " ON LA_tbl.lab_id=lob_tbl.lob_id " & _
             " Where (lob_tbl.lob = '" & Forms!Edit_frm_2!LOB_txt & "') " & _
             " ORDER BY BA;"
   Me.ba_txt.RowSource = new_sql[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

goGreen . . .

If you have any problems just post back! Don't try and fix it yourself.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[goGreen]

Hi AceMan1,

Yes I do see the confusion and I apologize.The last quote is what I am trying to do:

What i am trying to do is the user chooses a value for LOB_txt combo and based on that value another combo box- ba_txt, displays the appropriate dataset.

My issue is still that Forms!Edit_frm_2!LOB_txt is empty even when a value is selected for LOB_txt, so the sql does not filter.

Do you have any thoughs why this is?
Thanks,

 

[TheAceMan1]

goGreen . . .

Move your code from the lob_txt [blue]On Click[/blue] event to the lob_txt [blue]After Update[/blue] event and try again. Its possible to click the control in an area that won't register ... let me know what happens ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

If you always want the second combo based off the first, you also can simply make this the rowsource of the second combo:

SELECT LA FROM LA_tbl inner join lob_tbl ON LA_tbl.lab_id=lob_tbl.lob_id Where (lob_tbl.lob = Forms!Edit_frm_2!LOB_txt & ) ORDER BY BA;

Then in the second combo only code you need is.

public sub secondCombo_Enter()
secondComb.requery
end sub

Do not need any code in the first combo.

 

[goGreen]

Well its been sometime since i came back to this problem - I want to thank everyone for their assistance but i am still having this problem. I cant believe how stuck i am....

I re wrote this code based on the suggestions above. What i am trying to do below is after the lob_txt list box control is updated with any selections, the function below runs to retrieve the values from listbox and insert into a sql query and then update another control based on values selected.

Sub LOB_txt_AfterUpdate()
On Error GoTo lob_txt_afterUpdate_err

' Debug.Print "Inside 1"
    Dim string_ba As String
    Dim BA As String
    Dim string_lob As String
    Dim i As Variant
    Dim indexValue As Long
    
    'Debug.Print Me.LOB_txt.ItemsSelected.Count
    Me.LOB_txt.SetFocus
    If (Me.LOB_txt.ItemsSelected.Count > 0) Then
       
       indexValue = Me.LOB_txt.ListIndex
       Debug.Print "indexValue: " & indexValue
       
        ' go through items selected
            For Each i In Me.LOB_txt.ItemsSelected
     
                string_lob = Me.LOB_txt.ItemData(i)
                Debug.Print " i: " & i
                If (indexValue = 0) Then
                    BA = string_lob
                Else
                     BA = string_lob & "," & BA
                End If
           ' Debug.Print string_lob
            Debug.Print BA
            Next i
        
    Else
        BA = "Anna,Heather,Lina"
    End If
    
    strng_ba = " SELECT BA " & _
               " FROM BA_tbl inner join lob_tbl " & _
               " ON BA_tbl.lob_id=lob_tbl.lob_id " & _
               " Where Ucase(lob_tbl.lob) IN (" & BA & ") " & _
               " ORDER BY BA "
               
               Debug.Print strng_ba
               
          '     Me.BA_txt.RowSource = strng_ba
          '     Me.BA_txt.Requery
               
lob_txt_afterUpdate_err:
 Debug.Print " Encountered error in selecting BA based on LOB"
    Exit Sub
               
End Sub

I use the indexValue variable to check if the item is the first object in selection and if so to input into a BA variable without any commas otherwise input into BA variable with commas, to separate the other selections. but indexValue does not always work, look at results below...


Sample results:

[i]
indexValue: 1
 i: 0
Anna,
 i: 1
Heather,Anna,
 i: 2
Lina,Heather,Anna,
 
SELECT BA  FROM BA_tbl inner join lob_tbl  ON BA_tbl.lob_id=lob_tbl.lob_id  Where Ucase(lob_tbl.lob) IN(Lina,Heather,Anna,)ORDER BY BA 

Encountered error in selecting BA based on LOB
[/i]

THANKS

 

[PHV]

What about this ?

...
    If (Me.LOB_txt.ItemsSelected.Count > 0) Then
        ' go through items selected
        For Each i In Me.LOB_txt.ItemsSelected
            BA = BA & ",'" & Me!LOB_txt.ItemData(i) & "'"
        Next i
        BA = Mid(BA, 2)
    Else
        BA = "'Anna','Heather','Lina'"
    End If
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goGreen]

That did the trick. THANKS