Need a way to limit the number of entries a user can make to a form based on a value

[XV1957]

Hi Access experts.
My problem is the following: when I receive say 5 computers in a purchase form, I want to register the serial number of each of them in another form, bound to another table.
If I receive 2 units in the purchase form, my user should only be able to input 2 records in the serial form (a continuous form), if we receive 3, then only 3 records, ... etc
I cannot quite figure out how to build this second (serial number registration form), so that it refuses input after the correct number has been reached.
Thanks in advance for every help.:banghead:

 

[jdraw]

What is the process involved?

You don't really receive 5 computers on a Purchase form. You make out a Purchase Order or Order for 5 computers. The Supplier sends 1 or more computers with some sort of Invoice or Tracking Document or Receivable info. When computers(and paperwork) arrive, someone enters relevant data into the computer system and ensures goods were received.

The screen/form used to input the Receivable info could be a "holder/collector" screen, with some buttons to execute processes such as:
-generate an Asset Identifier and link it to the ComputerSerial number
-update the system to show receipt of 2 computers from PurchaseOrder 123
-use the operators loginid and associate it with the receivable...

All this to say it depends on how the system is designed and how well it supports the business involved.

 

[TimW]

Hi
I have this sort of thing in one of my databases. The trick is to count the number of records as you add them to the table. Then disable the addition of any other records when the total number expected has been reached.

HTH

T

 

[XV1957]

Thank you jdraw for your very fast answer.
You answered the business side of the question, which to me is not an issue, but which I will try to explain better, so as to get to the programming question.

I have a purchase order screen, with lots of funtionality.
For example, when I fill in the received quantities on the same screen which displays the order and press a certain button, this action creates a purchase receipt for the articles/quantities, closes the purchase order or creates a back order, ...

The issue with serial numbers is this: even though I enter the number 5 as the quantity received, if this article is associated with serial numbers, I have to enter 5 different serial numbers in another form(screen).
What I am looking for is to force the user to enter 5 serial numbers if I received 5 units and stop all entry after the 5 serial numbers have been added. If it is 3, limit the entry to 3.

Right now, I have a bound (to the serial number table) continuous form. I create new records by tabbing through the fields. I keep a total quantity field, but the way it is set up now, nothing prevents me from creating more records than I need. And that is the problem.
On the Form_current event, I set the values of article number, warehouse,... that are repeated on each line when tabbing, and which are passed on from my purchase order/receipt form.

Hope I did a better job explaining my problem. Thanks again!

 

[XV1957]

Thank you very much TimW.
Being a self-taught Access programmer, I may ask very basic questions.
Where would you put the counting and the control of the number reached?
I could count in one procedure/form and open the form anew for as long as I have not reached the number of entries, but this is exactly what I tray avoiding.
Ideally, the user would add new records in the same window till hitting the maximum allowed. And this is exactly where I am at a loss. Thanks again.

 

[jdraw]

... I have to enter 5 different serial numbers in another form(screen).
What I am looking for is to force the user to enter 5 serial numbers if I received 5 units and stop all entry after the 5 serial numbers have been added. If it is 3, limit the entry to 3...

Perhaps you should not be going to the other screen. When you enter the 5 (qty of goods requiring serial number) perhaps a textbox or textboxes or list should appear (be displayed and enabled) to receive these 5 and only 5 serial numbers. Then a process to update the appropriate table with these when serial numbers have been completed.

How are serial numbers assigned -are these the computer manufacturer numbers?
Do you also put your company asset identifier on a physical piece of equipment? If so, how is that generated?

When you have an operational issue -- as per the quote -- I have to enter numbers on a different screen, sometimes you have to step back and say WHAT should happen at this point? You can document your thoughts/findings, and put these on a list of enhancements which can be reviewed to establish should do/forget and with what priority. This is often how new versions of applications come about. Things change --and the old/current procedures/systems need some revamping. If you can associate the change with some savings in processing time, reduction of errors, fewer customer/management complaints, then use it to help with priorities.

Management, --- whatever you may think of them/it collectively -- does not want to waste money or time or people. Sooner or later, improving the flow, reducing production/operation time and effort, will be seen as an improvement.

 

