Checking for Existing Field in Table

Ok I have the following code:

Function CheckIfFieldExist(TableName As String, FieldName As String) As
Boolean
On Error GoTo CheckIfFieldExist_Err
'  Function that check if a field exist in the table
'  Return True if exist , and False if doesn't exist
Dim I As String
CheckIfFieldExist = True
I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
Exit Function
CheckIfFieldExist_Err:
   If Err = 3265 Then
       CheckIfFieldExist = False
   Else
       MsgBox Error
   End If

End Function

this is the other half of the code:

'Late

If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
  ' Exist
Else
  ' Doesn't Exist
  
    Dim dbCurLate As Database
    Dim tdfLate As TableDef
    Dim fldLate As Field
  
    Set dbCurLate = CurrentDb()
    Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
    Set fldLate = tdfLate.CreateField("Late", dbInteger)
  
    tdfLate.Fields.Append fldLate
    tdfLate.Fields.Refresh
End If

Problem is....regardless or whether or not "Late" exists (True or False The
code tries to create the "Late" field...of course if its already there I get
an error message.  I dont want it to try and create if its there....I've
looked at this code until Im crosseyed...Someone see what the hell Im doing
wrong?
Thanx!
RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/3/2010 6:26:22 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

18 Replies
1112 Views

Similar Articles

[PageSpeed] 56

A couple things come to mind.

1.  I never set a function to True as the default and then switch it to 
false.  I always do the inverse to avoid false positives.  Below is what I 
would do:

Function CheckIfFieldExist(TableName As String, FieldName As String) As 
Boolean
'  Function that check if a field exist in the table
'  Return True if exist , and False if doesn't exist
   Dim I    As String

On Error GoTo Error_Handler

   CheckIfFieldExist = False
   I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
   CheckIfFieldExist = True   'If we made it to hear without triggering an
                              'error all is good

Error_Handler_Exit:
   On Error Resume Next
   Exit Function

Error_Handler:
   If Err = 3265 Then   'Field not found in the specified table
   Else
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf 
& "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: CheckIfFieldExist" & vbCrLf & 
"Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
   End If
   Resume Error_Handler_Exit
End Function


Sub AddField2Tbl(TableName As String)
   Dim dbCurLate  As Database
   Dim tdfLate    As TableDef
   Dim fldLate    As Field
   
On Error GoTo Error_Handler

   If CheckIfFieldExist(TableName, "Late") = True Then
     ' Exist
   Else
     ' Doesn't Exist
     
       Set dbCurLate = CurrentDb()
       Set tdfLate = dbCurLate.TableDefs(TableName)
       Set fldLate = tdfLate.CreateField("Late", dbInteger)
     
       tdfLate.Fields.Append fldLate
       tdfLate.Fields.Refresh
   End If

Error_Handler_Exit:
   On Error Resume Next
   Set fldLate = Nothing
   Set tdfLate = Nothing
   Set dbCurLate = Nothing
   Exit Sub

Error_Handler:
   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & 
"Error Number: " & _
   Err.Number & vbCrLf & "Error Source: AddField2Tbl" & vbCrLf & "Error 
Description: " & _
   Err.Description, vbCritical, "An Error has Occured!"
   Resume Error_Handler_Exit
End Sub



2. Another method would be to simply add the field without testing and 
simply trap the error that arises if the field already exists.
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"RedHeadedMonster via AccessMonster.com" wrote:

> Ok I have the following code:
> 
> Function CheckIfFieldExist(TableName As String, FieldName As String) As
> Boolean
> On Error GoTo CheckIfFieldExist_Err
> '  Function that check if a field exist in the table
> '  Return True if exist , and False if doesn't exist
> Dim I As String
> CheckIfFieldExist = True
> I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
> Exit Function
> CheckIfFieldExist_Err:
>    If Err = 3265 Then
>        CheckIfFieldExist = False
>    Else
>        MsgBox Error
>    End If
> 
> End Function
> 
> this is the other half of the code:
> 
> 'Late
> 
> If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
>   ' Exist
> Else
>   ' Doesn't Exist
>   
>     Dim dbCurLate As Database
>     Dim tdfLate As TableDef
>     Dim fldLate As Field
>   
>     Set dbCurLate = CurrentDb()
>     Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
>     Set fldLate = tdfLate.CreateField("Late", dbInteger)
>   
>     tdfLate.Fields.Append fldLate
>     tdfLate.Fields.Refresh
> End If
> 
> Problem is....regardless or whether or not "Late" exists (True or False The
> code tries to create the "Late" field...of course if its already there I get
> an error message.  I dont want it to try and create if its there....I've
> looked at this code until Im crosseyed...Someone see what the hell Im doing
> wrong?
> Thanx!
> RHM
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 
> .
> 
0
Utf
2/3/2010 7:47:08 PM
If you are trying to insert a new field in an Access table, what safeguards 
do you have to ensure that the new field actually BELONGS in that table? 
That is, if you want to get the best use of Access' relationally-oriented 
features/functions, you need to "feed" it well-normalized data.  How are you 
ensuring that the new field "fits" the table?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RedHeadedMonster via AccessMonster.com" <u36819@uwe> wrote in message 
news:a317894e28334@uwe...
> Ok I have the following code:
>
> Function CheckIfFieldExist(TableName As String, FieldName As String) As
> Boolean
> On Error GoTo CheckIfFieldExist_Err
> '  Function that check if a field exist in the table
> '  Return True if exist , and False if doesn't exist
> Dim I As String
> CheckIfFieldExist = True
> I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
> Exit Function
> CheckIfFieldExist_Err:
>   If Err = 3265 Then
>       CheckIfFieldExist = False
>   Else
>       MsgBox Error
>   End If
>
> End Function
>
> this is the other half of the code:
>
> 'Late
>
> If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
>  ' Exist
> Else
>  ' Doesn't Exist
>
>    Dim dbCurLate As Database
>    Dim tdfLate As TableDef
>    Dim fldLate As Field
>
>    Set dbCurLate = CurrentDb()
>    Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
>    Set fldLate = tdfLate.CreateField("Late", dbInteger)
>
>    tdfLate.Fields.Append fldLate
>    tdfLate.Fields.Refresh
> End If
>
> Problem is....regardless or whether or not "Late" exists (True or False 
> The
> code tries to create the "Late" field...of course if its already there I 
> get
> an error message.  I dont want it to try and create if its there....I've
> looked at this code until Im crosseyed...Someone see what the hell Im 
> doing
> wrong?
> Thanx!
> RHM
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 


0
Jeff
2/3/2010 7:54:30 PM
Thanks....but even with your mods its still doing exactly the same thing.
Trying to add the field whether its there or not (TRUE or FALSE) and hanging
on the error when it tries to add a field thats there already.
*pulling hair out*

Daniel Pineault wrote:
>A couple things come to mind.
>
>1.  I never set a function to True as the default and then switch it to 
>false.  I always do the inverse to avoid false positives.  Below is what I 
>would do:
>
>Function CheckIfFieldExist(TableName As String, FieldName As String) As 
>Boolean
>'  Function that check if a field exist in the table
>'  Return True if exist , and False if doesn't exist
>   Dim I    As String
>
>On Error GoTo Error_Handler
>
>   CheckIfFieldExist = False
>   I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
>   CheckIfFieldExist = True   'If we made it to hear without triggering an
>                              'error all is good
>
>Error_Handler_Exit:
>   On Error Resume Next
>   Exit Function
>
>Error_Handler:
>   If Err = 3265 Then   'Field not found in the specified table
>   Else
>      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf 
>& "Error Number: " & _
>      Err.Number & vbCrLf & "Error Source: CheckIfFieldExist" & vbCrLf & 
>"Error Description: " & _
>      Err.Description, vbCritical, "An Error has Occured!"
>   End If
>   Resume Error_Handler_Exit
>End Function
>
>Sub AddField2Tbl(TableName As String)
>   Dim dbCurLate  As Database
>   Dim tdfLate    As TableDef
>   Dim fldLate    As Field
>   
>On Error GoTo Error_Handler
>
>   If CheckIfFieldExist(TableName, "Late") = True Then
>     ' Exist
>   Else
>     ' Doesn't Exist
>     
>       Set dbCurLate = CurrentDb()
>       Set tdfLate = dbCurLate.TableDefs(TableName)
>       Set fldLate = tdfLate.CreateField("Late", dbInteger)
>     
>       tdfLate.Fields.Append fldLate
>       tdfLate.Fields.Refresh
>   End If
>
>Error_Handler_Exit:
>   On Error Resume Next
>   Set fldLate = Nothing
>   Set tdfLate = Nothing
>   Set dbCurLate = Nothing
>   Exit Sub
>
>Error_Handler:
>   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & 
>"Error Number: " & _
>   Err.Number & vbCrLf & "Error Source: AddField2Tbl" & vbCrLf & "Error 
>Description: " & _
>   Err.Description, vbCritical, "An Error has Occured!"
>   Resume Error_Handler_Exit
>End Sub
>
>2. Another method would be to simply add the field without testing and 
>simply trap the error that arises if the field already exists.
>> Ok I have the following code:
>> 
>[quoted text clipped - 44 lines]
>> Thanx!
>> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/3/2010 8:18:05 PM
Because the code only fires when it belongs in the table and isnt.

Jeff Boyce wrote:
>If you are trying to insert a new field in an Access table, what safeguards 
>do you have to ensure that the new field actually BELONGS in that table? 
>That is, if you want to get the best use of Access' relationally-oriented 
>features/functions, you need to "feed" it well-normalized data.  How are you 
>ensuring that the new field "fits" the table?
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> Ok I have the following code:
>>
>[quoted text clipped - 47 lines]
>> Thanx!
>> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/3/2010 8:19:35 PM
Let me try stating my concern a different way...

How will your code prevent someone from adding a field to hold "My 
Grandmother's Age" into a table of "Customers"?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RedHeadedMonster via AccessMonster.com" <u36819@uwe> wrote in message 
news:a3188662498f9@uwe...
> Because the code only fires when it belongs in the table and isnt.
>
> Jeff Boyce wrote:
>>If you are trying to insert a new field in an Access table, what 
>>safeguards
>>do you have to ensure that the new field actually BELONGS in that table?
>>That is, if you want to get the best use of Access' relationally-oriented
>>features/functions, you need to "feed" it well-normalized data.  How are 
>>you
>>ensuring that the new field "fits" the table?
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Access MVP
>>
>>> Ok I have the following code:
>>>
>>[quoted text clipped - 47 lines]
>>> Thanx!
>>> RHM
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 


0
Jeff
2/3/2010 8:57:20 PM
That's very odd because I tried it at my end and it works fine for me.

What error is raise?
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"RedHeadedMonster via AccessMonster.com" wrote:

> Thanks....but even with your mods its still doing exactly the same thing.
> Trying to add the field whether its there or not (TRUE or FALSE) and hanging
> on the error when it tries to add a field thats there already.
> *pulling hair out*
> 
> Daniel Pineault wrote:
> >A couple things come to mind.
> >
> >1.  I never set a function to True as the default and then switch it to 
> >false.  I always do the inverse to avoid false positives.  Below is what I 
> >would do:
> >
> >Function CheckIfFieldExist(TableName As String, FieldName As String) As 
> >Boolean
> >'  Function that check if a field exist in the table
> >'  Return True if exist , and False if doesn't exist
> >   Dim I    As String
> >
> >On Error GoTo Error_Handler
> >
> >   CheckIfFieldExist = False
> >   I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
> >   CheckIfFieldExist = True   'If we made it to hear without triggering an
> >                              'error all is good
> >
> >Error_Handler_Exit:
> >   On Error Resume Next
> >   Exit Function
> >
> >Error_Handler:
> >   If Err = 3265 Then   'Field not found in the specified table
> >   Else
> >      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf 
> >& "Error Number: " & _
> >      Err.Number & vbCrLf & "Error Source: CheckIfFieldExist" & vbCrLf & 
> >"Error Description: " & _
> >      Err.Description, vbCritical, "An Error has Occured!"
> >   End If
> >   Resume Error_Handler_Exit
> >End Function
> >
> >Sub AddField2Tbl(TableName As String)
> >   Dim dbCurLate  As Database
> >   Dim tdfLate    As TableDef
> >   Dim fldLate    As Field
> >   
> >On Error GoTo Error_Handler
> >
> >   If CheckIfFieldExist(TableName, "Late") = True Then
> >     ' Exist
> >   Else
> >     ' Doesn't Exist
> >     
> >       Set dbCurLate = CurrentDb()
> >       Set tdfLate = dbCurLate.TableDefs(TableName)
> >       Set fldLate = tdfLate.CreateField("Late", dbInteger)
> >     
> >       tdfLate.Fields.Append fldLate
> >       tdfLate.Fields.Refresh
> >   End If
> >
> >Error_Handler_Exit:
> >   On Error Resume Next
> >   Set fldLate = Nothing
> >   Set tdfLate = Nothing
> >   Set dbCurLate = Nothing
> >   Exit Sub
> >
> >Error_Handler:
> >   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & 
> >"Error Number: " & _
> >   Err.Number & vbCrLf & "Error Source: AddField2Tbl" & vbCrLf & "Error 
> >Description: " & _
> >   Err.Description, vbCritical, "An Error has Occured!"
> >   Resume Error_Handler_Exit
> >End Sub
> >
> >2. Another method would be to simply add the field without testing and 
> >simply trap the error that arises if the field already exists.
> >> Ok I have the following code:
> >> 
> >[quoted text clipped - 44 lines]
> >> Thanx!
> >> RHM
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 
> .
> 
0
Utf
2/3/2010 9:31:02 PM
Because the code only adds a field name of LATE to a specific table at a
specific request of a specific report, but only if its not there already.
They dont get to decide what table or what information only that they want to
see a report.  They arent writing code, they arent editing information, they
aren't doing anything but selecting pre-ordained parameters to run a report. 

All Im looking for is a second set of eyeballs hopefully picking up something
that I've done wrong in the code that Im not seeing because I've looked at it
all day, and so far it attempts to add Late if its not there and if it is
there, I just want it added if its not.  So can you help with the problem or
not?

Thanx
RHM


Jeff Boyce wrote:
>Let me try stating my concern a different way...
>
>How will your code prevent someone from adding a field to hold "My 
>Grandmother's Age" into a table of "Customers"?
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> Because the code only fires when it belongs in the table and isnt.
>>
>[quoted text clipped - 16 lines]
>>>> Thanx!
>>>> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/3/2010 9:34:39 PM
3191

Daniel Pineault wrote:
>That's very odd because I tried it at my end and it works fine for me.
>
>What error is raise?
>> Thanks....but even with your mods its still doing exactly the same thing.
>> Trying to add the field whether its there or not (TRUE or FALSE) and hanging
>[quoted text clipped - 79 lines]
>> >> Thanx!
>> >> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/3/2010 9:55:50 PM
3191: Can not define define field more than once
Because its trying to add it even tho its already there.

RedHeadedMonster wrote:
>3191
>
>>That's very odd because I tried it at my end and it works fine for me.
>>
>[quoted text clipped - 4 lines]
>>> >> Thanx!
>>> >> RHM

-- 
Message posted via http://www.accessmonster.com

0
RedHeadedMonster
2/3/2010 9:58:11 PM
Here is a different function that checks a different way.  It is probably a 
bit slower, but not noticeably on most computers unless your were doing this 
in a loop for some reason and checking a very large number of tables and fields.

Function CheckIfFieldExists(strTableName As String, _
                             strFieldName As String) As Boolean
Dim tDef As TableDef
Dim db As DAO.Database
Dim tfTableCheck As Boolean, tfFieldCheck As Boolean
Dim fldAny As Field

Set db = CurrentDb()
'Make sure the table exists
For Each tDef In db.TableDefs
    If tDef.Name = strTableName Then
       'Now check for the field
       tfTableCheck = True
       For Each fldAny In tDef.Fields
          If fldAny.Name = strFieldName Then
             tfFieldCheck = True
             Exit For
          End If
          Exit For
       Next fldAny
    End If
Next tDef

If tfTableCheck = False Then
    'MsgBox "No such table"
    CheckIfFieldExists = False
Else
    CheckIfFieldExists = tfFieldCheck
End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

RedHeadedMonster via AccessMonster.com wrote:
> Ok I have the following code:
> 
> Function CheckIfFieldExist(TableName As String, FieldName As String) As
> Boolean
> On Error GoTo CheckIfFieldExist_Err
> '  Function that check if a field exist in the table
> '  Return True if exist , and False if doesn't exist
> Dim I As String
> CheckIfFieldExist = True
> I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
> Exit Function
> CheckIfFieldExist_Err:
>    If Err = 3265 Then
>        CheckIfFieldExist = False
>    Else
>        MsgBox Error
>    End If
> 
> End Function
> 
> this is the other half of the code:
> 
> 'Late
> 
> If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
>   ' Exist
> Else
>   ' Doesn't Exist
>   
>     Dim dbCurLate As Database
>     Dim tdfLate As TableDef
>     Dim fldLate As Field
>   
>     Set dbCurLate = CurrentDb()
>     Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
>     Set fldLate = tdfLate.CreateField("Late", dbInteger)
>   
>     tdfLate.Fields.Append fldLate
>     tdfLate.Fields.Refresh
> End If
> 
> Problem is....regardless or whether or not "Late" exists (True or False The
> code tries to create the "Late" field...of course if its already there I get
> an error message.  I dont want it to try and create if its there....I've
> looked at this code until Im crosseyed...Someone see what the hell Im doing
> wrong?
> Thanx!
> RHM
> 
0
John
2/4/2010 12:36:59 AM
Thanks for the clarification.  I still don't understand the why well enough 
to be able to help.  Hopefully one of the other newsgroup readers can help.

It sounds like you're using this approach to work out a report ... if so, it 
isn't necessary and is rarely a good idea to create a table in Access simply 
to pull together the data for a report.  In most instances, you can use 
queries.

I've been trying to learn enough about your business need (not the technique 
you propose) to see if there might be other options available to you.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RedHeadedMonster via AccessMonster.com" <u36819@uwe> wrote in message 
news:a3192e4fd1049@uwe...
> Because the code only adds a field name of LATE to a specific table at a
> specific request of a specific report, but only if its not there already.
> They dont get to decide what table or what information only that they want 
> to
> see a report.  They arent writing code, they arent editing information, 
> they
> aren't doing anything but selecting pre-ordained parameters to run a 
> report.
>
> All Im looking for is a second set of eyeballs hopefully picking up 
> something
> that I've done wrong in the code that Im not seeing because I've looked at 
> it
> all day, and so far it attempts to add Late if its not there and if it is
> there, I just want it added if its not.  So can you help with the problem 
> or
> not?
>
> Thanx
> RHM
>
>
> Jeff Boyce wrote:
>>Let me try stating my concern a different way...
>>
>>How will your code prevent someone from adding a field to hold "My
>>Grandmother's Age" into a table of "Customers"?
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Access MVP
>>
>>> Because the code only fires when it belongs in the table and isnt.
>>>
>>[quoted text clipped - 16 lines]
>>>>> Thanx!
>>>>> RHM
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 


0
Jeff
2/4/2010 12:56:03 AM
Changed to this and still getting error 3191.  So code still not recognizing
that field check = True.
Thanx for suggestion tho.

John Spencer wrote:
>Here is a different function that checks a different way.  It is probably a 
>bit slower, but not noticeably on most computers unless your were doing this 
>in a loop for some reason and checking a very large number of tables and fields.
>
>Function CheckIfFieldExists(strTableName As String, _
>                             strFieldName As String) As Boolean
>Dim tDef As TableDef
>Dim db As DAO.Database
>Dim tfTableCheck As Boolean, tfFieldCheck As Boolean
>Dim fldAny As Field
>
>Set db = CurrentDb()
>'Make sure the table exists
>For Each tDef In db.TableDefs
>    If tDef.Name = strTableName Then
>       'Now check for the field
>       tfTableCheck = True
>       For Each fldAny In tDef.Fields
>          If fldAny.Name = strFieldName Then
>             tfFieldCheck = True
>             Exit For
>          End If
>          Exit For
>       Next fldAny
>    End If
>Next tDef
>
>If tfTableCheck = False Then
>    'MsgBox "No such table"
>    CheckIfFieldExists = False
>Else
>    CheckIfFieldExists = tfFieldCheck
>End If
>
>End Function
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> Ok I have the following code:
>> 
>[quoted text clipped - 44 lines]
>> Thanx!
>> RHM

-- 
Message posted via http://www.accessmonster.com

0
RedHeadedMonster
2/4/2010 2:46:01 PM
Yes definitely using approach to work out report.
How would you do it with a query?

Jeff Boyce wrote:
>Thanks for the clarification.  I still don't understand the why well enough 
>to be able to help.  Hopefully one of the other newsgroup readers can help.
>
>It sounds like you're using this approach to work out a report ... if so, it 
>isn't necessary and is rarely a good idea to create a table in Access simply 
>to pull together the data for a report.  In most instances, you can use 
>queries.
>
>I've been trying to learn enough about your business need (not the technique 
>you propose) to see if there might be other options available to you.
>
>Good luck!
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> Because the code only adds a field name of LATE to a specific table at a
>> specific request of a specific report, but only if its not there already.
>[quoted text clipped - 32 lines]
>>>>>> Thanx!
>>>>>> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/4/2010 2:47:11 PM
The function works for me.  I tested it with a bad tablename and a bad field 
name in an existing table and with valid table and field names.  It only 
returned true in the last case.

You can test to see if the function is working by using the VBA immediate 
window and entering
   ?CheckIfFieldExists("SomeTable","SomeField")
and varying the validity of the table and field names you are passing

I am going to guess that something may have corrupted your database or that 
something in your calling function is wrong
If CheckIfFieldExists("tblCDRLMetricsFinal2", "Late")=True Then
   ' Exists
Else
  ...
End If

If corruption in the VBA code is the problem, you can try the following from 
Allen Browne.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
     Tools | Options | General | Name AutoCorrect
       Explanation of why:   http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
     Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
     Decompile the database by entering something like this at the command 
prompt while  Access is not running. It is all one line, and includes the quotes:
     "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
         "c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
     http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors 
are gone, the indexes are repaired, inconsistencies between the text- and 
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the 
database for you. Follow the steps for the first symptom in this article:
     Recovering from Corruption
at:
     http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html Reply to group, 
rather than allenbrowne at mvps dot org.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

RedHeadedMonster via AccessMonster.com wrote:
> Changed to this and still getting error 3191.  So code still not recognizing
> that field check = True.
> Thanx for suggestion tho.
> 
> John Spencer wrote:
>> Here is a different function that checks a different way.  It is probably a 
>> bit slower, but not noticeably on most computers unless your were doing this 
>> in a loop for some reason and checking a very large number of tables and fields.
>>
>> Function CheckIfFieldExists(strTableName As String, _
>>                             strFieldName As String) As Boolean
>> Dim tDef As TableDef
>> Dim db As DAO.Database
>> Dim tfTableCheck As Boolean, tfFieldCheck As Boolean
>> Dim fldAny As Field
>>
>> Set db = CurrentDb()
>> 'Make sure the table exists
>> For Each tDef In db.TableDefs
>>    If tDef.Name = strTableName Then
>>       'Now check for the field
>>       tfTableCheck = True
>>       For Each fldAny In tDef.Fields
>>          If fldAny.Name = strFieldName Then
>>             tfFieldCheck = True
>>             Exit For
>>          End If
>>          Exit For
>>       Next fldAny
>>    End If
>> Next tDef
>>
>> If tfTableCheck = False Then
>>    'MsgBox "No such table"
>>    CheckIfFieldExists = False
>> Else
>>    CheckIfFieldExists = tfFieldCheck
>> End If
>>
>> End Function
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>>> Ok I have the following code:
>>>
>> [quoted text clipped - 44 lines]
>>> Thanx!
>>> RHM
> 
0
John
2/4/2010 3:45:24 PM
How depends on what.  What data structure do you have?  Knowing that would 
provide folks here a way to offer suggestions on using queries.

As a general approach, you may find it much more flexible (and much less 
work) to use queries as your sources for your reports.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"RedHeadedMonster via AccessMonster.com" <u36819@uwe> wrote in message 
news:a32232650f9fe@uwe...
> Yes definitely using approach to work out report.
> How would you do it with a query?
>
> Jeff Boyce wrote:
>>Thanks for the clarification.  I still don't understand the why well 
>>enough
>>to be able to help.  Hopefully one of the other newsgroup readers can 
>>help.
>>
>>It sounds like you're using this approach to work out a report ... if so, 
>>it
>>isn't necessary and is rarely a good idea to create a table in Access 
>>simply
>>to pull together the data for a report.  In most instances, you can use
>>queries.
>>
>>I've been trying to learn enough about your business need (not the 
>>technique
>>you propose) to see if there might be other options available to you.
>>
>>Good luck!
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Access MVP
>>
>>> Because the code only adds a field name of LATE to a specific table at a
>>> specific request of a specific report, but only if its not there 
>>> already.
>>[quoted text clipped - 32 lines]
>>>>>>> Thanx!
>>>>>>> RHM
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 


0
Jeff
2/4/2010 4:24:56 PM
This is using ADO:

Private Function Check4Field(strTableName As String)

    Dim fldName As Variant
    Dim bFieldExists As Boolean
    Dim rs As New ADODB.Recordset
    
    rs.Open strTableName, CurrentProject.Connection
    
    bFieldExists = False
    For Each fldName In rs.Fields
        'Debug.Print fldName.Name
        If fldName.Name = "late" Then
            bFieldExists = True
            Exit For
        End If
    Next fldName
    rs.Close
    Set rs = Nothing
    
    Check4Field = "Field 'Late' does not exist."
    If bFieldExists Then Check4Field = "Field 'Late' exists."

End Function






"RedHeadedMonster via AccessMonster.com" wrote:

> Ok I have the following code:
> 
> Function CheckIfFieldExist(TableName As String, FieldName As String) As
> Boolean
> On Error GoTo CheckIfFieldExist_Err
> '  Function that check if a field exist in the table
> '  Return True if exist , and False if doesn't exist
> Dim I As String
> CheckIfFieldExist = True
> I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
> Exit Function
> CheckIfFieldExist_Err:
>    If Err = 3265 Then
>        CheckIfFieldExist = False
>    Else
>        MsgBox Error
>    End If
> 
> End Function
> 
> this is the other half of the code:
> 
> 'Late
> 
> If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
>   ' Exist
> Else
>   ' Doesn't Exist
>   
>     Dim dbCurLate As Database
>     Dim tdfLate As TableDef
>     Dim fldLate As Field
>   
>     Set dbCurLate = CurrentDb()
>     Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
>     Set fldLate = tdfLate.CreateField("Late", dbInteger)
>   
>     tdfLate.Fields.Append fldLate
>     tdfLate.Fields.Refresh
> End If
> 
> Problem is....regardless or whether or not "Late" exists (True or False The
> code tries to create the "Late" field...of course if its already there I get
> an error message.  I dont want it to try and create if its there....I've
> looked at this code until Im crosseyed...Someone see what the hell Im doing
> wrong?
> Thanx!
> RHM
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1
> 
> .
> 
0
Utf
2/6/2010 3:54:01 AM
I always use queries as the source for my reports.  This one was special case
because it involved using several parameters that the user could apply to
data that was being used by a crosstab query.  It was just easier to create a
temporary table to hold the special request and then run the crosstab.  You
probably know how persnickety crosstabs are.  Anyway, it got it to work...and
its working wonderfully.  

Jeff Boyce wrote:
>How depends on what.  What data structure do you have?  Knowing that would 
>provide folks here a way to offer suggestions on using queries.
>
>As a general approach, you may find it much more flexible (and much less 
>work) to use queries as your sources for your reports.
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> Yes definitely using approach to work out report.
>> How would you do it with a query?
>[quoted text clipped - 28 lines]
>>>>>>>> Thanx!
>>>>>>>> RHM

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
RedHeadedMonster
2/8/2010 7:07:12 PM
Finally got it to work, i went with checking for the existance of the field
and trapping for the error.  Works like a charm.
Thanks for your time and your suggestions.
Have a Nice Day!
RHM

John Spencer wrote:
>Here is a different function that checks a different way.  It is probably a 
>bit slower, but not noticeably on most computers unless your were doing this 
>in a loop for some reason and checking a very large number of tables and fields.
>
>Function CheckIfFieldExists(strTableName As String, _
>                             strFieldName As String) As Boolean
>Dim tDef As TableDef
>Dim db As DAO.Database
>Dim tfTableCheck As Boolean, tfFieldCheck As Boolean
>Dim fldAny As Field
>
>Set db = CurrentDb()
>'Make sure the table exists
>For Each tDef In db.TableDefs
>    If tDef.Name = strTableName Then
>       'Now check for the field
>       tfTableCheck = True
>       For Each fldAny In tDef.Fields
>          If fldAny.Name = strFieldName Then
>             tfFieldCheck = True
>             Exit For
>          End If
>          Exit For
>       Next fldAny
>    End If
>Next tDef
>
>If tfTableCheck = False Then
>    'MsgBox "No such table"
>    CheckIfFieldExists = False
>Else
>    CheckIfFieldExists = tfFieldCheck
>End If
>
>End Function
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> Ok I have the following code:
>> 
>[quoted text clipped - 44 lines]
>> Thanx!
>> RHM

-- 
Message posted via http://www.accessmonster.com

0
RedHeadedMonster
2/8/2010 7:09:22 PM
Reply:

Similar Artilces:

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

Prevent new pivot table items from being selected
I have a pivot table that contains accounts and reports sales by account. Users selected the accounts they want to see and save their spreadsheet. When new accounts are added to the accounts list, users do not want to see the new accounts. They only want to see the accounts they selected from a previous session when they refresh the pivot table. Does anyone know how to prevent the new accounts from appearing in the pivot table? Thanks! Tom, Use another column: Include, with values of Yes or No, and include it in your pivot table, showing only Yes. Then when new data is added, enter ...

typing in modeless dialog -> gets intercepted by accelerator table
I have an MFC application with a regular Accelerator Table in a resource. I have chosen for shortcuts containing no CTRL or SHIFT or ALT... since that makes for a fast workflow. This technique is used in many CAD & 3D-applications. However, when I use a modeless dialog (in fact, a dialog in a docked sidebar) and I want to type text into it, the accelerator keys get priority and thus prevent certain characters to be typed... I thought that I could use PreTranslateMessage to catch them before they are sent to the MainFrame, which I can, but at that time, they are also catched from ar...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

How to disable BCC field in Outlook 2003 clients running on Exchange 2003 ?
How to disable BCC field in Outlook 2003 clients running on Exchange 2003 ? (make BCC field unvalible) In news:u6NrOZiyFHA.2212@TK2MSFTNGP15.phx.gbl, RikS <riks.tw@yahoo.com.tw> typed: > How to disable BCC field in Outlook 2003 clients running on Exchange > 2003 ? (make BCC field unvalible) Don't know how you'd do this offhand, but I wouldn't. If users are misusing BCC, educate them, and make this an HR issue. It really isn't a technical one. HI Riks go to new mail over there on toolbar u will be getting option in that opti...

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

Employee Expense Description on Check Stub #3
How do you add the description from Employee Expense Entry in Project Accounting to the Payables check with stub on top and bottom? Thanks, Jocelyn ...

writing calculated fields
I am trying to avoid starting a Crystal report from scratch, please help me! I need to use the equivalent of the "If, Then, Else" formula in Crystal reports in Report Writer. I need my PO in report writer to: if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 30) else if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 40) else PO ordered QTY I can only place PO's to the vendor in eaches but I am entering the PO into GP in cases. I have the PO processing side setup correctly, I just need the PO to print now. Thank you! -- CK Create two new calculate...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

