where clause to find record from a table

Do a little testing, first.
Open the form in form view.
Open the VBA editor and type in:
?Forms!FormName!Combo20
And see what happens.

"georgiaboy" wrote:

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.