Form Design

[Trish at St Andrews]

I currently have a table containing data where the primary key indicates the
year the data was entered e.g. 04-123, this relates to a form design.

I want to start a new table with the primary key being 05-123 etc, but want
the design related to this new table to be the same as that for last year.

How do I copy the design of a form please?

I'm very much a novice user.

 

[Douglas J. Steele]

You can simply select the form from the database window, copy it (Ctrl-C, or
right-click with the mouse) and paste (Ctrl-V, or right-click with the
mouse). You can then go in and modify the new form.

However, I don't understand why you need a separate table (nor a separate
form) just because you want to have this year's data in it.

 

[Larry Daugherty]

Hi Trish,

To answer your question, you simply copy the form and give it a new name.
Then modify the code in the form that the rest of your application knows
about, if any so that it's the one handling the generation of the primary
key. It would be dangerous to leave the older form/code in place because,
by implication, it would generate new keys using on old year base.

But what you really need to do is to generalize your algorithm for
generating your sequence numbers so that it works flawlessly down through
the years without modification. Note that everything that follows is air
code so your mileage may vary ..

To achieve that level of performance requires that you manage the two parts
of the number somewhat separately and then concatenate them with the hyphen.
You may want to create a function procedure to do the whole job. Also I
often include a special table for application variable values that persist
across sessions. The table has two fields: VarName and VarValue Two such
values here will be YearPart and SequenceNumber, the parts of your primary
key field. The year part is the current 2 digit year and the sequence part
is the next available sequence number. Declare your function procedure
something

Private Function GetYearAndSeq() as String
Dim CurYear as integer
Dim CurSeq as integer

CurYeaar=format(Now(), "yy")

If CurYear > year part in tblApplicationVariables YearPart then
CurSeq=0
GetYeaAndSeq=CurYear & "-" & format(CurSeq,"000")

Write current year to the field in the table
Increment CurSeq and write that next value to the field in the table

else
CurSeq = DLookup the next available sequence value in
tblApplicationVariables
GetYeaAndSeq=CurYear & "-" & format(CurSeq,"000")
Increment CurSeq and write that next value to the field in the table
End If

End Function

Now make the Default value of the Sequence/Primary key field control on your
form

=GetYearAndSeq

That will call the function and it will return the correct concatenated
string.

Your form will now work across any number of years, always starting the
sequence number at "000" with the new year.

I'll leave it to you to add Error handling.

Note that there are other ways to get done what you want but if you change
parts of the above you have to make other changes to get it all to play.

If you get stuck please post back telling what you've done and where you're
stuck.

HTH