Solved

On Load Event handler Question

Posted on 2008-10-10
18
199 Views
Last Modified: 2010-04-21
What is the best way to load the following sizes at the "On Current Event Handler"
0.75, 1, 1.5 and 2 from tblConnSize.

tblConnSize
ConnSizeID         ConnSize       ConnSizeType
(Autonumber)        (Text)              (Text)
      1                         5/8                  0.625
      2                         1/2                  0.5
      3                         3/4                 0.75     '<<=== Size to be loaded
      4                          1                    1.0      '<<=== Size to be loaded
      5                        1-1/4               1.25
      6                        1-1/2               1.5       '<<=== Size to be loaded
      7                          2                    2.0      '<<=== Size to be loaded



Note:
Only sizes indicated above are to be loaded.

I tried the following but received a "Data Mismatch Error"

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype '0.75' Or '1' Or '1.5' Or '2' ORDER BY tblConnSize.ConnSizeType; "
cboConnSize.Requery
0
Comment
Question by:billcute
  • 8
  • 7
  • 2
18 Comments
 
LVL 57
ID: 22688838
You need:

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype = '0.75' Or tblconnsize.connsizetype =  '1' Or tblconnsize.connsizetype = '1.5' Or tblconnsize.connsizetype = '2' ORDER BY tblConnSize.ConnSizeType; "
0
 
LVL 4

Author Comment

by:billcute
ID: 22690686
I tested this and received
"Syntax Error (missing operator) in query expression:
'tblconnsize.connsizetype = '0.75' Or tblconnsize.connsizetype =  '1' Or tblconnsize.connsizetype = '1.5' Or tblconnsize.connsizetype = '2'
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22691757
What is your database platform and datatype of connsizetype?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22691769
Sorry I see you have above and believe you are using Access.
0
 
LVL 4

Author Comment

by:billcute
ID: 22691772
Ms Access 2003.
datatype of connsizetype = number
Field Size ==>> double
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22691777
Try like this:
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN('0.75','1','1.5','2') ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 251 total points
ID: 22691781
Well glad I asked then, as for number try this:
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.75,1,1.5,2) ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22691788
I missed a space:
IN(0.75,1,1.5,2)

Should be:
IN (0.75,1,1.5,2)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 4

Author Comment

by:billcute
ID: 22691816
In trying to open the combo (cboMainSize), I received an error message:

"The table tblConnSize is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated."

What do you think is wrong?
0
 
LVL 4

Author Comment

by:billcute
ID: 22691829
mwvisa1:
You were correct the first time. I tried your first posted solution with IN(0.75,1,1.5,2) and it worked.
Thanks
0
 
LVL 4

Author Comment

by:billcute
ID: 22691849
No space is required.
If I may ask you one more question?
What if I want to list the following:
0.625, 0.75
skip...1, 1.25, 1.50 and then....list all sizes greater than 2

this means:
IN(0.625,0.75) Or >2 such that it looked like this:

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.625,0.75); Or >2 ORDER BY tblConnSize.ConnSizeType;"

Is this possible?
 


0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22691924
Yes it is possible.
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.625,0.75) Or [tblConnSize].[ConnSizeType] > 2 ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 22692341
mwvisa1:
Thanks for the additional info

Regards
Bill
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 249 total points
ID: 22693627
<<Ms Access 2003.
datatype of connsizetype = number
Field Size ==>> double>>

Your initial post indicated this was text:

"tblConnSize
ConnSizeID         ConnSize       ConnSizeType
(Autonumber)        (Text)              (Text)"

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype = .75 Or tblconnsize.connsizetype =  1 Or tblconnsize.connsizetype = 1.5 Or tblconnsize.connsizetype = 2 ORDER BY tblConnSize.ConnSizeType; "

  Would have worked is well, although IN is certainly a lot more elagent.

JimD.
0
 
LVL 4

Author Comment

by:billcute
ID: 22693942
JDettman:
I just realized that youi were right. It was my fault originally.

The original question was 250 points as such I have asked CS to re-open the question and increase the points to 500 in order to be able to split the points.

Regards
Bill
0
 
LVL 4

Author Closing Comment

by:billcute
ID: 31505103
Both answers were correct but accepted mwvisa1' answer - Points split 251 to 249 .
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22694837
Well done, Billcute.  Don't see that happen often, but is very appropriate. :)

Regards,
Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now