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?

[Webinar] Streamline your web hosting managementRegister Today

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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
All Courses

From novice to tech pro — start learning today.