I've searched the threads but can't find an answer to this one...

[LSnyderinGA]

Hello.
Any guidance on this would be greatly appreciated.

I built a simple database that stores physical file locations for a single
department.
It works fine but the team is now asking for a few things I'm not sure how to
accomplish.

I have a table with Loan Number, First Name, Last Name, Date of Loss, Type of
Loss, Box Number, Associate Name and a susequent form where the user enters
the data.

The team wants the Box Number to auto populate in sequential order but each
box can hold up to 100 files. SO they want the same Box Number to populate in
each new record until the user closes the box and then with the next new
record the Box Number would advance +1.

I have no idea where to start with this one. I am an intermediate user.

Thank you for any assistance,

Laurie

 

[KARL DEWEY]

You need two tables set up in a one-to-many relationship. The 'one' table
would have the box number and information as to where it is, how full, etc
and the box number as primary key (unique number).
The 'many table would have the fields to describe the files in the box and a
number field, integer, as a foreign key that relates to the 'one' table
primary key.

The many table would then use a datatype that matches the primary key field.
If using an autonumber in the 'one' table the use Number - Integer in the
'many' table.

Set the relationship by adding the two tables in the Relations window.
Click on the primary key field and drag to the field of the 'many' table.
Double click on the resulting connector line. Select the first two options -
Referential integerity and cascade update.

Use a main form/subform for data entry and display of the box/files
information.

 

[LSnyderinGA]

Thank you Karl and Chris for this info. I will work with both set of
information and see if I can make it work. You may hear back from me with
additional questions.

Thanks Again!

Laurie

Hello.
Any guidance on this would be greatly appreciated.

[quoted text clipped - 18 lines]

Laurie

Laurie,

As far as I know, you would have to control this entirely with VBA
code (directed by an end-user working with a Form).

When the end-user brings up the Form they work with, there will have
to be some way of determining what the current Box Number for that
user has in front of them (the user will likely have to type in a
number by reading the one on the box). You might want to have an
option to re-display the entered box number for verification
purposes (this probably isn't something you want end-users to make
mistakes on).

This number would be entered into a variable, "intBoxNumber", and
would be used to help assemble (i.e. auto-populate) any new records
that are to be appended to any necessary tables while the end-user
is working.

Once the Box Number is determined, a query would be run internally
to determine the current "number of files" in that box.

As the end-user worked with the form and enters new files into the
box, an "intFilesInBox" variable would be incremented. If it hit
100, you would go through whatever process it is you decided you
need to inform the user that a new Box Number must be started. Your
system will determine this new Box Number in whatever manner it is
currently using, and supply it to the user so that it will be used
in the application (and for new records) as the end-user works.
"intFilesInBox" is reset and begins use again.

There are four situations that I can see off hand:

1) Where the user begins a new box.
2) Where the user begins with an existing box with some files
already in it.
3) Where the user is working and gets to the end of a box and begins
a new one.
4) Where the user is working with a box (from whatever starting
position), and decides to quit using it and switch to another box
(new or partially used).
5) Where the user has accidentally entered the incorrect box number
during any one of the processes above, and where this must be
corrected. (This one is complicated, and may require consideration
to provide history of changes and recoverability.)

As files are entered, you may want to have the end-user's form
display the updated file number. This will be a clue to the
end-user in case they have an empty box but the file number says 23
on-screen, or they have a half-full box in front of them, and the
file number says 0 on-screen, etc.

I apologize that I am not able to be more specific.

Sincerely,

Chris O.

 

[Chris2]

Hello.
Any guidance on this would be greatly appreciated.

I built a simple database that stores physical file locations for a single
department.
It works fine but the team is now asking for a few things I'm not sure how to
accomplish.

I have a table with Loan Number, First Name, Last Name, Date of Loss, Type of
Loss, Box Number, Associate Name and a susequent form where the user enters
the data.

The team wants the Box Number to auto populate in sequential order but each
box can hold up to 100 files. SO they want the same Box Number to populate in
each new record until the user closes the box and then with the next new
record the Box Number would advance +1.

I have no idea where to start with this one. I am an intermediate user.

Thank you for any assistance,

Laurie

Laurie,

As far as I know, you would have to control this entirely with VBA
code (directed by an end-user working with a Form).

When the end-user brings up the Form they work with, there will have
to be some way of determining what the current Box Number for that
user has in front of them (the user will likely have to type in a
number by reading the one on the box). You might want to have an
option to re-display the entered box number for verification
purposes (this probably isn't something you want end-users to make
mistakes on).

This number would be entered into a variable, "intBoxNumber", and
would be used to help assemble (i.e. auto-populate) any new records
that are to be appended to any necessary tables while the end-user
is working.

Once the Box Number is determined, a query would be run internally
to determine the current "number of files" in that box.

As the end-user worked with the form and enters new files into the
box, an "intFilesInBox" variable would be incremented. If it hit
100, you would go through whatever process it is you decided you
need to inform the user that a new Box Number must be started. Your
system will determine this new Box Number in whatever manner it is
currently using, and supply it to the user so that it will be used
in the application (and for new records) as the end-user works.
"intFilesInBox" is reset and begins use again.

There are four situations that I can see off hand:

1) Where the user begins a new box.
2) Where the user begins with an existing box with some files
already in it.
3) Where the user is working and gets to the end of a box and begins
a new one.
4) Where the user is working with a box (from whatever starting
position), and decides to quit using it and switch to another box
(new or partially used).
5) Where the user has accidentally entered the incorrect box number
during any one of the processes above, and where this must be
corrected. (This one is complicated, and may require consideration
to provide history of changes and recoverability.)

As files are entered, you may want to have the end-user's form
display the updated file number. This will be a clue to the
end-user in case they have an empty box but the file number says 23
on-screen, or they have a half-full box in front of them, and the
file number says 0 on-screen, etc.

I apologize that I am not able to be more specific.


Sincerely,

Chris O.