Select Case Statement with ComboBox

[Desitech]

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

[ChipR]

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.

[Desitech]

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"

[ChipR]

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.

[Desitech]

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=cobd ocumenttype'

[ChipR]

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.

[FishVal]

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=cobd ocumenttype'

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]".

[ChipR]

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.

[FishVal]

Yes, my bad.
I've corrected it to not mess Desitech.

[Desitech]

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.

[ChipR]

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 "*" & "*" ?

[Desitech]

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?

[ChipR]

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:

"LIKE *" & txtUserInput        'Ends with what the user typed in
"LIKE " & txtUserInput & "*"   'Starts with what the user typed in
"LIKE *" & txtUserInput & "*"  'Contains what the user typed in

Edit: Sorry I mean use LIKE instead of =

[Desitech]

Okay...I found another typing error of mine. I had cbodocumenttype spelled cobdocumenttype . The value now DOES populate the txtnewbox when selected. Now what?

[ChipR]

If you use

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.