filling fields

[mon]

I've put this query in before , but despite the best
efforts of one of the experts (Thanks so much) I still
can't make it work.
When the Form loads for a new activity entry I would like
the JobNumber field to default to the previous JobNumber,
ie the last activity entry. If you give me code please be
very specific where to put it as I am at the just a
tad past the beginner stage. Thanks Monika

 

[Wayne Morgan]

Are the JobNumbers in any particular order? If they get continuously larger, then the last
JobNumber would be the largest number in the table, is this correct?

If the above is correct, you could set the Default Value of the control for the JobNumber
to

=DMax("[FieldName]","FormQueryOrTableName")

The FieldName would be the name of the field for the JobNumber, the FormQueryOrTableName
would be the name of the table or query that is the form's RecordSource.

 

[Steve Schapel]

Monika,

From Access's point of view, the "last activity entry" doesn't exist.
There has to be some way to assess, from the data in the table, which
record was the last entry. In practice, there are normally two ways
of doing this. One is to include a Date/Time field in the table,
WhenCreated or some such. The other is an incremental ID field of
some sort. Do you have either of these?

- Steve Schapel, Microsoft Access MVP

 

[mon]

Yes I have a automatic incremental ID Field in this table.
With the When Created date field: that sounds pretty
useful. That can be put straight in the table with Date()
perhaps even date and time and doesn't have to show up in
forms does it?

-----Original Message-----
Are the JobNumbers in any particular order? If they get

continuously larger, then the last

JobNumber would be the largest number in the table, is this correct?

If the above is correct, you could set the Default Value

of the control for the JobNumber

to

=DMax("[FieldName]","FormQueryOrTableName")

The FieldName would be the name of the field for the

JobNumber, the FormQueryOrTableName

 

[mon]

Sorry Steve I posted a reply to you on the wrong line .
can you look a couple of lines down, please?
Thanks Monika

 

[Steve Schapel]

Monika,

