Auto-increment in Query

[terces]

I have a query named Project_Code which have the common fields plus this:
<[Agency] & "-" & [Research] & "-" & Format([Date_Started],"mmddyy") AS Code> to auto-generate a serial number.
What I need is an auto-increment based on the max count of agency field so as to avoid duplicate on the first three fields.

I am new to ms access and i would appreciate if replies would be simple as possible.

I have uploaded the db in case u need a reference.

Thanks

 

[mikebaldam]

Check the MAX function out ...

 

[terces]

indeed a very simple reply. but I wish to know the exact idea.

what I like to achieve in query is:
Agency_Name Agency_Count
Agency1 1
Agency2 1
Agency3 1
Agency1 2
Agency1 3
Agency3 2
Agency5 1
Agency1 4

how to apply the max function if i want the next record to auto-increment from the last count (eg. Agency1 , 5 or Agency3 , 3)

maybe the correct term is how to serialize based on the max count of the grouped field. I hope i won't confuse anybody.

Thanks

 

[RainLover]

terces

Further to the PM that you sent me.

The Demonstration Database of mine that you found is particurally designed for situations where there are multiple users and there is a possibility that two users might attempt to update at exactly the same time. However there is some code contained therein that would be suitable in most cases.

Private Sub txtMyLetter_AfterUpdate()
    If (conHandleErrors) Then On Error GoTo ErrorHandler
    ' If we have some data
    If Len(Me!txtmyLetter) Then
        ' Find the highest number and increase it by ONE.
        Me!myNumber = Nz(DMax("[myNumber]", "tblNumbers", "[myLetter] = '" & Me!txtmyLetter & "'"), 0) + 1
    Else
        MsgBox "A String is Required", vbInformation, "Missing Information"
        Screen.PreviousControl.SetFocus
        Me.Undo
    End If
    ' Added 03/02/2011 by Rainlover.
    Me.txtConcatenated = Me.txtmyLetter & Me.txtmyNumber
    Me.Refresh
ExitProcedure:
    Exit Sub
ErrorHandler:
    DisplayError "txtMyLetter_AfterUpdate", Me.Name
    Resume ExitProcedure
End Sub

This should be done in a Form at the time of entering a new record. This is not for use in a query.

Naturally you will have to change Names to suit your situation.

To do something in a Query you would need to run an update Query.

Hope this helps.

 

[RainLover]

I would also suggest that you replace the Error Handling with your own.

 

[terces]

sorry, but i don't know how to handle errors..and i haven't used update query yet so basically my grasp regarding this is very limited.

 

[RainLover]

Delete the error trapping. We can come back to that later.

As I said you do this in a Form.

My mention of an Update query was to steer you away from them except as a Record Source for the form.

 

[terces]

yea, i did this in the form instead of query before but if i do this in the form...then the concatenated field in the table would be dependent on the other field which would fail the NF1 afaik.

 

[RainLover]

There is a Big difference between Dependant and Utilises.

In other words it does not break any Normalisation Rules.

 

[terces]

is same code to be used if my sourcefield is a combobox?

 

[RainLover]

is same code to be used if my sourcefield is a combobox?

Not sure what a Combo Box has to do with this.

Can you expand on this. I need to know more.