Select Case Statement with ComboBox

[Desitech]

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

[ChipR]

What part of the code are you having trouble with?

[Desitech]

I Guess all of it. This is all I have so far:

Private Sub cmdSearch()

Dim stnumber As String


'Trying to reference "documenttype" from the combo box "cbodocumenttype"

  Select Case Document Type
   
   Case "RELEASES"
         If Me.cbodocument.Value = RELEASES Then
            If Me.txtuserinput.Value(" & ") Then
            docmdOpenForm RELEASES

        Case Else
            If cbodocumenttype.Value = Null Then
            MsgBox "DOCUMENT TYPE REQUIRED"
            If txtuserinput.Value = Null Then
            MsgBox "DOCUMENT NUMBER REQUIRED"
    End Select
End If

[ChipR]

See if this example code makes sense:

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 = "RELEASES"
  DoCmd.OpenForm "Releases", , , "[nameOfYourField] = " & txtUserInput
...
Case Else
  MsgBox "Error, unexpected Document Type!"
End Select

[Desitech]

That looks awsome. but I ran it in my database after adding to it. Here is what I added:

Private Sub cmdSearch()

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 = "RELEASES"
  DoCmd.OpenForm "Releases", , , "[NUMBER] = " & txtuserinput
  
Case Is = "ASSEMBLY DRAWINGS"
  DoCmd.OpenForm "ASSEMBLY DRAWINGS", , , "[NUMBER] = " & txtuserinput
  
Case Is = "EXTRUSION DRAWINGS"
  DoCmd.OpenForm "EXTRUSION DRAWINGS", , , "[NUMBER] = " & txtuserinput
  
Case Is = "EXTRUSION ASSEMBLY DRAWINGS"
  DoCmd.OpenForm "EXTRUSION ASSEMBLY DRAWINGS", , , "[NUMBER] = " & txtuserinput
  
Case Is = "FABRICATION DRAWINGS"
  DoCmd.OpenForm "FABRICATION DRAWINGS", , , "[NUMBER] = " & txtuserinput
  
Case Is = "PART DRAWINGS"
  DoCmd.OpenForm "PART DRAWINGS", , , "[NUMBER] = " & txtuserinput
  
Case Is = "INSTRUCTIONS"
  DoCmd.OpenForm "INSTRUCTIONS", , , "[NUMBER] = " & txtuserinput

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


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

[ChipR]

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.

[Desitech]

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?

[ChipR]

Do you have, at the very top of your code, before the beginning of any functions:
Option Explicit

[ChipR]

Lunchtime, but I'll check back later.

[Desitech]

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.

[ChipR]

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.

[Desitech]

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(cboDocum entType) Or cboDocumentType = "" Then
MsgBox "Document Type is required."

[ChipR]

"" means an empty string, nothing else. You can see which is the case by doing:

If IsNull(cboDocumentType) then MsgBox "Document Type is null"
If cboDocumentType= "" then MsgBox "Document Type is blank."

[Desitech]

Oh...Okay....Do es the fact that the options in the combobox are a rowsourse of the combobox properties? Or do I have that correct?

[ChipR]

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.