Evidently we must be screwing up elsewhere. We want to be
able to input the date manually as well. It seems we
should do this: strSQL = strSQL & "(Code, [Name], Date)".
What should be our line after this: strSQL = strSQL
& "(Code, [Name])"
Thanks for your help.
-----Original Message-----
'Name' is a reserved word in Access. These should
normally be enclosed in
square brackets. Perhaps try:
strSQL = strSQL & "(Code, [Name])"
and:
strSQL = strSQL & ",""" & Me![Name] & """);"
HTH,
TC
message
Okay, maybe you can help me here then. I found a way to
do two fields with the primary key set as random. When
I
add a third field though, it gives me a run time error
on
the next to last line.
Private Sub cmdInsert_Click()
Dim db As DAO.Database
Dim strSQL As String
Randomize
Set db = CurrentDb
strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, Name)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd ()
+
10000))
strSQL = strSQL & ",""" & Me!Name & """);"
Debug.Print strSQL
db.Execute strSQL
db.Close
End Sub
-----Original Message-----
message
Yes, what we are attempting is basically a five
character
ID, 10000 - 99999, that will appear to be random.
That
way our staff doesn't catch on to any patterns and
attempt to use false approval codes.
Except for the non-reuse restriction, you could use a
VBA function like
this to return a pseudo-random approval code in the
range 10000 - 99999:
'----- start of code -----
Function NewApprovalCode() As Long
Dim lngCode As Long
Static blnRandomized As Boolean
If Not blnRandomized Then
Randomize
blnRandomized = True
End If
lngCode = Int((Rnd() * 90000)) + 10000
NewApprovalCode = lngCode
End Function
'----- end of code -----
I designed the routine to return a number value,
rather
than a string,
but it could easily be modified to return the code as
a
5-digit
character string. The modified lines would be just:
Function NewApprovalCode() As String
and
NewApprovalCode = Format(lngCode, "00000")
However, the non-reuse issue isn't quite so simple.
Do
you really need
to check that no new approval code has already been
used? If so, you
must have a table somewhere in which the approval
codes
are stored, and
thus you'll need to do a lookup in the function to
verify that the code
that was just generated hasn't been used already, and
loop until you
come up with one that hasn't. Or else, you could put
a
unique index on
that field in the table, and let the attempt to save
the
record generate
an error. But I don't know enough about the context
in
which you'd be
doing this to give you advice as to the best way to do
it.
Note that random numbers in a range of only 90000
possibilities are
going to generate a substantial number of
collisions. I
hope you're not
planning to generate many of these.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
.
