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]

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

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]

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

What is the error that you're getting?

[mseo]

What is the error that you're getting?

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

[mseo]

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

Thank you very much
the field name is Job_ID and column that holds it is column(0)
and here's the structure of tbl_Jobs, and the fields is listed in the same order as I have in the database

Job_ID; String; PK
JobTitle;String; Indexed
Description;Memo

thank you again and over again

[patjones]

What I would do in your situation is use recordset processing. It isn't that your method can't work, however recordset processing will give you some flexibility. Try this:

Private Sub Search_Click()

Dim rst As DAO.Recordset
Dim strJobRef As String

If IsNull(Me.cboJob_ID) Or Me.cboJob_ID = "" Then
     MsgBox "Please enter or select a job!", vbOKOnly
     Me.cboJob_ID.SetFocus
     Exit Sub
End If

Set rst = Me.RecordsetClone
strJobRef = Me.cboJob_ID

rst.FindFirst "Job_ID = '" & strJobRef & "'"

If rst.NoMatch Then
     MsgBox "Match not found for " & strJobRef & ". Try again please.", vbOKOnly, "Invalid Search"
Else
     MsgBox "Match found for " & strJobRef & ".", vbOKOnly, "Search Successful"
End If

rst.Close
Set rst = Nothing

End Sub