Page 1 of 5 123 ... LastLast
Results 1 to 15 of 62
  1. #1
    lwampach
    Guest

    Custom Counter in Access 2000

    I found a Microsoft Knowledge Base Article that very nicely tells me how to create a Custom Counter (Article ID: Q210194). Unfortunately, I am not very familiar with Visual Basic. The article tells me to replace the error routine with a custom error routine, and I don't have a clue. Can anyone finish the job for me and give me an example of an error routine?

    Thank you very much!

  2. #2
    lwampach
    Guest

    Addition to Custom Counter Question

    This is an additional question I have on the Custom Counter function for Access 2000. My questions refer to Microsoft Knowledge Base Article Q210194 (not sure if I should copy the function here or not)

    I would still like to know about Error Routines, but I also got to thinking that it would be nice to be able to have this custom counter number automatically put into a form field, instead of just displaying a message box with the next number.

    If this is possible, could you please share your knowledge with me. Again, many thanks.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Counter in Access 2000

    A simple way to generate a basic error handling routine is to create a form, then place a button onto the form with the code wizard activated. Then just select the first thing it suggests. That will give you a basic error handling routine with a couple of spurious lines of code (the option selected) in it.

    With the example code for the counter instead of putting it a message box just assign it to the field eg.

    me!CounterField = counterFromCode

  4. #4
    melhado
    Guest

    Re: Custom Counter in Access 2000

    I am a little confused on how best to pass the value back to the receiving field in my situation. I thought that Me was used only in forms and reports, and I really want the value passed to the table when the new record is started. I have some ideas, but would appreciate any feedback or clarifications.

    I have a form that is set for Data Entry set to YES, as the purpose of this form is fairly straightforward. The line operator collects the needed samples and bags them for the test lab, recording the contents into this form and its underlying table, which is used for many other forms and links.

    Once they finish entering data, they click a button to print an identifying label for the envelope. The number I want this function to generate is the log number which is the primary key . Once done, I figure putting a NEXT button, which will open a new record, hopefully with the Log Number text control populated with the new number (They are numbers, but no math is done, so I currently have it as a text box -- can the value from the function be returned as a string, or is there no limitation? NextCounter is declared as Long. Does that mean I also have to make the data type of the tables holding the stored value (tblM_Log is the main table here)?)

    My only problem with that is how do I have that field populated when the form is first opened as well? This where I get confused.

    Would I want to include the value passing on the "NEXT" button or somewhere else? Once the log number is there, I do not want to have it edited or altered in any way -- otherwise I would use AutoNumber, but this numbering scheme can have no gaps in sequence, nor can I take any chances that when compacted, the needed precautions will be taken to assure continuity.

    Here is the snippet of the code where the message box part was, and I think where the value needs to be passed? Or do I just use the function to get the next number and pass the value in code behind the "NEXT" button?

    <pre>'Get next log number
    NextCounter = rs!NextAvailableCounter

    'Open table and get current value,
    'Increment value by 1, save the value back to tblCounter
    rs!NextAvailableCounter = NextCounter + 1
    NextCounter = rs!NextAvailableCounter
    rs.Update

    'pass value to tblM_Log
    'this is where the code passes it to the MsgBox

    rs.Close
    </pre>

    ..... and so on

    thank you,

    'dave

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Custom Counter in Access 2000

    In my form's BeforeUpdate event, I would put the following:
    If Me.Newrecord = true then
    CustomCounter = Dmax("customercounter","yourtablename") + 1
    endif

    I'm assuming a CustomCounter is a field that performs similar to an autonumber. That is, it increments by 1 each time it writes a new record. The only possible adverse situation above will occur if 2 users simultaneously try to get the next counter #. However, I figure 1 of these users will get a "duplicate primary key" error when they try to write the new record, which means all they have to do is try again (the routine will get a new customercounter).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Custom Counter in Access 2000

    In my solution, you don't calculate nor do you display the new Counter until the record has been saved. This provides an automatic number with no user intervention. Like I mentioned, the only possible problem occurs when 2 users try to write a new record at exactly the same time (which is not too likely), and even then one user merely has to try again to save the record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    melhado
    Guest

    Re: Custom Counter in Access 2000

    Mark,

    I assume you were responding to my post?

    The "Custom Counter" is a replacement for Autonumber, one that will not leave gaps in numbering or be affected by compacting and such. The KB article Q210194 gives a nice sample function that requires a one column table to maintain the numbering and the code to pick up the last entry in that column and add 1 (or any increment you want) and then writes it back to that table -- the snippet I posted represents how it does it -- to the point where it adds the new counter number to its own table.

    However, what the example does is passes that value to a msgbox advising the user of the new number, which they would be required to enter. It does it by calling the function from an event on the form. (The example makes a demo form with command button, whose OnClick property calls the function.)

    I prefer that the value is passed to the form, and thus to its underlying table, as the record is opened. It is highly unlikely that a second user will be using this form simultaneously. What I need is to pass the value the function plants in the last row of its table, to the underlying field of the form, not to a message box requiring human intervention.

    I understand the logic of using the form's Before Update event, with <pre><font color=blue>If Me.Newrecord = true then</font color=blue></pre>

    to check for the new record, which meets both my requirements of when a new record follows a filled one, AND when it first opens. Thank you very much for that clarifying idea.

    Where I am a bit confused, however, is this also where I would call the function, and how to assign the value to the proper text control. The function overwrites the previous value as it increments it, in the same table cell, leaving only one value in the entire table -- making the DMax function fairly moot used with this function. I am still a bit unsure of how to call the function and then pass the value. However, the idea of using the BeforeUpdate may do the trick.

    My Counter table that the function writes to is "tblCounter", the underlying table to this form is "tblM_Log", and the field I want filled with the result is "txtLogNum", the function name is "New_Log()". The field in tblCounter which is storing the function's result is called "NextAvailableCounter". SO....how close would this be?

    <pre><font color=blue>If Me.Newrecord = true then
    Call New_Log
    txtLogNum=[tblCounter].[NextAvailableCounter]
    endif </font color=blue></pre>


    What needs correcting? Also, the data type of [tblCounter].[NextAvailableCounter] has to match the data type property of [tblM_Log].[txtLogNum] -- true? Or is there some way get around that?

    Thank you

    'dave

  8. #8
    melhado
    Guest

    Re: Custom Counter in Access 2000

    Darn, I was editing my post for more clarity, as you responded <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    On review, I see what your solution is doing, it is making moot the need for the function, and is taking the last value entered (DMax) of the same field (the field which needs to be incremented is txtLogNum) to generate the new incremented sequential number. I missed that the first time, I was so focused on how to pass the value from the function to the form. Sorry <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Only 2 things I cannot get with it, one is if there are more than one user doing entry (it is a split database), the conflict you mention could happen, but more importantly, I need the value to appear as soon as the new record appears, whether as the form opens the first time, or a new record is opened. That number has to be linked to the samples collected and logged.

    The function that MS published takes care of the multi-user aspect, yet yours is by far simpler. Both, however, leave me needing to know which event to attach the code to. What is the order of events as a new record opens, I guess, is what I need to know -- regardless of which approach is used.

    Thanks again, Mark, for your feedback

    'dave

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Custom Counter in Access 2000

    The problem with calculating and displaying the counter as soon as user starts a new record is that if there are 2 users both adding records, they will both see the same counter number. Since it might take some time for a user to finally save a new record that was started, the chances of duplicates is high.

    With my technique, the counter is calculated only just before the moment the record is written for the first time. (I did mention checking for Me.NewRecord=True first didn't I?) Therefore, the chances that 2 users will choose to save their new record at virtually the same time is low. And it really doesn't impact anything else, because as soon as you move to a subform, the main record is saved automatically, triggering the calculation of the counter.

    As for the order of events on a form:

    BeforeInsert occurs as soon as you make an entry into a control when you are on a NewRecord.

    BeforeUpdate occurs when the record is about to be written to the table, whether a new or existing record.

    AfterUpdate occurs after the record has been written to the table, again it occurs for both existing and new records.

    AfterInsert event occurs last, but only if you've just added a new record to the table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    melhado
    Guest

    Re: Custom Counter in Access 2000

    Some superb tips and excellent points, Mark. Thank you.

    I understand what your solution does, but this is a single purpose form, no subform, simply a log of the samples collected and placed in an envelope by the machine operator. The table it writes to, however, is important in other parts of this database, and the number is critical. Also, a label is to be printed out prior to moving to the next record, thus my need to have the number when it opens. I am obviously going to preface the code on the Print button to commit the record first, so there will be something to print -- but the operator needs to have the number long before the label is printed.

    There is currently no plan for multiple operators to be simultaneously be entering data at the same time; however, that could change in a blink, which is why I like the idea of the multi-user functionality afforded by the separate function. The chances of multiple operators seeking a new counter number at the same time, is highly unlikely.

    I need only to pass the value to the current record as early as possible. From your description of the events order, it would seem that I could attach my code to the BeforeInsert event. [txtLogNum] is a locked control and not in the tab order, so if I understand it correctly, as soon a character is entered into the first control in the tab order, the log number shows up?

    Is my syntax in the previous message along the right idea?

    Appreciate your assistance,

    'dave

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Custom Counter in Access 2000

    I understand what you are saying, I just don't agree it is the best method. What benefit is seeing that Counter# before committing the record? Especially considering that another user may actually write the number before you do, so you will have to get a new number? If you get the number in the BeforeUpdate routine, and if you put your print routine in the AfterUpdate event (or AfterInsert event), you will print the information with the real Counter#, before moving to the next record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    melhado
    Guest

    Re: Custom Counter in Access 2000

    Kind of a unique situation. This data is not entered as a separate operation, but live as the samples are being collected for testing. Machine operators are doing the data entry, so I have to prepare for the worst, which is why we decided to go with creating the number in the function, and then wanting to pass it to the form/underlying table. Your method is much simpler, I concur, but for this situation, we are more comfortable with the function providing the number. We went through all possible scenarios, and decided to go with the function.

    The reason the number is needed early is because it is needed to complete the physical collection of materials, and then the rest of the form can be filled out. The print button will commit the record and then output the identifying information to a label printer attached to the workstation. I do not want to have it automatically print, as the work needs to be checked before going to the next record. The form's Data entry property is set to yes, so that backward navigation is not important here; however, there may be another form accessing this table, which is why I heed your advice about checking it for being a new record. BTW, should I not also check to make sure it is empty, or being dirty means its a new record, as this field is the Primary key?

    It seems as if the OnCurrent event would be the best place to attach the code? Would this be the correct way:
    <pre><font color=blue>
    If Me.NewRecord = True Then
    Call New_Log</font color=blue> <font color=448800>'name of function</font color=448800>
    <font color=blue>txtLogNum=DLookup("NextAvailableCounter ',"tblCounter")</font color=blue>
    <font color=448800>'txtLogNum is the field name on the form,
    'NextAvailableCounter is the field name holding
    'the updated number in tblCounter
    'as a result of the function. It is a single record,
    'so I believe only one number can be returned.</font color=448800>
    </pre>

    Is my thinking correct on this, or am I missing something, in passing the value from the function-produced value in tblCounter to my form/underlying storage table?

    Thank you for all your support and suggestions

    'dave

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Custom Counter in Access 2000

    I understand what you are saying, but my point is that if you Print without first saving the record, then you may print a number that is not right! Therefore, your print routine MUST save the record. If there is a problem at that time (that is, a duplicate #), then you can recall your function and get a new #.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    melhado
    Guest

    Re: Custom Counter in Access 2000

    I have already handled the saving of the record prior to printing, and have also set a flag to true, that I use to prevent the user from going to a new record, or quitting, if the current one is not printed.

    If you look at the function, it is locked, so that 2 people cannot get a number at one time. What I am attempting is to have the function do its thing, then pass the value to the form, thus in a second, another user can get a number.

    I am not as familiar as I would like to be with calling user-defined functions and passing the value...am I correct in thinking that OnCurrent is where to attach my proposed to the form, or was something missing (besides it should be Call New_Log() -- forgot the parentheses) that will give me problems?

    Thanks again,

    'dave

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Counter in Access 2000

    Can't you use Mark's suggestion for the log number, then use an autonumber in the same table to link additional records?

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •