Results 1 to 15 of 15
  1. #1
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239

    Autonumber field in forms

    Hello,

    How c
    an 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

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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?

  3. #3
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

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

  5. #5
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    I'm sorry to bother you, but I'm not quite a programmer, how and where can I put this code ?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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."

  7. #7
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    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 !

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  9. #9
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    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 ?

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Only ONE field name goes in there

  11. #11
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    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

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  13. #13
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    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

  14. #14
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    239
    Quote Originally Posted by ItsMe View Post
    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

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Lukael View Post
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. AutoNumber Field Format
    By andy-29 in forum Access
    Replies: 5
    Last Post: 11-13-2012, 07:31 PM
  2. Replies: 1
    Last Post: 09-25-2012, 03:58 AM
  3. autonumber in table and forms
    By ismith in forum Forms
    Replies: 1
    Last Post: 01-04-2012, 10:50 AM
  4. Using Dcount on an Autonumber Field
    By rfs in forum Forms
    Replies: 0
    Last Post: 03-17-2011, 10:04 AM
  5. Autofill field based on autonumber field
    By frevilla in forum Forms
    Replies: 1
    Last Post: 09-11-2009, 02:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums