Data Entry Form help (1 Viewer)

mpaulbattle · Jun 26, 2018

I created a data entry form to add a new provider. the form's record source is tblProviders.

1. Under the Data tab for form properties I changed Data Entry to Yes.
2. The On Load event I set the macro to open frmAddNewProvider with the gotorecord set to New
3. I added the fields for ProviderID,FN,LN,Credentials, Status, NPI, etc.

When I open the form and try to add a new provider I get a message that the changes were not successful because they would create duplicate values in the index, primarykey, or relationship. After looking at the form closely I noticed when I began typing in the new provider's first name the ProviderID that shows up is one that is already used. The ProviderID is an Autonumber field. The new record should be 601, instead it is starting at 520.

Does anyone know why this is happening?

bob fitz · Jun 26, 2018

mpaulbattle said:
.....2. The On Load event I set the macro to open frmAddNewProvider with the gotorecord set to New....

I don't understand this:

In the On Load event of what?

Is frmAddNewProvider the form bound to tblProviders ?

If you set a forms Data Entry property to Yes then the form will always open at a new record with no other records available. Only records entered since it was opened will remain visible.

mike60smart · Jun 26, 2018

Hi

Any chance you can upload a zipped copy of the Db?

mpaulbattle · Jun 26, 2018

bob fitz said:
I don't understand this:

In the On Load event of what?

Is frmAddNewProvider the form bound to tblProviders ?

If you set a forms Data Entry property to Yes then the form will always open at a new record with no other records available. Only records entered since it was opened will remain visible.

Bob,
I added a macro to the OnLoad event of the frmAddNewProvider. I was in the same boat as you with if I set the forms Data Entry to yes it will open to a new record. Unfortunately it kept going back to a previous ProviderID.

The frmAddNewProvider is bound to tblProviders. When I initially created the form it was working properly. After a couple of successful entries, it started to pull in already used ProviderID's.

mpaulbattle · Jun 26, 2018

mike60smart...I will try. Will need to remove some information and then see if i can do it from my work computer.

bob fitz · Jun 26, 2018

mpaulbattle said:
mike60smart...I will try. Will need to remove some information and then see if i can do it from my work computer.

remember to compact and repair the db and then zip it

bob fitz · Jun 26, 2018

mpaulbattle said:
Bob,
....After a couple of successful entries, it started to pull in already used ProviderID's.

What is the data type of ProviderID field

mpaulbattle · Jun 26, 2018

It won't let me post it.

mpaulbattle · Jun 26, 2018

bob fitz said:
What is the data type of ProviderID field

the data type is Autonumber

Pat Hartman · Jun 26, 2018

When the DataEntry property is set to Yes, you do NOT need code in the load event to go to a new record. In fact remove ALL the code (or macros) from the form events and see if the form works. If it doesn't, tell us what is not working.

Your database may be too large to upload.

missinglinq · Jun 26, 2018

What does

mpaulbattle said:
3. I added the fields for ProviderID,FN,LN,Credentials, Status, NPI, etc.

mean, exactly?

You cannot assign a value to ProviderID if it is an Autonumber, as the Access Gnomes do that. I suspect that this is the cause of your error.

Linq ;0)>

mpaulbattle · Jun 26, 2018

Pat Hartman said:
When the DataEntry property is set to Yes, you do NOT need code in the load event to go to a new record. In fact remove ALL the code (or macros) from the form events and see if the form works. If it doesn't, tell us what is not working.

Your database may be too large to upload.

It's not the DB size, it's the company policy.

I removed the macro from the form. When the form opens it has (New) in the providerid field. When I start typing the new provider's first name, the ProviderID now shows 355 when it is suppose to show 606 which would be the new autonumber ID when adding to the table.

The zipped file i created it is working properly (for now). I had the production one working properly this morning and it just started doing this. Could it be a network issue? I saved the FE on everyone's desktop. Maybe it is someone's login creating this issue.

It's not the DB size, it's the company policy.

bob fitz · Jun 26, 2018

mpaulbattle said:
The zipped file i created it is working properly (for now).

If you ran Compact and Repair as I suggested earlier, then that may be why.

missinglinq · Jun 26, 2018

mpaulbattle said:
...When I start typing the new provider's first name, the ProviderID now shows 355 when it is suppose to show 606 which would be the new autonumber ID when adding to the table...

Sorry, but not true! There is no guarantee that an Autonumber will increment the previous value by one. That's the reason why Autonumbers shouldn't be used for anything that is to be viewed by users. They're intended to be used for one purpose and one purpose only...to provide each Record with a unique identification.

For the thing you're doing, you should be generating a custom, auto-incrementing number.

Linq ;0)>

Pat Hartman · Jun 26, 2018

When Autonumbers are defined as "Increment" in the New Values property, the autonumber increments by 1 each time you add a new row. HOWEVER, failure to complete the addition of any row results in an unused ID. An append query that fails in full or in part will also result in unused IDs, and if you delete a row, that ID will not be reissued so even though the autonumber always increments by exactly 1, you will in the normal course of business end up with gaps in the sequence number.

If you have an autonumberID with a value of 601 and that is why you are expecting 603. The table is corrupted and therefore it is not generating the correct value. Sometimes compact and repair will fix the problem. If that fails to work, you can export the table to excel, delete the table from the database, compact nnd then import the exported file. The original autonumbers can be selected and appended if you need to keep them because they ave been used as foreign keys.

It is only if you choose "Random" a the "New Values" property that the numbers generated will be random.

Autonumbers are never reused so gaps will always exist. This is NOT a problem and should never bother you. If your process requires no gaps (a check writer for example), you will have to generate your own sequence number

mpaulbattle · Aug 3, 2018

The compact and repair worked.

Data Entry Form help (1 Viewer)

mpaulbattle

Registered User.

bob fitz

AWF VIP

mike60smart

Registered User.

mpaulbattle

Registered User.

mpaulbattle

Registered User.

bob fitz

AWF VIP

bob fitz

AWF VIP

mpaulbattle

Registered User.

mpaulbattle

Registered User.

Pat Hartman

Super Moderator

missinglinq

AWF VIP

mpaulbattle

Registered User.

bob fitz

AWF VIP

missinglinq

AWF VIP

Pat Hartman

Super Moderator

mpaulbattle

Registered User.

Similar threads

Users who are viewing this thread