VBA failed query help

[lagomorphmom]

Hi, I'm a microbiologist but also dabble with many of the computer needs of our little lab. My latest project involves an unfinished database (Access 2000 format, I'm running 2003 on XP PC) inherited from a vendor. Although I've not used Access before, I've been plugged along with the help of our data manager (currently out with hip surgery) and guess I'm an intermediate user now. I do not know VBA although several misc. programming classes (ack in college when Lincoln was a boy) have helped me do what I have needed to so far in that regard, but now I have (I think) a VBA syntax problem I cannot solve.

The problem is with a form which statistically analyzes data pairs. For easier understanding, you can view the form at, http://i55.photobucket .com/albums/g160/lagomorphmom/DeviceReportGen Form_jpg-1.jpg
The large grid square is pretty trick (for me) because clicking on any field produces a query containing the table data for the data pairs in that field - great for editing data. The small table ('Sanity Checks') to the bottom right (in a tempqry made when generating the form) shows single, unmatched data and upon double click, you can see the table data for these results as well. The whole form seems to have been designed in VBA as the only 'build items' are the three clickable buttons on the lower left.

The problem is with the Sanity Checks table - double-click is now unresponsive. It did work originally, and after some troubleshooting , I think the problem was when I had to change the IsolateID column data from number to text format because of number overlap between sites (this made the top data table not work correctly). The code for the unmatched data double-click is:

Sub SanityDoubleClick()
  Dim ctlList As Control, varItem As Variant
  Dim rslt, IsolateID, Site, qry As String
  Dim intNumColumns, intI As Integer
  Dim qd As QueryDef
  Dim dbs As DAO.Database
 
  On Error Resume Next ' This gets us past the problem of tempQry not existing
  Set dbs = CurrentDb
  Set ctlList = [Screen].ActiveControl
  intNumColumns = ctlList.ColumnCount
  IsolateID = ctlList.Column(0)
  Site = ctlList.Column(1)
  qry = "Select * from MIC_Tbl where IsolateID=" + IsolateID + " and Site = '" + Site + "'"
  DoCmd.DeleteObject acQuery, "_SanityResultQry"
  Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
  DoCmd.OpenQuery ("_SanityResultQry")
End Sub

I believe his 'On Error next...' line is outdated as both tables generate temp qry's successfully (when IsolateID is numeric). After the Preview button is run, looking in the master query list, the "_SanityResultQ ry" from the last run has been deleted, but the new one does not generate nor obviously open. When IsolateID is text, the upper table still works which is why I thought the problem must be with the "qry =" line sytax above, but I know not what I know not.

Any help here most appreciated, my head hurts! I will be out of the lab until Monday if I've left anything out.

[JConsulting]

Hi, I'm a microbiologist but also dabble with many of the computer needs of our little lab. My latest project involves an unfinished database (Access 2000 format, I'm running 2003 on XP PC) inherited from a vendor. Although I've not used Access before, I've been plugged along with the help of our data manager (currently out with hip surgery) and guess I'm an intermediate user now. I do not know VBA although several misc. programming classes (ack in college when Lincoln was a boy) have helped me do what I have needed to so far in that regard, but now I have (I think) a VBA syntax problem I cannot solve.

The problem is with a form which statistically analyzes data pairs. For easier understanding, you can view the form at, http://i55.photobucket .com/albums/g160/lagomorphmom/DeviceReportGen Form_jpg-1.jpg
The large grid square is pretty trick (for me) because clicking on any field produces a query containing the table data for the data pairs in that field - great for editing data. The small table ('Sanity Checks') to the bottom right (in a tempqry made when generating the form) shows single, unmatched data and upon double click, you can see the table data for these results as well. The whole form seems to have been designed in VBA as the only 'build items' are the three clickable buttons on the lower left.

The problem is with the Sanity Checks table - double-click is now unresponsive. It did work originally, and after some troubleshooting , I think the problem was when I had to change the IsolateID column data from number to text format because of number overlap between sites (this made the top data table not work correctly). The code for the unmatched data double-click is:

