Stuck trying to generate Uniqe string where the result is saved

[jameshutchinson008]

Basically I have created a hardware/software database that is based around the network ID on our computers (300+) At the start of time BM (Before Me) the network ID was generated by looking in server manager for a free number. As time went on and people got lazy the server manager aspect was forgotten. So as not to have clashes in computer names the kind people who set up the computer have inadvertantly created large gaps in our numbering system. (IE to be safe lets choose computer number 1256235 when we were actually on number 50

To fill in the gaps I am trying to automatically generate a script in VB that creates a string, compares is to the comp_num field, if the value exists the code counts in increments of one until the string doesnt match then the string is stored in comp_num field.

I have very little experience in VB but this is my best shot.

The script (following) actually generates the computer number and I can save the result into the correct field from an input form. The only thing I can't figure out is how to get the code to compare the outcome to the correct field!

Private Sub Label35_Click()
continue = True
Counts = 1

While continue
    stringkeynumber = Right("00" + (Counts), 2)
    stringkey = "21-" + comptype + "-" + stringkeynumber + division + location
        If [comp_num] = stringkey Then
        Counts = Counts + 1
        Else
    comp_num = stringkey
    docmd.Save
    continue = False
End If:
continue = False
Wend
End Sub

Any help will be muchly appreciated, (its giving me a headache!!)

 

[mac318]

James, I would take all sorts and filters off the table which would leave the table in the order in which the records were entered. Then I would create an autonumber field which would sort the records into the order in which they were created and would continue to do so as each new record is added.

mac

 

[930driver]

I think the following will work for you. It opens a recordset, looks for an existing record. If it doesn't find it it lets you use the one you've just tried. Else it loops and tries again.

This will work OK for a small database. If you start to get into 10's of thousands of records you'd want to consider ways of speeding this up.

By the way, in your line:

stringkey = "21-" + comptype + "-" + stringkeynumber + division + location

it looks like you're using the '+' operand for both string concatenation and addition. This could cause unexpected (read that undesired) results. Use '&' for string concatenation and '+' for addition.

Also, there will be line wraps! Make sure they are cleaned up before you run or compile.



Private Sub Label35_Click()

Dim Counts As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb 'set pointer to this database
Set rs = db.OpenRecordset("Tbl_comp_num&quot 'the table that has the field 'comp_num'
'change "Tbl_comp_num" to the actual name of the table!
Counts = 1

Do
stringkeynumber = Right("00" + (Counts), 2)
stringkey = "21-" + comptype + "-" + stringkeynumber + division + location
rs.FindFirst "[comp_num] = " & stringkey
If rs.NoMatch Then
'the stringkey has not been used
comp_num = stringkey
Exit Do
Else
'it has been used, try again
End If
Loop

'clean up
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[jameshutchinson008]

[930driver]

This is the code I was looking for!! unfortunately it doesnt seem to work in vis basic 6.3 (rs.NoMatch) didnt seem to work. (I am writing this database using Access 2002 BTW.)

At the start dim db As Database didn't seem to work either!!



Dont suppose you could shed a little light could you ?

 

[930driver]

Open a code window. Open Tools:References. Set a reference to Microsoft DAO 3.6 Object Library. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[jameshutchinson008]

[930 Driver]
I think I am out of my depth to be honest mate - if the network had gone down - no probs If VB script aint working unfortunately I am up sh*t creak without a paddle.

Can I ask you a really BIG favour? If you email me at james.hutchinson@wyg.com and I reply attatching the database could you take a look for me PRETTY please? (This is really starting to jerk my turkey)