Autonumber is easy...yeh right!!!

[Navyguy]

Hi Everybody

I have been searching on and off for the last couple of days looking for an answer and can't seem to get a grip. Not even sure if this is the right forum...

My db that I have been building was going pretty good and I thought I would make one of my fields an autonumber now that most of the "learning" is done and I can sit back and admire My field is called Incident Report Number and it was a text field and I changed it to an autonumber.

Seems simple enough...now the db doesn't work.

As soon as I tab from the first field in the form I get an error:

"This espression is typed incorrectly, or it is too complex to be evaluated. For Example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Then I click "OK" then get another error:

"Object Invalid Or No Longer Set."

Then I click OK and get stuck in this loop with the second error keeps returning. I have to Alt Ctrl Del to get out of it!!!

This is what I have done/know so far...

This worked perfectly fine when the field was a text field, and I have changed it back and again works OK;

Tried deleting all records, compacting and importing into new DB, no effect;
Changed the properties to various things like hidden, locked etc, no effect;
The form is populated from a qry, but when I have it populate directly from the tbl it was the same result;
Created and deleated relationships with tables, no effect;

Now I don't know...I have focused on the Autonumber field but may be it is something else?

I have been searching for autonumber, list/comboboxes, various errors, and can't seem to find what I am looking for.

Your help is appreciated.

Navyguy

 

[Rob.Mills]

Hey Navyguy,

The thing about autonumber is that once you've got records in a table you can't change any of the existing fields to it. You can create a complete new field and set it to autonumber.

Can you post your db here for me to look at?

 

[Navyguy]

Hi Rob!!

What I did was delete all the records in the main table where the field was the PK and all the related records where that field was joined, in fact I deleted all the records in the DB!!

I changed the field from text to Autonumber, reestablished my table relationships, compact and repaired. Now nothing. I have since reverted back to the text format so I can keep practicing but this seems to have me a little confused.

What part of the DB would you like to see? Just the tbl and forms that are related to this?

Navyguy

 

[Rob.Mills]

Need to see the frontend forms

 

[Navyguy]

Here is some forms for you

Edit: I deleted the file, enough stuff on the server I suspect

 

[Rob.Mills]

Here is some forms for you

My mistake. I'll need you to include the tables with it. If you could just create a duplicate with the tables included (not linked) that would be helpful. Also, which form was it that you were getting errors one?

 

[Navyguy]

Thanks Rob, I appreciate your help!!

Heres more stuff...

I included the sample tables for the forms and one dummy record.

I encountered the problem on these frms/SFrms that I am sending you. Basically they are the same, but I set one up for data entry. (I read how to do that in a post some where so I tried it out!!) One is linked to the tbl directly and one is linked via qry. This problem could be happening in other areas/forms, but from my previous post I got stuck in this loop using these forms and could not get out.

Navyguy

 

[Rob.Mills]

I believe I've got it. Creating an input form with an input subform is kind of tricky when there's relationships between the two. Let me see if I can explain this in a simple manner. Let's say you move to a new record in the main form and the subforms naturally requery so they're at a new record each. Well if you start typing in the main form it's creating that new record. And then you move to the subforms to add data there. If you try to add more than one record in the subform it can't update because your main record hasn't been saved yet. So an error occurs because the sub record can't be saved due to data integrity issues.

Are you familiar with VBA? My favorite way of solving this issue is to create a new main record in code, then have the main form move to the new record. That way it already exists when you try to enter sub records.

Hope that wasn't too confusing?

Hey are you currently in the Navy?

 

[Navyguy]

Ok Rob

I read your reply a couple of times and think I have what you said. What I am missing though is why this only happens when I changed it to Autonumber and not when it was a text field? Is the process not the same?

If you look at the main forms, you can add several (unlimited) records(names) before updating/saving the information in the main form. Also another question, One form is based on a qry, so that record is already saved yet the problem is still there.

Your solution make sense, but the factors don't seem to fall in place for my level of understanding. The way I am thinking is that the Autonumber is really just saving somebody from typing in the information. In the text field, if I put the "no duplicates" on, for the most part I get the same effect as autonumber...right?

I am trying to be retired
but Yes 20 yrs NavRes.

Navyguy

Never
Again
Volunteer
Yourself

 

[Rob.Mills]

Well you're right. It let's you do it with text but not with the autonumber. Don't know why exactly.

Another thing you could do is break them up into separate forms and for the sub records use a combobox so they can select the correct main record.

 

[Navyguy]

Well Rob

I must admit I am happy that it is an access problem and not a "me" problem. I thought I was missing something so simple (which is usually the case).

It is interesting that you can't do this though without creating separate forms to accomplish what a SForm will do...Sort of defeats the value of SForms!!!

I wonder what "it" is about the Autonumber...

Thanks for your help Rob and...

"Splice The Main Brace" you deserve it!!

Navyguy