?
Solved

does field exist

Posted on 2005-05-17
9
Medium Priority
?
841 Views
Last Modified: 2010-05-18
Hello Guys,
                  I need to determine if a field exist in a an open recordset. Please help. Thanks.

Serge
0
Comment
Question by:Autofreak
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:RDWaibel
ID: 14019258
you can use the Fields method in a records set like so:

Dim RS as DAO.Recordset
Dim sSQL as string, X as integer

ssql = "Select * from MyTable"
set rs = currentDB.openrecordset(ssql)

for x = 0 to rs.fields.count -1
    debug.print rs.fields(x).name
next x

from here you could look for a specific field name
0
 
LVL 8

Expert Comment

by:arcross
ID: 14019294

or you cant try to open the recordset with that field in the select query:

dim rst as adodb.recordset
set rst = new adodb.recordset
rst.open "SELECT " & YOURFIELD & " FROM YOURTABLE", currentproject.connection,.....

Now, trap the error and check if it couldnt find the file you passed to look for.


Álvaro
0
 

Author Comment

by:Autofreak
ID: 14019454
Well, that's exactly what I tried to do while waiting for the reply. There's one problem though. I have this process in a loop and this error trap I made catches this error only once. The second time i got an error message again whereas I want the execution goto reference, say 1, which is exactly what is instructed in Error_Handler: at the end of the code. How do I make the error trap work each time an error occurs.

Thanks,
Serge
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:arcross
ID: 14019855
Hi Serge, this is an example that loops through few fields in the table and checks the ones thta exists.
The table is called Table1. And the fields are: Code1,Code2

On Error Resume Next
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim str(2) As String
Dim i As Integer
Dim strMSG As String

str(0) = "Code3"   ' Doesnt exists
str(1) = "code2"   ' Exists
str(2) = "Code4"   ' Doesnt Eixsts

For i = 0 To UBound(str())
    rst.Open "SELECT " & str(i) & " FROM Table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    If Err <> 0 Then
        Err = 0
        strMSG = strMSG & " FIELD: " & str(i) & " DOESNT EXISTS" & vbCrLf
    Else
        strMSG = strMSG & " FIELD: " & str(i) & " EXISTS" & vbCrLf
    End If
       
Next
MsgBox strMSG

End Sub


you can use the loop that RDWaibel posted, then you dont need to open the recordset everytime. But this one will work as well

Álvaro
0
 

Author Comment

by:Autofreak
ID: 14020022
Thanks arcross,
                       The problem is that I work with ADO only and a recordset is already open.
                   All I need to do before I post values to Table2 fields from Table1 is making sure that the fields with the same name exist in Table2 and Table1. You see, Table2 is a subset of table1 and not all fileds from table1 are present in table2. So when the code loops thru all the fields and records in table1 to post them to table2 ,cases when there is no corresponding filed in table2 should be trapped and deal with in the error handler.

Sorry if that sounds ambiguous.
Serge
0
 
LVL 8

Accepted Solution

by:
arcross earned 500 total points
ID: 14021882
Hi serge, waht about this...

On Error Resume Next

Dim rstTABLE1 As ADODB.Recordset
Dim rstTABLE2 As ADODB.Recordset
Dim i As Integer
Dim strFIELD As String

Set rstTABLE1 = New ADODB.Recordset
Set rstTABLE2 = New ADODB.Recordset

rstTABLE1.Open "SELECT * FROM TABLE1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstTABLE2.Open "SELECT * FROM TABLE2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    For i = 0 To rstTABLE1.Fields.Count - 1

        strFIELD = rstTABLE2.Fields(rstTABLE1.Fields(i).Name).Name               ' This assigns the field name into a variable, if it doesn exists, then err <> 0
       
        If Err = 0 Then                                                                                 ' If the field exists in TABLE 2 then copy it.
            With rstTABLE2
                .AddNew
                    .Fields(strFIELD).Value = rstTABLE1.Fields(i).Value
                .Update
            End With
        Else
            Err = 0                                                                                        ' REset error
        End If
    Next i

This will copy the value to the same field in the table2.  This example will copy only the first record in table 1. To copy all, you must loop through all records first and add a rstTABLE1.movenext and rstTABLE2.movenext.

If you need for info, let me know.

Álvaro
0
 
LVL 8

Expert Comment

by:arcross
ID: 14021942
Serge, ive done for you what i said above. Look through all records in table1 and copy into table2

On Error Resume Next

Dim rstTABLE1 As ADODB.Recordset
Dim rstTABLE2 As ADODB.Recordset
Dim i As Integer
Dim strFIELD As String

Set rstTABLE1 = New ADODB.Recordset
Set rstTABLE2 = New ADODB.Recordset

rstTABLE1.Open "SELECT * FROM TABLE1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstTABLE2.Open "SELECT * FROM TABLE2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

   
    Do Until rstTABLE1.EOF
       
        For i = 0 To rstTABLE1.Fields.Count - 1

            strFIELD = rstTABLE2.Fields(rstTABLE1.Fields(i).Name).Name
       
            If Err = 0 Then
                With rstTABLE2
                    .AddNew
                        .Fields(strFIELD).Value = rstTABLE1.Fields(i).Value
                    .Update
                End With
            Else
                Err = 0
            End If
        Next i
       
        rstTABLE1.MoveNext
        rstTABLE2.MoveNext
       
    Loop
0
 
LVL 8

Expert Comment

by:arcross
ID: 14035882
is it working?
0
 

Author Comment

by:Autofreak
ID: 14069715
Hey arcross,
                    sorry for the delay, just got carried away by other stuff.
                    The code looks neat. I have not tried that but I like the idea so It has no right not to work. Thanks a lot for the hard work.

Serge
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question