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 |
#1
|
|||
|
|||
where clause to find record from a table
If its failing to find the Combo20, Could it be the Me.Combo?
Could you help/write the code differently? Would it help to change to a numeric field? I have all the table fields including ProjectNumber in the form and I can scroll through all projects, but the search doesn't perform. My forehead is getting sore. "Klatuu" wrote: Sorry, I don't know what to tell you. The code looks correct and from what you have been telling me, I can't see any other problems. "georgiaboy" wrote: Combo20 is Row/Source Type: Table/Query Row Source: TBL-ProjectTracker No subform. Be sure it is in the form recordset? "Klatuu" wrote: You said earlier that ProjectNumber is a text field in the table. Is your form based on the table or on a query based on the table. Be sure it is actually in the form recordset. Is there any chance there is a subform involved here? "georgiaboy" wrote: I appreciate your patience.... I deleted Text17 and added a Combo Box The error message and compile error is the same with the following code: Option Compare Database Private Sub Combo20_AfterUpdate() Dim rst As Recordset Set rst = Me.RecordsetClone rst.FindFirst "[ProjectNumber] = '" & Me.Combo20 & "'" If rst.NoMatch Then MsgBox "Record Not Found" Else Me.Bookmark = rst.Bookmark End If Set rst = Nothing End Sub Klatuu" wrote: Check the name of the control to make sure it is correct. It is saying it can't find Text17 "georgiaboy" wrote: The compile error is "Method or data member not found" A Blue highlight on .Text17 in line '" & Me.Text17 & "'" When I click OK the arrow is on Private Sub Text17_AfterUpdate() "Klatuu" wrote: What line does the compile error happen on. I really don't see a problem with the code. "georgiaboy" wrote: Almost I think Yes ProjectNumber is a text field in the table The code following - returns a compile error Option Compare Database Private Sub Text17_AfterUpdate() Dim rst As Recordset Set rst = Me.RecordsetClone rst.FindFirst "[ProjectNumber] = '" & Me.Text17 & "'" If rst.NoMatch Then MsgBox "Record Not Found" Else Me.Bookmark = rst.Bookmark End If Set rst = Nothing End Sub "Klatuu" wrote: That first End Sub in your module should not be there by itself. rst.FindFirst "[ProjectNumber] = " & Me.Text17 If ProjectNumber is a numeric field, the code is correct. If it is a text field, it needs to be like this: rst.FindFirst "[ProjectNumber] = '" & Me.Text17 & "'" [ProjectNumber] should not be a table, it should be the field in the form's record source. You don't use the table name in this context. "georgiaboy" wrote: I understood using a Combo Box and After Update. But I believe I'm butchering the Code for my unbound Combo Box 'Text 17' getting the results from table ProjectNumber Option Compare Database End Sub Private Sub Text17_AfterUpdate() Dim rst As Recordset Set rst = Me.RecordsetClone rst.FindFirst "[ProjectNumber] = " & Me.Text17 If rst.NoMatch Then MsgBox "Record Not Found" Else Me.Bookmark = rst.Bookmark End If Set rst = Nothing End Sub "Klatuu" wrote: It is not really necessary to have a command button. You can do it in the After Update event of the text box; however, for doing searches, a combo box works a lot better. It makes it easier for the user to find and select the value. In either case, the search control should be an unbound control. If you use a bound control, the problem will be that when you type in a value to search for, you have just changed the value in the form's recordset which will then try to update the current record when you try to move to the selected record. For forms where I do a search, I use a bound text field for the actual data and an unbound combo box to do the search. In any case, here is how you find and display the record you want to search for. I will use a text box for the example, but the code is almost identical for a combo. The difference is that with a combo, I use the NotInList event to add new records. Dim rst As Recordset Set rst = Me.RecordsetClone rst.FindFirst "[SomeTableField] = " & Me.txtSearchBox If rst.NoMatch Then Msgbox "Record Not Found" Else Me.Bookmark = rst.Bookmark End If set rst = Nothing "georgiaboy" wrote: I want build a form to display the record information from a table by inputting the search criteria in a form text field then clicking on a button to display the record results. For example I have project number with related information. I want to input the project number in a text box, click a button and display the results. |
Thread Tools | |
Display Modes | |
|
|