Custom Counter in Access 2000

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?

'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 

….. and so on

thank you,

‘dave

Reply | Quote

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).

Reply | Quote

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

If Me.Newrecord = true then

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?

If Me.Newrecord = true then
Call New_Log
txtLogNum=[tblCounter].[NextAvailableCounter]
endif 

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

Reply | Quote

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.

Reply | Quote

Darn, I was editing my post for more clarity, as you responded

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:

If Me.NewRecord = True Then
Call New_Log 'name of function
txtLogNum=DLookup("NextAvailableCounter',"tblCounter")
'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.

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

Reply | Quote

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 #.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

This post was edited after I found a fix for the first issue

First, I need to mention, that as much as I like Mark’s suggestion, and concur that it is a much simpler solution, I cannot use it, as I need to have the new log number appear as soon as it is open, and need to accommodate multiple users — which the Microsoft suggested KB article provides (which I list at the end, in its modified form).

I got it to work correctly by calling it in the OnCurrent event of the form. At first, it seemed not to work on the first new record — the value returned is “0”, but advancing to the next new record gives me the proper value. I fixed this by removing the default value of “0” in the table’s field structure to nothing– apparently the “0” was actually the value for the “new record.

I much prefer to pass the value from the function directly to the underlying table! Here is the code I am using to call it from the form, followed by my attempt to pass it direct, then just have the Current code test for new record and call the function:

Private Sub Form_Current()

If Me.NewRecord = True Then
'call function
    Next_Log

'pass value from function's table to underlying table
    Me![txtLog_Num] = DLookup("NextAvailableCounter", "tblCounter")
End If

End Sub

When I am try to pass the value directly from the function to the form calling it, I keep getting the error message “Operation no allowed when the object is opened.”

I get this error message no matter what I try. The function would always update its table properly, but failed on passing the value. I opened a new ADO recordset on then table I want to pass the value to, trying every locking, cursor, and Option arguments to no avail. Here is what I have, the code I used to attempt to pass value is commented, but in red.

Option Compare Database
Option Explicit

Public Function Next_Log() As Long

On Error GoTo Next_Log_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long

Set rs = New ADODB.Recordset

'Open the ADO recordset
rs.Open "tblCounter", CurrentProject.Connection, & _
adOpenKeyset, adLockOptimistic

'Get next log number
NextCounter = rs!NextAvailableCounter

'Open counter maintenence 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 (the underlying table of my form)
'rs.Open "tblM_Log", CurrentProject.Connection, & _
adOpenKeyset, adLockOptimistic, adCmdTable

'With rs
'    .AddNew
'    .Fields("txtLog_Num") = "NextCounter"
'    .Update
'End With

rs.Close

Set rs = Nothing

Next_Log = NextCounter

Exit_Next_Log:
Exit Function

Next_Log_Err:
MsgBox Err.Description
Resume Exit_Next_Log

End Function

Appreciate any assistance on this.
‘dave

Reply | Quote

did you try putting it on both the on current and the onopen events

Reply | Quote

Edited by melhado on 17-Jul-01 11:57.

See my previous post which I edited to reflect a fix for this issue.

Still would like to get the function to pass the value directly.

Because of the way the form will be filled in, I have to rely on the function and the Log Number being present on the record starting.

Thanks,

‘dave

Reply | Quote

Rather than the OnCurrent have you tried the AfterInsert event which should fire after you add a new record ie. as soon as your user starts entering data in any field.

If the form is bound to a recordsource then use something like the following

me!CustomCounterField = NextCounterValue()

Reply | Quote

OK, if I read your example correctly, I can actually not have to call the function first and then do the DLookup, as I showed in my previous post? I can just set the txt control to the function, and it will return the value? IOW, just test for the new record, then the line

Me![txtLog_Num]=Next_Log()

That will call the function and pass its value to the control? Saves me a line of code, and may speed it up a hair. However, it would still be nice to pass it directly to this table from the function. Not sure if there is any advantage to it, just seems more natural to me.

I needed to do it on Current, as without the Log Number, no fields can be filled out — its an unusual match of technology with people and process

Thank you for your suggestion

‘dave

Reply | Quote

Passing it through the form allows it to be seen by the user if that is a requirement, else you need to get it ‘back from the table’. If it does not need to be seen then why not create this custom number directly before you update the record? That allows the user to abort without an ID number being assigned.

Reply | Quote

Yep, works fine. Was not aware that I could pass it so simply without calling the function first and then going back to the table to retrieve it.

It did speed it up, as well. Thank you.

Unfortunately, I am resigned to using the OnCurrent event, as the log number starts an entire traceability scheme, and samples cannot be collected until that number is known, and without samples, no data can be entered. As I said before, it is an unusual match of technology to people and process.

As for an ability to abort, that is not an option. As this is for traceability, as well as to uniquely identify a set of associated charts, we can have no gaps in numbering; aborting would still advance the counter. We opted to use the separate table to maintain the numbering, as it is a multi-user environment.

Users do not advance to a new record until they are ready to log new samples. There are also traps to prevent them from going to the next record without completing critical information or printing its ID label of the current one, and quitting is only possible by the QUIT button on the form, which will not let them quit with an incomplete record.

In the event this should happen, our hack is that we would be able to print a label and fill in nonsense information to populate the critical fields. Since the log number is not a search criteria, it would only occupy a row in the table and not cause any issues. It is a dumb hack, but until I (or someone — anyone — can come up with some way to accommodate all the above conditions, it is the least damaging solution.

Andy, thank you for helping me on the passing values efficiently.

‘dave

Reply | Quote

Hi Mark,

I now have need for your DMax method of adding a counter .

I have to increment a field value by 1, however, I need to place criteria that limits the counter increment to that group. I am having a hard time with the criteria — Access 2K keeps complaining about a comma after the +1 and before my criteria:

txtStr_pc=DMax(“Str_pc”,”tblMaxCounter”)+1, _
“Bill_Num = “‘ & Forms!frmTestCounter!txtBill_Num AND
& Forms!frmTestCounter!txtBill_Half

I have just started working on this, and currently [txtStr_pc] is a text data type, as it needs to be later concatenated with other text fields, of which the two criteria are part of. I also am not sure about the quotes and escape characters. Is it necessary when working with incremental counters that they be Number types? How does this affect the concatenation — syntax-wise?

I also need to mention that this will eventually be a subform, linked on the two criteria fields to the master form, meaning the 2 fields are in this table as well as the other — I am just trying to work out the logic right now, while waiting for some areas of automation to be completed.

The idea here is that when the operator clicks the New button, [txtStr_pc] will be incremented by 1 — only within the group related to the Bill_Num and Bill_Half fields, as the 2 criteria fields change, so will the numbering. When the operator opens the master record, it should bring up the subform at the next number also, so the code may go OnCurrent — there is only one operator using this form at a time. But that’s another issue.

Right now, I could use ideas on how the syntax should be for my criteria, or is Access seeing the +1 as the criteria?

Thank you

‘dave

Reply | Quote

>>txtStr_pc=DMax(“Str_pc”,”tblMaxCounter”)+1, _
“Bill_Num = “‘ & Forms!frmTestCounter!txtBill_Num AND
& Forms!frmTestCounter!txtBill_Half

I have just started working on this, and currently [txtStr_pc] is a text data type, as it
needs to be later concatenated with other text fields, of which the two criteria are part of.<<

Dave,

First of all, I don't understand at all what you are trying to do here. Even if you substituted an & for that comma, you would end up with a value of txtstr_pc something like this:
1001Bill_Num=…etc.

Why are you creating this field, given that it is created from 2 fields that are already in your table? This definitely violates the Rules of Normalization.

Reply | Quote

Hi Mark,

I got Andy’s response before yours, and detailed what I am trying to do. detailed post of what I am doing

When you look at that post, you will realize that I am not violating any rules of normalization — I would never do such a thing . The field I need to increment relies on the other 2 fields which identify a specific group which can change and incrementation is 1 plus the highest number for that group. What I am trying to do is get the DMax formula you posted last week to increment the value based on the defined group.

As for the concatenation, at this operation, the product ID changes to a different naming scheme; that naming scheme concatenates other values with these (explained in detail in the aforementioned post)–this value is part of that concatenation. The only reason both identities have to be available is (actually in separate tables as the composite primary key and foreign keys in this table) is for traceability back to when it was raw materials.

On this, I am working one piece of a puzzle which was created before the assignment started, and I am trying to fill holes where functionality is missing. I have no control over the other pieces of the puzzle, yet have to find ways to accommodate it. This is just one.

Sorry for the confusion, but do look over the detailed post, albeit long-winded, is very detailed.

Thanks for the feedback

‘dave

Reply | Quote

Too late, I’ve already reported you to the Normalization Police!

I really don’t quite understand the situation. However, I still maintain that if you are creating a field in a table based on other fields in the database, then you are violating Rules for Normalization.

DMax does follow the same general format as DLookup, so you can use selection criteria. However, from your equation it was hard to tell that’s what you were trying to do. It looked like you were trying to append the Bill_Num to the DMax value, not use Bill_num as a search criteria.

Reply | Quote

Did you give the Normalization police my correct address?

I am confusing you with the concatenation thing. The concatenated value is not stored here, it just shows on reports and in queries, and on the form before the measurements are weighed against specs stored in the measurement program. At the final point, this concatenated value becomes the new identity and the pieces of the concatenation are never forwarded. It is then stored in other tables I have nothing to do with yet, but are the toys of Engineering, which thinks Access is a variant of Excel.

What I do need to do is get the counter to increment the specified field based on the criteria of the 2 fields comprising the “master piece group”. It is precisely the criteria portion of the code I am having problems with and could use help.

Thank you

‘dave

Reply | Quote

Yeah, I was confused about your use of that field. Here is your code, and below it the changes I think you need.

txtStr_pc=DMax(“Str_pc”,”tblMaxCounter”)+1, _
“Bill_Num = “‘ & Forms!frmTestCounter!txtBill_Num AND
& Forms!frmTestCounter!txtBill_Half

txtStr_pc=DMax(“Str_pc”,”tblMaxCounter”, _
“Bill_Num = ” & Forms!frmTestCounter!txtBill & _
” AND Bill_Half='” & Forms!frmTestCounter!txtBill_Half & “‘”) + 1

Reply | Quote

It is easy to be confused — that is why I wrote out such a detailed description.

The code you posted shows me I got the syntax and single/double quote thing all wrong, and the +1 goes after the criteria. Thank you. I am getting a runtime error, however, here is the code I put in:

txtStr_Num = DMax(“Str_Num”, “tblMaxCounter”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
“Bill_Half ='” & Forms!frmTestCounter!txtBill_Half & “‘”) + 1

The error message I get is:

Run Time Error ‘3075’
Syntax error (mising operator) in query expression “Bill_Num=’10579Bill_Half=’B”.

The values returned are correct, and the whole code block is yellowed, but this error keeps coming up, no matter what I do with quotes, ampersands, etc. Matter of fact, adding quotes gives me compile errors, expecting end of statement, etc.

This is a test table and form — just to see how this would work, as I have to increment 2 fields on the many side, based on the same criteria. The actual will be a master/sub form with the Bill_Half and Bill_Num actually generated from the master (one side) table and the Str_Num located in the sub (many side). Is this going to make any difference if I just want to test it?

Thanks again for all the help

‘dave

Reply | Quote

You left out the AND (I assume that’s what you want) between the two conditions:

txtStr_Num = DMax(“Str_Num”, “tblMaxCounter”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
“AND Bill_Half ='” & Forms!frmTestCounter!txtBill_Half & “‘”) + 1

Reply | Quote

How are you Charlotte?

I tried the AND, but I don’t think I got the quote syntax right — that trips me up more than I care to admit to. I pasted your snippet in and get the same error

I should mention that all fields here are text data types. Can I do that with the value I want to increment (Str_Num)? Or is this another sinful syntax error on my part?

Thanks for the feedback

‘dave

Reply | Quote

txtStr_Num = DMax(“Str_Num”, “tblMaxCounter”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
“Bill_Half ='” & Forms!frmTestCounter!txtBill_Half & “‘”) + 1

There are 2 problems here. The first is what Charlotte pointed out, you need an “And” between the 2 criteria. However, you put an apostrophe before the value of txtBill_num, but you didn’t add a closing apostrophe. If txtBill_num is numeric, then you don’t need an apostrophe at all, in which case you would need (I’ve some extra spaces around apostrophes so they are readable):

txtStr_Num = DMax(“Str_Num”, “tblMaxCounter”, _
“Bill_Num =” & Forms!frmTestCounter!txtBill_Num & _
” AND Bill_Half = ‘ ” & Forms!frmTestCounter!txtBill_Half & ” ‘ “) + 1

Or else this:
txtStr_Num = DMax(“Str_Num”, “tblMaxCounter”, _
“Bill_Num =’ ” & Forms!frmTestCounter!txtBill_Num & _
” ‘ AND Bill_Half = ‘ ” & Forms!frmTestCounter!txtBill_Half & ” ‘ “) + 1

Reply | Quote

Mark, Charlotte — thanks so much for getting me going on this. The quotes in strings keeps throwing me off. At one point I did add the AND, and the opening double quotes, but forgot the apostrophe.

I created my form/subform and the field that needs to advance (Str_Num) is on the subform, but advances in relation to the Bill_Num and Bill_Half fields in the master form. Since this value is needed right away, I put the code on the Current event. All works really well, except for 2 little issues, which I could use help on.

First, When there is a value[/u] in the Str_Num field of the subform’s underlying table (tblTinTest), it goes forward as expected, but when it is null or 0, whether I make it text or number data type, I cannot get the counter to go to 1. When I use a default value of ) in the table, I quickly see the 0 in the text control, but that’s all.

This happens only when there is a new record for the grouping (Bill_Num and Bill_Half ). I have tried changing data types, making a default of 0, nothing seems to work. Do I need to test for a condition, or change something else?

Here is the finished code I am using on the Current event of the subform:

If Me.NewRecord = True Then

txtStr_Num = DMax(“Str_Num”, “tblTinTest”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
” ‘AND Bill_Half ='” & Forms!frmTestCounter!txtBill_Half & “‘”) + 1

End If

Second, when I try to use the value in my concatenated value (to show on the form), I lose the leading 0. I tried using input masks and format of “00” on the form, and the table (on the form, txtStr_Num is a hidden field — the operator needs to see only the concatenated value) .. here is what I have in the control:

=[Str_Typ] & [Contractor] & [Bill_Num] & [Bill_Half] & Format([Str_Num],0) & “S”

I cannot seem to get 2 “0”‘s to hold when I try to format the Str_Num field in the concatenation.

Making this harder, is that I have no real control on what data is stored or where, or its data types or format.

Appreciate your help,

‘dave

PS- Does anyone know why using the tags seems to extend all the lines in Preview mode, with the warning that it cannot extend the demo bar — even when the continuation characters are used and it doesn’t exceed the demo bar?

Reply | Quote

You probably need to wrap the whole DMax clause in the NZ function (Null-to-Zero). If there is no prior value, DMax returns null, and Null propogates itself; that is, any math done to Null yields Null. So, do this:
NZ( DMax(…all the stuff from before..) , 0) + 1

If I wrote this correctly, you should be adding 1 to the result of the NZ function.

Reply | Quote

I tried that and got a

Run Time error ‘-2147352567 (800200009)
The field is too small to accept the amount of data you
attempted to add. Try inserting or pasting less data

which made absolutely no sense to me. All I was trying to do was convert any empty space to “0”. The field size is 2, as it was earlier. I have posted the code below.

I started thinking of a different track, figured if I put the default value of that field as “01”, a new record will open with that as its value, unfortunately, it doesn’t seem to work that way. Is there a different event I can attach this code to so that idea would work?

As part of testing, I manually put 01 in a record, and it picked up at 2 and so on, but at “10” it stopped and assigned 10 to each subsequent record — any idea why?

Also, it will not hold the leading 0, in the table or in the concatenated value — this is extremely important. I have tried the format property of the table, of the form; the input mask of 00;0;” ” on both, and tried Format in the concatenation:

=[Str_Typ] & [Contractor] & [Bill_Num] & [Bill_Half] & Format([Str_Num],”00″) & “S”

Here is the counter advance code with the Nz that gives the above runtime error:

If Me.NewRecord = True Then

txtStr_Num = Nz(DMax(“Str_Num”, “tblTinTest”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
” ‘AND Bill_Half ='” & _
Forms!frmTestCounter!txtBill_Half & “‘”), 0) + 1

‘Me.Requery

End If

Thanks for the
‘dave

Reply | Quote

I’m not sure why you are having this problem. The only thing I could detect is this line of code:

” ‘And Bill_Half='” & _

You could try eliminating the space before the apostrophe, and adding a space after it instead.

It also might be easier to debug if you created a strWhere variable first, and made it equal to “Bill_num=etc…….”. Then in de######, you could see the actual string you are passing to Dmax.

As for the leading 0. You can’t store leading zeros in a numeric field. You can, however, use the format function to align the raw number to a specific number of postions, and provide leading zeros. That is usually all you need.

Reply | Quote

Edited by melhado on 31-Jul-01 15:46.

I like the idea of the strWhere variable, I was thinking of that, but didn’t as this is the only place I need it. The idea of seeing it when debugging is good, however, I see it here as well.

It occurs to me that I may not have been clear on the data type, the value I want to increment is a text data type — I did try using the Number type while troubleshooting, but I do not have control over the data types used here, they have been previously assigned. Not sure if this makes a difference.

What I don’t understand is why the counter works without the Nz function wrapper, and then with it, it choked. Exception — It isn’t incrementing past 10 for some reason, and I cannot get the leading 0 to hold in the table, despite the fact this is a text field. I have managed to get the leading 0 to show up in the displayed concatenation.

I test manually by putting a 01 in the table field or form field, and the counter picks that up– it remains 01 in the table cell, yet advances to 2 on the form and in the table (no leading 0), yet 02 in the concatenation, yet the “01” I put in the table holds the leading “0”, but the counter generated “2” shows as “02” in the form (I have the form default value property set to “01” and the format property “00” –or the Input Mask to 00;0;” ” — on both the form and table design views — which makes no difference, same unwanted result. Could this be contributing to the not advancing past 10?

I have managed to get leading zeros to hold in text fields of tables before, is DMax changing something I am overlooking. Maybe there is a way to add the FORMAT capability to the DMax function, as I did with the concatenation, or some kind of test to + “01”?

Maybe I need to attach the code to a different event so that the “01” populates before the counter is fired up? But then wouldn’t that effect the NewRecord test ?(There are also 2 other fields used in this concatenation that are default values — again, they need to be stored, by no choice of mine).

I need help to figure out why the counter can’t get past 10 — is that something to do with the format or input mask or default value — 11-99 are still 2 characters, and a way to get the DMax to increment past 10

Thanks for all your help
‘dave

Reply | Quote

NZ works with any datatype.

As for your leading zero, I’m not sure why you are losing it. Just in case, why not try converting to an integer before you format.

Reply | Quote

Nz isn’t working here for some reason — with the exception of the holdup at 10 — the counter works fine without Nz; yet without Nz, getting counter to start on a new record is an elusive task. I still don’t understand the error message I got about field size no being large enough — it is a 2 character text field, and all I am doing is advancing the previous value by 1. Could it be thinking that I am trying to add 1 to the Bill_Num (5 character text) or Bill_Half (1 character text) fields in the criteria . The field I want to increment, Str_Num, is a 2 character text field — otherwise, how could it possibly think the field was too small.

Can someone take a look at the post describing this message and code I used, and tell me if I did something wrong in my use of Nz wrapping the DMax counter code? As I said, this code works fine (except with 10) without it, I figure it must be the way I wrapped it?

I should mention that if I manually place in the table, an 01, it works through 10, but if I use 10, it seems to advance to 11,12, and so on; Any number over 10, ie, 19, 20, etc., advances just fine (without the Nz part)!! Yet, using the default value set to “01” doesn’t seem to work — besides, the counter runs on Current, maybe the default is planted after? Could this be killing it? I do have some flexibility in this, as this form is used for setting up the process, prior to the automation of recording of measurements being fed to my tables, which is when this number has to be present in its pieces in the table.

I am not really sure if that is such a good idea, as it creates a record, and I am testing for a new record — so looking at alternatives, is there some code I can use to separately test for null values on the record and plant a “01” if that is the case (imitating what I do manually to get it to run). When I do this manually , the counter subsequently adds 1(except as noted on 10). Is there possibly other events I can use for the counter that could help me use this idea — unless I can get Nz working. This field has to be a text field, so converting to Integer before formatting is not a good option.

I was successful at getting the FORMAT to work for the concatenation on the form, and if I can’t get it to write to the table with the leading 0, I can use my formula again to plant the concatenated value into the table it is needed. Getting it is critical, as this concatenated value becomes the new identity for this component for all subsequent processes — it literally changes identity at this point.

I have removed default value, as well as the input mask, tried “00” in FORMAT property, but nothing is helping to keep the leading 0 in the table, or get the counter to see it.

I am all ears as to any suggestions.

Thanks for all the feedback

‘dave

Reply | Quote

It it’s a numeric field, you can’t actually *store* the leading zero, you can only format the display to show it.

Reply | Quote

Hi Charlotte,

It’s not a numeric field, it is a text field, and cannot be changed from that, for reasons way beyond my control.

I think I may have found a way to deal with not having the leading 0. When I concatenate the values for display on the form, I was able to get the 0 in there by wrapping the field in a FORMAT function and using “00” — it works nicely; the field can have 2, but the display value shows that part of it with a leading 0–02. (I sort of got the idea from an earlier one of your posts on another issue — thanks)

=[Str_Typ] & [Contractor] & [Bill_Num] & [Bill_Half] & Format([Str_Num],”00″) & [CoatUnc]

Raw values may be 02 (which, BTW, stores properly, leading me to believe that DMax is treating [Str_Num] like a number type, this one is a regualr value) & G & 12345 & A & 2 & S. This reads in the display as 02G12345A02S — just like it should.

I am pretty sure I can use that same string to append the concatenated value to the other table in another database I have nothing to do with. Makes the leading 0 issue moot, albeit, a mystery to be solved another day, or lifetime

HOWEVER, I still need to figure out how to get Nz to work for me, or a workaround to handle the new record, where there is no value in that field, like somehow planting a 1 in there that will stay, and the next record does the counter — some kind of IF condition testing? Then all that leaves is the mystery of why the counter is jamming at 10, as described in my last post. That is the final challenge.

Thank you all again for the ideas and feedback

‘dave

Reply | Quote

I still think you should break this up into smaller pieces, so you can see thru de###### what you are putting together. It is so easy to misplace a ” or a ‘, that sometimes you can look at the same line of code for hours and not see it.

I would also put the incrementer in the AfterInsert event. In this way, it will change the default value before the next line is displayed.

Reply | Quote

I agree with turning the criteria into the strWhere you suggested, but right now, it works — except for the issue with passing 10 — when I take out the Nz wrapper, so I don’t think it is a syntax issue; more like something Nz doesn’t like or what-not.

It looks like we are assuming that Nz may not work for this, and the default value idea I sort of nixed in my last post, as I felt that it would kill the counter, as each new record with no value gets the default. As I read your post about using the default value, I realized that the counter code will replace that with the new number — can DMax increment from “00” or will that be considered not a new record anymore? I would think that using “01” as the default would make the first record of that group “02” (or 2); am I not thinking correctly?

I will try running the counter as you suggested on the after insert, as the operator does have to enter his clock number first, and potentially change one of the fields included in the concatenation.

I have one foot out the door, so I will try it tomorrow, but if you could, deconfuse me on the default value as the workaround.

Thanks for all the feedback

‘dave

Reply | Quote

It all just depends on when you are setting the default. I think the initial default value should be ’01’ (assumeing you want to start at ’01’). You then change the default value to ’02’ on the AfterInsert event, which means you just wrote line 01. I would also set the default value to ’01’ each time I exited the subform (and perhaps in AfterUpdate event of mainform).

Reply | Quote

From the tone of this, it seems that working with the default value to get the leading 0 to hold may be the way to go, instead of Nz. If I can get the default value to write to the table with the leading 0, maybe that will help DMax get past 10, when incrementing from a single digit. Emilia’s post postulates that because of no leading 0, it is treating the 2 character text field as it would a sort. Unfortunately, it has to remain a text field.

I think I understand the direction you are taking me, but I am not clear on what exactly you are suggesting. Are you saying I should do the Default Value property at “01”, then use the DMax counter on the AfterInsert event of the form? Then after that increment the default value in code? Or keep resetting default value to “02” on the AfterInsert?? Not clear on that. How would I do that? Is resetting the default value part of the counter code or a separate line of code following it on the same event. Or are you saying the counter then picks up from there? What advantage is there to incrementing the default value — once I have a value in there with the leading 0, then I should be able to increment it — on the exit function of the subform?

Also — get ready to smile — this form is not only a subform, but has a nested subform attached to it, which the operator will go back and forth to and from, so I am not too sure about putting any critical code on the exit event. It is an interesting challenge, but when done, will save them lots of time and stop the error collection they have been doing.

I have tried planting the “01” direct, and the DMax counter makes the next one 2, no leading 0, will this not also happen with the default value? Or will the initial Default Value just be overwritten with the next count? “01” is necessary to have, I cannot skip it. Will using 01as the default value do the same?

Wondering if I can use the combination of the default value idea along with wrapping the DMax counter in a FORMAT function to force the leading 0 might work? Not real knowledgeable of how I would go about doing that — where would I put the “00” as the format, so as not to get it confused with the +1 of the DMax function? Format (DMax (field, table, criteria),”00″)+1? Would Dmax understand if instead of +1, I used +”01″ for the increment? Is there maybe some IIf test that may help here? Even more convoluted, is there some way to convert it to a number for it to add its increment to, then convert it back to a 2 character text field with a leading 0. I have to find a way to increment this field automatically.

YUP, I am reaching for straws here — I need to have it a text field, and apparently, I am going to need to get that leading 0 to store in the text field, so that it will increment properly, and it has to remain a text field.
Thanks for the
‘dave

Reply | Quote

Try looking up the DMax, adding a one to that value and then converting it back to a string. If the number is less than 10, add a leading zero to the string. Otherwise, not.

Format (Nz(DMax (field, table, criteria),0) + 1, “00”)

Reply | Quote

Well, Charlotte, folowing your suggestion, I no longer get that oddball RunTime error, but now I cannot even get the counter to work. I have picked up on Emilia’s post regarding using an integer instead of text, and it is now storing the leading 0. However, I cannot get the DMax to work when wrapped in either the Nz or Format functions, so I cannot test my concatenation string to see if I no longer need the Format in that string.

I have now tried it using Integer for data type, formatted 00 at the table level; I tried taking out the Nz, and using just the DMax using a default value of 0 and 1, and it will not advance, nor does it even do anything on a new record. I am not sure what I may have done. Here is the code I am using for the DMax:

If Me.NewRecord = True Then

txtStr_Num = DMax(“Str_Num”, “tblTinTest”, _
“Bill_Num ='” & Forms!frmTestCounter!txtBill_Num & _
“‘AND Bill_Half ='” & _
Forms!frmTestCounter!txtBill_Half & “‘”) + 1

‘Me.Requery

End If
[/blue
For the Nz, I pretty much used the snippet example you posted, adjusting for not having Format.

Here is the concatenation string I am using to display the serial number — this worked with the single character, text data type — could it be affecting my now having changed to Integer?

=[Str_Typ] & [Contractor] & [Bill_Num] & [Bill_Half] & Format([Str_Num],”00″) & [CoatUnc]

Should I just take the quotes off the “00”? Once I can get the counter working again, I will see if the increment code will plant it into the table with the leading 0, or should I resort to the FORMAT function in the DMax code?

Thanks for all your help

‘dave

Reply | Quote

Stop trying to do it all in a single statement. It isn’t any more efficient and it’s much harder to debug. Plus, you’ve dropped the space from before the AND again. Breaking it out into pieces lets you see where you’ve made a mistake. Your naming is also extremely confusing, and it’s one of the reasons I don’t like naming conventions applied at the field level. As I understand it, you’ve changed your field str_Num from text to an integer but the field name still suggests that it’s text.

Dim intNum as Integer
Dim strWhere as String
Dim strNum as String

strWhere = "Bill_Num ='" & Forms!frmTestCounter!txtBill_Num & _
    "' AND Bill_Half ='" & _
Forms!frmTestCounter!txtBill_Half & "'"

intNum = Nz(DMax("Str_Num", "tblTinTest", strWhere),0)
txtStr_Num = intNum + 1
If txtStr_Num <10 Then
  strNum= "0" & txtStr_Num
Else
  strNum = cstr(txtStr_Num)
End If

Where exactly are you trying to use:[indent]

---

=[Str_Typ] & [Contractor] & [Bill_Num] & [Bill_Half] & Format([Str_Num],”00″) & [CoatUnc]

---

[/indent]If it’s a control on the same form and from code, just replace the format portion of the expression with the variable strNum. If you’re doing it by setting the controlsource in the property sheet, why?

Reply | Quote

Stop trying to do it all in a single statement.

I have been meaning to since Mark yelled at me for the same reason. However, I am still in the process of testing all the pieces, and this is a test form, so it didn’t seem as high priority. Looking at the code snippet you posted, however, makes me realize that it simplified the cstring part of this. Thank you for the reminder.

Plus, you’ve dropped the space from before the AND again

All just part of the confusion. I did notice that after I posted.

naming is also extremely confusing, and it’s one of the reasons I don’t like naming conventions applied at the field level. As I understand it, you’ve changed your field str_Num from text to an integer but the field name still suggests that it’s text.

Interesting note, in this case Str_Num is not string, but “Strand Number”–I just like really short field names. Since there are engineers here, I can’t use Std_Num, so I guess I will call it S_Num. The field is now Integer (it will never get past 60, or there is no shippable product), but I am guilty of calling the form control txtStr_num still. It is clear now that it would be beneficial to change all that now.

The concatenation is being used solely on the form for the operator’s viewing the new ID nomenclature. It is a combo of fields already collected elsewhere, by incrementing this field, the new ID can be handled with no operator error/input. I suspect that the counter you have outlined plants this as a 2 character “number”, thus formatting it to keep the leading 0 may not be necessary.

I did do the concatenation in the control source property, as it is just for display. Are you suggesting I do it from code on the after insert or update of a certain field, or as an extension of the code snippet? There is one field that is part of that concatenation that may have to be changed, that is why I did it in the control source property.

One thing I am not clear on is whether I should keep the Strand number I am incrementing as an Integer field or is intNum holding it in memory as an Integer and the CStr function is converting it back to text? Should I keep it Integer in the table? It would be nice to keep it a text field.

It may be helpful to understand what is going on here. This particular project was using a database that, to put it kindly, didn’t meet the needs of the manufacturing or testing process. A critical part of the process needed something that worked. IT was asked to provide something, but be able to feed the data back to the partially working database, with all its abnormalized tables and other joys, by no choice of my own.

We are automating most of the instrumentation data collection using a specialty program that reads the instruments’ output, and on operator input sends the accepted data to the Access database. I have had to build a form composed of multiple subforms, which grows as it progresses. The second phase needs to see certain data from the previous operation, and the next needs to see the previous step, as well as the following process.

At step 3, it takes on a completely new identity using this concatenated value, and from herein, that is its ID. (Testing and manufacture, BTW, is back and forth, with Step 4 only linking to step 3 on one field, which doesn’t exist in 1 or 2, but can be traced back by the link to step 3.) There may be several step 2’s to the one step 1, and multiple step 3 for that, and an even more incongruous number of step 4 processes to step 3.

In addition, failures need to be recorded and sent to an archive table for tracability, yet it retains its recently assigned ID and the field that links step 3 and 4. There is also the need to navigate back to it and retain the fact it has been flagged for archive, yet all the data (except the ID and link) has been deleted or overwritten after appending, so that a single final record will exist in the current table. The appending is just for the records, yet the final record needs to be notated that it was reworked.

I have found it easy to get distracted by the many flags to set and hidden fields to use, and incrementers to use to accomplish this and be able to speak to the automation software. Add to this QA wanting to make sure their needs are met — but of course, they don’t find anything out or get back to us until after parts are done.

And for that reason, I really appreciate all the assistance you and the others have provided.

‘dave

Reply | Quote

To most VB/VBA programmers, str is a prefix representing “string”. It’s perfectly valid to use it otherwise, but it is confusing when someone else is trying to figure out your code–something to keep in mind in case anyone else ever has to maintain what you’re building.

I make it a rule to store things that will be calculated (i.e., incremented or used in numeric formulas or operations) as numbers. I store things like social security numbers and phone numbers as text because they are not used in calculations. VBA is not very tolerant of trying to perform calculations on strings, and that approach helps keep me honest. If a value is only going to be used to create an incremental number within a string, I would store it as a number. Otherwise, you have to convert it to a value to get the next number and then convert it back to a string to store it. To me, it’s simpler to simply increment it and then format is as a string to concatenate it to your string key.

Reply | Quote

I realize that str is typical for String, I just didn’t pay attention to it until you pointed it out. I may have caught it by the time I started getting into this deeply; right now, I have gotten the one table they showed me broken into 5 (with 3 fields that are in other tables — without a form!!), and queried the parties that may be able to use the data we are collecting and have found that there is a lot they haven’t collected. As they decide they want it, yesterday’s work is pretty much wasted. Its a challenge, but in the end, it will reduce error collection and streamline the process.

I make it a rule to store things that will be calculated (i.e., incremented or used in numeric formulas or operations) as numbers..VBA is not very tolerant of trying to perform calculations on strings

If I understand this correctly, the code snippet you posted is assuming Str_Num (now called “S_Num”) is stored as a text field or Integer? It seems that the “intNum=Nz(DMax(“Str_Num”,tbl…),0)” is converting it to a number for the sake of incrementing, and “txtStr_Num = intNum+1” is actually incrementing it; then it tests for <10 to see if it should add the leading 0, and on the Else statement “strNum = CStr(txtStr_Num) seems to be converting it to text for storage? Or am I reading it wrong? Either way, if the leading 0 gets stored, I won’t need the Format function in my concatenation (?)

I am still curious as to what you were thinking about as an alternative to putting the concatenation into the property sheet’s controlsource? They do need to see the ID string fairly early in the process, by the time the setup phase is done and the first test series begins — and there is the chance that one field may change during step 3 or 4.

Thank you so much for the feedback and advice — and for waking me up on my field naming.

‘dave

Reply | Quote

I know that some experts endorse “light-weight” forms, but I don’t really like building control sources that way unless all I’m doing is referencing something for display. What I posted wasn’t intended to be *the* way to do it, it was just intended to clarify what you appeared to be doing.

Except in Visual Basic, where it isn’t customary, I don’t hang field or control names out there without square brackets around them to announce to the Jet engine that they’re fields or controls rather than variables. I couldn’t tell if StrNum was a control or a variable, and I assumed it was a variable.

I frankly don’t care which way you store it. I’ve given you my reasons for handling things a certain way. What you do is up to you.

Reply | Quote

Charlotte,

Your help has gotten me over the hump — all is working well. As there is another counter in this form (Just a “Dmax(field, table)+1” deal), it is now just a matter of what event triggers each, which depends more on the interaction of manufacturing process and data collection. Your advice helped me so much, I was curious as to your comment on the concatenation not being on the controlsource; always eager to learn alternate ways from the masters

Your assumptions of the field names was accurate. I admit to the confusion. I was just curious as to what the code was doing with the integer and storage (intNum for the increment, strNum for the storage). Str_Num was (I did change it) the field name, strNum as in the code (As String) was a variable. It appears I may have been getting confused by the naming as well.

Again, thank you for the help

‘dave

Reply | Quote

I tend to avoid putting complicated expressions like that into control sources, and that doesn’t work when you want to actually save the value to the field anyhow. I’ m more likely to put all of that into a code routine and then simply populate the bound control from code.

Reply | Quote

I appreciate what you are saying, and I agree. This is just for display, and serves no other purpose other than as a bridge from their old way to this one. That expression was developed more for my ability to export the various fields as a single one to another operation, covered by a different database! (What I am working on is an interim piece to automate some test data and reporting.

After this stage of process, the component takes on a completely different identity scheme. This is kind of a bridge, but it also serves as the link to its old identity.

Had I stored it, Mark Liquorman would have reported me to the Normalization police

Thanks again for all your input.

‘dave

Reply | Quote

Hi dave,

————————-
I should mention that if I manually place in the table, an 01, it works through 10, but if I use 10, it seems to advance to 11,12, and so on;
————————

I think the real problem is the field being a text and not a number. In that case, the sorting – and thus DMax – will be different, it’s good ol’ ASCII sorting and not the normal numeric. I’ll try to illustrate this:

Say you have in the table the values (inserted no matter how):
1
2
10

Normally, you’d expect DMax brings up 10, but this won’t happen I suppose. As sorting/comparing for text is done character by character, the biggest value in this case will be 2, because it’s greater than the first character in ’10’! The sorting would be OK if indeed the content would always have those leading zeros.

I’ll say you should change the datatype of the field, otherwise the DMax won’t work correctly no matter what you’re doing. Or you’ll have to do such way too much work just to make such simple things like incrementing work.
Then you can format the content to show 2 (or more) digits everywhere on the UI, i.e. controls on forms, reports etc., even in the table and all kind of DMax, incrementing etc. will work correctly.

BTW, is 99 the biggest possible value for that field?

Reply | Quote

Emilia,

Interesting prognosis regarding the idea of DMax treating this as a sort — which may explain why it cannot get past 10, yet if I start at 10, it works? Also, your other post, theorizing that the Nz wrapper might cause DMax to concatenate 1 to the value, thus trying to get 3 characters into a 2 char field, is interesting, but it may seem then, that since the code almost works (the getting past 10 being the exception) perfectly, it would seem to be an issue with the syntax of wrapping DMax in the Nz function.

Converting data type to number — if I had that privilege — would not hold the leading 0, and my FORMAT trick in the concatenation may not work. That leading 0 is part of a serial number, so it is critical. Thus, the need (or attempt) to use) for text field.

Your post reminded me that in my testing, if I manually plant 09 in the field, it will increment past 10,just as it does with any other 2 character “number”. This leads me to think that despite having found a way to get the leading 0 into the concatenation for display and reporting, I need to get it to store, in order for it to increment.

Another part of this thread, the most recent post in an exchange with Mark, I have been running the idea of using the default value property with the FORMAT function to do just that. Feel free to join in

Your input is much appreciated in attempting to meet this challenge.

‘dave

Reply | Quote

dave,

DMax and sorting are both doing the same thing: comparing the values with each other to find out which one is greater. I should have written ‘compare’ instead of ‘sort’

As for formatting a real number field with leading zeros, this is no problem, the Format function works (see uder defined number formats in the Help). I also have in a database an AutoKey field and a normal integer field formatted with leading zeros in the table: the AutoKey with ‘00000’ and the number with ’00’ (these are magazine issue numbers per year), and they all show up everywhere with leading zeros, including datasheet view of the table itself. So the concatenating is also no problem.

In my opinion, this way around is easier, more flexible and reliable then the “make a text behave like a number” way.

To find out exactly what causes the error message needs careful testing I think, maybe it’s something one wouldn’t even imagine (not so uncommon with Microsoft ). Maybe I’ll take some time and play around with it.

The default ’01’ (or 1) is good, you could use this anyway because it saves you the trouble (and programming) for a new record in the group.

Reply | Quote

Symantics aside, when you mentioned the analogy to sorting, it started to make sense. I have since changed the field to Integer and format property in the table to 00. When I input a 01 it sticks, however, I have made some kind of error or overlooked something in all my trials and tribulations with this, and the counter will no longer work at all!

I posted the details in a response to Charlotte on this. My code is in there; it is attached to the AfterInsert event of the form. I had no luck with using the default value property set to 01 or 00 — it did ok for the first record, but set it for the default value, and then never advanced. I think I have to do it with Nz.

That oddball error message went away when I applied Charlotte’s code sample for Format with Nz, and changing the incrementing field to Integer. I still am curious why I got it.

Have a great weekend, Thanks for the input and feedback

‘dave

Reply | Quote

Hi dave,

—————————
Run Time error ‘-2147352567 (800200009)
The field is too small to accept the amount of data you

which made absolutely no sense to me. All I was trying to do was convert any empty space to “0”. The field size is 2, as it was earlier. I have posted the code below.
—————————

Well… at first glance, it doesn’t make sense indeed. I don’t know, but I just took a look in Access Help for the + operator. There is something like this (I have to translate from german):

“If you use the + operator, you cannot always define if it’s going to be an addition or a concatenation.”
And there is a list of various combinations of the operands.

Could it just be, that DMax brings up some 2 character text (because your field is in reality this and not a number) which then gets _concatenated_ with 1? Which would return a 3 character text, like 011 (for ’01’+1) or 101 (for ’10’+1)?

I agree this sounds a bit fantastic and it has nothing to do with Nz, but trying to make a text work like a number can be fantastic.

Reply | Quote

Try the following:

(DMax(“Str_pc”,”tblMaxCounter”)+1)

replacing

DMax(“Str_pc”,”tblMaxCounter”)+1

I think that should stop access complaining at that point. However I don’t understand what you are trying to do with
Forms!frmTestCounter!txtBill_Num AND
& Forms!frmTestCounter!txtBill_Half

Reply | Quote

I guess I need to explain this a bit better. BTW, doing the formula part of it didn’t seem to give me problems, adding the criteria did.

Forms!frmTestCounter!txtBill_Num AND _
& Forms!frmTestCounter!txtBill_Half

is the criteria that I want DMax to increment by.

They represent the one side of a one to VERY many relationship; consider them the master piece. This so called master piece is used in a final ID that is concatenated from a Type Code (02), Contractor code (G), the Bill_Num field (i.e., 43213), Bill_Half field (A or , and the incremented number I want to automate (“str_pc”), plus another static field (S). The fields “Bill_Num” and “Bill_half” come from another operation and are carried over to this one. This operation is the one providing the “str_pc” value, which is a sequential number within the “master piece” grouping. This value needs to be incremented by one, but only by its grouping.

The following might be a row representing numerous “str_pc” values for a few groupings:

Bill_Num  Bill_half  str_pc[/u]
43213           A           05
43213           B           02
25484           A           04
25847           B           02

Now if they are working on 43213 A, the 05 needs to be incremented to 06, but if it is 43212 B, then the counter needs to be incremented to 03, and so on. However, if the “Bill_Num” and “Bill_half” fields are “new”, the first record for that grouping would be 01 — and so forth.

I was under the understanding that DMax could use criteria as does DLookup, but I seem to have a problem with it working with the +1 part of the formula. Can I make the criteria a variable, ie, Dim it as strCriteria, then define it, as I also need to increment another field based on the same criteria. That is where I need badly.

I hope I clarified what I am trying to do.

Thanks for the feedback

‘dave

Reply | Quote