combo box after update event

[Jeffrey Tan]

i have this code in a combo box after update event.

what it's supposed to do is open the User Problem Log form and go to the specific record.

I have this combo box on another form.
trouble no is the autonumber and that's what is used to go to that specific record on the User Problem Log form.

    DoCmd.OpenForm ("User Problem Log")
 
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
 
    [Forms]![User Problem Log]![trouble_no] = " & Str(Nz(Me![Combo5], 0))"
 
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 

this is similar to the "Jump To" where it goes to that specific record. however, somehow this is not working.

i am getting an error code:
"Run-time error '2113':
The value you entered isn't valid for this field."

and then it breaks to this line of code

[Forms]![User Problem Log]![trouble_no] = " & Str(Nz(Me![Combo5], 0))"

thanks!

[Jeffrey Tan]

This part only opens the form, but does not go to that record

    Dim rs As Object
 
    DoCmd.OpenForm ("User Problem Log")
 
    Set rs = [Forms]![User Problem Log].Recordset.Clone
 
    rs.FindFirst "[trouble_no] = " & Str(Nz(Me![Combo5], 0))

[NeoPa]

I would suggest using :

Call DoCmd.OpenForm("User Problem Log")
With Forms("User Problem Log").Recordset
  If Not IsNull(Me.Combo5) Then Call .FindFirst("[Trouble_No]=" & Me.Combo5)
End With

[Jeffrey Tan]

Thanks neopa.. forgot to update this thread yesterday.

i was thinking correctly

it was actually a pretty easy one.

i used

DoCmd.Open Form("User Problem Log")
[Forms]![User Problem Log]![Combo74].Text = Str(Nz(Me![Combo5], 0))
 

and that basically solved it.

i was a bit worried that it wouldn't work but i guess the Combo74.Text (from user problem log) sets the text and also goes to the specific record based on the after update for my combo5.

[ADezii]

Here is another approach you can take that will Filter for the specified [Trouble_No], as well as check for its existence:

If IsNull(Me![Combo5]) Then
  Exit Sub
'Does a Record even exist for the [Trouble_No]?
ElseIf DCount("*", "Employees", "[<Data Source for Form>] = " & Me![Combo5]) = 0 Then
  MsgBox "No Records match specified Criteria", vbExclamation
Else        'All is well, Open the Form for the [specified Trouble_No]
  DoCmd.OpenForm "User Problem Log", acNormal, , "[Trouble_No]= " & Me![Combo5], acFormEdit
End If

[Jeffrey Tan]

@ADezii :)

Thanks. it should work 100% with no problem because the query for Combo5 comes directly from the user problem log table (main table) so anything in combo5 is valid :P

Thanks again.

[NeoPa]

Well, if that works then fine Jeffrey. It includes elements and concepts that aren't mentioned in the question, so not something anyone could have helped with, but if you're happy then we can move on ;-)