 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008how to search two fields in form
I need my users to be able to search a form for a combination of two fields. The fields are "Division" and "SeqNumber". Below is the instruction and code Dave Hargis gave me, but I don't understand how to search once it is in there. The only way I know how to search a form is to do a ctrl+F , indicate select the field you want to search and type in what you want to find. How would this new field work? I'm confused. From Dave: > Now, in your case, you need to find the division and the sequence number. > You will need to search using both of those values. > > Let's call it cboFindRecord > > Private Sub cboFindRecord_AfterUpdate > With Me.RecorsetClone > .FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" & > Me.Division & "'" > If Not .NoMatch Then > Me.Bookmark = .Bookmark > End If > End With > > End Sub The code Dave provided should fire whenever you select a value in the combo box. You shouldn't have to do anything else for the search to run. However, before trying to solve your combo box issues, I read some of your previous posts and have a question. Will you users always know in advance the division and sequence number they want to search for? (in which case it may be easier to use two unbound text boxes and a command button) Or do you want to present them with a list to choose from? (in which case the combo box method would be better)  Signature_________ Sean Bailey > I need my users to be able to search a form for a combination of two fields. > The fields are "Division" and "SeqNumber". Below is the instruction and code [quoted text clipped - 19 lines] > > > > End Sub I think I"m still confused. But, they will always know what combination they will be searching for. > The code Dave provided should fire whenever you select a value in the combo > box. You shouldn't have to do anything else for the search to run. However, [quoted text clipped - 31 lines] > > > > > > End Sub Sorry for the confusion. I know you've been trying to figure this out for some time, so I'll try to clarify why I think a combo box *might* not be the best option in this case (I say might, because there is always the possibility that I'm wrong, wouldn't be the first time). A combo box has a row source property, which determines the values that are displayed in the combo box. The row source can be either a value list that you define, or a query that pulls values from a table. In your case it would be a query that pulls the existing division and sequence numbers from your table. So let's say you create a combo box with a row source query that shows the division and sequence numbers in two separate columns. In it's unexpanded state, your combo box will only show the division (the first visible column). That's probably no big deal, but your users would also not be able to type a sequence number into the combo box. They could type a division to narrow the results, but then they would still have to click the row with the appropriate sequence number from the remaining choices. So if your users are always going to know both values it might be a little easier just to have two unbound text boxes where they could enter the division and sequence number, and then have your code run the search. You could have a command button that they could click or you could just run the code in the after update event of one of the text boxes. If you have a preference of one method over the other, post back and we can try to get it working for you. Signature_________ Sean Bailey > I think I"m still confused. But, they will always know what combination they > will be searching for. [quoted text clipped - 34 lines] > > > > > > > > End Sub I think your idea sounds good and would probably want to use a command button. It seems to me that would be easier for the user...and that is the most important thing. > Sorry for the confusion. I know you've been trying to figure this out for > some time, so I'll try to clarify why I think a combo box *might* not [quoted text clipped - 64 lines] > > > > > > > > > > End Sub OK. This is just an example with control names that I've made up, you can change the names to whatever you want. If you change the names you will also need to change the naming in the code. Create two new unbound text boxes in, say, the header of your form named txtDivisionSearch and txtSequenceSearch, plus a command button named cmdRunSearch. When you add the command button, the command button wizard will pop up, just hit cancel to get rid of it. Open the properties sheet for the command button, go to the events tab/ On Click event and click the elipse to the right, then select code builder. This should open the VBA window with the following lines displayed; Private Sub cmdRunSearch_Click() End Sub In between those lines put the following code (or something similar) If IsNull(Me.txtDivisionSearch) Then Msgbox "Please enter a Division" Exit Sub ElseIf IsNull(Me.txtSequenceSearch) Then Msgbox "Please enter a Sequence number" Exit Sub End If With Me.RecorsetClone .FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_ " AND [SeqNumber] = """ & Me.txtSequenceSearch & """" If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With I've tried to adjust for line wrap in this response so you could just copy and paste the code if you want, but it might still need a little more adjustment. Also, I've assumed that your Division and Sequence fields in the table are of the text data type. If not, some of the qoutes will need to be removed. Signature_________ Sean Bailey > I think your idea sounds good and would probably want to use a command > button. It seems to me that would be easier for the user...and that is the [quoted text clipped - 68 lines] > > > > > > > > > > > > End Sub I'm getting an error on this code. I had the underscore after the ampersand but it said it was an invalid character so I took it out. It's still not working. .FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND [SeqNumber] = """ & Me.txtSequenceSearch & """" > OK. This is just an example with control names that I've made up, you can > change the names to whatever you want. If you change the names you [quoted text clipped - 109 lines] > > > > > > > > > > > > > > End Sub The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi > I'm getting an error on this code. I had the underscore after the ampersand > but it said it was an invalid character so I took it out. It's still not [quoted text clipped - 130 lines] > > > > > > > > > > > > > > > > End Sub In VBA, it is all on one line. > The email has obligingly broken this up but the bit of code you quoted needs > to be 1 long line [quoted text clipped - 171 lines] > > > > > > > > > > > > > > > > > > End Sub Is SeqNumber text or number? Evi > In VBA, it is all on one line. > [quoted text clipped - 173 lines] > > > > > > > > > > > > > > > > > > > > End Sub It's a number. But I can change it if need be. > Is SeqNumber text or number? > Evi [quoted text clipped - 208 lines] > > > > > > > > > > > > > > > > > > > > > > End Sub Just to clarify, when we say "is it a number?", we are talking about the actual data type of the underlying field in the table. In other words, you can enter a number in a text field, but Access still views it as text. So, if both fields are a number data type, then the code should look like; .FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " & Me.txtSequenceSearch (put it all on one line in your code window) Post back if you have more questions. Signature_________ Sean Bailey > It's a number. But I can change it if need be. > [quoted text clipped - 210 lines] > > > > > > > > > > > > > > > > > > > > > > > > End Sub Yes, the field SeqNumber in the original table is a data type Number, long integer. I put this code in and I'm getting another error. Here's exactly what I did. I tried to do a search by typing FIN in the DivisionSearch field and 002 in the SequenceSearch field and then clicking the cmdRunSearch button and the following error popped up: Runtime error 438. Object doesn't support this property or method. > Just to clarify, when we say "is it a number?", we are talking about the > actual data type of the underlying field in the table. In other words, you [quoted text clipped - 222 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > End Sub OK, I threw together a quick db based on what I know about yours. My table has the following fields; Division - (data type of text) SeqNumber - (data type of text) I made the SeqNumber a text data type because you appear to want to use leading zero's My table has the following data; Division SeqNumber FA 001 FA 002 FA 003 EXE 001 EXE 002 EXE 003 My form has the following controls; txtDivision - (text box bound to Division) txtSeqNumber - (text box bound to SeqNumber) txtDivisionSearch - (unbound text box in the form header) txtSequenceSearch - (unbound text box in the form header) cmdRunSearch - (command button in the form header) Here is the code behind my command button (On Click event); Private Sub cmdRunSearch_Click() On Error GoTo HandleError If Nz(Me.txtDivisionSearch, "") = "" Then MsgBox "Please enter a Division to search for" Me.txtDivisionSearch.SetFocus Exit Sub End If If Nz(Me.txtSequenceSearch, "") = "" Then MsgBox "Please enter a Sequence Number to search for" Me.txtSequenceSearch.SetFocus Exit Sub End If With Me.RecordsetClone .FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _ " AND [SeqNumber]=""" & Me.txtSequenceSearch & """" If Not .NoMatch Then Me.Bookmark = .Bookmark Else MsgBox "No matching record found" End If End With Exit_cmdRunSearch_Click: Exit Sub HandleError: MsgBox Err.Description Resume Exit_cmdRunSearch_Click End Sub The above code works exactly as it is supposed to. As long as your field and control names are the same, and your Division and SeqNumber fields are a text data type, then you should be able to copy the above code sample exactly as is, paste it into the code window for your form, do a debug/compile and it should work. You will want to remove any existing code behind your Search button first, and you may need to open the properties sheet for your command button, got to the On Click event, and select the code builder to sort of "re-link" the new code to the button. As always - !MAKE A BACKUP! before you do any of the above. Signature_________ Sean Bailey > Yes, the field SeqNumber in the original table is a data type Number, long > integer. [quoted text clipped - 231 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > End Sub My Division control in the form is actually a combo box and the Control Source is tblDivision and row source is qryDivision. Is that going to cause any problems? > Division - (data type of text) I have this set up as a combo box > SeqNumber - (data type of text) [quoted text clipped - 253 lines] > > > > > > > > > > > > > Will you users always know in advance the division and > > > > > sequence A month ago, when I asked if you wanted to use the combo box method or try using two unbound text boxes, you said you wanted to try the two unbound text boxes. Now you're saying you are still using the combo box. Will it cause problems? Maybe. Maybe not. I was under the impression that you are trying to search for records. If that is the case, then you should not have anything in the control source of the combo box. If you do, then it will change the value in the source table when you change the value in the combo box. As far as the code goes, it would still work with the combo box as long as you use the correct combo box name and the column within the combo that holds the Division value is the bound column. Of course, this is all based on the presumption that you are trying to search for records that match a given Division and Sequence number. If that is not what you are trying to do, then this and all my previous posts are basically irrelevant. Signature_________ Sean Bailey > My Division control in the form is actually a combo box and the Control > Source is tblDivision and row source is qryDivision. Is that going to cause [quoted text clipped - 256 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > Will you users always know in advance the division and I think the confusion is in the two separate fields...one for entering data and one for searching. The one for enterting data in the detail of the form is a combo box so that the choice of our seven divisions is listed in a pull-down menu. I don't, however, want the control in the header that they are using for searching to be a combo box...although, now that I think about it, it probably wouldn't really matter if it was. So, the way I've set it up is that the control in the detail of the form called "Division" has a Control Source of "Division", the field in the table, and a Row Source of "qryDivision." The control "txtDivision" in the header is unbound and has no control source. It is a text field. I'm apologize for my ignorance, but for some reason, Access really throws me for a loop. > A month ago, when I asked if you wanted to use the combo box method > or try using two unbound text boxes, you said you wanted to try the two [quoted text clipped - 253 lines] > > > > > > > > > > > > > If you have a preference of one method over the other, post back > > > > > > > and > A month ago, when I asked if you wanted to use the combo box method > or try using two unbound text boxes, you said you wanted to try the two [quoted text clipped - 253 lines] > > > > > > > > > > > > > If you have a preference of one method over the other, post back > > > > > > > and I put the code in and tried to do a search. Typed ITS in the division control, typed 002 in the sequence control and then clicked the button and i got this error: Data type mismatch in criteria expression. > A month ago, when I asked if you wanted to use the combo box method > or try using two unbound text boxes, you said you wanted to try the two [quoted text clipped - 253 lines] > > > > > > > > > > > > > If you have a preference of one method over the other, post back > > > > > > > and The code example I posted is based on both the Division and Sequence fields being a text data type (in the table). If both fields are not a text data type in your table, then either the code will need to be modified slightly, or the data type will need to be changed. As stated previously, I used a text data type for the sequence number because you appear to want to have leading zeros Signature_________ Sean Bailey > I put the code in and tried to do a search. Typed ITS in the division > control, typed 002 in the sequence control and then clicked the button and i [quoted text clipped - 251 lines] > > > > > > > > > > search. > > > > > > > > > > > > > > You could have a command button that they could click or you If you see my first response to your last post, it explains how the fields are set up and why. How will I need to change the code to make it work with what I have? I have the SequenceNumber set up as a text field. If you didn't see it, here's my first response to your last post: "I think the confusion is in the two separate fields...one for entering data and one for searching. The one for enterting data in the detail of the form is a combo box so that the choice of our seven divisions is listed in a pull-down menu. I don't, however, want the control in the header that they are using for searching to be a combo box...although, now that I think about it, it probably wouldn't really matter if it was. So, the way I've set it up is that the control in the detail of the form called "Division" has a Control Source of "Division", the field in the table, and a Row Source of "qryDivision." The control "txtDivision" in the header is unbound and has no control source. It is a text field. I'm apologize for my ignorance, but for some reason, Access really throws me for a loop." > The code example I posted is based on both the Division and Sequence > fields being a text data type (in the table). If both fields are not a text [quoted text clipped - 244 lines] > > > > > > > > > could > > > > > > > > > > > > > > > type a division to narrow the results, but then they would still Ok. The combo box in the detail section should not have any effect on what we are trying to do here, which is search for a record using two unbound text boxes and a command button in the header. If both of the fields (Division and Sequence) in the table are text, then the code I posted earlier should work. Perhaps something got screwed up during the copy/paste into your db. Can you copy the code from you module and post it as is in a reply? And actually, now that I think about it, what fields are in qryDivision that you use for the row source of the combo box in the detail section? Also, which column is the bound column? Signature_________ Sean Bailey > If you see my first response to your last post, it explains how the fields > are set up and why. How will I need to change the code to make it work with [quoted text clipped - 239 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > A combo box has a row source property, which determines the |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.