[MarkK]

This is almost certainly evidence of a design flaw in your tables. A row in a table should completely describe an object. If adding a row necessitates the addition of another row, that's a problem.

 

[XV1957]

This is almost certainly evidence of a design flaw in your tables. A row in a table should completely describe an object. If adding a row necessitates the addition of another row, that's a problem.

Hi MarkK,
thanks for your comment.
I guess I have not expressed myself clearly enough.

Tables:
1. tblPurchaseHeader
2. tblPurchaseLines : Artno, Qty, QtyReceived, ...(linked with Purchase Header through order number)
3. tblArticle in which one field indicates the need for a serial number (per unit received/handled/sold) (linked with tblPurchaseLines through Artno)
4. tblSerialNumbers: linked to tblPurchaseLines through Artno and WhseLocation

Forms
1. Purchase form: consists of fields from tblPurchaseHeader and a subform consisting of fields of tblPurchase Lines
2. Form for entry of Serial numbers

Question: for a received quantity of say 5 in the Purchase Form, I need to be able to utilize the Serial number entry form 5 times. If that can be done in a continuous form, you would ideally see 5 lines with 5 separate records and not one more.

Adding a row is not at all dependent on adding another row. Adding a row is dependent on the number of units received in the purchase form, and the number of serial numbers already entered in the Serial Number entry form. I find using the tab key very fast and convenient, but if a user is not attentive, he/she can create useless/blank records. This is no different from using a tab key when entering values directly in a table: when one comes to the end of a row, a new row is automatically added and so on.

The question is: can we limit the number of entries in an Access continuous entry form? If so, how? If not, how could we achieve some similar result?

I can see a text box or a window popping up, and counting the number of times the text box or window has been called, but if a user only sees one entry, this is not very user-friendly. As one is frequently interrupted in the middle of data entry, one needs to be able to see what was entered previously in the same form.

 

[XV1957]

Perhaps you should not be going to the other screen. When you enter the 5 (qty of goods requiring serial number) perhaps a textbox or textboxes or list should appear (be displayed and enabled) to receive these 5 and only 5 serial numbers. Then a process to update the appropriate table with these when serial numbers have been completed.

Thank you jdraw for your message.
Being the founder/owner of a machine building company (we need to trace the serial numbers of important parts we build into our machines), and having maintained an ERP package in another company, I can pretty much visualize what I have in mind, but do not know how to do it in Access, as access has other ways of doing things compared to Progress for example:
1. I do not want things to popup in the middle of a Purchase form, which is already full enough
2. I want the user to be able to see what he has already entered (serial numbers are assigned to the products we buy by our suppliers), this is: the number of lines and the serial numbers, so that he/she can always go back to input after an interruption

If Access cannot simply limit the number of records I can add in a form, I guess I will have to call the separate entry form as many times as records need to be filled out, enter the serial number in a single text box, press a save command button which will then load each entry into a separate list box on the same form for the user to see his entries. Which sounds a bit heavy for the task really.

What would be your view?

 

[TimW]

This is roughly how my database deals with an issue like yours
1. You have a number of computers that need to be booked in [BOOKED_COMPUTERS]
2. In table tblSerialNumbers then you need to count the number of records that are associated with the records in tblPurchaseLines (I am assuming that there is a connection between the two. [COUNT_METERS_BOOKED]. (Stored in a field in an unbound form)
3. I use an unbound form that uses SQL to enter records WHILE [COUNT_METERS_BOOKED]=<[BOOKED_COMPUTERS] THEN {SQL to enter records into tblSerialNumbers}

HTH

 

[XV1957]

3. I use an unbound form that uses SQL to enter records WHILE [COUNT_METERS_BOOKED]=<[BOOKED_COMPUTERS] THEN {SQL to enter records into tblSerialNumbers}

Thank you very much TimW
This is certainly a very good way to go on a single user system.
Is it also applicable on a multi user system? If other people add to the Serial number file at the same time as you, would your count not be reached prematurely?

 

[TimW]

Hi Again,
This method is used on a multi user system. However, it is a bit more refined to account that it needs to stop booking and allow for work in progress. However, it should be said that if 5 computers need booking in to a system then it an exception to have someone book more than the required 5. If more computers are booked then surely they are booking the wrong ones!
In my system the number of items unbooked, in WIP and booked are tracked in a table which contains the order details (number to be booked)

 

[jdraw]

Things do NOT pop up by magic. They pop up as/when required by design.
Progress/Access/Oracle are tools that are programmed according to your requirements.
There is nothing inherently magical about the software. Things may look different, but they don't automatically write custom code for you.
The form you are working on (in support of a procedure or process) should have room/controls to do what you need. The operator should be able to verify/confirm that they have done something or not. Once data on the form is verified, processes can place that data in the proper tables behind the scenes.

In my view if you need x serial numbers to be entered, you invoke a routine that gives you x spaces, confirms that those spaces have data, allows the operator to indicate the values are complete and correct, then a button to invoke other processes/updates.

The form doesn't have to be specific to a single table. Your form collects data for the process/procedure, gets verified, then gets applied to tables as required.

 

[XV1957]

In my view if you need x serial numbers to be entered, you invoke a routine that gives you x spaces, confirms that those spaces have data, allows the operator to indicate the values are complete and correct, then a button to invoke other processes/updates.

The form doesn't have to be specific to a single table. Your form collects data for the process/procedure, gets verified, then gets applied to tables as required.

Thank you jdraw for your answer, which leaves me a bit puzzled though.
My question is and has been all along what the most efficient access programming technique would be.
I would not even know how to create x spaces on the fly. I know how to create and use some forms, until I hit a limit somewhere, be it my own or an Access limit. This is why I tried using a continuous form. If I knew the answer to my problem, obviously there would have been no need for me to ask.
I can create a form with a fixed, not variable, number of entry fields (say 10), control the input to these fields, even disable fields that are not needed (any field above the number of serial numbers to be inputted). It would not give me the look and feel of what I was looking for and may/may not be possible in Access.

 

[jdraw]

My remarks were about a generic approach. Along the lines of --I'm in the middle of entering Invoice/PO info, at some point I indicate I have received 5 computers which need serial numbers, so I suggested 5 text boxes into which I can enter the serial numbers, review them, hit a button to confirm...

But it isn't really the most efficient programming technique that is the key question -- in my view. I don't know your business process or operations, but, to me, the key question is WHAT do you want/need to happen at this point? If you can describe WHAT you want to accomplish clearly and concisely, then readers can offer/suggest options and then, after selecting the "best option", can design and develop how it would be implemented. There may be several options, and my 5 textboxes may not even be in the top 5. But it starts with the business requirement and What should happen.

I guess we have to hear more, or see some mock ups of the look and feel you were looking for.

Perhaps others may have some sample code or form with techniques and could post same for review.

NOTE: I did look in the code archives to see if I could find any samples. I did find this one by ChrisO that may be of interest. Unfortunately, Chris died last year, but has provided various samples in posts and archives.
I am not familiar with the code involved, but it seems he is allowing various things to happen while maintaining control and allowing some verification before adding/saving a record.
Others may have incorporated a similar approach based on Chris' sample.

 

[XV1957]

Hi jdraw,
The business case is as follows. On machines we build, certain components are valuable and you want to track their serial numbers. If you do not link a serial number of a valuable component, customers will start sending a lot of defective components back, and as by magic, they are all linked to your last delivery, which is still covered by warranty.
So these components have a field that indicates in the article table that you want to register the serial number.
When I get a delivery note for goods shipped in, my system asks me to enter the serial numbers as soon as I have entered a received quantity. And yes, this is by design.
I now solved it by having a separate window pop up, I enter the serial number in a text box and each time I press a save button, the serial number appears in a list box, and I can only input as many serial numbers as the total number received on the packing list in question for that article. May not be the most elegant, but it works for me, and that closes the chapter. I will look at Chris' code, and see if it helps me further for other challenges. Thanks again.

 

[jdraw]

Glad you have it resolved. I was just trying to highlight "design it the way you want/need it" and someone will have options to accomplish it.

Chris's stuff deals with popup non modal forms, it seems.

Good luck with your system.