Full Version: Populate Combobox With Field Captions (not Field Names)
UtterAccess Forums > Microsoft® Access > Access Forms
italiano21
Hi Guys,
I have a Split Form, with a combobox named "Cbofield2" and a textbox named "txtsearch"...
This 2 objects filter de split form, reducing number of data when u type.. it works fine if i populate the combobox as a fieldset for the table where the data is...
but what i want is that when people click the combo they see the field captions i set for each field when i created the table...
Osearch the internet and found out a code to do it...
the only problem is that when i put the code.. the combo does not populate...
any solution or help with the code will be very apreciated
thanks in advance
call the code on load:
CODE
Private Sub Form_Load()
Dim strTemp As String
strTemp = getFieldCaptions("Lista de Operador")
Me.cbofield2.RowSource = strTemp
bFindLastKeySpace = False
End Sub

code itself for the field captions:
CODE
Private Function getFieldCaptions(cTable As String) As String
Dim cRes As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
On Error Resume Next
Set db = CurrentDb
Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Caption") & ";"
If Err = 3270 Then
Err = 0
cRes = cRes & fd.Name & ";"
End If
Next
Set td = Nothing
getFieldCaptions = Left(cRes, Len(cRes) - 1)
Set db = Nothing
End Function
Kenny Holmes
Hi,
I think I may be able to help. I will give it a try and get back to you.
egards
Kenny
italiano21
nop mate, i want the combobox to display the captions i set when i created the table...
in the property of each field...
o the search gets the value from combobox for instance... "FirstName" --> and the search code searches the value on txtsearch in the column "FirstName" of the table... the thing is i want the combobox to display like "First Name" and not "FirstName"
Kenny Holmes
Ok, a couple of things.
Why don't you just have field names with spaces. I realise this is bad practise, but if you always put the field name in square brakcets or quotes, depending on scenario, access should handle it just fine. eg;
[First Name] or "First Name"
Alternatively, assuming you dont need this field list to be changed regularly you could set it up as a value list and have VB remove the space when it picks up the text from the combo box.
Let me know if you want to do that and I will post the code.
Regards
Kenny
Bob G
what version of access are you using? You may want to look at the TAG property which might give you more flexibility.
BruceM
I had success when I requeried the combo box (Row Source type set to Value List) in the Load event after setting the Row Source. I would not have expected it to be necessary, but it seems to work. I should mention I rarely use table captions, so in my quick test I ended up with what amounts to a field list. However, if the function returns the captions (in the Immediate window, for instance), it should work the same way as it did for me.
Kenny Holmes
Bob,
Wouldn't the TAG property refer to an attribute of the combo box, rather than the table fields? Also I thought the TAG property didn't affect anything? It won't fetch data from the table will it?
talliano21,
Sorry I guess I wasn't completely paying attention earlier,
The line;
getFieldCaptions = Left(cRes, Len(cRes) - 1)
is redundant and can be set as;
getFieldCaptions = cRes
Where you build the string containing the list of captions, if you build it field1name; field1caption; field2name; field2caption; field3name; field3caption; etc. and set your combobox to have 2 columns the left column will contain the field names and the right column the field captions. If you don't want the user to be able to see the field names then set the width of the 1st column to 0.
This way your search field should pick up the field name and the user will see the caption.
Regards
Kenny
Bob G
i was kind of suggesting a different approach to a table holding the caption information. Seems like a lot of administrative work everytime you add / delete / or change a control.
Kenny Holmes
for example by adding an extra line in your function above where you get the caption
Res = cRes & fd.Properties("name") & ";"
cRes = cRes & fd.Properties("Caption") & ";"
italiano21
thanks all for the help
ob im using access 2007 <
BruceM Even with the requery the combo still remains blank with no values what so ever in it, and its set to value list in the property's...
Kenny Where should i add the last line of code you gave me?
like here?
CODE
Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Name") & ";"
cRes = cRes & fd.Properties("Caption") & ";"

shouldn't i then edit down here too?
CODE
If Err = 3270 Then
Err = 0
cRes = cRes & fd.Name & ";"
cRes = cRes & fd.Caption & ";"
End If

I think the rowsource is not setting properly.. not sure why... cause in the propertys of the combo in design view nothing apears there :S
cheekybuddha
What is the RowSourceType of the combobox?
It must be 'Value List' and not 'Table/Query'
hth,
d
italiano21
the type is Value List, the combo ins unbound.. the rowsource come out blank in the propoerty's :S not sure why..
cheekybuddha
In your function add:
CODE
Debug.Print getFieldCaptions

before the last 'End Function' statement and see what is output to the Immediate Window. Post back here.
italiano21
it returns nothing does not populate the combo.. nor do nothing :S
!--c1-->
CODE
Private Sub Form_Load()
Dim strtemp As String
strtemp = getFieldCaptions("List")
Me.cbofield2.RowSource = strtemp
bFindLastKeySpace = False
End Sub

CODE
Private Function getFieldCaptions(cTable As String) As String
Dim cRes As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
On Error Resume Next
Set db = CurrentDb
Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Caption") & ";"
If Err = 3270 Then
Err = 0
cRes = cRes & fd.Name & ";"
End If
Next
Set td = Nothing
getFieldCaptions = cRes
Set db = Nothing
Debug.Print getFieldCaptions
End Function

