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.