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/DeviceReportGenForm_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 "_SanityResultQry" 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/DeviceReportGenForm_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 "_SanityResultQry" 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, "_SanityResultQry" 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, "_SanityResultQry" 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!

[lagomorphmom]

Wooo-hooo, works now! Many thanks to you both!!!

'nuther question. I'm a big fan of the Dummies books and got the Access book as my first resource. For my purposes, I like it fine, but it is very lacking in an area that sums up the syntax/punctuation of VBA. Is there an online resource for this or do I need a VBA book (but don't plan on writing any for my job).

Thanks again!

[JConsulting]

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!

I'll try to teach you at lease one good lesson using VBA.

Lesson 1
Put the word STOP in at the top of your sanity check function. Verbatim.
When the code executes, it will literally open the VBA window up and highlight the line. From there, you can "step" through the code. Use <F8> for this. It will advance the code, one line at a time. As you pause on each line, if you move your cursor over and hover over your variables and values, Access will pop-up values in yellow baloon boxes. You can check your code in this way to make sure that everything is working as it should. Now...

Lesson 2, there is a very useful tool inside the VBA window called the Immediate window. You can get to this by going to View/Immediate window. Open this. Move it out of your way until you get to the line in your code where it's defining your sql statement. "Step" one line past it.....then in the immediate window, type in ?sql and hit enter.
This will display the actual sql that we're building in the code.

Lesson 3.
Copy that sql out of the immediate window and paste it into a "New" query's SQL View. In doing this, you can test to see what you're building in a familiar window and figure out exactly what...or why your sql isn't returning the results you expect. If you find that you've made an error..you can correct it and copy the SQL again from this view and go back to your VBA window and paste it back in there and re-include your variables into it.

These three simple steps will give you some very powerful abilities to troubleshoot your code.

Let me know if you get stuck.
J

[Denburt]

Wooo-hooo, works now! Many thanks to you both!!!

'nuther question. I'm a big fan of the Dummies books and got the Access book as my first resource. For my purposes, I like it fine, but it is very lacking in an area that sums up the syntax/punctuation of VBA. Is there an online resource for this or do I need a VBA book (but don't plan on writing any for my job).

Thanks again!

Cool glad ya got it. Nice work J.

[lagomorphmom]

Thanks a bunch J, I've already got your lesson saved to a file and will try it tomorrow morning. The best part is the STOP command, because I couldn't step through the program to save my life (I did discover the Intermediate & Locals windows) using the Breakpoint. The Dummies book also suggested a debug.print "xyz" command, but that gets pretty tedious. Also great about pasting into a new query, will give that a go as well.

Thanks so much for your time!!!

[lagomorphmom]

Hi, J and Denbert - the more you know, the more you know you don't know....

I'm troubleshooting another form in the same database as above with the same 'changed a number field to text' problem. I went through your lessons, but could not get the F8 step to work in any way after the STOP command (and thus couldn't do Lesson 3) but was at least able to look at the qry in the Intermediate window using '?qry'(enter).

In a nutshell, I get the "Run-time error '3464': Data type mismatch in criteria expression". I know this is back to the format change of "IsolateID" from number to text, but for the life of me can't diddle the 's and "s in the "....IsolateID=" + CStr(IsolateID)...." part to fix it. Can you help? Here is the code, if I can get past this, I believe I can fix the rest of the code like it:

  sitevalqry = "Select site," + Drug + " as drugVal," + Drug + "L as drugL," + Drug + "G as drugG from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  minQry = "Select count(*) as mosCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "L=true and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  maxQry = "Select count(*) as mosCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "G=true and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  qryRoot = "Select count(" + Drug + ") as micCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "L=false and " + Drug + "G=false  and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID) + " and " + Drug

Thanks again!

[JConsulting]

Hi, J and Denbert - the more you know, the more you know you don't know....

I'm troubleshooting another form in the same database as above with the same 'changed a number field to text' problem. I went through your lessons, but could not get the F8 step to work in any way after the STOP command (and thus couldn't do Lesson 3) but was at least able to look at the qry in the Intermediate window using '?qry'(enter).

In a nutshell, I get the "Run-time error '3464': Data type mismatch in criteria expression". I know this is back to the format change of "IsolateID" from number to text, but for the life of me can't diddle the 's and "s in the "....IsolateID=" + CStr(IsolateID)...." part to fix it. Can you help? Here is the code, if I can get past this, I believe I can fix the rest of the code like it:

  sitevalqry = "Select site," + Drug + " as drugVal," + Drug + "L as drugL," + Drug + "G as drugG from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  minQry = "Select count(*) as mosCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "L=true and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  maxQry = "Select count(*) as mosCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "G=true and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID)
  qryRoot = "Select count(" + Drug + ") as micCount from mic_tbl where (" + BuildSitePred + ") and " + Drug + " is not null and " + Drug + "L=false and " + Drug + "G=false  and agarDilution=false and DoNotReport=false and Category='Bias/Reproducibility' and IsolateID=" + CStr(IsolateID) + " and " + Drug

Thanks again!

hey,
since you're declaring the IsolateID in the code as a string, then it's the field in the query that you need to format

and cstr([IsolateID]) ='" & IsolateID & "';"

and you really should consider changing the declared variable to a name that reflects what it is

dim strIsolateID as string

J

[lagomorphmom]