Oopen form and does nothing :S
cheekybuddha
OK, I guess you are getting an error somewhere, but you have turned off error reporting so it's not showing up. Maybe a table called 'List' isn't a good idea as it *may* be a reserved word.
omment out the 'On Error Resume Next' statement in your function and see whether the function throws an error when you run it.
d
italiano21
already changed the name
!--c1-->
CODE
Private Sub Form_Load()
Dim strtemp As String
strtemp = getFieldCaptions("tblradios")
Me.cbofield2.RowSource = strtemp
bFindLastKeySpace = False
End Sub

If i remove quotes from the name of the table i get an error in the code like "variable not defined"
with the quotes the code seems not to read the table name :S
CODE
Private Function getFieldCaptions(cTable As String) As String
Dim cRes As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
On Error Resume Next
Set db = CurrentDb
Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Caption") & ";"
'If Err = 3270 Then
'Err = 0
'cRes = cRes & fd.Name & ";"
'End If
Next
Set td = Nothing
getFieldCaptions = cRes
Set db = Nothing
Debug.Print getFieldCaptions
End Function

commented out the error handler.. but it does not show nothing :S
cheekybuddha
You don't appear to have commented out the 'On Error Resume Next' statement after the variable declarations. Did you do that one?
italiano21
kenny the second options sounds plausible... and might work if this code is doomed LOL can u explain me how to remove spaces via vb?
thanks
italiano21
oh my bad u are right LOl
the error i get is
quot;run-time error : '3265'
Item not found in this collection"
in this line
CODE
Set db = CurrentDb
-------> [b]Set td = db.TableDefs(cTable)[/b]
For Each fd In td.Fields
cRes = cRes & fd.Properties("Caption") & ";"
cheekybuddha
So we're getting somewhere!
ccess doesn't think you have a table called "tblradios". Did you check the spelling?
d
italiano21
the table is here and has that same name :S
mate it actually works now..
Oopened the table again and this time got error 3270 - no property defined.. uncommented the on resume next and it worked
thank you very
cheekybuddha
As a debugging exercise try:
CODE
Private Function getFieldCaptions(cTable As String) As String
Dim cRes As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
On Error Resume Next
Set db = CurrentDb
For Each td In db.TableDefs
    Debug.Print td.Name
    If td.Name = cTable Then MsgBox "Found It!"
    Exit For
Next
' Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Caption") & ";"
'If Err = 3270 Then
'Err = 0
'cRes = cRes & fd.Name & ";"
'End If
Next
Set td = Nothing
getFieldCaptions = cRes
Set db = Nothing
Debug.Print getFieldCaptions
End Function
cheekybuddha
OK - glad you got it working!
Ignore my last post!
Good luck with your project icons/default/thumbup.gif" style="vertical-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
d
italiano21
now i have another problem :S the search does not recognize the Caption as Field Name i want to search :S
maybe ill have to do as kenny says.. and add both
cheekybuddha
Hi,
don't know why this is giving so much trouble!
I had a play and made the following function which seems to work:
CODE
Function fTableCaptions(strTable As String) As String
On Error GoTo Err_fTableCaptions
    Dim i As Integer, fd As Object, strRet As String, strCaption As String
    With CurrentDb
        With .TableDefs(strTable)
            For Each fd In .Fields
                On Error Resume Next
                strCaption = fd.Properties("Caption")
                If Err <> 0 Then
                    strCaption = fd.Name
                    Err = 0
                End If
                On Error GoTo Err_fTableCaptions
                strRet = strRet & IIf(Len(strRet) > 0, ";", vbNullString) & strCaption
            Next fd
        End With
    End With
Exit_fTableCaptions:
    fTableCaptions = strRet
    Exit Function
Err_fTableCaptions:
    Select Case Err.Number
    Case Else
        MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
               "Description: " & Err.Description & vbNewLine & vbNewLine & _
               "Procedure: fTableCaptions" & vbNewLine & _
               IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
               "Module: basTest", , "Error: " & Err.Number
    End Select
    Resume Exit_fTableCaptions
        
End Function

Give it a go and see if it works for you.
hth,
d
italiano21
your code works buddy, the earlier code works too.. the combo is populated with the captions the thing is.. now the search function does not know where to search.. cause it does not recognize the name of the field.. and looks for the name of the field as the caption... i should split the code maybe.. and put the combo with 2 columns, display only second column with the captions.. and bound to the names...
It works...
for anyone interested the code is like this:
On Load:
CODE
Private Sub Form_Load()
Dim strtemp As String
strtemp = getFieldCaptions("tbloperador")
Me.cbofield2.RowSource = strtemp
End Sub


Function:

CODE
Private Function getFieldCaptions(cTable As String) As String
Dim cRes As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
On Error Resume Next
Set db = CurrentDb
Set td = db.TableDefs(cTable)
For Each fd In td.Fields
cRes = cRes & fd.Properties("Name") & ";"
cRes = cRes & fd.Properties("Caption") & ";"
If Err = 3270 Then
Err = 0
cRes = cRes & fd.Name & ";"
End If
Next
Set td = Nothing
getFieldCaptions = Left(cRes, Len(cRes) - 1)
Set db = Nothing
End Function

Combo Box Settings:
Under Data:
CODE
Row Source Type: "Value List"
Bound To Column: "1"

Under Format:
CODE
Column Count: "2"
Column Widths: "0cm;5cm"

I want To Thank All The People Who Took The Time To Help Me With This... Thank You All... You Should All Be Proud For Being Such a Good Help In This Community... And For That I Thank You Guys:
Kenny Holmes
Bob G
BruceM
And Last But Not Least cheekybuddha
Thank You !
BruceM
We're all glad to help. border="0" alt="thumbup.gif" />
Good luck with the rest of the project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.