Select Case Statement with ComboBox

[Desitech]

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!"

[ChipR]

So each case should be:

Case Is = "RELEASES"
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?

[Desitech]

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?

[ChipR]

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

MsgBox cboDocumentType

right before the Select Case and see what it is and why it doesn't match.

[Desitech]

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...Unexpe cted Document Type" message. It must be something in the Cases code. Do I need to make a string statement of some kind?

[ChipR]

What is the result of

MsgBox cboDocumentType

You can also do

MsgBox "Combo box = " & cboDocumentType

[Desitech]

Do I need a control source on the Combo Box or Text Box ? This property is blank in both.

[Desitech]

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(cbodocum enttype) Or cbodocumenttype = "" Then
MsgBox "Document Type is required."

Exit Sub
End If
If IsNull(txtuseri nput) 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 = "FABRICATIO N DRAWINGS"
DoCmd.OpenForm "FABRICATIO N DRAWINGS", , , "NUMBER like '" & txtuserinput

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

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

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

Exit Sub

End Sub

[FishVal]

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.

[Desitech]

I don't understand what is the constant?

[FishVal]

[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'

[Desitech]

[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?

[FishVal]

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 (#)

[Desitech]

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.

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", , , "REL DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "ASSEMBLY DRAWINGS"
  DoCmd.OpenForm "ASSEMBLY DRAWINGS", , , "ASSY DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "EXTRUSION DRAWINGS"
  DoCmd.OpenForm "EXTRUSION DRAWINGS", , , "EXTRU DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "EXTRUSION ASSEMBLY DRAWINGS"
  DoCmd.OpenForm "EXTRUSION ASSEMBLY DRAWINGS", , , "EXT ASSY DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "FABRICATION DRAWINGS"
  DoCmd.OpenForm "FABRICATION DRAWINGS", , , "FAB DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "PART DRAWINGS"
  DoCmd.OpenForm "PART DRAWINGS", , , "PART DOC like ' " & txtuserinput
  
Case Is = cbodocumenttype = "INSTRUCTIONS"
  DoCmd.OpenForm "INSTRUCTIONS", , , "INSTR DOC like ' " & txtuserinput

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

End Sub

[FishVal]

• 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:
  

  DoCmd.OpenForm "[ASSEMBLY DRAWINGS]", , , "[ASSY DOC] like '" & txtuserinput & "'"

• 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:
  

  DoCmd.OpenForm "[ASSEMBLY DRAWINGS]", , , "[ASSY DOC] = '" & txtuserinput & "'"

• 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