query after insert fails

  • Thread starter Thread starter OldEnough
  • Start date Start date
O

OldEnough

I am new to ADO and would appreciate any suggestions. I have a form that
allows users to select criteria to shape a query against a table of service
records. Based on the selection criteria the search then inserts selected
records into a local table. At this point the selection form is closed and a
form based on a query against the table is opened to review the selected
records. The query doesn't work when opened by the calling form, but if the
table is opened the records are there, and if the form is then reopened the
records appear. Thanks for looking at this.

relevant code below:
'Called by buttonclick event

Dim IntPos As Integer
Dim IntPos1 As Integer
Dim AccessConnect As String

'These lines identify current location and name of database
IntPos = InStrRev(getdbPath(), "\", -1, vbTextCompare) - 1
CFPath = Left(getdbPath(), IntPos)
MyStr = MyAppPath()
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & MyStr
& "; DefaultDir=" & CFPath & ";" & _
"Uid=Admin;Pwd=;"

Dim strSQL As String
Dim oCmd As Command
Set oCmd = New Command
Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection

MyStr = CurrentDb.Name
'This is the shaped SQL criteria string to insert the selected records
strSQL = strSQL1 & MyStr & strSQL2

cnn.ConnectionString = AccessConnect

cnn.Open
With oCmd
.CommandText = strSQL
.CommandType = adCmdText
.ActiveConnection = cnn
Set oRS = .Execute
End With

Set oRS = Nothing
Set oCmd = Nothing
cnn.Close
Set cnn = Nothing
 
After you run this insert query code, requery the form that is displaying
those data.
 
Thanks for looking at this. I had tried that. I also tried putting a requery
event in the form open code for the form that uses the query. I don't
understand why, but it appears that the records aren't "saved" until I close
the originating form. I can open the query directly and it will show the
recordset, but the form does not, even with a click event to requery the
subform. When the orginating form is closed and that module is not loaded the
form works. I assumed there is something wrong with my code to insert the
records...but I definitely need help.
 
Post all the VBA code that you have in the form so that we can see "where"
and "when" and "how" you're running the code snippet that you posted.
Normally, the Open event of a form is too early to do any manipulation of
the data that the form will display.
 
Once again...thanks for looking at this. The first sub is the button click
event. It in turn calls the sub that follows. I don't know if it makes any
difference but I recently put the functions that locate the front end and
back end databases "getdbPath()" and "MyAppPath()" in a class module that is
available to all forms in the database, rather than having the same functions
repeated in each form. I can't think of any other changes that might have
resulted in this behavior.

Private Sub btnSearch_Click()
Call SearchNow
End Sub

Private Sub SearchNow()
On Error GoTo ErrorSearch

Dim db As DAO.Database
Set db = CurrentDb
DoCmd.SetWarnings False
strSQL = "DELETE tblSvcCall.* FROM tblSvcCall;"
db.Execute strSQL, dbFailOnError
strSQL = "DELETE tblSvcCallTmp.* FROM tblSvcCallTmp;"
db.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True

Flag = 0

strSQL1 = "INSERT INTO tblSvcCall IN '"
strSQL2 = "'SELECT tblSvcCall.* FROM tblSvcCall WHERE"

'Search By Customer
If Len(Me.CustomerID & "") > 0 Then
lngCust = Me.CustomerID
Flag = Flag + 1
If Flag = 1 Then
strSQL2 = strSQL2 & " (((tblSvcCall.CustomerID)=" &
CStr(lngCust) & "))"
End If
End If

'Search By Employee
If Len(Me.EmployeeID & "") > 0 Then
lngTech = Me.EmployeeID
Flag = Flag + 1
Select Case Flag
Case Is = 1
strSQL2 = strSQL2 & " (((tblSvcCall.AssignedTo)=" &
CStr(lngTech) & "))"
Case Is = 2
strSQL2 = strSQL2 & " AND (((tblSvcCall.AssignedTo)=" &
CStr(lngTech) & "))"
End Select
End If

'Search By Appliance Type
If Len(Me.ApplianceType & "") > 0 Then
lngType = Me.ApplianceType
Flag = Flag + 1
Select Case Flag
Case 1
strSQL2 = strSQL2 & " (((tblSvcCall.ApplianceType)=" &
CStr(lngType) & "))"
Case Is > 1
strSQL2 = strSQL2 & " AND (((tblSvcCall.ApplianceType)=" &
CStr(lngType) & "))"
End Select
End If

'Other Criteria using identical syntax

Result:
strSQL2 = strSQL2 & ";"

If Flag >= 1 Then
DoCmd.SetWarnings False
Call MyTableUpdate
DoCmd.SetWarnings True
Application.Echo True
DoCmd.Close
DoCmd.OpenForm "SA_frm_FuzzySrch", , , , , acWindowNormal
Exit Sub
End If

NormalClose:
Application.Echo True
Exit Sub
ErrorSearch:
Msgbox "Error # " & Err.Number & " " & Err.Description
Resume NormalClose
End Sub

Private Sub MyTableUpdate()
On Error GoTo ErrorCU

Dim IntPos As Integer
Dim IntPos1 As Integer
Dim AccessConnect As String

IntPos = InStrRev(getdbPath(), "\", -1, vbTextCompare) - 1
CFPath = Left(getdbPath(), IntPos)
MyStr = MyAppPath()
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & MyStr
& "; DefaultDir=" & CFPath & ";" & _
"Uid=Admin;Pwd=;"

Dim strSQL As String
Dim oCmd As Command
Set oCmd = New Command
Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection

MyStr = CurrentDb.Name
strSQL = strSQL1 & MyStr & strSQL2

cnn.ConnectionString = AccessConnect

cnn.Open
With oCmd
.CommandText = strSQL
.CommandType = adCmdText
.ActiveConnection = cnn
Set oRS = .Execute
End With
With oRS

End With

Set oRS = Nothing
Set oCmd = Nothing
cnn.Close
Set cnn = Nothing

NormalClose:
DoCmd.SetWarnings True
Exit Sub
ErrorCU:
MsgBox "Error # " & Err.Number & " " & Err.Description
Resume NormalClose
End Sub
 
You're not explicitly closing your oRS recordset in your code, so add that
step just before you set it to Nothing.

Also, it may be a timing issue; insert this step after you call the
MyTableUpdate sub that does the oRS insert:
DoEvents

Also, it may be because you're using an ADO connection to the table. If your
new form is using a standard DAO connection, it's possible that the form is
not seeing the new data because the data were updated via a different
connection, and therefore you need to force the form to refresh its data. In
the new form's Load event procedure, add this line as the last line in the
procedure:
Me.Requery

Your code is a lot more complicated than what I'd use to insert data into a
linked table. Assuming that the table into which you're inserting the data
is in a backend file to which your frontend is located, I'd just build a
complete SQL statement for the insert query, and then execute it using the
CurrentDB object, such as you do in the first sub in your posted code.

I note that you're using strSQL1 and strSQL2 variables in the MyTableUpdate
sub, but they're not declared or set there nor do you pass the values for
them to the sub. Are these global variables in your form's module? If not,
how do you make their values avaiable to the MyTableUpdate sub?

--

Ken Snell
<MS ACCESS MVP>
 
If I add the line oRS.Close just before Setting oRS to Nothing it throws a
3074 Error - not allowed when object is closed. Not being familiar with ADO I
assumed that perhaps the Set oRS = .Execute line resulted in a transaction
that closed the recordset. Anyway, with the oRS.Close line it throws the
error.

strSQL1 and strSQL2 are global variables set in the declarations of the
module. Values are passed to it by the calling sub. i.e.

Private Sub FillAnyTable()
strSQL1 = "INSERT INTO tblAnyTable IN '"
strSQL2 = "' SELECT tblAnyTable.MyCriteriaFields FROM tblAnyTable WHERE
(((tblAnyTable.MyCriteriaField)=Whatever));"
Call MyTableUpdate
End Sub

Building the srtSQL1 & strSQL2 criteria strings doesn't seem much different
than for building a customer selected SQL string for the db.Execute DAO
insert. The resulting dataset is not intended for immediate editing/updating
in the current form.

I added the DoEvents line as you suggested, and the Me.Requery line to the
LOAD event of the Form that views the dataset. The table shows the data but
the form does not show data unless it is closed and then reopened. The
original form can remain open and the viewing form be closed and reopened and
the data is there, but a click event on the viewing form to requery the
subform does not make the data appear.
 
I don't have any other suggestion except that you consider simplifying the
code so that you use DAO methods to run the insert query so that you don't
have the ADO connection to the data as well as your DAO connection. Sorry.
 
Back
Top