On Load Event handler Question

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
LVL 4
billcuteAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
billcuteAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
What is your database platform and datatype of connsizetype?
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry I see you have above and believe you are using Access.
0
 
billcuteAuthor Commented:
Ms Access 2003.
datatype of connsizetype = number
Field Size ==>> double
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
I missed a space:
IN(0.75,1,1.5,2)

Should be:
IN (0.75,1,1.5,2)
0
 
billcuteAuthor Commented:
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
 
billcuteAuthor Commented:
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
 
billcuteAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
billcuteAuthor Commented:
mwvisa1:
Thanks for the additional info

Regards
Bill
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<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
 
billcuteAuthor Commented:
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
 
billcuteAuthor Commented:
Both answers were correct but accepted mwvisa1' answer - Points split 251 to 249 .
0
 
Kevin CrossChief Technology OfficerCommented:
Well done, Billcute.  Don't see that happen often, but is very appropriate. :)

Regards,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.