Creating Duplicate Values Error

[duckster]

I have a table with one primary key (ClientID) which is an autonumber (cannot be duplicated), and another field which is a number field which is set to a random 6 digits (also cannot be duplicated) whenever a new client record is created.

Now, the main database is on a desktop, and my partner and i are connected to this main database through our laptops whenever we're in the office. For the last 6 months, I had only the ClientID in the form (from the Client Table), and everything would work fine whenever we created a new record in the Form, even if we created a new record at the same time on our personal laptops, the numbers would automatically be increased by one w/ no error.

I just added the other field now, the regular 6 digit random number field, and sometimes (only sometimes) when we both have a different client and start a new record, we get error messages that says we're creating duplicates. When that error message comes up, one of us has to close down the form completely, and reopen and start a new record again. Not sure why this is happening. Here is the error message.

Any help appreciated.!!

 

[RuralGuy]

Trap error 3022 in your error handler and retry.

 

[duckster]

Trap error 3022 in your error handler and retry.

Sorry, I don't know how to do this. Can you help me? Thanks!

 

[RuralGuy]

Are you using code to create a new record? If so, would you post it so we can review it?

 

[duckster]

In my client table, the primary key is the ClientID which is an autonumber. For the second field, it is set as a random number and default value is:

Int((999999*Rnd())+100000)

Not sure why I always get the error message stated above though...seems the database always defaults to the same three 6-digit random numbers whenever it opens.

Help is appreciated.

 

[RuralGuy]

Look up Randomize() in VBA help. You need to initialize the random number generator seed.

 

[duckster]

I haven't used or am not sure where to add the "Randomize()" code. I've done a search and founds a few hints, i.e. inputting code like:

Randomize
RandomNumber = .....


Where do I add this?? I have no idea where the coding should go

thanks again

 

[RuralGuy]

If you are setting the default value for the field at the table level then you will always have problems with the random number. It needs to be done at the form level when you are adding new records.

 

[duckster]

Thanks...let me rephrase a bit...right now I have in the "ClientID" field:

Int((999999*Rnd())+100000)

as Default Value in the table.

I have this "ClientID" field in a form.

When I open the form, it often changes the 6 digit numbers up a bit; however, a lot of times the number stays the same...

I'm not sure what and where to put the coding to make that field show up without the form always opening up to the same "supposedly" random numbers...with the code right now, it doesn't always randomize the field in the form, it often (once every few new records) starts w/ the exact same number.

I'm not sure where exactly to put: "Randomize()", and I do not use VBA. I've looked at other threads and have found some "Randomize()" coding; however, have not been able to figure where to put the coding...should it go in the Default Value of the table? or somewhere on the Form? If in the Form, should it go in the "On Open" property of the Form, or the Property of the Actual field???

Please help, much appreciated again!

 

[RuralGuy]

Put it in the BeforeUpdate event of your form.

 

[duckster]

I tried inputting Randomize() in the form properties in the BeforeUpdate; however, had the same problem. Whenever I try to add a record, it always starts with the same 6 digit number. Also, when I try to close the record, it has the message "Access can't find the Macro 'Randomize()'"

I'm quite a newbie at this, not sure how the coding works. What exactly to I have to put in the "expression builder" of the Before Update box?

As mentioned, in the table for this field, I have, as a Default Value:

Int((999999*Rnd())+100000)

 

[RuralGuy]

Go to the BeforeUpdate on the event tab of the properties sheet as you did before but this time when you press the "..." (BuilderButton) select Code Builder. When you are done it will look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Randomize()
[YourFieldName] = Int((999999*Rnd())+100000)

End Sub

Using Your Field Name of course and remember to remove the default from the table.

 

[duckster]

I did that, but when I create a new record now NO numbers default in the field (ClientID) at all, and am getting an error. I think it has something to do w/ the "Randomize()" line as it shows as red in the code.

---
Private Sub Form_BeforeUpdate(Cancel As Integer)

Randomize ()
[ClientID] = Int((999999 * Rnd()) + 100000)

End Sub
---



I also tried coding:

---
Private Sub Form_BeforeUpdate(Cancel As Integer)

Randomize (0)
[ClientID] = Int((999999 * Rnd()) + 100000)

End Sub
---

and it gave me an error of:

"Cannot assign value to this object" when trying to create a new record. When I click on "debug", the " [ClientID] = Int((999999 * Rnd()) + 100000) " line shows in yellow, so the problem lies there I think.

Currently the "ClientID" field is in the "header" section of the form (if that helps).

thanks again

 

[RuralGuy]

I'm thinking that Randomize can be in the OnLoad event of the form. "Fields" are in records of tables and "controls" are on forms. Is [ClientID] the name of a field in your underlying query/table?

 

[duckster]

Right, ClientID is the field name. I tried putting it in the OnLoad event, i.e.:

---
---
Private Sub Form_Load()
Randomize (0)
[ClientID] = Int((999999 * Rnd()) + 100000)
End Sub
---
---

It doesn't seem to work. Numbers don't populate in the field, and when moving on the next record, I get a prompt to debug. When I get prompted to debug, the: " [ClientID] = Int((999999 * Rnd()) + 100000) " is in yellow.

Still searching for an answer, and trying things... thanks again.

 

[RuralGuy]

My thoughts were to put Randomize() without a number in the OnLoad event and leave
If Me.NewRecord Then
[ClientID] = Int((999999 * Rnd()) + 100000)
End If
in the BeforeUpdate event of your form.

By the way, you stated in your 1st post that ClientID was an AutoNumber and the Primary Key for the table. Access will *not* let you change an AutoNumber and you shouldn't be trying anyway. What is the actual name of the second field for which you need this random number?

 

[duckster]

Sorry I will break it down correctly and simplify my request.

I have a table, with:

1. a primary key field, which is an autonumber called "ControlID".
2. a field which is a number field, called "ClientID".

The ClientID is the number that needs to be a 6 digit random number.

The form has the above two fields in the header of the form.

So, I've tried inputting various coding in the "OnLoad" event and "BeforeUpdate" event in the form properties, with no luck (I use the "codebuilder" function when I input the code). The coding that I've tried is mainly what I received in the earlier posts from your help, i.e.:

Randomize ()
[ClientID] = Int((999999 * Rnd()) + 100000)

I don't know if I"m putting the above 2 lines in the right area, but they always go at the end of the codebuilder, right before the "End Sub".

I've tried the "Randomize()" with, and without a "0" in the brackets, and both don't seem to work.

Thanks again for your help and patience...still trying!

 

[RuralGuy]

Hi duckster,
This is turning out to be more difficult than I thought. Any chance you could post your db for us to review? Remove any sensitive data of course but leave enough data to demonstrate the problem.

 

[duckster]

Here is a very stripped down version of the form. It includes the Control ID and the Client ID <-- the random field that is giving me problems.

Your help is much appreciated!

 

[RuralGuy]

Try this one and see what you think.