Need to stop form from auto-saving records.

[Dherrera]

there are 3 forms and 1 table involved here. the first form (frmExpSnapShot) has a button to open the second form (frmViewNotes) and the second form has a button to open the third form (frmEnterNotes). the table (tblNotes) contains the notes and has a autonumber ID for each different note entry.

frmExpSnapShot contains expense report information and has a button that will open frmViewNotes for the user to view notes that correspond to that record(they are connected by an expense id). on frmViewNotes there is a button to open frmEnterNotes that will allow the user to enter new notes for that record.
when ever there is a new expense report with no notes and the frmViewNotes is opened and closed without adding any new notes a new record is still generated.

 

[Dherrera]

ive noticed that the first record in the Notes table will always get associated with the current Expense ID when ever the ViewNotes form is opened. so its always changing its related record.

 

[willir]

Hmmm

Is the Notes on a separate table ?
If so, is there a one-to-many relationship between the Expense table and the Notes table ?

Or is the Notes included with the Expense table ?

This is an important detail that you need to resolve. Use the relationship tool (from the Menu, "Tool" -> "Relationships"), and query builder to look at your data.

Next, you have to look at the cause. It looks like there is coding involved that makes assumptions that the current Notes view is associated with the current expense record. This is always a danger when synchronizing two forms. Coding on both the forms have to accommodate expected and unexpected actions from the end-user.

For example, locking the current expense record until the Notes table is closed. (And locking the PageDown and PageUp keys while the Notes table is open, and you have separate Notes table where it is expected to have only one Note per Expense record.)

Look at the logic - look for holes in the logic. Then look for the code.

Richard

 

[Dherrera]

the notes is on a seperate table and has a 1-m relationship with the expense table. im not sure what you mean by the danger when synchronizing the two forms. i thought when opening a form that has related information there is no problem since it is an event that is generated by the access wizard. as for the locking, the expense record is locked until it is release by the current user so thats not a proble but the page up and down, what does that have to do with this?

 

[TheAceMan1]

How are ya Dherrera . . . . .

First you said:

[blue]there are 3 forms and 1 table involved here.[/blue]

Then is answering Willar, you said:

[blue]the notes is on a seperate table and has a 1-m relationship with the expense table.[/blue]

I assuming the expense table is on the one side.

[blue]when ever there is a new expense report with no notes and the frmViewNotes is opened and closed without adding any new notes a new record is still generated.[/blue]

Are you saying a new record is being added to the [blue]Notes Table[/blue]?

Post the following Info:

[blue]Expense Table - Name
Expense Table - PrimaryKey Name & DataType

Notes Table - Name
Notes Table - PrimaryKey Name & DataType
Notes Table - ForeignKey Name & DataType (in Join 1 to many)[/blue]

I see several things, including synchronizing of forms. [purple]The info will spell it all out[/purple].

See Ya! . . . . . .

 

[willir]

Dherrera

the first form (frmExpSnapShot) has a button to open the second form (frmViewNotes) and the second form has a button to open the third form (frmEnterNotes). the table (tblNotes) contains the notes and has a autonumber ID for each different note entry.

This suggests something different from

there are 3 forms and 1 table involved

...Moving on

button to open the third form (frmEnterNotes).

Is this form for entering Notes a subform, or a popup / separate form? It sounded to me like you were describing a seperate / popup form. Here is the danger - How does the frmEnterNotes now how to link to the Expense record in form frmExpSnapShot ??

If you are using subforms, this is not a big issue. But if you have two seprate forms open (can move them around, close them), then the forms have to have coding that synchronize the two.

Richard

 

[Dherrera]

TheAceMan1,
here is the information you asked for. what actually happens is that the very first time that the frmViewNotes form is opened it creates a blank record but stores the FK in that record. Then when another record is opened and the ViewNotes form is opened it assigns the FK of that record to the blank record. does that make sense?

Expense Table info:
tblExpenses
--PK: ExpenseID
----Data Type: Text

