By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

VBA failed query help

lagomorphmom
P: 13
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:

Expand|Select|Wrap|Line Numbers
  1. Sub SanityDoubleClick()
  2.   Dim ctlList As Control, varItem As Variant
  3.   Dim rslt, IsolateID, Site, qry As String
  4.   Dim intNumColumns, intI As Integer
  5.   Dim qd As QueryDef
  6.   Dim dbs As DAO.Database
  7.  
  8.   On Error Resume Next ' This gets us past the problem of tempQry not existing
  9.   Set dbs = CurrentDb
  10.   Set ctlList = [Screen].ActiveControl
  11.   intNumColumns = ctlList.ColumnCount
  12.   IsolateID = ctlList.Column(0)
  13.   Site = ctlList.Column(1)
  14.   qry = "Select * from MIC_Tbl where IsolateID=" + IsolateID + " and Site = '" + Site + "'"
  15.   DoCmd.DeleteObject acQuery, "_SanityResultQry"
  16.   Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
  17.   DoCmd.OpenQuery ("_SanityResultQry")
  18. 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.
May 10 '07 #1
Share this Question
Share on Google+
20 Replies


JConsulting
Expert 100+
P: 603
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:

Expand|Select|Wrap|Line Numbers
  1. Sub SanityDoubleClick()
  2.   Dim ctlList As Control, varItem As Variant
  3.   Dim rslt, IsolateID, Site, qry As String
  4.   Dim intNumColumns, intI As Integer
  5.   Dim qd As QueryDef
  6.   Dim dbs As DAO.Database
  7.  
  8.   On Error Resume Next ' This gets us past the problem of tempQry not existing
  9.   Set dbs = CurrentDb
  10.   Set ctlList = [Screen].ActiveControl
  11.   intNumColumns = ctlList.ColumnCount
  12.   IsolateID = ctlList.Column(0)
  13.   Site = ctlList.Column(1)
  14.   qry = "Select * from MIC_Tbl where IsolateID=" + IsolateID + " and Site = '" + Site + "'"
  15.   DoCmd.DeleteObject acQuery, "_SanityResultQry"
  16.   Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
  17.   DoCmd.OpenQuery ("_SanityResultQry")
  18. 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

Expand|Select|Wrap|Line Numbers
  1.   qry = "Select * from MIC_Tbl where IsolateID='" & IsolateID & "' and Site = '" & Site & "';"
  2.  
  3.  
May 11 '07 #2

lagomorphmom
P: 13
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!!!

Expand|Select|Wrap|Line Numbers
  1. Function IsDataInsane(AllBugs As Boolean) As Boolean
  2.   Dim dbs As DAO.Database
  3.   Dim rst As DAO.Recordset
  4.   Dim qry, rsltStr, aStr As String
  5.   IsDataInsane = False
  6.   SanityCheck.OnDblClick = "SanityDoubleClick"
  7.   SanityCheck.RowSource = ""
  8.   aStr = "IsolateID;Site;Record Count"
  9.   SanityCheck.AddItem (aStr)
  10.   Set dbs = CurrentDb
  11.  
  12.   If (AllBugs) Then
  13.     qry = BuildHistogramQry(True, True)
  14.   Else
  15.     qry = BuildHistogramQry(False, True)
  16.   End If
  17.  
  18.   Set rst = dbs.OpenRecordset(qry)
  19.   SCCount.Value = "(" + CStr(rst.RecordCount) + ")"
  20.   If (rst.RecordCount > 0) Then
  21.     IsDataInsane = True
  22.   Else
  23.     Exit Function
  24.   End If
  25.   rst.MoveFirst
  26.   Do While Not rst.EOF
  27.     SanityCheck.AddItem (CStr(rst!IsolateID) + ";" & rst!Site & ";" & CStr(rst!madness))
  28.     rst.MoveNext
  29.   Loop
  30.   SanityCheck.Value = rsltStr
  31. End Function
May 14 '07 #3

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. SanityCheck.AddItem (CStr(NZ(rst!IsolateID,"")) + ";" & NZ(rst!Site,"") & ";" & CStr(NZ(rst!madness,"")))
Good luck hope it helps.
May 14 '07 #4

