want to hightlight a field on form from combo box 1

[DougP]

I have a form with 130 fields on it.
I would like to have a combo box with all the field names in it and be able to click it and have it hightlight the field somehow on the form. setfocus to it or something

I have code for adding fields to combo just need code to hightlight the field.


DougP

 

[PWise]

if your controls are named the same as the fields then in combo box after update

me(me.combo).setfoucs

 

[DougP]

Error

run time error '438'
object doesn't support this proptery or method

Me(Me.Combo111).SetFocus

If I try this for example, just to see if something would work...
me.SignatureDate.SetFocus
the intellisense only shows .value in the list of items to pick.
I am trying this in the Combo111_afterupdate event

this is Acc 2003 BTW

DougP

 

[MajP]

If the names are not the same then something like this might work

public sub selectCtrl
dim ctrl as access.control
for each ctrl in me.controls
if ctrl.controltype = actextbox or ctrl.controltype = accombobox or ctrl.controltype = aclistbox then
if ctrl.controlsource = nz(me.myCombo,"") then
ctrl.setfocus
exit sub
end if
end if
next ctrl
end sub

 

[DougP]

MajP, it Works !!!!

DougP

 

[DougP]

I have another request of this to sort the field names
since there are 138 fields is and the oreder is aout of whack. it came from a .pdf fill-in file.
I have used this code forever for other purposes. can fields be sorted somehow?
do I need to put the
For Each fldLoop In tdfNew.Fields
Me.Combo111.AddItem fldLoop.Name
Next

into a recordset and sort that?

    Dim Conn2 As ADODB.Connection
    Dim tdfNew As ADODB.Recordset
    Dim fldLoop As Object

    Set Conn2 = CurrentProject.Connection  ' <<<<Note same as CurrentDb
    Set tdfNew = New ADODB.Recordset

    SQLCode = "Select * From [Data from pdf Latest]"
    tdfNew.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic

    For Each fldLoop In tdfNew.Fields
        Me.Combo111.AddItem fldLoop.Name
    Next

    Set fldLoop = Nothing
    Set tdfNew = Nothing
    Set Conn2 = Nothing

TIA

DougP

 

[MajP]

I would think simply
SQLCode = "Select * From [Data from pdf Latest] ORDER BY yourFieldName"

I have no experience with PDF file as a datasource.

 

[Andrzejek]

In your Combo111 (what a number!) do you have Sort property? If so, set it to True and the entries should be sorted by your combo box. Works in 'classic' VB 6

Have fun.

---- Andy

 

[DougP]

Andrzejek, I remember that sort property in VB6, no it is not in Access 2003 anyway.
MajP,

SQLCode = "Select * From [Data from pdf Latest] ORDER BY yourFieldName"

Answer no? because the way it is filled. it is not actually using the record set to sort the data. Since it is using the select statement to just get the table name???
then using this to fill it > fldLoop In tdfNew.Fields

Anyway what I did was use the above code to fill a table since the fields never change. then I connected the combo box to that table and then used MajP’s SQL code there
In the row source of the combo box.
SELECT [TableFields(DoNotErase)].Fieldname FROM [TableFields(DoNotErase)] ORDER BY [TableFields(DoNotErase)].Fieldname;
Which works fine.

Thank you all


DougP

 

[MajP]

Here is generic code to sort any combo or listbox that uses a value list. It is only set up for a 1 column value list, but you could modify for multi column.

[/code]
Public Sub sortValueList(ctrl As Access.Control)
Dim coll As New Collection
Dim itm As Variant
Dim i As Integer
Dim tempVal As Variant
Dim tempI As Integer

If Not (ctrl.ControlType = acComboBox Or ctrl.ControlType = acListBox) Then
MsgBox "Must be a combo or list box"
Exit Sub
End If
For i = ctrl.ListCount - 1 To 0 Step -1
coll.Add ctrl.ItemData(i)
ctrl.RemoveItem (i)
Next i
Do While coll.Count > 0
tempVal = Null
tempI = 0
For i = coll.Count To 1 Step -1
If IsNull(tempVal) Then
tempVal = coll(i)
tempI = i
ElseIf Not IsNull(coll(i)) And coll(i) < tempVal Then
tempVal = coll(i)
tempI = i
End If
Next i
ctrl.AddItem tempVal
coll.Remove (tempI)
Loop
End Sub