Sub SanityDoubleClick()
  Dim ctlList As Control, varItem As Variant
  Dim rslt, IsolateID, Site, qry As String
  Dim intNumColumns, intI As Integer
  Dim qd As QueryDef
  Dim dbs As DAO.Database
 
  On Error Resume Next ' This gets us past the problem of tempQry not existing
  Set dbs = CurrentDb
  Set ctlList = [Screen].ActiveControl
  intNumColumns = ctlList.ColumnCount
  IsolateID = ctlList.Column(0)
  Site = ctlList.Column(1)
  qry = "Select * from MIC_Tbl where IsolateID=" + IsolateID + " and Site = '" + Site + "'"
  DoCmd.DeleteObject acQuery, "_SanityResultQry"
  Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
  DoCmd.OpenQuery ("_SanityResultQry")
End Sub

I believe his 'On Error next...' line is outdated as both tables generate temp qry's successfully (when IsolateID is numeric). After the Preview button is run, looking in the master query list, the "_SanityResultQ ry" from the last run has been deleted, but the new one does not generate nor obviously open. When IsolateID is text, the upper table still works which is why I thought the problem must be with the "qry =" line sytax above, but I know not what I know not.

Any help here most appreciated, my head hurts! I will be out of the lab until Monday if I've left anything out.

Change this line
J

  qry = "Select * from MIC_Tbl where IsolateID='" & IsolateID & "' and Site = '" & Site & "';"
 
 

[lagomorphmom]

Thanks, J, for helping. I plugged in the new "qry=...". That lead to a "Run Time Error 94: Incorrect use of Null" in the following function, I highlighted the line the debugger stops at with bold... Again, help here is really appreciated!!!

Function IsDataInsane(AllBugs As Boolean) As Boolean
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim qry, rsltStr, aStr As String
  IsDataInsane = False
  SanityCheck.OnDblClick = "SanityDoubleClick"
  SanityCheck.RowSource = ""
  aStr = "IsolateID;Site;Record Count"
  SanityCheck.AddItem (aStr)
  Set dbs = CurrentDb
 
  If (AllBugs) Then
    qry = BuildHistogramQry(True, True)
  Else
    qry = BuildHistogramQry(False, True)
  End If
 
  Set rst = dbs.OpenRecordset(qry)
  SCCount.Value = "(" + CStr(rst.RecordCount) + ")"
  If (rst.RecordCount > 0) Then
    IsDataInsane = True
  Else
    Exit Function
  End If
  rst.MoveFirst
  Do While Not rst.EOF
    SanityCheck.AddItem (CStr(rst!IsolateID) + ";" & rst!Site & ";" & CStr(rst!madness))
    rst.MoveNext
  Loop
  SanityCheck.Value = rsltStr
End Function

[Denburt]

One of the following is probably a null value. I did not see any checks to prevent this and if you use cstr() on a null value you will get this error, so here is one method.

SanityCheck.AddItem (CStr(NZ(rst!IsolateID,"")) + ";" & NZ(rst!Site,"") & ";" & CStr(NZ(rst!madness,"")))

Good luck hope it helps.

[JConsulting]

Thanks, J, for helping. I plugged in the new "qry=...". That lead to a "Run Time Error 94: Incorrect use of Null" in the following function, I highlighted the line the debugger stops at with bold... Again, help here is really appreciated!!!

Function IsDataInsane(AllBugs As Boolean) As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qry, rsltStr, aStr As String
IsDataInsane = False
SanityCheck.OnDblClick = "SanityDoubleClick"
SanityCheck.RowSource = ""
aStr = "IsolateID;Site;Record Count"
SanityCheck.AddItem (aStr)
Set dbs = CurrentDb
 
If (AllBugs) Then
qry = BuildHistogramQry(True, True)
Else
qry = BuildHistogramQry(False, True)
End If
 
Set rst = dbs.OpenRecordset(qry)
SCCount.Value = "(" + CStr(rst.RecordCount) + ")"
If (rst.RecordCount > 0) Then
IsDataInsane = True
Else
Exit Function
End If
rst.MoveFirst
Do While Not rst.EOF
 SanityCheck.AddItem (CStr(rst!IsolateID) + ";" & rst!Site & ";" & CStr(rst!madness))
