If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I've searched the threads but can't find an answer to this one...
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 |
#2
|
|||
|
|||
I've searched the threads but can't find an answer to this one...
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" wrote: 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 |
#3
|
|||
|
|||
I've searched the threads but can't find an answer to this one...
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 Chris2 wrote: 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. |
#4
|
|||
|
|||
I've searched the threads but can't find an answer to this one...
"LSnyderinGA" u28017@uwe wrote in message news:67f2ed4775506@uwe... 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. |
Thread Tools | |
Display Modes | |
|
|