Programmatically adding fields to table based on structure and content of a particular record

[rwfreeman]

Access 2033. Code below evaluates the .Fields of an ADO table and
adds a field as needed (the first two fields are already in the table,
other fields will contain data associated with those fields but there
is no a priori way to know the number of additional fields for any
particular record). All records would have the same number of
additional filelds but only some of those records would have data in
all fields. Structure for the table would eventually have fields
PlanYear, SetAside (the two required fields), Claim1, Claim2, ... ,
ClaimN.

What I''d really like to do is go to a particular record in the table,
see which of the fields higher than Field2 contain data, and then add
data to the first unoccupied field or add a new one, if needed.

If anyone's following what I want to do, could you suggest some VBA
code that would make my desires happen?

BTW if a field has to be added it would be added in another procedure
(the user has some decision points before the field would need to be
added).

Thanks in advance for any suggestions.

Richard Freeman
Public Sub GetClaimNumberandFieldName()
On Error GoTo Err_GetClaimNumberandFieldName

Dim i As Integer
Dim cnCAFEPlan As ADODB.Connection
Dim rsPlanYear As ADODB.Recordset
Dim adoField As ADODB.Field 'strDateOfClaimFieldName and
strClaimIdentifier are DIMmed in the Declaration section of the form's
module

Set cnCAFEPlan = CurrentProject.AccessConnection
Set rsPlanYear = New ADODB.Recordset
rsPlanYear.Open "tblPlanYear", cnCAFEPlan

i = 0
If rsPlanYear.Fields.Count = 2 Then 'tblPlanYear reflects no claims
have been made
strDateOfClaimFieldName = "Claim0" & i + 1
strClaimIdentifier = Forms!frmMain.Form.cboxPlanYear & "Claim" & i
Else
For Each adoField In rsPlanYear
i = i + 1
Next adoField 'count the number of fields
i = i - 2
strClaimIdentifier = Forms!frmMain.Form.cboxPlanYear & "Claim" & i
strDateOfClaimFieldName = "Claim" & IIf(i < 12, "0", "") & i
End If

Exit_GetClaimNumberandFieldName:
rsPlanYear.Close
Set rsPlanYear = Nothing
Exit Sub

Err_GetClaimNumberandFieldName:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_GetClaimNumberandFieldName

End Sub

 

[John W. Vinson]

Structure for the table would eventually have fields
PlanYear, SetAside (the two required fields), Claim1, Claim2, ... ,
ClaimN.

Then the structure of your table WOULD BE WRONG.

You're using a relational database... *use it relationally*! "Fields are
expensive, records are cheap". You have a one (plan) to many (claims)
relationship; model it as a one to many relationship, with TWO tables. Each
Claim would be in a new record in the claims table, and that table would have
a foreign key linking it to your Plan table.


John W. Vinson [MVP]