Notes Table info:
tblNotes
--PK: NotesID
----Data Type: Auto Number
--FK: fkExpenseID
----Data Type: Text

 

[TheAceMan1]

Dherrera . . . . .

That was'nt what I expected. So to go a little deeper:

Post the code of the button on the [blue]frmExpSnapShot[/blue] form.

Post the [blue]Record Source[/blue] of frmViewNotes.

See Ya! . . . . . .

 

[Dherrera]

here is "frmExpSnapShot"

Its record source is the following sql query:

SELECT tblExpenses.ExpenseID, tblExpenses.TotalExpenses, tblExpenses.SubmittedBy, tblExpenses.ApprovedBy, tblExpenses.TotalReimbursement, tblExpenses.Today, tblExpenses.Status, tblEmployees.userLevel, * FROM tblEmployees LEFT JOIN tblExpenses ON tblEmployees.EmployeeID=tblExpenses.SubmittedBy;

The code behind the Notes button is:

Private Sub cmdNotes_Click()
On Error GoTo Err_cmdNotes_Click

    Dim stDocName As String
    Dim strExpenseID As String
    Dim strMainExpID As String
    
    Dim stLinkCriteria As String
    
    strExpenseID = Forms!frmExpSnapShot!ExpenseID

    stDocName = "frmViewNotes"
    
   
    DoCmd.OpenForm stDocName, , , , acFormEdit, ,    strExpenseID
    
    Forms!frmViewNotes!ExpenseID = strExpenseID
   
    
Exit_cmdNotes_Click:
    Exit Sub

Err_cmdNotes_Click:
    MsgBox Err.Description
    Resume Exit_cmdNotes_Click
    
End Sub

 

[willir]

Dherrera

Pretty slick stuff Dherrera.

Okay, to me it seem obvious that you have a 1:M with Expenses on the 1 side and Notes on the M side. Is this what you want?

When I look at the Notes button, Notes (0) suggests the intention is to count the number of Notes for the expense report. I am not sure if there is code to support this (perhaps for the OnCurrent event)??

Have you tried making the Notes form "Modal"?

Modal Property
You can use the Modal property to specify whether a form opens as a modal form. When a form opens as a modal form, you must close the form before you can move the focus to another object. Read/write Boolean.

This would force the Notes form to be closed before the end user can switch back to the Expense part.

Otherwise, you are going to have to create OnCurrent events that ensure the Expense record matches Notes record(s).

Although I like the use of "space" on your form, you have room in some sections. For example the "Total Expesnes / Reimbursements" has a lot of space that will never be used. Same with many of the fields on the right side for Vehicle, Misc. (Not to sure about Summary) which suggests you can squeeze things over.

Another approach, and I like this one, is to use a tab control form for your subforms - a summary view on top with the details on each tab page.

By using a tab form, you can make use of the ability of master / child form links to maintain the linkage and preserve the data integrity.

You might want to add a commit button too, and then add the ability to lock an expense once it has been submitted or reimbursed -- nothing like changing the numbers after the fact to mess up the accounting.

Richard

 

[Dherrera]

yes i have a 1-m relationship between the Expense and Notes table. as far the counter on the button i have that coded in the OnLoad event of that form and in also on OnClose event of the frmViewNotes form.
ive tried setting the modal property on but that doesnt seem to help. i dont know what to do with this thing.

 

[TheAceMan1]

OK Dherrera! . . . . . .

[blue]when ever there is a new expense report with no notes and the frmViewNotes is opened and closed without adding any new notes [purple]a new record is still generated[/purple].[/blue]

The following code in your button is resopnsible for this:

[blue]Forms!frmViewNotes!ExpenseID = strExpenseID[/blue]

This instantiates a record. [blue]When you close the form its saved![/blue] According to you [blue]frmViewNotes[/blue] is for viewing only. You should'nt be pumping anything into any controls at all! In fact, editing should be disallowed on this form. You should be doing this for [purple]frmEnterNotes![/purple], and setting the ID from within [purple]frmEnterNotes[/purple] . . . . triggered by some data entry. What if the user changes their mind and decides to close [purple]frmEnterNotes?[/purple] . . . . . again the record will be saved with ID only.