If you mean the ID field is an Autonumber data type, under some
circumstances (for example if the database was ever replicated) where
this would not work. If you add a WhenCreated field to the table,
with its Default Value set to Now() the only way this will cause an
error is if the system clock goes haywire (so, assuming you're not in
Queensland, don't do any data entry on Daylight Saving night!) And
no, this field doesn't need to show on your forms or reports.

As regards getting the required data into your new record, there are
many approaches to this. Here's one, which uses a couple of domain
aggregate functions, which is not the most elegant but is possible
relatively intuitive. I am assuming that you at least know how to
enter vba code into an event procedure. So, use the BeforeInsert
property of your form, and do something like...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim LastEntry As Date
Dim LastJob as Long
LastEntry = DMax("[WhenCreated]","Activities")
LastJob = DLookup("[JobNumber]","Activities", _
"[WhenCreated]=#" & LastEntry & "#")
Me.JobNumber = LastJob
EndSub

Having said all this, I wonder whether you are going about this in the
best way. You haven't told us enough about your project to really
know. But it seems like it could be the case that you have Jobs, each
with a JobNumber, and then for each job there are a number of
Activities. Is this right? So the relationship between the Jobs
table and the Activities table is on the basis of the JobNumber,
right? So one of the standard ways of handling such a scenario is to
put your Activities form as a subform on the Jobs form. So you can go
to the record for the Job in question, and then enter any number of
Activities for that Job, and the JobNumber for the Activities will
automatically be entered for you, without you needing to bother with
any code and stuff.

Please post back if you need any more help with any of this.

- Steve Schapel, Microsoft Access MVP

 

[mon]

thanks steve
Yes there are jobs with job numbers and they have a
quantity of activities, However there is also a table of
Instructions which I have in a subform so that as the user
enters new activities the list of instructions is right in
their face to remind them and also that they can update
them (mark them as completed when appropriate). The job
details are all in a seperate database. What I am trying
to do is build individual user databases so that they can
do their entering at home (they do a lot of work at home)
and then (at this testing stage) I will upload the records
into a similar, but with extra reports etc database, for
our Manager to keep track of the jobs. Any suggestions
are verrrrrry appreciated.
Cheers
Monika
PS I will have a go at the code (after I come home from
visiting a sick relative.

-----Original Message-----
Monika,

If you mean the ID field is an Autonumber data type, under some
circumstances (for example if the database was ever replicated) where
this would not work. If you add a WhenCreated field to the table,
with its Default Value set to Now() the only way this will cause an
error is if the system clock goes haywire (so, assuming you're not in
Queensland, don't do any data entry on Daylight Saving night!) And
no, this field doesn't need to show on your forms or reports.

As regards getting the required data into your new record, there are
many approaches to this. Here's one, which uses a couple of domain
aggregate functions, which is not the most elegant but is possible
relatively intuitive. I am assuming that you at least know how to
enter vba code into an event procedure. So, use the BeforeInsert
property of your form, and do something like...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim LastEntry As Date
Dim LastJob as Long
LastEntry = DMax("[WhenCreated]","Activities")
LastJob = DLookup("[JobNumber]","Activities", _
"[WhenCreated]=#" & LastEntry & "#")
Me.JobNumber = LastJob
EndSub

Having said all this, I wonder whether you are going about this in the
best way. You haven't told us enough about your project to really
know. But it seems like it could be the case that you have Jobs, each
with a JobNumber, and then for each job there are a number of
Activities. Is this right? So the relationship between the Jobs
table and the Activities table is on the basis of the JobNumber,
right? So one of the standard ways of handling such a scenario is to
put your Activities form as a subform on the Jobs form. So you can go
to the record for the Job in question, and then enter any number of
Activities for that Job, and the JobNumber for the Activities will
automatically be entered for you, without you needing to bother with
any code and stuff.

Please post back if you need any more help with any of this.

- Steve Schapel, Microsoft Access MVP

Yes I have a automatic incremental ID Field in this table.
With the When Created date field: that sounds pretty
useful. That can be put straight in the table with Date ()
perhaps even date and time and doesn't have to show up in
forms does it?

.

 

[mon]

I tried the code and it's a bit erratic since a lot of
entries are created on the same day, seems to take the
first jobnumber for the day. Also it brings up all the
jobnumber lines in the subform which I didn't really want,
but can that be avoided?
Mon

-----Original Message-----
thanks steve
Yes there are jobs with job numbers and they have a
quantity of activities, However there is also a table of
Instructions which I have in a subform so that as the user
enters new activities the list of instructions is right in
their face to remind them and also that they can update
them (mark them as completed when appropriate). The job
details are all in a seperate database. What I am trying
to do is build individual user databases so that they can
do their entering at home (they do a lot of work at home)
and then (at this testing stage) I will upload the records
into a similar, but with extra reports etc database, for
our Manager to keep track of the jobs. Any suggestions
are verrrrrry appreciated.
Cheers
Monika
PS I will have a go at the code (after I come home from
visiting a sick relative.

-----Original Message-----
Monika,

If you mean the ID field is an Autonumber data type, under some
circumstances (for example if the database was ever replicated) where
this would not work. If you add a WhenCreated field to the table,
with its Default Value set to Now() the only way this will cause an
error is if the system clock goes haywire (so, assuming you're not in
Queensland, don't do any data entry on Daylight Saving night!) And
no, this field doesn't need to show on your forms or reports.

As regards getting the required data into your new record, there are
many approaches to this. Here's one, which uses a

couple
of domain

aggregate functions, which is not the most elegant but

is

possible
relatively intuitive. I am assuming that you at least know how to
enter vba code into an event procedure. So, use the BeforeInsert
property of your form, and do something like...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim LastEntry As Date
Dim LastJob as Long
LastEntry = DMax("[WhenCreated]","Activities")
LastJob = DLookup("[JobNumber]","Activities", _
"[WhenCreated]=#" & LastEntry & "#")
Me.JobNumber = LastJob
EndSub

Having said all this, I wonder whether you are going about this in the
best way. You haven't told us enough about your project to really
know. But it seems like it could be the case that you have Jobs, each
with a JobNumber, and then for each job there are a number of
Activities. Is this right? So the relationship between the Jobs
table and the Activities table is on the basis of the JobNumber,
right? So one of the standard ways of handling such a scenario is to
put your Activities form as a subform on the Jobs form. So you can go
to the record for the Job in question, and then enter

any
number of

Activities for that Job, and the JobNumber for the Activities will
automatically be entered for you, without you needing to bother with
any code and stuff.

Please post back if you need any more help with any of this.

- Steve Schapel, Microsoft Access MVP




.

.

 

[Steve Schapel]

Monika,

Maybe I am missing your meaning, but I don't see why the Instructions
should be a reason not to use a subform for entering the Activities.

As regards your need for data management in separate locations,
constructing routines to import into your master database in the home
office should be possible, but you will need to make sure you cater to
preserving the correct relationships between tables. Other approaches
to the same situation are a) Replicated databases, which is a built-in
feature of Access, requiring a bit of a steep learning curve, but
works well, and b) having your workers use Terminal Services to access
the home database via the internet. In all 3 cases, you would need to
be prepared for a fair bit of work.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Monika,

Did you use Now() to enter the date created? I checked it out here,
and it produces the most recent entry.

As regards the jobnumber lines on the subform, I don't understand what
you are referring to here. Maybe you could give a fuller description
of what your setup really is?

- Steve Schapel, Microsoft Access MVP

 

[mon]

Thanks Very much for your help Steve, this seems to be
working well (I hade Date() instead of Now())
Thanks for all your other advice - saved for future
reference. I'll be on to reporting next (but pretty simple
ones for this project. Thanks again
Monika