Help With Loop

[AlanW]

I am trying to write some code that will search my database and generate the next available file number when I click a button on my form.

Table = Client Details; Field = File Number

Example

File Number
1000
1001
1002
1004

I am trying to generate File Number 1003. I’ve tried different loops but can’t get anything to work. Any help appreciated

 

[Shep]

Alan,
Where does the data for your File Number field come from originally?

 

[AlanW]

The data was originally entered into the table from a spreadsheet.

 

[Shep]

Thank you. I was wondering if the field's data source was an autonumber.

Well, I have no suggestion for you at the moment, but I will try to help you write a bit of code. Someone else may come up with something sooner...I expect to be able to give it a try within the next several hours (working right now).

What if the range of File Numbers is already full?
May I assume that if:

1000
1001
1002
1003
1004

are already used, for example, that you would like for the next File Number to be 1005?

Shep

 

[RV]

First an advice:

don't use spaces in tablenames, column names et cetera (object names).

Second, try this for instance:

SELECT ClientDetail.FileNumber, MAX(ClientDetail1.FileNumber)+1
FROM ClientDetail, ClientDetail AS ClientDetail1
WHERE ClientDetail1.FileNumber<ClientDetail.FileNumber
AND ClientDetail.FileNumber-1>
(SELECT MAX(ClientDetail1.FileNumber)
FROM ClientDetail AS ClientDetail1
WHERE ClientDetail.FileNumber>ClientDetail1.FileNumber)
GROUP BY ClientDetail.FileNumber;

HTH,

RV

 

[AlanW]

Thanks RV but it’s a bit out of my league

I’m trying to do something like this but it doesn't work. Can anyone help?

Private Sub Command43_Click()

Dim Check, Counter
Check = True: Counter = 2000

Do
If Counter = (DLookup("[File Number]", "ClientList", "[File Number] = Counter")) Then
Counter = Counter + 1
Check = True
Else
Check = False
Me.File_Number = Counter
End If
Loop Until Check = False
Exit sub

 

[RV]

Let me try to explain my suggestion.
You can cteate a query (SQL statement) which retrieves the first available FileNumber.

Your code suggests you're using a form, right?
OK, then you could use this query as the control source for a textbox on your form

I adaptad the query so it will allways retrieve the first available FileNumber:

SELECT MIN(ClientList.FileNumber)
FROM ClientList
WHERE NOT EXISTS
(SELECT FileNumber
FROM ClientList AS ClientList1
WHERE ClientList1.FileNumber=ClientList.FileNumber+1);

You could "run" this query through VBA code, that's however a bit out of my league....

HTH,

RV

 

[John.Woody]

Unless your running a large network with lots of people creating records at the same time, try the following:

Create the following in a module and call it on the form at the correct time.

Public Sub NextFileNo()

If Forms!F_ClientDetails!FileNo > 0 Then Exit Sub

If DMax("FileNo", "ClientDetails") = 0 Or IsNull(DMax("FileNo", "ClientDetails")) Then
Forms!F_ClientDetails!FileNo = 1
Else
Forms!F_ClientDetails!FileNo = DMax("FileNo", "ClientDetails") + 1
End If

End Sub