[blue]ive noticed that the first record in the Notes table will always get associated with the current Expense ID when ever the ViewNotes form is opened. so its always changing its related record.[/blue]

This is an indication that [blue]frmViewNotes[/blue] is not being synchronized properly. I notice you pass the [blue]Forms!frmExpSnapShot!ExpenseID[/blue] via the variable [blue]strExpenseID[/blue] through the [purple]OpenArgs[/purple] of the DoCmd to [blue]frmViewNotes[/blue] as follows:

[blue]DoCmd.OpenForm stDocName, , , , acFormEdit, , strExpenseID[/blue]

Are you sure your handling the OpenArgs in [blue]frmViewNotes[/blue] properly?

Be aware, OpenArgs has to be a string. So if the data you want to pass is numeric, you'll have to do two conversions:

1) Str([blue]strExpenseID[/blue]) to pass thru OpenArgs.

2) Val(Me.OpenArgs) in [blue]frmViewNotes[/blue] to convert back.

See Ya! . . . . . .

 

[willir]

Dherrera

ive tried setting the modal property on but that doesnt seem to help

I guess I see an issue here. Your code should open up a Notes record for an existing record, but may not accommodate a new record, and may not accommodate multiple records.

First, fix your bad data. This may mean deleting bad records if you can not determine which record a memo is associated to.

For your open form code, cmdNotes_Click use the following...

Dim stDocName As String
Dim strExpenseID As String
Dim strMainExpID As String
Dim stLinkCriteria As String

Dim intOpenMode as Interger
    
strExpenseID = Me.ExpenseID
'Note that you can use the Me local reference
'Instead of the long, absolute reference

stDocName = "frmViewNotes"

'If a record exists, edit it, if add new record
If DCount("[fkExpenseID]" , "tblNotes", "[fkExpenseID] = " & CLng(strExpenseID)) > 0 Then
    intOpenMode = acFormEdit
Else
    intOpenMode = acFormAdd
End If

'Refresh to commit data
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70   
DoCmd.OpenForm stDocName, , , , intOpenMode, ,    strExpenseID
    
'DO NOT DO THIS - here Forms!frmViewNotes!ExpenseID = strExpenseID

Now for the code on the frmViewNotes form

For the OnLoad event

Dim lngExpenseID as Long
Dim booValid as Boolean
Dim strMsg as String

'Set Modal property so form has to be closed before moving on
Me.Modal = True

booValid = True

If Len(Nz(OpenArgs, "")) = 0 Then
   booValid = False
   strMsg = "No valid Expense Account" & vbCRLF _
   & "Please enter a valid Expense record first"
Else

   lngExpenseID = CLng(OpenArgs)

   If (DLookUp("[ExpenseID]", "tblExpenses", "[ExpenseID] = " & lngExpenseID)) Then
        Me.fkExpenseID = lngExpenseID
   Else
        booValid = False
        strMsg = "Invalid Expense Account ID: " & OpenArgs _
        & vbCRLF & "Please enter a valid Expense record first"
   End If
End If

If Not booValid Then
   'Invalid record - close form so no data is entered
   MsgBox strMsg, vbOKOnly, "Invalid Expense Record" 
   DoCmd.Close
End If

And another event procedure for OnCurrent

'Openning Arguments persists, ensure it is used
Me.fkExpenseID = CLng(OpenArgs)

This code does...
Expense record form
- Ensures valid expsense record committed to table
- Decides if Notes form to opened in Edit / Add New record

Notes record form
- Set Modal property so Notes form has to be closed before doing anything else - avoids bad data issues
- Ensures a valid ExpenseID was used
-- If ExpenseID is invalid, form is closed
- Ensures ExpenseID inserted as foreign key, fkExpenseID

Richard