How can i null out a field going through CRM Update object
I have a custom field that i want to set back to null. Any ideas how i can do this through the object.update function? thanks ...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

ADO Field is Nothing error
Running ANY integration on version 10 (have SP 4 installed in Dynamics AND integration manager) yields this error for EVERY user except me (I am an administrator on our domain). There are no symbols or weird charcters. These worked fine a couple months ago, but now no one can run any. These are not even sql optimized eConnect one. They are basic standard integrations. Are you using a shared Integration Manager database or does each user have their own. If the latter, then users may not be seeing the file you are seeing in the location you are seeing it. Best regards, --...

Check this important pack
--pzjhsbtbubmrf Content-Type: multipart/related; boundary="yzosxialgcaog"; type="multipart/alternative" --yzosxialgcaog Content-Type: multipart/alternative; boundary="jqfzlzeiexnbeit" --jqfzlzeiexnbeit Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install...

trying to select the last 3 digits of a field
hello all this is the query im using (I am looking to get the last 3 characters of a field): rep_code: Right(tablename.fieldname,3) And I get this error: IDBC--call failed [Informix][Infomirx ODBC Driver][Informix]A syntax error has occurred. (#-201) Is it something wrong with my query or something outside Access 2003 (since the table I am trying to work with its using an ODBC conexion)? Any ideas??? Thanks ...

Reversing a cancelled contract in Field Service Contract Adminstra
I am a regular lurker on this board and really appreciate the information I have learned here so far. I have a specific question that I'm hoping someone can help me with. We are on GP 10.0 and use Field Service Contract Administration to manage customer contracts and billing. We processed a cancellation on a contract in Contract Administration (Field Service - Transactions - Contract Administration - Contract Entry/Update - Selected a contract - clicked Cancel button - completed info). This contract is now showing as "Cancelled" in the bottom right corner of that screen. ...

Paste row under existing data
Hello to the group, this is the first time I'm here and I'm getting a little desperate looking for a solution. I hope I'll find someone who can help me with this. Anyone who helps me has a virtual beer from me, cause this is rather urgent and I don't know how to do it. :-) Okay, here's the deal. I'm using Excel 2000. I have a worksheet which is used to enter different kinds of data (no kidding). When it's all entered, it creates a row of 5 cells which needs to be copied to another worksheet in another file for tracking purposes. Furthermore, it needs to be done for...

Does RMS provide integrated check verification?
Andrea No, try TPI Software Matt "Andrea" <Andrea@discussions.microsoft.com> wrote in message news:FB16A826-6455-48EE-9C36-CAC86FB8F55A@microsoft.com... > Andrea What is TPI Software? Andrea "Matt Landis" wrote: > No, try TPI Software > > Matt > > "Andrea" <Andrea@discussions.microsoft.com> wrote in message > news:FB16A826-6455-48EE-9C36-CAC86FB8F55A@microsoft.com... > > Andrea > > > http://www.tpisoft.com/products/servicemanager/rms.aspx RMS Plug-in, you maybe able to keep your existing Merchant Se...

check or tick marks as in Lotus
Is there anyway to go through an Excel sheet and either click or somehow quickly check off that you have reviewed and/or approved specific cells? I know that Lotus has this function and puts tick marks for each cell you have reviewed. Thank you, Tom Daugherty MIS Manager Tessera Inc. ...