VBA Errors (1 Viewer)

I am trying to set up a rather simple Journal tracker based on a primary entry and secondary entries with updates, most of it works but I have some glitches:

The main form has mandatory fields such as description and status, if no text is entered and it will not save the record nor it will give a warning (unless your tab through all records in the form, all mandatory fields are to be filled to be saved.

If I delete a record the main form will display #Deleted rather than removing the record (If I refresh this will disappear), I have tried to place Me.Requery at the end of the code but it does not seem to be working.

When I add a record from frmMain it opens the frmJournalEntry but it won’t set JournalEntryDescription to focus (error on closing form), same for the sfrmJournalEntryTimeline.

The search box does not seem to be working it should only be looking in the field description and contact.

Why have frmJournalEntryList as a subform on an unbound main form instead of a standalone?

Tabbing out of any control tries to set focus on the Search box. This is because TabStop property of each control is set to No. Why? If you are going to allow data entry/edit in this form, set TabStop Yes.

How do you delete record? I don't see a button or any code nor record selectors. What does 'not working' mean - error message, wrong results, nothing happens?

Should set TabOrder property of controls to control sequence of movement.

Please provide more info about what info you will be tracking and some comments re the processes(business rules) involved.
Why doesn't your relationship window show all tables involved?
Good luck with your project.

test adding or deleting records.

frmJournalEntry form :


Private Sub Form_Current()
Me.JournalEntryDescription.SetFocus
End Sub

Private Sub CmdSave_Click()
If Me.JournalEntryDescription = vbNullString Or IsNull(Me.JournalEntryDescription) Then
Me.JournalEntryDescription.SetFocus
MsgBox "Please Enter a description for this Journal entry", vbInformation, "WHO ENTERS A JOURNAL ENTRY WTHOUT A DESCRIPTION??"
GoTo exithere
End If


If Me.Status = vbNullString Or IsNull(Me.Status) Then
Me.Status.SetFocus
Cancel = True
MsgBox "Please Enter a Status for this Journal entry", vbInformation, "STATUS REQUIRED"
GoTo exithere
End If

Me.Refresh
DoCmd.Close acForm, Me.Name
exithere:

End Sub


Private Sub CmdDelete_Click()
On Error GoTo CmdDelete_Click_Err

If Not Me.NewRecord Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "frmJournalEntry"
Forms("frmMain").Form.frmJournalEntry.Requery
Else
MsgBox "Can not delete new record"
End If

CmdDelete_Click_Exit:
Exit Sub

CmdDelete_Click_Err:
MsgBox Error$
Resume CmdDelete_Click_Exit

End Sub

As for your search not working you need to change these values:

Private Sub CmdSearch_Click()
Dim strSearch As String
Dim strTxt As String
strTxt = Me.TxtSearchBox.Value
'strSearch = "SELECT * from tblJournalEntry where ((tblJournalEntry.Description like ""%" & strTxt & "%"") or (tblJournalEntry.Contact like ""%" & strTxt & "%""))"
strSearch = "SELECT tblJournalEntry.*" _
& " FROM tblJournalEntry" _
& " WHERE (((tblJournalEntry.JournalEntryDescription) Like '*" & strTxt & "*' )) OR (((tblJournalEntry.Contact) Like '*" & strTxt & "*'))"

Me.RecordSource = strSearch
Me.RecordSource = Replace(strSearch, "{0}", Me.TxtSearchBox)
'Me.TxtSearchBox = "" DONT REMOVE THE SEARCHTERM IT'S BAD PRACTICE
End Sub

First of all thanks for all the imput, you are dealing with a rather newby here.....

June7 said:

Why have frmJournalEntryList as a subform on an unbound main form instead of a standalone?

Click to expand...

Can you give me more info on what you mean for standalone?

The Delete button is on the frmJournalEntry and has the code attached and the result is on the Pic 01 attached, I would like to requery the frmMain

I have managed to solve the issue of the search box as it is now working, I created a qry to work on it.

jdraw: not all the relationships are there as I am not sure as yet as what I want to keep a table for the contact or leave it as free format.

arnelgp: thanks for the sample, still experiencing the problem as on the pic for the deleted record. can you tell me the txtDummy you have added what is it for? And how did you achieve that cool option to highlight all the record on the list?

How can I set to focus the description field when Adding a record or when double click on the record?

Txtdummy is used on conditional format, the highlight. I set its value on the current event of the form.

arnelgp: thanks! the record ID is already available in the form, any particular reason you have placed it on the form header?