JConsulting
Expert 100+
P: 603
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!!!

Expand|Select|Wrap|Line Numbers
  1. Function IsDataInsane(AllBugs As Boolean) As Boolean
  2. Dim dbs As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim qry, rsltStr, aStr As String
  5. IsDataInsane = False
  6. SanityCheck.OnDblClick = "SanityDoubleClick"
  7. SanityCheck.RowSource = ""
  8. aStr = "IsolateID;Site;Record Count"
  9. SanityCheck.AddItem (aStr)
  10. Set dbs = CurrentDb
  11.  
  12. If (AllBugs) Then
  13. qry = BuildHistogramQry(True, True)
  14. Else
  15. qry = BuildHistogramQry(False, True)
  16. End If
  17.  
  18. Set rst = dbs.OpenRecordset(qry)
  19. SCCount.Value = "(" + CStr(rst.RecordCount) + ")"
  20. If (rst.RecordCount > 0) Then
  21. IsDataInsane = True
  22. Else
  23. Exit Function
  24. End If
  25. rst.MoveFirst
  26. Do While Not rst.EOF
  27. SanityCheck.AddItem (CStr(rst!IsolateID) + ";" & rst!Site & ";" & CStr(rst!madness))
  28. rst.MoveNext
  29. Loop
  30. SanityCheck.Value = rsltStr
  31. End Function

Expand|Select|Wrap|Line Numbers
  1. Sub SanityDoubleClick()
  2. Dim ctlList As control, varItem As Variant
  3. Dim rslt, IsolateID, Site, qry As String
  4. Dim intNumColumns, intI As Integer
  5. Dim qd As QueryDef
  6. Dim dbs As DAO.DataBase
  7.  
  8. On Error Resume Next ' This gets us past the problem of tempQry not existing
  9. Set dbs = CurrentDb
  10. Set ctlList = [Screen].ActiveControl
  11. intNumColumns = ctlList.ColumnCount
  12. If Nz(ctlList.Column(0), "") = "" Or Nz(ctlList.Column(1), "") = "" Then
  13. MsgBox " You must select an item from the List"
  14. Exit Sub
  15. 'qry = "Select * from MIC_Tbl;" '<--Uncomment to return all records
  16. Else
  17. IsolateID = ctlList.Column(0)
  18. Site = ctlList.Column(1)
  19. qry = "Select * from MIC_Tbl where IsolateID='" & IsolateID & "' and Site = '" & Site & "';"
  20. End If
  21. DoCmd.DeleteObject acQuery, "_SanityResultQry"
  22. Set qd = dbs.CreateQueryDef("_SanityResultQry", qry)
  23. DoCmd.OpenQuery ("_SanityResultQry")
  24. End Sub
  25.  
May 14 '07 #5

lagomorphmom
P: 13
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.
May 14 '07 #6

lagomorphmom
P: 13
[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.
May 14 '07 #7

JConsulting
Expert 100+
P: 603
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 & "';"
May 14 '07 #8

JConsulting
Expert 100+
P: 603
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
May 14 '07 #9

lagomorphmom
P: 13
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!
May 14 '07 #10

lagomorphmom
P: 13
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!
May 14 '07 #11

JConsulting
Expert 100+
P: 603
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
May 15 '07 #12

Denburt
Expert 100+
P: 1,356
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.
May 15 '07 #13

lagomorphmom
P: 13
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!!!
May 16 '07 #14

lagomorphmom
P: 13
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:

Expand|Select|Wrap|Line Numbers
  1.   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)
  2.   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)
  3.   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)
  4.   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!
May 29 '07 #15

JConsulting
Expert 100+
P: 603
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:

Expand|Select|Wrap|Line Numbers
  1.   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)
  2.   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)
  3.   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)
  4.   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
Jun 1 '07 #16

lagomorphmom
P: 13
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
Jun 1 '07 #17

JConsulting
Expert 100+
P: 603
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
Jun 1 '07 #18

lagomorphmom
P: 13
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! ;-)
Jun 2 '07 #19

Denburt
Expert 100+
P: 1,356
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.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
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).
Jun 4 '07 #20

lagomorphmom
P: 13
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
Jun 5 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.