hey,
since you're declaring the IsolateID in the code as a string.....]

Don't paint me with that brush! <grin!>, you should say "since the guy before you" is declaring... ;-)
I had forgotten how very frustrating it is as an amateur to follow someone's style as well as code when all I have is my beginner references (and, of course you guys (much appreciated!!!)).

and you really should consider changing the declared variable to a name that reflects what it is

dim strIsolateID as string

Can I use "dim IsolateID as string" instead of "dim strIsolateID..."??? If it's just a programming reminder, I'm too ignorant still to know <gr>.

Say, before I make the changes, a thought. He didn't 'dim' IsolateID at all. If I dim as you have above, do I need to use the "cstr" anymore and instead use (something like):

and (IsolateID) ='" & IsolateID & "';"

Maybe that's what you implied with the dim reminder, just thought I should be clear.

Lastly, it's the little things that I can't find in my books. I finally found out what ! does, but can't find what the ; does. I notice you use it at the end of queries but 'he' did not. Is it better practice, what does it do???

More thanks,
Kerin

[JConsulting]

Don't paint me with that brush! <grin!>, you should say "since the guy before you" is declaring... ;-)
I had forgotten how very frustrating it is as an amateur to follow someone's style as well as code when all I have is my beginner references (and, of course you guys (much appreciated!!!)).



Can I use "dim IsolateID as string" instead of "dim strIsolateID..."??? If it's just a programming reminder, I'm too ignorant still to know <gr>.

Say, before I make the changes, a thought. He didn't 'dim' IsolateID at all. If I dim as you have above, do I need to use the "cstr" anymore and instead use (something like):

and (IsolateID) ='" & IsolateID & "';"

Maybe that's what you implied with the dim reminder, just thought I should be clear.

Lastly, it's the little things that I can't find in my books. I finally found out what ! does, but can't find what the ; does. I notice you use it at the end of queries but 'he' did not. Is it better practice, what does it do???

More thanks,
Kerin

You should try interpreting someone else's code, that was written by someone else...and being presented by someone that isn't that familiar with code!! :o)

You're using the IsolateID in your SQL as a variable.and you're right, I didn't see where it was declared...but I assumed it was text from somewhere. .and you mentioned that you'd converted the table's field to text...so it was rather confusing, but if you do declare it as string....or use Val(IsolateID) in your SQL statement to turn that field back into a number...either way, you'll get rid of the mismatch. I couldn't tell you which one is best...

as far as me putting the ; at the end of the SQL...it's a habit I got into writing SQL in VBA for Access. Your code was written by a SQL guy. That's why you have + instead of & which is used in Access VBA. they do the same thing, unless they are a part of an equation, but the way you're using them is fine.

Hope I haven't confused you further.
J

[lagomorphmom]

You should try interpreting someone else's code, that was written by someone else...and being presented by someone that isn't that familiar with code!! :o)

DOH!!! Kind of like reading a tech manual for a Chinese manufactured copy machine translated into English by someone Japanese???

either way, you'll get rid of the mismatch. I couldn't tell you which one is best...

Welll, either way there's the same number of corrections but at least by dim'ing IsolateID as string at the beginning it's not bass-ackwards.

as far as me putting the ; at the end of the SQL...it's a habit I got into writing SQL in VBA for Access. Your code was written by a SQL guy. That's why you have + instead of & which is used in Access VBA.

I see. I think I like the &s better, it's less visually confusing.

Hope I haven't confused you further.
J

Not at all. That will come when I try to find all the corrections! ;-)

[Denburt]

Can I use "dim IsolateID as string" instead of "dim strIsolateID..."??? If it's just a programming reminder, I'm too ignorant still to know <gr>.

Say, before I make the changes, a thought. He didn't 'dim' IsolateID at all. If I dim as you have above, do I need to use the "cstr" anymore and instead use (something like):

and (IsolateID) ='" & IsolateID & "';"

In the top of any module it is wise to use the statement "Option Explicit" This will help ensure all your variables are declared.

Option Compare Database
Option Explicit
 

In your query from what you posted it would appear that:
and (IsolateID) =
Would imply that IsolateID is a field name in your table. It is never a good idea to use the same name for a variable. This is one point J was trying to make also the fact that using strIsolateID will easily allow you as well as others to see that this is a string variable and not a field name. For similar reasons you would not want to use a single word for a variable for example, you can use strDate and it will not confuse MS Access since "Date()" is a function in MS Access and will give you the current date (the parens are removed automatically in VBA so it would look like Date).

[lagomorphmom]

In your query from what you posted it would appear that:
and (IsolateID) =
Would imply that IsolateID is a field name in your table. It is never a good idea to use the same name for a variable. This is one point J was trying to make also the fact that using strIsolateID will easily allow you as well as others to see that this is a string variable and not a field name.

I can see the wisdom of this and will keep what you both have said in mind as I continue this project.

However, I'm quite sure I will make a mess of it if I try to fix his field/variable name duplication at this point because IsolateID occurs in many, many places, including other forms and a couple of macros.

That said, thanks to you guys, I've got the form working again (woo-hoo!!!) I think that I will just have to be happy that it does work and not worry about his ugly code. Time is running short and I still have to fix his implementation of the mode & median before it will be complete.

I really can't thank you both enough for your time, I couldn't have done it on my own,
Kerin