Do all fields exists if so Count records 1

[Zeroanarchy]

Hi,

I have been banging my head of a wall for a bit on this one.

What I am tring to do is work out if certain fields exist in a table if so proceed to count them to make sure we have some records.

here is what I am up to

Dim td As TableDef
Dim fld As DAO.Field
Dim FieldsExist As String
Dim intx As String

FieldsExist = False
Set td = CurrentDb.TableDefs("Raw Material Prices")

For Each fld In td.Fields
   If fld.Name = "Cost" And "PRODUCT CODE" And "Product Name" Then
       FieldsExist = True
      intx = DCount("[COST]", "Raw Material Prices")
      MsgBox intx
      End If
Next fld

Greatful for your help.


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[BuilderSpec]

Hi

fld.Name can only be one field at once..

Try


dim FieldCount as Integer
FieldCount = 0
For Each fld In td.Fields
If fld.Name = "Cost" Or fld.Name = "PRODUCT CODE" or fld.Name = "Product Name" Then
FieldCount = FieldCount + 1
end if
if FieldCount = 3 then
FieldsExist = True
intx = DCount("[COST]", "Raw Material Prices")
MsgBox intx
End If
end if
Next fld



Hope this helps!

Regards

BuilderSpec

 

[Zeroanarchy]

Thanks Builderspec for your help, having one problem, it gets to

For Each fld In td.Fields

And comes up with the follwing error:
td.Fields object invalid or not set.

Any IDeas??

Dim td As TableDef
Dim fld As DAO.Field
Dim FieldsExist As String
Dim intx As String
Dim FieldCount As Integer

FieldsExist = False
Set td = CurrentDb.TableDefs("Table1")

FieldCount = 0
For Each fld In td.Fields
   If fld.Name = "Cost" Or fld.Name = "PRODUCT CODE" Or fld.Name = "Product Name" Then
      FieldCount = FieldCount + 1
   End If
   If FieldCount = 3 Then
       FieldsExist = True
      intx = DCount("[COST]", "Table1")
      MsgBox intx
      End If
Next fld
End Sub

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Golom]

Try this

Dim fld            As DAO.Field
Dim FieldsExist    As Boolean
Dim intx           As Long
Dim FieldCount     As Integer

FieldsExist = False
FieldCount = 0
For Each fld In CurrentDb.TableDefs("Table1").Fields
   If fld.Name = "Cost" Or fld.Name = "PRODUCT CODE" Or fld.Name = "Product Name" Then
      FieldCount = FieldCount + 1
   End If
Next fld

If FieldCount = 3 Then
   FieldsExist = True
   intx = DCount("[COST]", "Table1")
   MsgBox intx
End If

End Sub

 

[Zeroanarchy]

Thanks Golom, I gave it a go but it to came up the same error.

I notice the DAO.Field is not set at the Dim Level, for some reason it does not highlight Blue.

The error is : Object is invalid or nolonger set.

Reference being used is: Microsift DAO 3.51 object library

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[TheAceMan1]

. . . and this:

[blue]   Dim [purple][b]db As DAO.Database[/b][/purple], fld As Field, intx As Long
   Dim FieldsExist As Boolean, FieldCount As Integer

   Set [purple][b]db[/b][/purple] = CurrentDb
   FieldsExist = False
   FieldCount = 0
   
   For Each fld In [purple][b]db[/b][/purple].TableDefs("Table1").Fields
      If fld.Name = "Cost" Or _
         fld.Name = "PRODUCT CODE" Or _
         fld.Name = "Product Name" Then
         FieldCount = FieldCount + 1
      End If
   Next
   
   If FieldCount = 3 Then
      FieldsExist = True
      intx = DCount("[COST]", "Table1")
      MsgBox intx
   End If[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

[blue]Reference being used is: Microsoft DAO 3.51 object library[/blue]

If you have DAO 3.6 switch as soon as possible.

By any chance is your op access 97?

See Ya! . . . . . .

 

[Zeroanarchy]

Hey TheAceMAn1

Yes I am using Access97 on this project, why do you ask?

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[TheAceMan1]

[blue]Yes I am using Access97 on this project, why do you ask?[/blue]

We have to [blue]take account of differences[/blue] in successor versions . . .

For instance, I don't remember ever using the [blue]DAO[/blue] prefix when declaring an object in my 97 days. ADO didn't come out until 2000 and it was then it became required to distinguish between the two:

[blue]   Dim db As [purple][b]DAO[/b][/purple].Database, rst As [purple][b]DAO[/b][/purple].Recordset [green]'DAO[/green]
   Dim db As [purple][b]ADO[/b][/purple].Database, rst As [purple][b]ADO[/b][/purple].Recordset [green]'ADO[/green][/blue]

See Ya! . . . . . .

 

[Zeroanarchy]

Thanks TheAceMan1, works great, I now understand why the problem occurs.

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[TheAceMan1]

[blue]I now understand why the problem occurs.[/blue]

Just to verify: Its not the [blue]DAO[/blue] object prefix that did it but rather setting the database object.

[blue]   Dim db As Database

   Set db = CurrentDb[/blue]

Should work one and the same and is native for 97. I remember it was required to set the object more often than not.

[blue]Cheers![/blue]

[blue]You take care . . . Ya Hear![/blue]

See Ya! . . . . . .

 

[Zeroanarchy]

Thanks TheAceMan1 for your help, works great. Certainly worth a Star.

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.