Creating Duplicate Values Error

[duckster]

Thanks, but I can't get it to work.

Currently my main form has the ClientID field in the header. I also have two subforms embedded into the form. I tried adding a "Save" button and added the coding:

If Not IsNumeric([ClientID]) Then
[ClientID] = Int((999999 * Rnd()) + 100000)
End If

in the "On Click" codebuilder section of the button; however, it still does not automatically populate the ClientID field.

Where exactly on the form do I have to put the:

" If Not IsNumeric([ClientID]) Then
[ClientID] = Int((999999 * Rnd()) + 100000)
End If "

?

I currently have a few command buttons on the form, two of them which print reports "on click" functions, and another which is an Add Record "on click" function.

Thanks again, this does seem to be quite a challenge for what seemed like a simple problem. !

 

[duckster]

Here is another stripped down version of the form, with my two additional subforms embedded as it is now. I have many more fields in the main and subforms; however, the main PK fields are included in the current forms...hope this helps with your much appreciated analysis!

 

[RuralGuy]

Try this one.

 

[duckster]

RuralGuy, I can't view "design view", and when I right click the form, nothing happens...I also can't see the underlying tables, other forms, etc. ... or create any new tables.

Did you lock up the database?

Thanks.

 

[RuralGuy]

What version of Access do you use? Do you hold down a <SHIFT> key when you open the db? I did nothing to lock the db.

 

[duckster]

+ Shift worked! Is that some sort of security feature? My database doesn't require the +Shift for it to open...I'm using Access 03.

Thanks a million RuralGuy, I inputted your coding in the On Current and On Load in my form, and yes, the duplicates have decreased dramatically. The only thing is, sometimes the ClientID will generate 7 digits, whereas I thought the coding only allows for 6 digits maximum (6 digits is what I need).

Doesn't the coding below only allow for numbers between 100,000 to 999,999?

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

 

[RuralGuy]

If it were Int((899999 * Rnd()) + 100000) then I would say that would hold the result to 6 digits. Holding the <SHIFT> key down stops the first form from opening. It actually stops *any* startup code and bypasses anything done on the StartUp screen.

 

[duckster]

Well, I've implemented your changes RuralGuy, and it seems to be working well! The coding in the "On Load" and "On Current" that you implemented seems to have done the trick.

I did try the coding in just the "On Load" event, and duplicates did still occur; however, when I also added the additional coding in the "On Current", the duplicates stopped.

Thanks so much for your help

 

[RuralGuy]

Glad to hear it. Much success with the rest of your project.