Hello,

I tried search it but i cannot find such a thing. Let me explane myself:

I have a Table A and Table B

Table A is filled with "requests"
Table B is filled with "requests" that have been approved

When an item from Table is approved it goes from table A to B. When you push the button_approve the data goes from A to B and then Deleted from A.

Now I need an automatic name creater. This means that if someone selects the form "F_Request" from the dashboard that the form field [Issues].[reference] (= primary key) automaticly creates PRO0001 etc etc ... but ... The form has to go and look in Table B [Issues].[Reference] for the current number and then put in Table B [Request].[Reference] Number + 1... This is because the records in Table B gets deleted and it starts from PRO0001 all over again and that is not correct.

I hope all is clear and you understand what I want. Maybe you can give me the function name or a link to page where something like this is explained, or explain it here.

Thank you in advance.
Bjorn

Hi,

   This is easy.

SELECT TOP 1 TableB.FieldID
FROM TableB
ORDER BY TableB.FieldID DESC

   This will always return the highest value in the column. Then add 1 to it.

Did that help?



mmcdonal

Sorry, you can also use MAX


mmcdonal

Thank you very much,, it does the trick

It did not exactly what I wanted so I changed the approach and did this.... Now it works great: Maybe someone can benefit from it aswell..

Dim A, B, C
        A = DMax("[ID]", "Issues")
        B = DMax("[ID]", "T_Request")
        If IsNull(A) Then A = 0
        If IsNull(B) Then B = 0
    If A > B Then
        C = A + 1
    Else
        C = B + 1
    End If
  Me.ID = C