Dirk, here is the code that adds the records:
'*********************************************************************
'*********************************************************************
Public Function gfunCreateService(ByRef lngServiceID As Long,
lngCurrentMileage As Long, _
lngFieldUnitID As Long, intPersonnelID As Integer,
blnIsPrimingRead As Boolean) As String
On Error GoTo gfunCreateServiceErr
Dim cnn As New ADODB.Connection
Dim rstCreateService As New ADODB.Recordset
Dim rstGetTasks As New ADODB.Recordset
Dim rstCreateTasks As New ADODB.Recordset
Dim strSQLCreateService As String
Dim strSQLGetTasks As String
Dim strSQLCreateTasks As String
Dim lngNewServiceID As Long
Dim lngToNextServiceKM As Long
Dim strSuccess As String
' call function to open the connection to the database
If gfunOpenConnection(cnn) = True Then
' call function to grab the amount of KM to next service
' will add later, but for now just hard code
lngToNextServiceKM =
gfunGetToNextServiceKM(lngToNextServiceKM)
'code to unset the last service as last service
strSuccess = gfunUnSetLastService(lngFieldUnitID)
If strSuccess <> "Success" Then
gfunCreateService = strSuccess
Else
' SQL statement to open tblServiceServiceTasks to create
a new Service Record
strSQLCreateService = "SELECT tblServices.* " _
& "FROM tblServices;"
' create and open a recordset to add a new service
' set the service date to today and add the appropriate
amount of KM's to
' get to next service
With rstCreateService
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Source = strSQLCreateService
.Open
.AddNew
.Fields("UnitID") = lngFieldUnitID
.Fields("ServiceDate") = Format(Now(), "Short Date")
.Fields("LastServiceKM") = lngCurrentMileage
.Fields("NextServiceKM") = lngCurrentMileage +
lngToNextServiceKM
.Fields("CurrentServiceKM") = lngCurrentMileage
.Fields("PersonnelID") = intPersonnelID
.Fields("IsLastService") = True
.Fields("IsPrimingService") = blnIsPrimingRead
lngNewServiceID = .Fields("ServiceID")
.Update
End With
' if we are adding a new field unit, we need to add a
priming service
' so we can get the next service amount
If blnIsPrimingRead = False Then
' SQL statement to select all the active service
tasks strSQLGetTasks = "SELECT
tblServiceTasks.ServiceTaskID, tblServiceTasks.ServiceTaskName, " _
& "tblServiceTasks.ServiceActive " _
& "FROM tblServiceTasks " _
& "WHERE
(((tblServiceTasks.ServiceActive)=True));"
' SQL statement to open tblServiceServiceTasks to
create
all the Service Tasks which
' will creata the illusion of a new Service Form
strSQLCreateTasks = "SELECT tblServiceServiceTasks.*
" _ & "FROM
tblServiceServiceTasks;"
' create and open a recordset to add a new
ServiceServiceTask With rstCreateTasks
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Source = strSQLCreateTasks
.Open
End With
' create and open a recordset of Service Tasks
' we will loop through all the active ServiceTasks
and add each active
' one and assign it the current ServiceID with the
recordset we opened above
With rstGetTasks
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Source = strSQLGetTasks
.Open
If .EOF Or .BOF Then
gfunCreateService = "No Service Task Records
Found!" Else
.MoveFirst
Do Until .EOF
rstCreateTasks.AddNew
rstCreateTasks.Fields("ServiceID") =
lngNewServiceID
rstCreateTasks.Fields("ServiceTaskID") =
rstGetTasks.Fields("ServiceTaskID")
rstCreateTasks.Update
.MoveNext
Loop
' set the value of lngServiceID, so that the
caller of the function
' can grab the value for whatever reason,
since it is passed by ref
lngServiceID = lngNewServiceID
gfunCreateService = "Success"
End If
End With
Else
gfunCreateService = "Success"
End If
End If
End If
gfunCreateServiceExit:
Set rstCreateService = Nothing
Set rstGetTasks = Nothing
Set rstCreateTasks = Nothing
Exit Function
gfunCreateServiceErr:
gfunCreateService = "Access Error: " & Err.Number & " " &
Err.Description GoTo gfunCreateServiceExit
End Function
And here is the call to the function, now I have tired a bunch of
different things, and if I use the "dirty refresh" I call it, 9 times
it seems to work. It has to be the database is not regonizing the
records or something. Is there some better way of doing this, it just
seems wrong. This version here I use a textbox on the main form to
open the second form. I would rather use the Where condition of
docmd.open, I have not yet tried it with the multiple dirty refreshes.
'****************************************************************
'****************************************************************
Private Sub cmdAddNewService_Click()
Dim strSuccess As String
Dim lngServiceID As Long
' call function that creates the new Service,
' it will instance a record in tblServices, and each record
' in the tblServiceServiceTasks for all active Services
strSuccess = gfunCreateService(lngServiceID,
Me.txtCurrentMileage, _ Me.txtFieldUnit,
Me.cboPersonnel, False)
' call function to create a new service
If strSuccess <> "Success" Then
MsgBox "Error! Cannot Open the Add New Service Form!@" _
& strSuccess & "!@" _
& "Please contact Application Support for assistance."
Else
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Refresh to get the order total to save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoEvents
' open the form to the newly created service entry
'DoCmd.OpenForm "frmServiceAdd", , , "[ServiceID]=" &
lngServiceID, , , lngServiceID
DoCmd.OpenForm "frmServiceAdd"
Me.txtServiceID = lngServiceID
'DoCmd.Close
'MsgBox lngServiceID
End If
End Sub
Dirk Goldgar said:
I'm working in Access 2000. I have some code, which runs through and
adds records into two tables (tblServices, tblServiceTasks). The
function returns the Service ID of the newly added record. After I
recieve the ServiceID I open the next form to the new record.
This is where the error occurs, and unfourtunetly, it is not
consitent. When the form opens sometimes it has no records. So I
tried a dovents, and a couple different ways of refreshing the data
once the form is open. Still no luck. Sometimes it works, sometimes
it doesn't and there is no real pattern to when it works or not. Any
ideas.
If you'd post the code that adds the records, maybe something will
become apparent.
