VBA to loop and increment if variable does not match data in field

[newbieX]

I am trying to write a script that look through a table, counts the
number of records that meet meet a given criteria. The field ID_NO
in the Customers table is the primary key. It is a concatenation of
the Customer code, a hypen, and a number which is incremented each
time a new record is added. Once in awhile a record might be deleted
so I have to check if the ID_NO exists before assigning it a value.
I am trying to automate this process.

So I am thinking that I need to create a script that counts the number
of records in my Customer table that match the CustCode entered in my
form, increment by 1, create the concatenated variable, and loop through
the table to see if the variable matches the ID_NO. If it does, increment
variable by one and loop through table again. If not, make the text box
ID_NO in my form (which is bound to the Customer table) equal to the
variable.

count = DCount("CustCode", "Customers", "CustCode = '" & Me!CustCode & "'")
    
'Loop starts here I think
Do while cnt_ID <> Tables!Customers!ID_NO
    count = count + 1
    'creates variable, cnt_ID, that is CustCode field concatenated with a 
    'hypen and the count number
    cnt_ID = CustCode & "-" & count
    'checks to see if the cnt_ID exists in the table
End Do while

'When finds unique ID, assigns variable to Me!ID_NO text box

cnt_ID = Me!ID_NO

End Loop

Thus if my data looks like this

ID_NO   CustCode
ABCD-1  ABCD
ABCD-2  ABCD
ABCD-4  ABCD
ABCD-5  ABCD
EFGH-1  EFGH

and the user enters ABCD as the CustCode in my form, the script
would eventually assign my text box ID_NO the value ABCD-6. (I don't
want it to generate ABCD-3 as this record may exist in historical
table.

I'm not sure how to set this all up. Ideas anyone?

[orange]

You're asking for ideas --so here goes.
In my view you are making life difficult for yourself. A fundamental point with data base --1 field 1 fact - Use atomic data. Said differently, don't use concocted fields that involve some sort of combination. For example, You have CustomerCode ABCD; and you have ID_NO ABCD_1.
Why wouldn't/couldn't/shouldn't you have ID_NO 1 What's the importance of the ABCD? If that is suppose to repesent CustomerCode, you already have a separate field called CustomerCode with value ABCD.

You can run queries against your table to get a count of the various CustomerCodes.

SELECT CustomerCode, Count(CustomerCode) from MyTable
GROUP BY CustomerCode

see http://www.w3schools.com/sql/sql_groupby.asp for additional info and SQL statements.

I recommend this article for Database Design Principles

Do you have a clear statement of WHAT your database is about? Tables and relationships?
Getting your tables and relationships designed to support your "business" is critical to a successful database.

Good luck.

[newbieX]

The coding system is not mine and is not changeable. I am merely trying to design a data entry form and automate the process of assigning these ID numbers. The Customers table is a child table with the CustCode as the linking field. The ID_NO is the unique ID for the Customer table. The ID_NO identifies the unique number of franchises the customer has. I think the design of my database is fine. I just don't know how to code the automation of the assignment of their coding system.

As with so many of the projects I work on, the data collection and coding assignments have been in place for a long time. They are institutionalized and inflexible. I just try to do the best I can with the mess I am given. I am aware of what the database design principles are, however, implementing them can be nearly impossible for some things when inheriting data (and the process for collecting that data) that is beyond my control for changing them. Bureaucratic bigwigs, often with data control issues and loads of red tape, control this and I am just a little bottom feeder trying to implement it. As with so many things, theory gets tossed out the window when reality comes-a-knockin.

[rpeare]

you don't need to cycle through all the records and count them to do what you're asking. If you are stuck with this system of number things I would suggest using the DMAX function
secondly, don't delete your data, you can simply add a 'active/inactive' flag and show/report only the items that are active. This prevents you from losing historical data and possibly orphaning data.

tblTest
ID_NO   CustCode 
ABCD-1  ABCD 
ABCD-2  ABCD 
ABCD-4  ABCD 
ABCD-5  ABCD 
EFGH-1  EFGH

So let's say this is your table.
I am assuming you have a form where you are selecting the customer so you can recall that value, let's call that form frmTest, and on that form there is a field [CustCode]

you could have an 'add new' button that would have code like:

dim sMaxID 

sMaxID = dmax("[ID_NO]", "tblTest", "[CustCode] = '" & forms!frmTest!CustCode & "'")
'If you have customer ABCD chosen you will get the value ABCD-5 with this dmax statement
sMaxID = left([sMaxID], instr(sMaxID,"-")) & Right([sMaxID], len([sMaxID])-(instrev([sMaxID], "-") +1))

then you can populate the 'new' record with the 'new' sMaxID, just make sure you debug.print smaxID while you test your code.

[newbieX]

Rewrote last line to the following as it complained about instrev. Figured you mean InStrRev;

sMaxID = Left([sMaxID], InStr(sMaxID, "-")) & Right([sMaxID], Len([sMaxID]) - (InStrRev([sMaxID], "-") + 1))

I put Debug.Print sMaxID after both sMaxID = statements and got the following results:

ABCD-5
ABCD-

Doesn't seem to be adding the incrementing number to the end.

[rpeare]

You have to mess around with the places, I didn't test this

sMaxID = Left([sMaxID], InStr(sMaxID, "-")) & clng(Right([sMaxID], Len([sMaxID]) - (InStrRev([sMaxID], "-"))) + 1

[newbieX]

DMax does not seem to find the highest number after 9. e.g. 1,2,3,4,5,6,7,8,9,10
returns 9 because it really sorts 1,10,2,3,4,5,6,7,8,9. Been searching the internet as to how to sort numerically but still haven't found an answer other than pad the 0's which is not an option. Otherwise coding above works except need 4 ")" at the end of the statement not 3.

[June7]

The field is text type and therefore alpha sort applies. Can use Val() function to convert to number value. Be aware Val() will error on Null, as will any of the conversion functions.

If you want to correctly sort ABC-1, ABC-9, ABC-20, then need placeholder 0's (ABC-0001, ABC-0009, ABC-0020) or extract the number part and sort on that.

[rpeare]

Other than june's suggestion there is another way to do this.

Create a query

SELECT TOP 1 CustCode, clng(Right([ID_NO], len([ID_NO]) - instrev([ID_NO], "-"))) AS SeqNum WHERE CustCode = [Forms]![FormName]![CustCode] ORDER BY clng(Right([ID_NO], len([ID_NO]) - instrev([ID_NO], "-"))) DESC

This should give you a query (didn't check the syntax so I may be off a bracket) that gives you only one result, that being the most recent 'number' part of the ID_NO then you can change your dlookup to look at this query instead of the table and just add 1 to the maximum.