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
44 9761
What part of the code are you having trouble with?
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
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
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????
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.
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?
Do you have, at the very top of your code, before the beginning of any functions:
Option Explicit
Lunchtime, but I'll check back later.
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.
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.
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."
"" 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."
Oh...Okay....Does the fact that the options in the combobox are a rowsourse of the combobox properties? Or do I have that correct?
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.
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
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.
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"
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.
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'
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.
@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]".
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.
Yes, my bad.
I've corrected it to not mess 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.
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 "*" & "*" ?
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?
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 =
Okay...I found another typing error of mine. I had cbodocumenttype spelled cobdocumenttype. The value now DOES populate the txtnewbox when selected. Now what?
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.
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!"
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?
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?
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
right before the Select Case and see what it is and why it doesn't match.
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?
What is the result of
You can also do - MsgBox "Combo box = " & cboDocumentType
Do I need a control source on the Combo Box or Text Box ? This property is blank in both.
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
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.
I don't understand what is the constant?
[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'
[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?
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 (#)
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
- 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 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: JMCN |
last post by:
hello
i have your basic select case question. i created a combo box and save
it as a query. so whenever the user selects the value and clicks the
export button, the select case should then export...
|
by: Susan Bricker |
last post by:
The following error:
"The current field must match the join key '?' in the table that seves
as t the 'one' side of one-to-many relationship. Enter a record in the
'one' side table with the...
|
by: Vmusic |
last post by:
Hi,
I'm using MS Access 2002. I have a form with a combo box built from a
query that returns one column, and that one column is the bound column.
How do you use VBA to programmatically change...
|
by: rdemyan via AccessMonster.com |
last post by:
I have the following SQL statement in code that is set to the RowSource of a
combobox. The combobox has two columns.
SELECT '(ALL)' As Site, '' As , 0 As SortFirst
FROM
GROUP BY SITE_COMPLEX...
|
by: microsoft.public.dotnet.languages.vb |
last post by:
Hi All,
I wanted to know whether this is possible to use multiple variables to
use in the select case statement such as follows:
select case dWarrExpDateMonth, dRetailDateMonth
case...
|
by: FishVal |
last post by:
Hereby I'm proposing a way of convinient work with properties containing SQL Select statements, particulary RowSource property of ComboBox and ListBox.
The usual way is the following.
Private...
|
by: The.Daryl.Lu |
last post by:
Hi, two parts to my problem if someone can help address either one or
both:
1. I want to SELECT everything in the table if it matches the
criteria when the query button is pressed (this is just...
|
by: billa856 |
last post by:
Hi,
I have to use the table(PRODUCTION) already generated in MS Access in which all fields are of TEXT type.fields like (orderdate,palletno,customercode,itemno,pono,carto n,pcs,totalquantity)Now i...
|
by: SteveArmstrong |
last post by:
This code is used in conjunction with an excel spreadsheet, i need to be able to remove each item from the comboBox list as the item is selected this can be in any order and if and when the last item...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |