incrementing a reference number

[jordanh]

I have a query that picks id fields from three tables (named 'scope', 'categories' and 'title') and creates a ref number from them (the numbers represent system ids, and they're not primary keys or autogenerated numbers).

The query in question uses a simple module to build the three numbers into a unique id such as 1.1.2 or 1.2.1 etc. Very simple so far.

My problem arises that it's possible to have more than one record in the query with the same 'built' id, so a fourth number is required that must increment if the three numbers are the same.

I can't find a way to do this simply -- can anybody help me?

Thanks in advance you wonderful, wonderful people....

Jordan

 

[Remou]

Hi
Here is one idea, I am sure there are better

Dim i
MyKey = "2.1"
MyKeyX = MyKey

Do While DLookup("Field2", "table1", "Field2 = '" & MyKeyX & "'") <> ""
    MyKeyX = MyKey & "." & i
    i = i + 1
Loop
MyKey = MyKeyX

 

[jordanh]

I see where you're coming from, but I keep getting an Error 3014: cannot open any more tables.

The problem (I think) is that 'table1' in your function is actually a query, not a table, and it's the query that I'm builing as the function's running. If you know what I mean.

 

[Remou]

Ah. Sorry. I thought you were storing the number in a table, once the query had run. Here is a thread that might help: Numbering in Access thread701-909927

 

[jordanh]

Thanks for the help.

I've resorted to setting the number on creation of a record and actually storing it in the table. Not elegant and a little wasteful but it's the simplest solution.

On creation (and update of related fields) I now just recreate the first three digits from the selected fields, and then do a DCount to get the number of records with the ref number field "Like 'n.n.n*'", add one to the result of the DCount and tack it on to the end.

It works fine, but I'm still perturbed that there isn't a simple way of incrementing a number without resorting to auto-increment fields.

Thanks again.

Jordan