Tricky autonumber one.

[boblarson]

(im thinkng putting an if funcion which only runs the dmax code if the value is null)

that's what I would do

 

[Jimmythemadman]

ok ive got it all working now
here is the code what i ended up with
----------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.hardwareid = 0 Then
Me.hardwareid = DMax("[hardwareid]", "tblhardware") + 1
Else
End If

End Sub
-----------------------------------------------------------
big thank to everyone especialy you mr larson.
this will help with other parts of the database.

now all i have to do is explain it to my computing teacher who had to admit defeat in the face of this problem.

exelent results considering i only became a member 4 hours ago.

 

[dfenton]

If you just want a unique number (and aren't concerned about consecutive numbers) then you can, of course, use an autonumber to generate the number if that is easier for you. Just don't set it to random and don't use replication as it will also generate negative numbers.

A trick that I sometimes use is to use random Autonumbers, with the negative numbers, and then only display it to users as Hex(). This results in nice non-negative numbers that look random. The mix of numbers and letters makes them more compact and also makes them easier for a human being to perceive than a long string of digits.

For lookup, though, you'd have to do a Hex() to standard numeric conversion, but all that could be done behind the scenes so the user would never know.

--
David W. Fenton
David Fenton Associates
http://dfenton.com/DFA/

 

[WayneRyan]

Jimmy,

Try:

hardwareid = Nz(DMax("[hardwareid]", "tblhardware"), 0) + 1

And to add your "Prefix":

hardwareid = Nz(DMax("[hardwareid]", "tblhardware", "[Prefix] = '" & Me.Prefix & "'"), 0) + 1

Wayne

 

[The_Doc_Man]

Omit the 0 in the DMax arguments. You probably don't get back anything useful, or you always get back a null, right?

Jimmy, here's the straight skivvy. THERE IS NO SINGLE FIELD that does what you said you wanted to do. A field with a number, in order to increment, has to be numeric. A field with a text prefix cannot be incremented because it isn't a number. So CPU000001 (3 x text, rest numeric) cannot exist as a field. But... with an appropriate format statement for the numeric part, and the & operator, you can BUILD that identifier out of two parts. And the two parts can also participate in a compound key.

When you ask for a text ID that increments, that creature is not part of the Access menagerie.

You say you know about VBA at least somewhat.

Suppose you have this VBA declaration:

Dim PartNum as String

PartNum = "CPU00001"

PartNum = PartNum + 1

What happens? You get a mixed mode error. Why? 'cause strings and numbers don't have the same properties.

Well, asking for a single field to have those properties is the same thing. That dog just won't hunt.

Rule of thumb: If you can't do it to a variable in VBA, you probably can't do it to a field using SQL techniques either. (Some exceptions exist, since VBA has a pointer type variable.)