Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Why not build out a test scenario where you create a new Workbook and a new Access database. In the Access database, create a new table and include a couple of columns. One column will be type Autonumber and the other text.

    Add a couple of records with random information in the text column.

    Then, in your new Excel workbook, connect to your Access DB and create a new record in the table. Then, retrieve the Autonumber value and save the record, in that order.

    If you can accomplish that, you should be halfway to resolving your issue.

  2. #17
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you having trouble developing the code to convert the current date number? Is that what you want us to do?

  3. #18
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok:

    I have solution for this!!!

    First of all user is fullfiling In Excel's Useform his database within his workbook with proper data.

    Excel added this data after user clicks Send Button to sheet "Reported tickets" (attachment User's database) There is empty column - FormattedTicket Number (5000020_2016_4)

    After send button also in sheet "Counter of Entries" is added to table:

    TicketNo - 5000020
    Time Entry - 2016-03-12 15:34:34
    User - login.

    Code:
    Sub SQL_Baza()
    Dim Connectstr As String
    Dim HurtowniaADO As New ADODB.Connection
    Dim ZdanieSQL As String
    Dim Login As String
    Dim FileName As String
    Dim Moja As New MyForm
    Dim Lokalizacja_Pliku As String
    Dim Lokalizacja_Folderu As String
    Dim TimeEntry As String
    Dim TicketNumber As String
    Dim Wiersz As String
    Dim rs As ADODB.Recordset
    Dim NumerSpółki, User, CzasWpisu As String
    Dim rsQuery As ADODB.Recordset
    
    // path of the database
    Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\Baza_Pełnomocnictwa.mdb"
    Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa"
    
    //name of Excel
    FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"
    
    //entries in user Workbook
    Login = "ljar01"
    TicketNumber = "5000020"
    TimeEntry = "29-08-2016 14:33:33"
    
    //connection string to access
    Connectstr = "DSN=MS Access Database;DBQ=" & Lokalizacja_Pliku & ";DefaultDir=" & Lokalizacja_Folderu & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    On Error Resume Next
    
    // opening databse
    
    HurtowniaADO.Open Connectstr
    Set rs = New ADODB.Recordset
    Set rsQuery = New ADODB.Recordset
    
    // opening table with all records and adding new entry
    
    rs.Open "tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With rs
        .AddNew
        ![NumerSpółki] = TicketNumber
        ![CzasWpisu] = TimeEntry
        ![User] = Login
        .Update
    End With
    // refreshing query with D count Function

    rsQuery.Open "qry_tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable
    rsQuery.Update

    Set rs = Nothing
    Set rsQuery = Nothing
    And after that my code above in Excel VBA is going:
    1. added last record for table with TicketNo, TimeEntry and User on basis of
    entry by User (only time is counted by formula Now())
    2. Connecting to access and add last row to table1 - with all entries from all users.
    3. refreshing query with Dcount function (qryTable)
    4. Added first_row from qryTable to sheet "Reported tickets" and that is all.

    We have full database.

    Database in attachment.
    The topic was solved in other forum - if you are interested, here is the link:
    http://www.access-programmers.co.uk/...d.php?t=288927


    Attached Files Attached Files

  4. #19
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Thank you. I will take note you chose to cross post your question in another forum.

  5. #20
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I had to to it ItsMe - sorry - the most important for me was time...

    Jacek

  6. #21
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Cross Posting is Okay as long as you follow a few simple rules:
    http://www.excelguru.ca/content.php?184

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-26-2016, 06:42 AM
  2. Replies: 5
    Last Post: 02-09-2015, 04:18 PM
  3. Replies: 7
    Last Post: 07-04-2014, 08:33 AM
  4. PO Detail Subform Line Numbering Problem
    By ChrisThomas99 in forum Programming
    Replies: 4
    Last Post: 02-05-2013, 04:31 PM
  5. Trouble recovering database : Auto numbering
    By ArseniusCamillus in forum Access
    Replies: 4
    Last Post: 02-14-2012, 04:56 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