update query and autonumber

[banza]

Hello access friends,
the following update query UPDATE Screen SET Screen.ScreenID = [CarModel] & "-" & Left$([Category],1) & Format(100,"000"); gives me duplicate values. How to avoid this and increment the next number?
I have attached the database to show what I wanted to achieve in the form.
with many thanks for your help.

 

[WayneRyan]

banza,

UPDATE Screen SET Screen.ScreenID = [CarModel] & "-" & Left$([Category],1) & Format(100,"000");

The above will set EVERY row of your table! You're bound to get duplicates.

Keep your [CarModel] column.
Keep your [Category] column.
Add a new [Sequence] column.

Don't keep a table field named ScreenID (it can be calculated).

Then on your input form, use the BeforeInsert event:

Me.Sequence = Nz(DMax("[Sequence]", _
                      "[Screen]", 
                      "[CarModel] = '" & Me.CarModel & "' And " & _
                      "[Category] = '" & Me.Category & "'"), 0) + 1

Me.ScreenID = Me.CarModel & "-" & Left(Me.Category, 1) & Format(Me.Sequence, "000")

Store the parts seperately, it makes it handy for things like searching & sorting.
When you display them, like for Me.ScreenID above, it just takes one line and/or
one column in a query.

Wayne

 

[RuralGuy]

The use of "Screen" is a poor choice. It is an Access reserved word: http://support.microsoft.com/kb/q286335/

 

[banza]

DMax() and autonumber

Wayne thanks for taking the time to help. I finally got it to work. Don't know why, but I have to use the AfterUpate of the category field

Me.Sequence = Nz(DMax("Sequence", "tblScreen", "CarModel = '" & Me.CarModel & "'  And " & "Category = '" & Me.Category & "'"), 0) + 1

Me.ScreenID = Me.CarModel & "-" & Left(Me.Category, 1) & Format(Me.Sequence, "000")

However why this one put in the control source of ScreenID doesn't seem to work :

[ScreenID] =[CarModel] & "-" & Left$([Category],1) & Format(CStr(Val(Right(Nz(DMax("[ScreenID] ","tblScreen","[ScreenID] Like '*'"),"000"),3))+1),"000")

RuralGuy thanks for pointing out the good practice. I am learning ..... I have given myself three months to complete " Alison Balter's Mastering Access 95 Development ".

 

[banza]

Correct Syntax

Thanks Pat..... I got it. I wanted it just for display.
Once again thank you. Hope to have the opportunity to return the favor.
Whenever you come to Shanghai (China) I will be happy to have a drink with you.

Banza

 

[banza]

Shanghai

Hi Pat
Shanghai is a wonderful city. I loved this city since my very first visit back in 1989.
I believe you'll love it!
Looking forward to c u . Meanwhile I will be around this forum as I have decided to learn this powerfull tool : MS access.