Autonumber field in forms

[Lukael]

Hello,

How can I open my form which is made from "leading" table and linked subforms(tables) in that way so that AUTONUMBER field woudn't be set as "Autonumber", but It would already assign new number of recordwithout entering anything on fields (As it does when you enter data in "leading" table field) ?



Regards, Luka

[Dal Jeanis]

The particular autonumber that will be selected for the field doesn't exist until a new record is created.

Also, in general you shouldn't be using an autonumber field for anything visible to the user. Using an autonumber field as your "official" customer number of employee id, for example, usually leads to trouble.

What are you trying to achieve with this desired behavior?

[Lukael]

I am working on school project. I created priority table that has primary key (ID number) and other data, and three tables(also with data) that are linked to this table, with ID number of priority table and primary keys of other tables (relations many-to-many, I formed tables between them).

PROBLEM - my solution is created so that I can fill all neccessary data of ALL tables in ONE FORM. But ofcourse Access wants me to enter at least one field in primary table for opening new record, so that It links to all other tables (in my case with ID number) - then It is possible to enter data of all other tables in whatever tab order you like.

What I want to do is that my autonumber field from primary table would automatically open a new record number when opening the form, without entering any data to primary table. Is that even possible ?

So far I tried to cheat Access and add another field in my primary table (Date/time format) and set the date to =Now(), used macro of Refresh on form, but nothing happens. Date is shown in field, but Autonumber doesn't generate new number of record. Maybe I did it wrong...

Any other idea, or maybe option to lock Tab order and force user to fullfill data only in desiring order ? I want to make solution more user friendly

P.S.: I'm using Access 2003, professor demands It, not my choice !

Thanks for your help,

Regards,
Luka

[ItsMe]

What Dal said.....

You can add an empty string to a field. This will save the Autonumber and a new record. Like Dal mentioned, Autonumber does not save until after another field within the recordset gets updated.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("leading")
    rst.AddNew
    rst![RandomFieldName] = ""
    rst.Update
    
rst.Close
Set rst = Nothing

[Lukael]

I'm sorry to bother you, but I'm not quite a programmer, how and where can I put this code ?

[ItsMe]

That code can be placed in a click event. You can place it in a form's Load event. You can place it just about anywhere. I am not sure what you are trying to do or when it needs to be done. That is why the code. It can be placed in any function sub routinem wherever.

If you are not sure where, you can start with a command button and place it on the button's "Click" event. It may serve you well in a form's "Load" event. YOu did mention something about, "when a form loads."

[Lukael]

Thanks for help, but this code doesn't work for me. When I enter in form first time nothing happens, then I close It and on second entering It generates new record number as desired - but then when I want to erase record, It generates another record...Finally you can erase both...And that goes in circles...I would be great If It generated everytime on entering... I tried on Load, Enter, Update, Click, everything same...On existing fields, new field of form, and form itself. Did I make mistake with (RandomFieldName - is that name of my table fields or something else) ? Or any other solutions maybe, to determine Tab order for fields and lock other fields first ?

Thanks anyway !

[ItsMe]

You will need to replace RandomFieldName with the name of a field within your table. I do not know those names because you have not provided them to us.

Also, you need to make sure the field you choose is of the data type that will accept an empty string, like a text field. Additionally, you can not have any OTHER fields that REQUIRE a value in a new record.

[Lukael]

I have replace RandomFieldName with names of my fields, text fields that is. I have one Autonumber field, but 4 of Text data type, is that maybe the problem ?

[ItsMe]

Only ONE field name goes in there

[Lukael]

Yes, I know that, that' how I did It all the time, but still nothing's changed. Too bad that I don't have English Access, I could send you printscreens of form and table

[ItsMe]

I do not know if printscreens will help. What you are trying to accomplish is not a conventional approach. It is hard to understand your approach.

If you want to save an "empty" record, the code I provided will do that. The only other thing I can imagine is you can just update the field through a bound control, using the form's current event.

I guess somewhere you are using a form to navigate to a new record. At that point and place you can put the following....

In the form's current event.

Me.RandomFieldName.Value = ""

If you are looking at and viewing the empty and blank record, you can update the field with an empty string there, at that point. Since you don't want to type data into the field, you can use the above code to insert an empty string into the field instead.

[Lukael]

Ok, I will try that, looks like that's my only option. Thanks for all your help, consider this thread to be Solved, I won't bother you anymore

Regards, Luka

[Lukael]

I do not know if printscreens will help. What you are trying to accomplish is not a conventional approach. It is hard to understand your approach.

If you want to save an "empty" record, the code I provided will do that. The only other thing I can imagine is you can just update the field through a bound control, using the form's current event.

I guess somewhere you are using a form to navigate to a new record. At that point and place you can put the following....

In the form's current event.

Me.RandomFieldName.Value = ""

If you are looking at and viewing the empty and blank record, you can update the field with an empty string there, at that point. Since you don't want to type data into the field, you can use the above code to insert an empty string into the field instead.

Just to let you know. Code works fine when inserting It into Click event of command button for New Record.

Private Sub Ukaz29_Click()
On Error GoTo Err_Ukaz29_Click


DoCmd.GoToRecord , , acnewrec
Me.Lokacija_objekta.Value = ""
Exit_Ukaz29_Click:
Exit Sub


Err_Ukaz29_Click:
MsgBox Err.Description
Resume Exit_Ukaz29_Click

Thanks for help

[ItsMe]

Just to let you know. Code works fine when inserting It into Click event of command button for New Record.

It is interesting you commented about this. Recently, I was testing default values verses user inputs. In A2003 you can use an empty string as a default value but not in A2010. In A2010 default values of "" translate to null. However, if you "Update" a record with an empty string in A2010 it will return and equal to an empty string.

Not sure why you were unable to get an empty string to work in the current event. I tested it the other day in my Empty String vs Null test. Maybe the extra step of saving the record is required for the results you are looking for.

Anyway, glad you got things the way you need them to be.