inserimento numero

[sergran]

Hello everyone, I need your help. I anticipate that I have zero programming alas
I find myself having to associate with the given numbers. To give an example in order to be more clear
I have a table containing:
Column "Brand", column "Model" column "version", Column "Numero_Auto"

Ad. es.

Audi A4 1.9TDi 45587
Audi A4 2.0TDi 45587
Audi Q7 3.0 52558
Bmw X3 2.0 75878
What I would like to get and that it varies the data in the column "Numero_Auto," I should get this

Audi A4 1.9TDi 45587 -> AU0001_01
Audi A4 2.0TDi 45587 -> AU0001_02
Audi Q7 3.0 52558 -> AU0002_01
Bmw X3 2.0 75878 -> BM0001_01

In practice, the data that I get is formed: the first two letters of the brand (Au, BM) + a 4 digit number (0001 0002 .... etc etc) + _01 _02 ... etc.. The number _01 _02 ... etc etc, must vary if you have multiple records of the same "Numero_Auto" (45587)


I hope I was clear in explaining
1000 Thanks

[rpeare]

What you're asking to do can be done, however, what are you using that final ID for.

Let's say it's possible that after you determine this ID that you have an audi A4 1.95Di 45587, with your current numbering system anything after 1.9 would have it's number changed. and if you're using the AU0001_01 to link to other tables all the references will be broken.

In other words any other tables that were updated with a 'new' unique identifier would also have to be changed in any other tables in your database.

So what is the goal of having these identifying strings?

[sergran]

Hello, thanks for the reply. In effect this serves to me eprchè each number corresponds to a design that is in turn coupled to a model of car. If the same number is assigned to more than one model of car, I can use the same design and why do I need this program. In practice, the AU0001 eg. will be a drawing and ... 02 _01 etc etc I need to identify the various models it is associated with the design.
1000 Thanks

[rpeare]

That doesn't really answer the question and it's a critical one

Audi A4 1.9TDi 45587
Audi A4 2.0TDi 45587

this is your current list of audi A4's

After your update you want it to look like

Audi A4 1.9TDi 45587 AU0001_01
Audi A4 2.0TDi 45587 AU0001_02

Now what happens if audi puts out another model of car

Audi A4 1.9TDi 45587 AU0001_01
Audi A4 1.95TDi 45587
Audi A4 2.0TDi 45587 AU0001_02

Do you intend the model 1.95 to have the code AU0001_03 or do you intend it to have the code AU0001_02 because that's where it places in an alphabetical list?
if you intend it (model 1.95) to have the code AU0001_02 you have to modify all the models of the same car after 1.95, and that is where the problem lies, particularly if you are referencing another table in your database.

[sergran]

Hello, if you take out a new model Audi (model 1.95) AU0001_03 I match would be fine. In fact I do not need to order the models in alphabetical order
thanks
Hello

[vincent-leeway]

How about adding a alpha numeric column as primary key?

[sergran]

It's right. However I would be using the first two letters of the brand of car (Audi--> Au) or even wanting the full brand name and a number that identifies the drawing conil _ 01 _ 02 ... .... used to indicate more than one template with the same design Thanks 1000

[rpeare]

Below is the code you need. You'll have to substitute your actual field/table names where you see

tblTest
Brand
Model
Version

and you'll also have to have a field named UK (unique key) on your tblTest (or rename it in both code and table) for this to work.

Dim db As Database
Dim rstBrand As Recordset
Dim rstModel As Recordset
Dim rstVersion As Recordset
Dim sSQL As String
Dim sBrand As String
Dim sModel As String
Dim sVersion As String
Dim sUK As String
Dim iMax As Integer
Dim iVersion As Integer

Set db = CurrentDb

