Results 1 to 2 of 2
  1. #1
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Append Query and Increment Number

    My db is a check register db that uses a scheduler and so a event can have many dates that is supposed to entered into the check register. If an event is missed then the user has an opportunity to enter it. Here is where the dilemma comes in. Can't append to the same primary key so must find another way. Any ideas?

    The below code is used to Update the data in table tblEvent and it works. What it does NOT do is add the records
    selected and append them to the table, it just adjusts the dates and check number and yes/no fields.


    Private Sub Command27_Click() 'Purpose: Update values to tblEvent for missed events.


    Dim sSQL As String

    If Me.List0.ItemsSelected.Count = 0 Then
    MsgBox "You Must select at least 1 Item"
    Exit Sub
    End If


    Dim myVar As Variant
    For Each myVar In Me.List0.ItemsSelected

    sSQL = "UPDATE tblEvent SET tblEvent.enter = True, tblEvent.misselect = False, " _
    & "tblEvent.MYDte = #" & Date & "#, tblEvent.dteentered = #" & Date & "#, " _
    & "tblEvent.dtemod = #" & Date & "#, tblEvent.tmemod = #" & Time() & "#, " _
    & "tblEvent.lastmodified = #" & Now() & "#, tblEvent.ChkNo = " & Next_Custom_Counter & ", " _
    & "tblEvent.missev = False, tblEvent.missdte = Null " _
    & "WHERE tblEvent.EventID= " & Me.List0.Column(0)

    CurrentDb.Execute sSQL, dbFailOnError
    'Debug.Print sSQL



    Next myVar

    __________________________________________________ __________________________________________________ _
    The above code uses tblmissedtransactions as the record source.
    tblmissedtransactions has as it's primary key as EventID
    The destination table tblEvent also it's primary key as EventID
    Both tables also have newid as a number and set to field size Double and format fixed.
    I know I can't append to the same Primary Key so here is what I am trying to do
    I have a module named ModEventIDCounter and here is the code for it.
    __________________________________________________ __________________________________________________ ___
    Function Next_CustomEventID_Counter()
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
    'For this code to work, it is required that you reference the:
    ' Microsoft ActiveX Data Objects 2.x Library
    'To reference this library, go to the Tools menu, click
    'References, and select the library from the list of available
    'references (version 2.1 or higher).
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
    On Error GoTo Next_Custom_Counter_Err

    Dim rs As ADODB.Recordset
    Dim NextCounter As Long

    Set rs = New ADODB.Recordset

    rs.Open "ModEventIDCounter", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Open the ADO recordset.

    NextCounter = rs!NextEventIDCounter 'Get the next counter.
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
    'Open table and get the current value of NextAvailableNumber,
    'increment the value by 10, and save the value back into the table
    '
    'The next line can be changed to conform to your custom counter
    'preferences. This example increments the value by 1 each time.
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
    rs!NextEventIDCounter = NextCounter + 0.1
    NextCounter = rs!NextAvailableCounter
    rs.Update

    'MsgBox "Next available counter value is " & Str(NextCounter)
    rs.Close

    Set rs = Nothing

    Next_Custom_Counter = NextCounter

    __________________________________________________ __________________________________________________ ___________
    I also have a table named EventCounterTable and the field name is NextCustomEventIDCounter and it is the Primary Key
    It is set to no duplicates and field size is Double and format is Fixed.
    __________________________________________________ __________________________________________________ ____________
    I am trying to make the field newid increment to the next number using the module and counter table.
    If for example table tblEvent has a primary key of 30, then I want to add the matching records in table
    tblmissedtransactions and increment the newid by 0.01 0.02 and so on.



    ________
    The problem with the counter has been solved, but NOT the other part.
    Attached Thumbnails Attached Thumbnails tblEvent.jpg   appendtotblEvent.jpg  
    Last edited by burrina; 02-01-2013 at 04:30 PM. Reason: Updated

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    not getting anywhere. Thanks Anyway! Marking as Solved.

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

Similar Threads

  1. Increment part of a serial number
    By swavemeisterg in forum Forms
    Replies: 10
    Last Post: 04-15-2013, 01:59 PM
  2. increment number in first column
    By learning_graccess in forum Queries
    Replies: 7
    Last Post: 04-03-2012, 02:56 PM
  3. Increment number from master table
    By leighturner in forum Queries
    Replies: 1
    Last Post: 12-08-2010, 08:19 AM
  4. Replies: 5
    Last Post: 11-12-2010, 12:10 PM
  5. How to Increment A Number Field
    By Advanced in forum Programming
    Replies: 3
    Last Post: 01-27-2010, 02:36 PM

Tags for this Thread

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