Create new tablefield and fill with current date

[abregman]

All,

I'm creating a db where the history of submitted documents can be traced.
However, these documents can be submitted more than once (actually they will
be submitted untill the document is approved). Therefore, we need to be able
to enter more than one submitdate.

Because the number of submitdates (every submitdate is a revisison) can
differ for every document I want to create a new revisionfield for each
submitdate (new submitdate = revisionnumber0 or current revisionnumber+1) and
fill this with the current date.

On the internet I found some code which I altered. However this code only
creates a new field one time and does not fill it with the current date. Can
anyone help me with this code, so when the submitdate box is clicked a new
revision field is created (new submitdate = revisionnumber0 or current
revisionnumber+1) and filled with the current date?

The code I have is printed below:

Private Sub SubmitDate1_Click()
'create new revisonfield in the submitdates table and fill this with the
currentdate, and lock afterwards.

Dim dbs As DAO.Database
Dim tdfSubmitdates As DAO.TableDef
Dim fldTemp As Field

Set dbs = CurrentDb()
Set tdfSubmitdates = dbs.TableDefs("Submitdates")
Set fldTemp = tdfSubmitdates.CreateField("Revisions", dbText, 255)

With tdfSubmitdates.Fields
Append fldTemp
End With

End Sub


Thanks for helping me!

Regards,
Arjan

 

[Tom van Stiphout]

This is NOT the way to do it (I chose the low end of several possible
qualifications ). You're thinking like an Excel person would: let's
just pop the new date in the next column. But even they eventually run
out of columns, and more importantly any formulas or data integrity
checks become more and more difficult.
Start with the right db design, and your problems will go away. Each
document has many revisions. So create two tables in a 1:M relation:
tblDocuments
DocumentID autonumber PK
DocumentName text
PrimaryAuthorID long int FK

tblRevisions
RevisionID autonumber PK
DocumentID long int FK
RevisionNo int
DateSubmitted datetime
SubmittedByID long int FK
Also put a Unique index on DocumentID+RevisionNo.

etc.

Enforce all relationships.

-Tom.
Microsoft Access MVP

 

[breg]

Tom,

Ok, thanks for helping, the situation I have does have a tblSubmitdates (your
tblRevisions) related to a tblDocuments.

However, I do not understand; what do you mean by FK? (PK is PrimaryKey)

Regards,
Arjan

This is NOT the way to do it (I chose the low end of several possible
qualifications ). You're thinking like an Excel person would: let's
just pop the new date in the next column. But even they eventually run
out of columns, and more importantly any formulas or data integrity
checks become more and more difficult.
Start with the right db design, and your problems will go away. Each
document has many revisions. So create two tables in a 1:M relation:
tblDocuments
DocumentID autonumber PK
DocumentName text
PrimaryAuthorID long int FK

tblRevisions
RevisionID autonumber PK
DocumentID long int FK
RevisionNo int
DateSubmitted datetime
SubmittedByID long int FK
Also put a Unique index on DocumentID+RevisionNo.

etc.

Enforce all relationships.

-Tom.
Microsoft Access MVP

All,

[quoted text clipped - 38 lines]

Regards,
Arjan

 

[Tom van Stiphout]

FK = Foreign Key: the ID value that is linked to the primary key of
the parent table.
For example PrimaryAuthorID is a foreign key linked to AuthorID in
tblAuthors. In the Relationships window there is a line between the
two fields, and the relation is enforced.

If you have two tables, then why would you need to add a field like
you posted in first instance? It seems like you would simply need to
add a ROW to that table.

-Tom.
Microsoft Access MVP

Tom,

Ok, thanks for helping, the situation I have does have a tblSubmitdates (your
tblRevisions) related to a tblDocuments.

However, I do not understand; what do you mean by FK? (PK is PrimaryKey)

Regards,
Arjan

This is NOT the way to do it (I chose the low end of several possible
qualifications ). You're thinking like an Excel person would: let's
just pop the new date in the next column. But even they eventually run
out of columns, and more importantly any formulas or data integrity
checks become more and more difficult.
Start with the right db design, and your problems will go away. Each
document has many revisions. So create two tables in a 1:M relation:
tblDocuments
DocumentID autonumber PK
DocumentName text
PrimaryAuthorID long int FK

tblRevisions
RevisionID autonumber PK
DocumentID long int FK
RevisionNo int
DateSubmitted datetime
SubmittedByID long int FK
Also put a Unique index on DocumentID+RevisionNo.

etc.

Enforce all relationships.

-Tom.
Microsoft Access MVP

All,

[quoted text clipped - 38 lines]

Regards,
Arjan