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

Select Case Statement with ComboBox

P: 56
I am very new at this and trying to learn. I have built a database and on my main form, I have a combo box titled cbodocumenttype, a text box for user input entitled txtuserinput, and a command button entitle cmdsearch. I want to write a select case statment event procedure on the cmdsearch button that will get the document type from the combo box (the document type is a form that is linked to it's own table) get the value the user types (all or part of the value) (a field from the table) from the text box, open the selected (form/table) and filter by the user typed value. Close the Main form. If there is a null value in the text box, diplay a msgbox "Document Number Required" and if there is a null value in the combo box, display a msgbox "Document Type Required". I have been trying to write a code for this for 2 weeks now. I am now at my wits end.
PLEASE HELP....NEWBE
Apr 9 '09 #1
Share this Question
Share on Google+
44 Replies


Expert 100+
P: 1,287
What part of the code are you having trouble with?
Apr 9 '09 #2

P: 56
I Guess all of it. This is all I have so far:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch()
  2.  
  3. Dim stnumber As String
  4.  
  5.  
  6. 'Trying to reference "documenttype" from the combo box "cbodocumenttype"
  7.  
  8.   Select Case Document Type
  9.  
  10.    Case "RELEASES"
  11.          If Me.cbodocument.Value = RELEASES Then
  12.             If Me.txtuserinput.Value(" & ") Then
  13.             docmdOpenForm RELEASES
  14.  
  15.         Case Else
  16.             If cbodocumenttype.Value = Null Then
  17.             MsgBox "DOCUMENT TYPE REQUIRED"
  18.             If txtuserinput.Value = Null Then
  19.             MsgBox "DOCUMENT NUMBER REQUIRED"
  20.     End Select
  21. End If
Apr 9 '09 #3

Expert 100+
P: 1,287
See if this example code makes sense:
Expand|Select|Wrap|Line Numbers
  1. If isNull(cboDocumentType) or cboDocumentType = "" Then
  2.   msgBox "Document Type is required."
  3.   exit Sub
  4. End If
  5.  
  6. If isNull(txtUserInput) or txtUserInput = "" Then
  7.   msgBox "Document Number is required."
  8.   exit Sub
  9. End If
  10.  
  11. Select Case cboDocumentType
  12. Case is = "RELEASES"
  13.   DoCmd.OpenForm "Releases", , , "[nameOfYourField] = " & txtUserInput
  14. ...
  15. Case Else
  16.   MsgBox "Error, unexpected Document Type!"
  17. End Select
Apr 9 '09 #4

P: 56
That looks awsome. but I ran it in my database after adding to it. Here is what I added:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch()
  2.  
  3. If IsNull(cboDocumentType) Or cboDocumentType = "" Then
  4.   MsgBox "Document Type is required."
  5.   Exit Sub
  6. End If
  7.  
  8. If IsNull(txtuserinput) Or txtuserinput = "" Then
  9.   MsgBox "Document Number is required."
  10.   Exit Sub
  11. End If
  12.  
  13. Select Case cboDocumentType
  14. Case Is = "RELEASES"
  15.   DoCmd.OpenForm "Releases", , , "[NUMBER] = " & txtuserinput
  16.  
  17. Case Is = "ASSEMBLY DRAWINGS"
  18.   DoCmd.OpenForm "ASSEMBLY DRAWINGS", , , "[NUMBER] = " & txtuserinput
  19.  
  20. Case Is = "EXTRUSION DRAWINGS"
  21.   DoCmd.OpenForm "EXTRUSION DRAWINGS", , , "[NUMBER] = " & txtuserinput
  22.  
  23. Case Is = "EXTRUSION ASSEMBLY DRAWINGS"
  24.   DoCmd.OpenForm "EXTRUSION ASSEMBLY DRAWINGS", , , "[NUMBER] = " & txtuserinput
  25.  
  26. Case Is = "FABRICATION DRAWINGS"
  27.   DoCmd.OpenForm "FABRICATION DRAWINGS", , , "[NUMBER] = " & txtuserinput
  28.  
  29. Case Is = "PART DRAWINGS"
  30.   DoCmd.OpenForm "PART DRAWINGS", , , "[NUMBER] = " & txtuserinput
  31.  
  32. Case Is = "INSTRUCTIONS"
  33.   DoCmd.OpenForm "INSTRUCTIONS", , , "[NUMBER] = " & txtuserinput
  34.  
  35. Case Else
  36.   MsgBox "Error, unexpected Document Type!"
  37. End Select
  38.  
  39.  
  40. End Sub
But I got this error:

"The expression On Click you entered as the event property setting produced the following error:
Member already exisits in an object module from which this object module derives.

????I don't have any Modules in my database????
Apr 9 '09 #5

Expert 100+
P: 1,287
Private Sub cmdSearch() is not the name of the On Click event of the command button. Go to the command button's properties and the On Click event and see what code is there. You can put this code there instead.
Apr 9 '09 #6

P: 56
oooh...I am getting closer. I fixed That, But when I have a document type selected in the combobox I am getting the error message "Document Type Required" so It is not recognizing the value in the combo box. It must be returning a null value. Any Ideas?
Apr 9 '09 #7

Expert 100+
P: 1,287
Do you have, at the very top of your code, before the beginning of any functions:
Option Explicit
Apr 9 '09 #8

Expert 100+
P: 1,287
Lunchtime, but I'll check back later.
Apr 9 '09 #9

P: 56
No I don't but I tried putting it there and there was no difference. I noticed that my combobox and textbox are unbound. Could this be the problem? I don't know how to bind them though.
Apr 9 '09 #10

Expert 100+
P: 1,287
No, you have it right, they shouldn't be bound to any part of your record. That would just mean that changing a value in your text box would change the data in the record. That's not what you're trying to do, so they should be unbound.
It's still not obvious to me why there woulndn't be a value for the combo box. Try moving the If...End If section related to the text box to the top above the one for the combo box, so that it gets checked first, and see what you get.
Apr 9 '09 #11

P: 56
Tried moving the text box code above the combo box code and both give the same result with the "document type required message"

Doesn't this statement mean if there is a null value then launch the message and if there is a value launch the message? Don't the "" mean what ever is between the quotes?

If IsNull(cboDocumentType) Or cboDocumentType = "" Then
MsgBox "Document Type is required."
Apr 9 '09 #12

Expert 100+
P: 1,287
"" means an empty string, nothing else. You can see which is the case by doing:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(cboDocumentType) then MsgBox "Document Type is null"
  2. If cboDocumentType= "" then MsgBox "Document Type is blank."
Apr 9 '09 #13

P: 56
Oh...Okay....Does the fact that the options in the combobox are a rowsourse of the combobox properties? Or do I have that correct?
Apr 9 '09 #14

Expert 100+
P: 1,287
Do you have values to select from in the dropdown for the combo box?
Your Row Source will either be a list of values with a Row Source Type of Value List that you typed in or a SELECT from a table with a Row Source Type of Table/Query. Whatever you want your users to select from. I usually set Limit To List = Yes and Allow Value List Edits = No. You don't want the user to pick anything other than the options you provide.
Apr 9 '09 #15

P: 56
I don't have an "Allow List Edits" property. I did set the other one though. I appreciate all your help. I am way further along now than I was. Just hope I can get the code to see the value in the combobox now. Thanks
Apr 9 '09 #16

Expert 100+
P: 1,287
Glad to help. Frustrating though, not to be able to figure out what the problem is that's hanging around.
Here's an idea. Make a new text box on your form. In the AfterUpdate event of the cboDocumentType combo box, put a line
txtNewBox = cboDocumentType
Now, when you select something from the dropdown of the combo box, it should show up immediately in the text box. If not, that will help narrow down the problem.
Apr 9 '09 #17

P: 56
I took out the code for null in the combobox and now I am getting a "enter parameter error and during debugging it stopped on this line. I think something is wrong with this code.

DoCmd.OpenForm "RELEASES", , , "[NUMBER] = " & txtuserinput"
Apr 9 '09 #18

Expert 100+
P: 1,287
I think you can get rid of the [ ]. "Number" should be the name of the field in the table that form is bound to right? Also, no " at the end.
Apr 9 '09 #19

P: 56
okay...I did all that and I made the new text box. When I select a value from the combobox, I now get an error that reads "Miscosoft Access can't find the macro 'txtnewbox=cobdocumenttype'
Apr 9 '09 #20

Expert 100+
P: 1,287
You need spaces in there around the =, and check spelling of the control names.

The good thing about Option Explicit is it will usually tell you before hand if you spell something wrong, if you go and hit the compile button in the editor.

Another thing I find very helpful is, if you make the names of your controls with a certain capitalization, the editor will autocorrect them when you finish a line. If the name of your combo box is cboDocumentType and you type cbodocumenttype, when you hit enter it will fix it. But if you type cobdocumenttype, it will leave it in lower case and you will notice immediately.
Apr 9 '09 #21

FishVal
Expert 2.5K+
P: 2,653
@Desitech
Apparently, you've put it in AfterUpdate property. It should be in event handling code (sub cboDocumentType_AfterUpdate) while cboDocumentType.AfterUpdate property should be set to "[Event Procedure]".
Apr 9 '09 #22

Expert 100+
P: 1,287
Actually, FishVal, that part was supposed to be in the AfterUpdate event code of the combo box; but yes, it should be in an Event Procedure and I didn't realize from the error that it was not.
Apr 9 '09 #23

FishVal
Expert 2.5K+
P: 2,653
Yes, my bad.
I've corrected it to not mess Desitech.
Apr 9 '09 #24

P: 56
This is what I have in the combobox event code for the txtnewbox.

Private Sub cbodocumenttpye_AfterUpdate()

txtnewbox = cobdocumenttype

End Sub
The new text box does not return a value.

I also chaged this line of code.

Select Case cboDocumentType

Case Is = cboDocumentType = "RELEASES"
DoCmd.OpenForm "RELEASES", , , "NUMBER" = "*" & "*" & txtuserinput

It now opens the form but only the RELEASES form. no other. and with no records.
Apr 9 '09 #25

Expert 100+
P: 1,287
If Access generated that sub for AfterUpdate, then you have your combo box spelled as cbodocumenttpye.
The inconsistent capitalization, if you copied this code exactly, means that you have spelled things wrong. This is going to cause problems until you fix it.
What's with the "*" & "*" ?
Apr 9 '09 #26

P: 56
I did catch the misspelling of cobdocumenttype. I do not have any capitalization in my naming like txtnewbox or cobdocumenttype. Should it be CobDocumentType? I didn't think it would matter as long as everything was consistant. As far as the "*" & "*". I was told that this would work if you want the user to enter the whole field, start of the field, or part of the field. Am I wrong?
Apr 9 '09 #27

Expert 100+
P: 1,287
It won't matter if everything is spelled correctly, but like I said, it's much easier to tell if they are spelled correctly if you use the auto-capitalization trick.

The * is a wildcard for the LIKE condition, and it depends what part you want to match.
So, you can use any of:
Expand|Select|Wrap|Line Numbers
  1. "LIKE *" & txtUserInput        'Ends with what the user typed in
  2. "LIKE " & txtUserInput & "*"   'Starts with what the user typed in
  3. "LIKE *" & txtUserInput & "*"  'Contains what the user typed in 
Edit: Sorry I mean use LIKE instead of =
Apr 9 '09 #28

P: 56
Okay...I found another typing error of mine. I had cbodocumenttype spelled cobdocumenttype. The value now DOES populate the txtnewbox when selected. Now what?
Apr 9 '09 #29

Expert 100+
P: 1,287
If you use
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Releases", , , "NUMBER LIKE '" & txtuserinput & "*'"
you should see all the records whose NUMBER field starts with the one you type in. Sorry I just realized also that you need ' ' around the string after LIKE, even for numbers.
Apr 9 '09 #30

P: 56
Do you mean 'LIKE'

Like that. Here is each case looks now:

Case Is = cbodocumenttype = "RELEASES"
DoCmd.OpenForm "RELEASES", , , "NUMBER 'LIKE' " & txtuserinput

It is running to the end.

I removed the first msgbox code, and now the msgbox code with the "document number reqired" code is working.

But none of the cases are working and I am getting the "Error, unexpected Document Type!"
Apr 9 '09 #31

Expert 100+
P: 1,287
So each case should be:
Expand|Select|Wrap|Line Numbers
  1. Case Is = "RELEASES"
  2. DoCmd.OpenForm "Releases", , , "NUMBER LIKE '" & txtuserinput & "'*"
RELEASES
ASSEMBLY DRAWINGS
EXTRUSION DRAWINGS
EXTRUSION ASSEMBLY DRAWINGS
FABRICATION DRAWINGS
PART DRAWINGS
INSTRUCTIONS

These are the options listed when you click the dropdown in your combo box, and also the names of your forms?
Apr 9 '09 #32

P: 56
Yes Correct. This Still doesn't work though. Does it matter that my "NUMBER" field, is not a number but a value something like this?

ER-2001-001 or A-KI7500-5 or 12316 or F-12316-12820DD-4?
Apr 9 '09 #33

Expert 100+
P: 1,287
No, that doesn't matter. You said you were getting the Unexpected Document Type message. If that's still true, then you aren't matching your Case with your combo box value correctly. Put back in the
Expand|Select|Wrap|Line Numbers
  1. MsgBox cboDocumentType
right before the Select Case and see what it is and why it doesn't match.
Apr 9 '09 #34

P: 56
Okay...I put it back in and both messages are working correctly. If I leave the ComboBox empty I get the "Document Type Required" message. If I pick RELEASES from the list in the Combo Box and leave the Document number (userinputbox) blank, I get the "Document Number Required" message. When I select RELEASES from the Combobox and enter a value in the userinput box I get the "ERROR...Unexpected Document Type" message. It must be something in the Cases code. Do I need to make a string statement of some kind?
Apr 9 '09 #35

Expert 100+
P: 1,287
What is the result of
Expand|Select|Wrap|Line Numbers
  1. MsgBox cboDocumentType
You can also do
Expand|Select|Wrap|Line Numbers
  1. MsgBox "Combo box = " & cboDocumentType
Apr 9 '09 #36

P: 56
Do I need a control source on the Combo Box or Text Box ? This property is blank in both.
Apr 9 '09 #37

P: 56
I have to leave now. Can we pick this up tomorrow? Here is the code I have so far:

Private Sub cmdSearch_Click()

If IsNull(cbodocumenttype) Or cbodocumenttype = "" Then
MsgBox "Document Type is required."

Exit Sub
End If
If IsNull(txtuserinput) Or txtuserinput = "" Then
MsgBox "Document Number is required."

Exit Sub
End If
Select Case cbodocumenttype

Case Is = cbodocumenttype = "RELEASES"
DoCmd.OpenForm "RELEASES", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "ASSEMBLY DRAWINGS"
DoCmd.OpenForm "ASSEMBLY DRAWINGS", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "EXTRUSION DRAWINGS"
DoCmd.OpenForm "EXTRUSION DRAWINGS", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "EXTRUSION ASSEMBLY DRAWINGS"
DoCmd.OpenForm "EXTRUSION ASSEMBLY DRAWINGS", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "FABRICATION DRAWINGS"
DoCmd.OpenForm "FABRICATION DRAWINGS", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "PART DRAWINGS"
DoCmd.OpenForm "PART DRAWINGS", , , "NUMBER like '" & txtuserinput

Case Is = cbodocumenttype = "INSTRUCTIONS"
DoCmd.OpenForm "INSTRUCTIONS", , , "NUMBER like '" & txtuserinput

Case Else
MsgBox "Error, unexpected Document Type!"
End Select

Exit Sub

End Sub
Apr 9 '09 #38

FishVal
Expert 2.5K+
P: 2,653
Is = cboDocumentType = "RELEASES"
will never evaluate to True. No wonder it always executes Case Else branch.
Does it matter that my "NUMBER" field, is not a number but a value something like this?

ER-2001-001 or A-KI7500-5 or 12316 or F-12316-12820DD-4?
It does matter when you construct WhereCondition of DoCmd.OpenForm method. As soon as [NUMBER] field is apparently text type, constant it is being compared with should be enclosed in quotes.
Apr 9 '09 #39

P: 56
I don't understand what is the constant?
Apr 9 '09 #40

FishVal
Expert 2.5K+
P: 2,653
[NUMBER] = ER-2001-001

NUMBER - field name
RELEASES - constant

as soon as [NUMBER] is apparently text type field the syntax should be the following
[NUMBER] = 'ER-2001-001'
Apr 9 '09 #41

P: 56
[NUMBER] is just the title of my field. I could have named it [BOB] so I don't understand why the code would be looking for a numeric value. So how should I fix this?
Apr 10 '09 #42

FishVal
Expert 2.5K+
P: 2,653
Well.

Since the values you are storing in [NUMBER] fields are alphanumeric, the type of the field is Text. Open the table in design view and check it (2nh column on the grid). Want to know more about datatypes supported by Access - point cursor to data type field and press F1.

Since datatype of constant value (in you case document number compared with [NUMBER] field) could not be unambiguously determined, there is a special syntax to explicitely give datatype to constant values:
Quotes (') and Double-Quotes (") - Where and When to use them
Literal DateTimes and Their Delimiters (#)
Apr 10 '09 #43

P: 56
My Datatype is text for the number column. Okay, I changed my column titles for each table from NUMBER to REL DOC, ASSY DOC, EXT ASSY DOC, EXTRU DOC, INSTR DOC, PART DOC, and FAB DOC. Code still does not work. Here is what the code looks like now that I changed the field titles.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.  
  3. If IsNull(cbodocumenttype) Or cbodocumenttype = "" Then
  4.   MsgBox "Document Type is required."
  5.  
  6.   Exit Sub
  7.   End If
  8. If IsNull(txtuserinput) Or txtuserinput = "" Then
  9.   MsgBox "Document Number is required."
  10.  
  11.   Exit Sub
  12.   End If
  13. Select Case cbodocumenttype
  14.  
  15. Case Is = cbodocumenttype = "RELEASES"
  16.   DoCmd.OpenForm "RELEASES", , , "REL DOC like ' " & txtuserinput
  17.  
  18. Case Is = cbodocumenttype = "ASSEMBLY DRAWINGS"
  19.   DoCmd.OpenForm "ASSEMBLY DRAWINGS", , , "ASSY DOC like ' " & txtuserinput
  20.  
  21. Case Is = cbodocumenttype = "EXTRUSION DRAWINGS"
  22.   DoCmd.OpenForm "EXTRUSION DRAWINGS", , , "EXTRU DOC like ' " & txtuserinput
  23.  
  24. Case Is = cbodocumenttype = "EXTRUSION ASSEMBLY DRAWINGS"
  25.   DoCmd.OpenForm "EXTRUSION ASSEMBLY DRAWINGS", , , "EXT ASSY DOC like ' " & txtuserinput
  26.  
  27. Case Is = cbodocumenttype = "FABRICATION DRAWINGS"
  28.   DoCmd.OpenForm "FABRICATION DRAWINGS", , , "FAB DOC like ' " & txtuserinput
  29.  
  30. Case Is = cbodocumenttype = "PART DRAWINGS"
  31.   DoCmd.OpenForm "PART DRAWINGS", , , "PART DOC like ' " & txtuserinput
  32.  
  33. Case Is = cbodocumenttype = "INSTRUCTIONS"
  34.   DoCmd.OpenForm "INSTRUCTIONS", , , "INSTR DOC like ' " & txtuserinput
  35.  
  36. Case Else
  37.   MsgBox "Error, unexpected Document Type!"
  38. End Select
  39.  
  40.   Exit Sub
  41.  
  42. End Sub
Apr 10 '09 #44

FishVal
Expert 2.5K+
P: 2,653
  • It is no matter what name do you give to table fields.
    The only point here is that if field name breaks naming rules, e.g. has symbols which are not allowed (space in your case), it should be enclosed in square brackets when referencing it. BTW, form names has to be enclosed in square brackets for the same reason. And take care that text constant enclosed in quotes.
    Example:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.OpenForm "[ASSEMBLY DRAWINGS]", , , "[ASSY DOC] like '" & txtuserinput & "'"
    2.  
  • Be careful when constructing WhereCondition string. Your current code adds leading space to what user has entered in [txtuserinput] textbox.
  • If you expect user to enter exact "doc number" you should use "=" operator instead of "Like". BTW, look in Access help how "Like" operator works, in current implementation it does nothing it is suited for.
    Example:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.OpenForm "[ASSEMBLY DRAWINGS]", , , "[ASSY DOC] = '" & txtuserinput & "'"
    2.  
  • Your current Case condition
    Is = cbodocumenttype = "RELEASES"
    is not being evaluated the way you are expecting. I would not explain what actually it does because it does a senseless action in context of your application.
    Just change it to
    "RELEASES"

And the last but not the least, I would recommend you to read the following stuff since you are developing code in a very ineffective trial-and-error manner.
Debugging in VBA
Apr 10 '09 #45

Post your reply

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