By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,297 Members | 1,429 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,297 IT Pros & Developers. It's quick & easy.

Form For Edit & Delete

mseo
100+
P: 181
hi,
I have form for edit and delete
within me form edit or delete job
I have before_update trigger
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_beforeUpdate(Cancel As Integer)
  2.     If IsNull(Me![JobTitle]) Then
  3.         MsgBox "You must enter a Job first then you can save it", vbExclamation
  4.         Cancel = True: Me![JobTitle].SetFocus
  5.     ElseIf IsNull(Me![Job_ID]) Then
  6.         MsgBox "You must enter The ID then you can save it", vbExclamation
  7.         Cancel = True: Me![Job_ID].SetFocus
  8.     End If
  9. 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2.     Dim strjobRef As String
  3.     Dim strSearch As String
  4.     If IsNull(Me![cboJob_ID]) Or (Me![cboJob_ID]) = "" Then
  5.         MsgBox "Please enter or Select a Job!", vbOKOnly, "Invalid Search Criterion!"
  6.         Me![cboJob_ID].SetFocus
  7.     Exit Sub
  8. End If
  9.     DoCmd.ShowAllRecords
  10.     DoCmd.GoToControl ("job_ID")
  11.     DoCmd.FindRecord Me!cboJob_ID
  12.    Job_ID.SetFocus
  13.     strjobRef = Job_ID.Text
  14.     cboJob_ID.SetFocus
  15.     strSearch = cboJob_ID.Text
  16.     If strjobRef = strSearch Then
  17.         MsgBox "Match Found For: " & strSearch, , "Congratulations!"
  18.         Job_ID.SetFocus
  19.         cboJob_ID = ""
  20.         Else
  21.           MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  22.             , "Invalid Search Criterion!"
  23.             cboJob_ID.SetFocus
  24.  
  25.     End If
  26. End Sub
  27.  
the error stems from this line:
Expand|Select|Wrap|Line Numbers
  1. docmd.showallrecords
any help would be appreciated
thank you very much
Jun 26 '10 #1
Share this Question
Share on Google+
11 Replies


mseo
100+
P: 181
hi,
I need a solution for this problem
thank you
Jul 8 '10 #2

patjones
Expert 100+
P: 931
What happens when you remove DoCmd.ShowAllRecords? 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
Jul 8 '10 #3

mseo
100+
P: 181
@zepphead80
thank you for your reply
when I remove DoCmd.ShowAllRecords I get error for next lines of code
DoCmd.GoToControl ("job_ID")
DoCmd.FindRecord 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
Jul 8 '10 #4

patjones
Expert 100+
P: 931
Is this a bound form, and if so where are you setting the form's recordsource?
Jul 8 '10 #5

mseo
100+
P: 181
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
Jul 8 '10 #6

mseo
100+
P: 181
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
Jul 9 '10 #7

patjones
Expert 100+
P: 931
@mseo
What is the error that you're getting?
Jul 12 '10 #8

mseo
100+
P: 181
@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.showallrecords 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
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.cboJob_id) Then
  2.         strWhere = strWhere & " AND " & "tbl_jobs.[Job_id] = '" & Me.cbojob_id & "'"
  3.     end if
this works great with beforeupdate trigger
thank you very much
I really appreciate your dedication
Jul 13 '10 #9

patjones
Expert 100+
P: 931
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
Jul 13 '10 #10

mseo
100+
P: 181
@zepphead80
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
Expand|Select|Wrap|Line Numbers
  1. Job_ID; String; PK
  2. JobTitle;String; Indexed
  3. Description;Memo
  4.  
thank you again and over again
Jul 13 '10 #11

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2.  
  3. Dim rst As DAO.Recordset
  4. Dim strJobRef As String
  5.  
  6. If IsNull(Me.cboJob_ID) Or Me.cboJob_ID = "" Then
  7.      MsgBox "Please enter or select a job!", vbOKOnly
  8.      Me.cboJob_ID.SetFocus
  9.      Exit Sub
  10. End If
  11.  
  12. Set rst = Me.RecordsetClone
  13. strJobRef = Me.cboJob_ID
  14.  
  15. rst.FindFirst "Job_ID = '" & strJobRef & "'"
  16.  
  17. If rst.NoMatch Then
  18.      MsgBox "Match not found for " & strJobRef & ". Try again please.", vbOKOnly, "Invalid Search"
  19. Else
  20.      MsgBox "Match found for " & strJobRef & ".", vbOKOnly, "Search Successful"
  21. End If
  22.  
  23. rst.Close
  24. Set rst = Nothing
  25.  
  26. End Sub
Jul 13 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.