Form For Edit & Delete

[mseo]

hi,
I have form for edit and delete
within me form edit or delete job
I have before_update trigger

Private Sub Form_beforeUpdate(Cancel As Integer)
    If IsNull(Me![JobTitle]) Then
        MsgBox "You must enter a Job first then you can save it", vbExclamation
        Cancel = True: Me![JobTitle].SetFocus
    ElseIf IsNull(Me![Job_ID]) Then
        MsgBox "You must enter The ID then you can save it", vbExclamation
        Cancel = True: Me![Job_ID].SetFocus
    End If
End Sub

If I left or removed the textbox Job_id
and clicked save it works great and the same for delete
but if select the value for combobox to research and the value of job_id is null in the viewed record i get error
number 3021 (no current record)
the code on search button

Private Sub Search_Click()
    Dim strjobRef As String
    Dim strSearch As String
    If IsNull(Me![cboJob_ID]) Or (Me![cboJob_ID]) = "" Then
        MsgBox "Please enter or Select a Job!", vbOKOnly, "Invalid Search Criterion!"
        Me![cboJob_ID].SetFocus
    Exit Sub
End If
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("job_ID")
    DoCmd.FindRecord Me!cboJob_ID
   Job_ID.SetFocus
    strjobRef = Job_ID.Text
    cboJob_ID.SetFocus
    strSearch = cboJob_ID.Text
    If strjobRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        Job_ID.SetFocus
        cboJob_ID = ""
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            cboJob_ID.SetFocus
 
    End If
End Sub
 

the error stems from this line:

docmd.showallrecords

any help would be appreciated
thank you very much

[mseo]

hi,
I need a solution for this problem
thank you

[patjones]

What happens when you remove DoCmd.ShowAllRe cords? I have never used this before, but based on a quick review it seems like you shouldn't need it unless there was a filter previously applied to the form's recordsource.

Pat

[mseo]

@zepphead80
thank you for your reply
when I remove DoCmd.ShowAllRe cords I get error for next lines of code
DoCmd.GoToContr ol ("job_ID")
DoCmd.FindRecor d Me!cboJob_ID
I think the code I use doesn't work perfectly for the purpose of edit form
so I would use create predicate , with beforeupdate trigger to prevent controls to be null
please provide me any suggestions
thank you I really appreciate your help

[patjones]

Is this a bound form, and if so where are you setting the form's recordsource?

[mseo]

I use bound controls form and tbl_jobs record source
because i am not familiar with using unbound forms
but if the form work properly with unbound controls form I will try it
thank you very much

[mseo]

thank you very much
I can use create predicate code to find the record and it works fine, for edit's forms and doesn't collide with before_update trigger but i still need why the above-mentioned code generates this error, just for knowing
thank you

[patjones]

@mseo
What is the error that you're getting?

[mseo]

@zepphead80
thank you
the error I get is run-time error 3021 No current record
in this problem I should to set filter and Docmd.showallre cords It's intended to remove a filter, if we can handle the showallrecords if the record found that will solve the whole problem, I still think about this problem, because it is the first time I use this method (showallrecords )
so, I used this till I know if I can do it using the previous approach

If Not IsNull(Me.cboJob_id) Then
        strWhere = strWhere & " AND " & "tbl_jobs.[Job_id] = '" & Me.cbojob_id & "'"
    end if

this works great with beforeupdate trigger
thank you very much
I really appreciate your dedication

[patjones]

I would probably rewrite your code slightly and make use of recordset processing. I can show you what I would do...can you just let me know what column in your table holds job ID information, i.e. what is the column name? Thanks.

Pat