If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how to search two fields in form
It's a number. But I can change it if need be.
"Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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) -- _________ Sean Bailey "bbypookins" wrote: 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 |
#12
|
|||
|
|||
how to search two fields in form
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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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) -- _________ Sean Bailey "bbypookins" wrote: 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 |
#13
|
|||
|
|||
how to search two fields in form
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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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) -- _________ Sean Bailey "bbypookins" wrote: 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 |
#14
|
|||
|
|||
how to search two fields in form
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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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) -- _________ Sean Bailey "bbypookins" wrote: 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 |
#15
|
|||
|
|||
how to search two fields in form
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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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 |
#16
|
|||
|
|||
how to search two fields in form
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. -- _________ Sean Bailey "bbypookins" wrote: 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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: I think I"m still confused. But, they will always know what combination they will be searching for. "Beetle" wrote: 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 |
#17
|
|||
|
|||
how to search two fields in form
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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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 |
#18
|
|||
|
|||
how to search two fields in form
"Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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 |
#19
|
|||
|
|||
how to search two fields in form
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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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 |
#20
|
|||
|
|||
how to search two fields in form
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 -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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) 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: It's a number. But I can change it if need be. "Evi" wrote: Is SeqNumber text or number? Evi "bbypookins" wrote in message ... In VBA, it is all on one line. "Evi" wrote: The email has obligingly broken this up but the bit of code you quoted needs to be 1 long line Evi "bbypookins" wrote in message ... 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 & """" "Beetle" wrote: 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. -- _________ Sean Bailey "bbypookins" wrote: 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. "Beetle" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|