sSQL = "SELECT Brand FROM tblTest GROUP BY Brand ORDER BY Brand"
Set rstBrand = db.OpenRecordset(sSQL)
If rstBrand.RecordCount > 0 Then
    Do While rstBrand.EOF <> True
        sBrand = rstBrand.Fields("Brand")
        Debug.Print sBrand
        sSQL = "SELECT Model FROM tblTest WHERE ((Brand) = '" & sBrand & "' AND (UK) Is Null) GROUP BY Model ORDER BY Model"
        Set rstModel = db.OpenRecordset(sSQL)
        If rstModel.RecordCount > 0 Then
            'determines current maximum integer assigned to this brand/model
            If DCount("[UK]", "tblTest", "[Brand] = '" & sBrand & "' AND [Model] = '" & rstModel.Fields("Model") & "'") = 0 Then
                iMax = 1
            Else
                iMax = CInt(Mid(DMax("UK", "tblTest", "[Brand] = '" & sBrand & "' AND [Model] = '" & rstModel.Fields("Model") & "'"), 3, 4)) + 1
            End If
            Do While rstModel.EOF <> True
                sModel = rstModel.Fields("Model")
                Debug.Print "    " & sModel
                
                sSQL = "SELECT Version, UK FROM tblTest WHERE ((Brand) = '" & sBrand & "' AND (Model) = '" & sModel & "' AND (UK) Is Null)"
                Set rstVersion = db.OpenRecordset(sSQL)
                
                If rstVersion.RecordCount <> 0 Then
                    'determines current maximum integer assigned to this version
                    If DCount("[UK]", "tblTest", "[Brand] = '" & sBrand & "' AND [Model] = '" & sModel & "'") = 0 Then
                        iVersion = 1
                    Else
                        iVersion = CInt(Right(DMax("UK", "tblTest", "[Brand] = '" & sBrand & "' AND [Model] = '" & sModel & "'"), 2)) + 1
                    End If
                    Do While rstVersion.EOF <> True
                        sVersion = rstVersion.Fields("Version")
                        Debug.Print "        " & sVersion
                        sUK = UCase(Left(sBrand, 2)) & Right("0000" & iMax, 4) & "_" & Right("00" & iVersion, 2)
                        rstVersion.Edit
                        rstVersion!UK = sUK
                        rstVersion.Update
                        iVersion = iVersion + 1
                        Debug.Print "            " & sUK
                        rstVersion.MoveNext
                    Loop
                End If
                rstVersion.Close
                iMax = iMax + 1
                rstModel.MoveNext
            Loop
        End If
        rstModel.Close
        rstBrand.MoveNext
    Loop
End If
rstBrand.Close
Set db = Nothing

[sergran]

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Sorry ... the last question ... where do I put this code? I created a button on the form and I've copied to [event procedure] the code but it doesn't work. Certainly not to upload it there but I don't understand where ...Thanks again for the code

[rpeare]

Well you didn't tell me you needed the number generated at the time of data entry, which is a different problem entirely, so to avoid that issue. Just do your data entry for your cars without including the UK the code is generating.

Just create a module and put the code I gave you into that module (again, make sure you are modifying all references to tables/fields that actually exist in your database).

When you are done doing your data entry run the module itself.

Any time you add a new car type run the module after the item is added and it will generate a UK for you.

[sergran]

I'm sorry but I don't understand ...
Be patient ... If you alelgo a table you can make me understand how I need to do? Then I designed the table name and fields Double.zip

[rpeare]

I'm not going to look at your database. Here's my sample database.

sergran.zip

the data in the table matches your example data.

Run the module.

Then add new cars/models/versions and run the module again

[sergran]

I don't know how to thank you for the job done!!! If I did only upon one thing ... I realized that if the pattern varies, varies the number UK.
E.g. If I insert "model" A5 and its "Numero_Auto" 52558 and
"Model" A6 and Numero_Auto ", I get" 52558 as Uk AU0001_01 and AU0002_01. To me it would be that if the Numero_Auto is equal, AU0001_01 and AU0001_02 appear
Thanks again so much for the code

[rpeare]

The code is not looking at the field with the 52558 value, look at the code.

It's going by the MODEL not the numero_auto field. If you want the code to use the numero_auto field you have to substitute that in wherever it's currently using MODEL.

[sergran]

I understand ... the problem I have replaced the Model field with the number coampo car trying to figure out where he was going replaced, but I made a mess ..