Finding a control on a form

[Dryseals]

I have a db that has been modified over and over again. What I have is a form for entering new data, the controls are all unbound. When the data input is finally completed, the user will click a button and the data checked before adding to the table.
The table has far more fields than is required but is still very large.
What I would like to do is to open the table and read the field names which is simple enough. Then see if there is a control name on the form the same field name in the table. If there is no control with the same field name, skip it and go on. I'm stumped.

 

[hkaing79]

Wouldn't it be better to loop through the controls and see if the field exists in the table?

 

[Dryseals]

Yes that works, these sinuses have me so numbed, I can't think straight. Sometimes its easier to see it from the outside.
Thanks
Mike

 

[Tranman]

Dryseals,
I wrote something really close to what you are wanting a couple of months ago. Take a look at this and see if you can make it fit your need:

Private Sub Command9_Click()
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim ctrl As Control
Dim strtbName As String

On Error GoTo Err_Command9_Click

rs.Open "Select * from cust where 1 = 2", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
For Each ctrl In Me.Controls
  Select Case ctrl.ControlType
    Case acTextBox
      strtbName = UCase(ctrl.Name)
      For Each fld In rs.Fields
        Select Case UCase(fld.Name)
          Case Is = strtbName
            Debug.Print strtbName
            Exit For
        End Select
      Next
  End Select
Next

rs.Close
Set rs = Nothing

Exit_Command9_Click:
    Exit Sub

Err_Command9_Click:
    MsgBox Err.Description
    Resume Exit_Command9_Click
    
End Sub

It runs from a command button on the form--you would probably want to do that differently. But it does check each textbox control for a matching field in the table, which seems to be what you're wanting.

Good luck,
Tranman

 

[hkaing79]

I was going to suggest something like...

For each ctl in Me.Controls
If IsNull(DLookup(ctl.Name, "table name")) Then
//Control is not in table
End If
Next

 

[Tranman]

hkaing79,
Tried your code. At least on my system--Access 2003/Win XP Pro, I encountered the following problems:

1) If the field is present in the table, but contains no data (all rows null), the field is treated as if it were not there.

2) If the field is not present I get a Run-time error 2001; "You cancelled the previous operation."

It seems to me that you somehow have to get into a fields collection in order to determine if a field exists. The fields collection in a recordset is pretty easy to get to.

You could also instantiate a tabledef from the tabledefs collection of the currentdb object, and use the tabledef's fields collection, but that seemed even more oblique than using the recordset's fields:

Private Sub blahblah()
Dim tbl As dao.TableDef
Dim ctl As Control
Dim fld As dao.Field
Dim dbs As Database
Set dbs = CurrentDb
For Each ctl In Forms!myform.Controls
  'Debug.Print ctl.Name
  Set tbl = dbs.TableDefs("CUST")
  For Each fld In tbl.Fields
    If fld.Name = ctl.Name Then
      Debug.Print ctl.Name
      Exit For
    End If
  Next
Next
End Sub

There are many ways to accomplish almost any task in code...

Tranman

 

[Dryseals]

I used a couple of short loops to do what I needed.

Dim dbs As Database, rst As Recordset, Brst As Recordset
Dim NM As String
Dim oControl As Control

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Add Parts", dbOpenDynaset)
Set Brst = dbs.OpenRecordset("Add Parts Base", dbOpenDynaset)
FC = rst.Fields.Count

For I = 1 To FC
NM = rst.Fields(I - 1).Name

For Each oControl In Controls
If oControl.Name = NM Then
Do what you want in here
End If
Next
Next I


This worked the way I wanted, I was just hoping there was a way to find the control in some sort of collection without having to loop through.

 

[Tranman]

Dryseals,
Like I said, there are many ways to solve a problem. Yours works just as well as mine, but in circumstances like I have at work (many of my tables have 200,000 to 1,000,000 rows), my use of a query instead of a table:

rs.Open "Select * from cust where 1 = 2", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

will run a lot more efficiently because it always returns a recordset that has no data--only structure (1 is never equal to 2).

All in all, a nice piece of code you wrote. I had never done the (I - 1) trick you used, but I *will* be using it in the future.

Thanks,
Tranman

 

[PHV]

I had never done the (I - 1) trick you used, but I *will* be using it in the future
I personally prefer the following:
For I = 0 To FC - 1
NM = rst.Fields(I).Name

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