Results 1 to 6 of 6
  1. #1
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88

    generate autonumber

    hi im new to access and i have created a table of date/time only has one field which is the for date/time, with default value as =date() so it may show the present day.
    im looking for a way to create a new row, a new autonumber, into that table, but not manually by going in table, i wanna be able to create a new row by


    clicking a button in form, or maybe if theres a way a macro of some sort which excute the new row upon opening access to

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you not want to go this way?

    https://www.accessforums.net/access/...nly-42626.html

    I mean, what you ask is possible, but why have it be so manual?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    oh hi u were helpful u solved partially the problem like 50% but the thing is i need to have the date as a foreign key in the conjunction table so it will be impossible to put the wrong date later on when i create a data entry based on the conjunction table,

    the other story why i need to separate the date is long lol but i try to explain

    ok make a long story short, ill be able to search by date, meaning ill set a date, and by doing so, ill be redirected into a report page showing the date number as main report and under it a big sub-report which shows everykind of operation that took place during that particular day which will be the conjunction table, i will link master fields in report dateID to other main table DateID and ill have a date filter.

    thank you in advance for ur time,

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm fine with the table if you want to go that way, though I don't see the point. It's adding dates one-by-one that I don't like. I had a function that steps through dates so I modified it to add dates to a table. It took a fraction of a second to add 18 years worth of dates, which I randomly decided on.

    Code:
    Public Function FindMissingDates()
      Dim dteStartDate            As Date
      Dim dteEndDate              As Date
      Dim dteCurrDate             As Date
    
      Dim strSQL                  As String
      Dim db                      As DAO.Database
      Dim rs                      As DAO.Recordset
    
      dteStartDate = #1/1/2011#
      dteEndDate = #12/31/2029#
    
      Set db = CurrentDb()
    
      strSQL = "SELECT * FROM TableName"
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      For dteCurrDate = dteStartDate To dteEndDate
        rs.AddNew
        rs!FieldName= dteCurrDate
        rs.Update
      Next dteCurrDate
    
      Set rs = Nothing
      Set db = Nothing
    End Function
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    oh right first i would like to thank you for the macro which u have provided me with and erm well
    how do i where do i paste this in access lol ^^ sowi for newb question and how does it work ?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since it's a one-time thing, with Access open hit Alt-F11 to open the VBA editor. Click Insert/Module to create a new standard module. Paste the code there, change the table and field names to yours, change the start and end dates as appropriate. Hit F8 then F5 which should run the process. Check your table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 12-10-2013, 06:27 AM
  2. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  3. Replies: 4
    Last Post: 11-15-2012, 09:33 AM
  4. Replies: 3
    Last Post: 06-09-2012, 09:49 PM
  5. Replies: 1
    Last Post: 06-09-2012, 05:13 PM

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