G
Guest
Below this question is a description on how to replicate the probelm, but
i'll overview it here:
As access saves a record, that record is then needed to be accessed
immeaditly via ADO, however it takes ADO 2/3 seconds to be able to access the
new data. The record needs to be accessed to generate a custom autonumber.
Can make a working solution in DAO, however I feel uneased not knowing how to
complete the task using ADO.
Code:
---------------------------------------------------------------------------------------------
'Class Module (dbInteract)
Option Compare Database
'Class consists of no constructors or destructors, must be called manually
through
'Setup and Finish Methods
'Populate method requires a SQL parameter, runs the SQL, if it returns a
recordset it is then available through
'the Data object as a recordset
Private Connection As New ADODB.Connection
Private Command As New ADODB.Command
Public Data As New ADODB.Recordset
Public Sub Setup()
If Data.State <> 0 Then Data.Close
If Connection.State <> 0 Then Connection.Close
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
"C:\Documents and
Settings\Lee\Desktop\Test Database Backend.mdb" & ";Mode=Read|Write"
Connection.Open
With Command
.ActiveConnection = Connection
.CommandType = adCmdText
End With
With Data
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With
End Sub
Public Sub Populate(ByRef SQL As String)
If Connection.State <> 0 Then
Command.CommandText = SQL
If Data.State <> 0 Then Data.Close
Data.Open Command
Else
MsgBox "Unable to Populate with Data :: " & Connection.State
End If
End Sub
Public Sub Finish()
If Data.State <> 0 Then Data.Close
If Connection.State <> 0 Then Connection.Close
Set Data = Nothing
Set Command = Nothing
Set Connection = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Description & "::" & Err.Number
End Sub
---------------------------------------------------------------------------------------------
'Module (modInterface)
Option Explicit
Public Interface As New dbInteract
Sub Setup()
Interface.Setup
End Sub
Sub Finish()
Interface.Finish
End Sub
---------------------------------------------------------------------------------------------
'Form (frmEnquiry)
Option Compare Database
Option Explicit
Sub ProblematicFunction()
If IsNull(Me.EnquiryID) Then
'Works------
'FakeEnquiryID = "Auto-Gen: " & DLookup("[Enquiry ID]",
"qryNewEnquiryID")
'-----------
'Doesnt Work
Interface.Populate CurrentDb.QueryDefs("qryNewEnquiryID").SQL
FakeEnquiryID = "Auto-Gen: " & Interface.Data.Fields(0).Value
'-----------
Else
FakeEnquiryID = EnquiryID
End If
End Sub
Private Sub Form_Load()
ProblematicFunction
End Sub
Private Sub Form_Current()
ProblematicFunction
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(EnquiryID) Then
Interface.Populate CurrentDb.QueryDefs("qryNewEnquiryID").SQL
EnquiryID = Interface.Data.Fields(0).Value
End If
End Sub
---------------------------------------------------------------------------------------------
Query (qryNewEnquiryID)
SELECT Max([EnquiryID])+1 AS [Enquiry ID]
FROM tblEnquiry;
---------------------------------------------------------------------------------------------
i'll overview it here:
As access saves a record, that record is then needed to be accessed
immeaditly via ADO, however it takes ADO 2/3 seconds to be able to access the
new data. The record needs to be accessed to generate a custom autonumber.
Can make a working solution in DAO, however I feel uneased not knowing how to
complete the task using ADO.
Code:
---------------------------------------------------------------------------------------------
'Class Module (dbInteract)
Option Compare Database
'Class consists of no constructors or destructors, must be called manually
through
'Setup and Finish Methods
'Populate method requires a SQL parameter, runs the SQL, if it returns a
recordset it is then available through
'the Data object as a recordset
Private Connection As New ADODB.Connection
Private Command As New ADODB.Command
Public Data As New ADODB.Recordset
Public Sub Setup()
If Data.State <> 0 Then Data.Close
If Connection.State <> 0 Then Connection.Close
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
"C:\Documents and
Settings\Lee\Desktop\Test Database Backend.mdb" & ";Mode=Read|Write"
Connection.Open
With Command
.ActiveConnection = Connection
.CommandType = adCmdText
End With
With Data
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With
End Sub
Public Sub Populate(ByRef SQL As String)
If Connection.State <> 0 Then
Command.CommandText = SQL
If Data.State <> 0 Then Data.Close
Data.Open Command
Else
MsgBox "Unable to Populate with Data :: " & Connection.State
End If
End Sub
Public Sub Finish()
If Data.State <> 0 Then Data.Close
If Connection.State <> 0 Then Connection.Close
Set Data = Nothing
Set Command = Nothing
Set Connection = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Description & "::" & Err.Number
End Sub
---------------------------------------------------------------------------------------------
'Module (modInterface)
Option Explicit
Public Interface As New dbInteract
Sub Setup()
Interface.Setup
End Sub
Sub Finish()
Interface.Finish
End Sub
---------------------------------------------------------------------------------------------
'Form (frmEnquiry)
Option Compare Database
Option Explicit
Sub ProblematicFunction()
If IsNull(Me.EnquiryID) Then
'Works------
'FakeEnquiryID = "Auto-Gen: " & DLookup("[Enquiry ID]",
"qryNewEnquiryID")
'-----------
'Doesnt Work
Interface.Populate CurrentDb.QueryDefs("qryNewEnquiryID").SQL
FakeEnquiryID = "Auto-Gen: " & Interface.Data.Fields(0).Value
'-----------
Else
FakeEnquiryID = EnquiryID
End If
End Sub
Private Sub Form_Load()
ProblematicFunction
End Sub
Private Sub Form_Current()
ProblematicFunction
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(EnquiryID) Then
Interface.Populate CurrentDb.QueryDefs("qryNewEnquiryID").SQL
EnquiryID = Interface.Data.Fields(0).Value
End If
End Sub
---------------------------------------------------------------------------------------------
Query (qryNewEnquiryID)
SELECT Max([EnquiryID])+1 AS [Enquiry ID]
FROM tblEnquiry;
---------------------------------------------------------------------------------------------