checking existence of records

[akbigcat86]

I am making a database that organizes several hundred CAD drawing filenames. A lot of files have already been named with consecutive numbers, but there are a few gaps of open numbers that I would like the database to fill in first when adding a new drawing file. The format of the filenames is AF####_100_X0. I tried to write some code that goes to the first filename in the table which is AF0000_100_X0 and pulls out the #'s. It then adds 1 to the number and creates a new filename from it AF0001_100_X0. I am having trouble figuring out how to check if that filename is taken or still available. If it is taken, the code has to loop and add 1 again to check for the next file number. If it is available, I want it outputed to a text box on a form. Here is what I have so far:


Dim rst As dao.Recordset
Dim rec As String
Dim nextrec As String
Dim tempstr As String
Dim strcriteria As String
Dim drawnum As Integer
Dim nextdrawnum As Integer
Dim af As String
Dim test As String
Dim lead As String
Dim newfile As Variant

af = "AF"
test = "_100_X0"
Set rst = CurrentDb.OpenRecordset("AirFrame Files")
rst.MoveFirst
'Do
rec = rst![Drawing#].Value
drawnum = Mid(rec, 3, 4)
rst.MoveNext
nextdrawnum = drawnum + 1
If nextdrawnum < 10 Then
lead = "000"
ElseIf nextdrawnum < 100 And nextdrawnum > 9 Then
lead = "00"
ElseIf nextdrawnum < 1000 And nextdrawnum > 99 Then
lead = "0"
ElseIf nextdrawnum < 10000 And nextdrawnum > 999 Then
lead = ""
End If
Str (nextdrawnum)


strcriteria = "[Drawing#]= '" & af & lead & nextdrawnum & test & "'"
newfile = DLookup([Drawing#], "AirFrame Files", "[Drawing#]= '" & af & lead & nextdrawnum & test & "'")

'Loop Until rst.EOF


The loop is commented out because I haven't gotten past the dlookup bugs yet. What is the best way to go about doing this?

Thanks in advance

[pbaldy]

For starters, you don't need all that If/Then code to determine the leading zeros. You can use the Format() function to pad a number with leading zeros:

Format(nextdrawnum, "0000")

Rather than DLookup I'd use DCount (with the DLookup you'd have to test for Null). See if this works:

If DCount("*", "[AirFrame Files]", strcriteria) = 0 Then
  'this number is not in the table, do what you want with it
End If

Your DLookup did not have the field name in quotes, which would have caused a problem.

[akbigcat86]

First off, I tried the format function, but I got an error message saying there should be an = sign. I then made the variable equal to the format function, but it didn't add any zeros on the output. Am I doing this wrong?

I added in the Dcount code and it works perfectly. The code is much simpler than I thought it would be.

Thank you very much for your help.

[pbaldy]

Glad it worked for you. Hard to say what you did wrong without seeing what you did. If a variable didn't hold the zeros, my guess would be that it was declared with a numeric data type (like nextdrawnum in your code). To hold the zeros it would have to be a String. You could have used the Format function like this (and eliminated all the code related to lead):

strcriteria = "[Drawing#]= '" & af & Format(nextdrawnum, "0000") & test & "'"

[akbigcat86]

yup that was the problem. Defining the format after the variable becomes a string made it work.

Thank you very much ... it could have been a long day

[RuralGuy]

Just a post thought here but it seems as though a .FindFirst on the RecordSetClone would be quite a bit faster than opening a new recordset with the DCount(). Just my $0.02.

[pbaldy]

That 2 cents is worth at least a nickel.

That makes sense Allan. I didn't really think it out beyond solving the immediate problem.