Functions within a Private Sub Command0_Click() and help with logic

[sparkes84]

Dear all,

If somebody could help me, then I'd be so grateful! All due to sheer inexperience of vba programming.

I would like all of the following to occur at the click of a button on a form, hence my private sub command0_Click() but when I started to put together my 'If' logic, all started to fail. How can I correct this so that not only does it create the 3 fields for me, but it also populates other fields with particular data using the specified logic?

Here is my code so far:
Private Sub Command0_Click()
'Need to create 3 new fields: CSP_Level, Import_Date and Count
On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbl = db.TableDefs("CSP Export")


Set fld = tbl.CreateField("CSP_Level", dbText, 30)
tbl.Fields.Append fld
'set ordinal position
fld.OrdinalPosition = 1
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False

Set fld = tbl.CreateField("Import_Date", dbDate)
tbl.Fields.Append fld
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False

Set fld = tbl.CreateField("Count", dbInteger)
tbl.Fields.Append fld
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False

'Populate the CSP_Level (Rec_Ref, Location_Name and Event_Title are fields within the table

Function CSP_Level(Rec_Ref, Location_Name, Event_Title)
If Rec_Ref > "" Then CSP_Level = "Project"
ElseIf Location_Name > "" And Event_Title Is Null Then CSP_Level = "Site"
ElseIf Event_Title > "" Then CSP_Level = "Event"
End If

CleanUp:
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Function
ErrHandler:
MsgBox "Error in addFieldToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Function
End Sub

Thank you for any help that you can give,
sparkes84

 

[DCrake]

Why are you trying to add fields to a table anyway?
What happens next time you run this the feilds will already be there?

David

 

[sparkes84]

Thanks David. This particular bit of programming will eventually (with hope!!) be neat enough to create the fields within an import button, and then have the other part of the code as part of another button.

This will need to be rolled out to various places around the UK, so it's important that the creation of the fields occurs automatically.

Thanks,
sparkes84