rst.MoveNext
Loop
SanityCheck.Value = rsltStr
End Function

Sub SanityDoubleClick()
Dim ctlList As control, varItem As Variant
Dim rslt, IsolateID, Site, qry As String
Dim intNumColumns, intI As Integer
Dim qd As QueryDef
Dim dbs As DAO.DataBase
 
On Error Resume Next ' This gets us past the problem of tempQry not existing
Set dbs = CurrentDb
Set ctlList = [Screen].ActiveControl
intNumColumns = ctlList.ColumnCount
If Nz(ctlList.Column(0), "") = "" Or Nz(ctlList.Column(1), "") = "" Then
MsgBox " You must select an item from the List"
Exit Sub
'qry = "Select * from MIC_Tbl;" '<--Uncomment to return all records
Else
IsolateID = ctlList.Column(0)
Site = ctlList.Column(1)
qry = "Select * from MIC_Tbl where IsolateID='" & IsolateID & "' and Site = '" & Site & "';"
End If
DoCmd.DeleteObject acQuery, "_SanityResultQry"
Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
DoCmd.OpenQuery ("_SanityResultQry")
End Sub
 

[lagomorphmom]

One of the following is probably a null value.

My bad. I'm really sorry, the null issue is a 'null issue'. I had been testing with a master data table in which I had changed the IsolateID text format back to numbers (and thus taken out all of the alpha-numeric values (but not their records)) so I had empty fields in this column and forgot to switch back to the complete table.

So back to J's answer: that did help, "_SanityResultQ ry" is now generated again with the format for IsolateID as text!

But, the qry is empty (in the picture example, by double clicking on the first line in the Sanity Checks, the qry should have two records containing the data pairs for "1026_MSI") . In Design View, the qry is two columns, "IsolateID" and "Site" with the criteria in this case of "1026" and "MSI", if that helps.

[lagomorphmom]

[code]
'qry = "Select * from MIC_Tbl;" '<--Uncomment to return all records

I know not what I know not about that. I can say that when it was working, it only returned the unmatched (unpaired) records.

[JConsulting]

My bad. I'm really sorry, the null issue is a 'null issue'. I had been testing with a master data table in which I had changed the IsolateID text format back to numbers (and thus taken out all of the alpha-numeric values (but not their records)) so I had empty fields in this column and forgot to switch back to the complete table.

So back to J's answer: that did help, "_SanityResultQ ry" is now generated again with the format for IsolateID as text!

But, the qry is empty (in the picture example, by double clicking on the first line in the Sanity Checks, the qry should have two records containing the data pairs for "1026_MSI") . In Design View, the qry is two columns, "IsolateID" and "Site" with the criteria in this case of "1026" and "MSI", if that helps.

if the value in IsolateID is numeric then you just need to take the tick marks out from around it in the SQL statement

ie

qry = "Select * from MIC_Tbl where IsolateID=" & IsolateID & " and Site = '" & Site & "';"

[JConsulting]

I know not what I know not about that. I can say that when it was working, it only returned the unmatched (unpaired) records.

I did not know what you wanted to do if the values were null...so I gave the code a message box and an exit...so it wouldn't return anything except a message to the user. I left the sql line there commented in case you wanted to return ALL records from that table. Testing for null, you either return the user to step 1 telling him what he did wrong, or you consider the choice to NOT select is valid, and return everything.
J

[lagomorphmom]

if the value in IsolateID is numeric then you just need to take the tick marks out from around it in the SQL statement

No, you were correct, it is text (it was numeric but I had to change it to text to resolve duplicate IsolateID numbers from multiple sites; when I changed to text, I broke the Sanity Checks table).

DoubleClick is now fixed enough to generate the qry, the problem is that it doesn't fill the qry with all records whose IsolateID number matches that of the double-clicked line in the Sanity Checks result table, NOR does it take the next step to open the qry.

I just had a childhood flashback, "Are we almost there yet???" Arggg!
Thanks!