Find record based on text box

[tazzzz]

Hello,
This is what I want to accomplish: find a record in my form using a value from a text box(the value in text box comes from a main combo box).
I know it is very easy to accomplish this using a combo box .
I did some research but seems that this way requires some coding and I don't have much experience with it.
I appreciate your help,


Thank you

[Missinglinq]

...find a record in my form using a value from a text box(the value in text box comes from a main combo box).
I know it is very easy to accomplish this using a combo box...

Why, if the data for the Textbox comes from an existing Combobox, don't you simply use the cbo to retrieve the Record?

At any rate, the code/Macro in the AfterUpdate event of a Textbox, to do this, would be identical to that used in the AfterUpdate of a Combobox, to do this:

Private Sub ControlName_AfterUpdate()
  
 Dim rs As Object

 Set rs = Me.Recordset.Clone
   rs.FindFirst "[Field Name] = '" & Me!ControlName & "'"
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

I vaguely seem to remember that in some versions of Access the line

Set rs = Me.Recordset.Clone

would have to be

Set rs = Me.RecordsetClone

without the dot between Recordset and Clone.

Linq ;0)>

[tazzzz]

Thank you for your interest in this topic.
I tried your code but something is off (no records are coming and no message of error).
In the meantime I found this code. Is working only when data is input manually. When I reference the combo box the code stop working.

Option Compare Database
 
Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.RecordSet
    Set rs = Me.RecordsetClone
   rs.FindFirst "[jobnumber]=""" & txtGoTo & """"
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.RecordSet.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
End Sub

[Missinglinq]

Is jobnumber actually defined as a Number or as Text in the Table?

For Text:

rs.FindFirst "[jobnumber]= '" & txtGoTo & "'"

For a Number:

rs.FindFirst "[jobnumber]= " & txtGoTo

Linq ;0)>

[June7]

You might find this of interest http://www.allenbrowne.com/ser-62.html

[tazzzz]

Still not working. I attached a sample database. If your time allows you please have a look.(open MAIN)
Thank You

[June7]

There is no MAIN, there is jobs.

What do you mean by 'not working'? The code works for me. Must hit 'Enter' or 'Tab' after typing the jobnumber to trigger the event.

[orange]

tazzzz,

You may find this video tutorial useful.

Good luck.

[tazzzz]

Thank for trying the code. Did You open form "MAIN" and it worked by changing values in Combo11?
Thanks.

[June7]

As already stated - there is no form MAIN, only jobs and therefore no Combo11.

[tazzzz]

My mistake. I loaded a different db (with a different code). This is the one with the code provided above.
Thanks.

[June7]

Okay, combobox is on UNBOUND main form and records to be filtered are in subform.

Code must be behind the combobox AfterUpdate, not the textbox because the textbox is a calculated value. AfterUpdate only triggers when data is input, not calculated.

The value of combobox is ID but the code attempts to filter on JOBNUMBER field.

This is a Navigation Form. I don't like and don't use them. I am having difficulty getting code that would work with a normal form to work with this.

So another approach to consider is use of a single form set as Continuous then put combobox in that form's header (arrange controls to look like datasheet if you want). This I can get to work.

Private Sub Combo11_AfterUpdate()
With Me.RecordsetClone
   .FindFirst "[ID]= " & Me.Combo11
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

[tazzzz]

This per your provided code.

[June7]

So I should ignore the MAIN form code and only look at the ALLJOBS?

Did you read my post thoroughly? Code in textbox AfterUpdate WILL NOT WORK.

My suggested alternative is a solo form (just ALLJOBS) with a combobox in its Header.

[tazzzz]

Database is huge and is setup as Navigation Form with more than 8 tabs. This is the only reason I try to make it work this way. I would change it for a solo form in a blink of an eye if others will agree with a different format.