Keeping a text field info after Add New button is clicked. 1

[Dherrera]

I have a form, "frmEmpExpenses",that allows users to enter expense report information one day at a time. When the form is initially opened an ExpenseID is generated and is stored in field "txtExpenseID". After the user has entered all info they are to click on the "Save" button which saves the info and clears the form.

What i am trying to do is this, lets say the user has two days of info to enter, he opens the form and ExpenseID # 0604001 is generated. He enters all the info for the first day and clicks "Save", form is cleared and user enters the next days info. I can get this to work however when the "Save" button is clicked it generates a new ExpenseID # which i dont want.

Is there a way to keep the "ExpenseID" constant while the form is open?

 

[scriverb]

Just how is the ExpenseID being generated? Through a Function call? Please post that code and method.

What I have in mind is prompting the user when the Save button is clicked to indicate if there is more info to be entered? If there is then load of a Global variable with the already generated ExpenseID value. Now when the new record is started up and the function call tries to generate a new id the code will check the global variable and if it contains an ExpenseID then use it rather than generate a new one.

If this sounds like it will work then post the code for the generation of the ExpenseID.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TheAceMan1]

How are ya Dherrera . . . . .

The key question here is:

[purple]How are you recognizing the same user?[/purple]

Use that in your code to bypass generation of new ExpenseID and use previously generated value.

See Ya! . . . . . .

 

[Dherrera]

Thanks for the reply guys. The Expense ID is generated through a function. Here is the code for generating that.

The user is identified with the "currentuser()" function.

Private Sub txtExpenseID_Enter()

'This procedure generates the Expense ID with the month and
'the last two digits of the current year and increments 
'each time.  The year counter does roll over at the end of
'the year.

Dim rsa As Recordset
Dim rsb As Recordset
Dim DateNow
Dim strMonth As String
Dim strYear As String
Dim temp As Integer

Set rsta = CurrentDb()
Set rstb = CurrentDb()
Set rsa = rsta.OpenRecordset("tblCurrentYear1", dbOpenDynaset)
Set rsb = rstb.OpenRecordset("tblCounter1", dbOpenDynaset)
rsa.Edit
rsb.Edit

DateNow = date
strYear = Right(DatePart("yyyy", Now()), 2)
strMonth = Right(DatePart("m", Now()), 2)
    If IsNull(txtExpenseID.Value) Then
        If DateNow > rsa![year] + 365 Then
            rsa![year] = rsa![year] + 365
            rsb![counter] = 0
            rsb.Update
            rsa.Update
        Else
        End If
     
     'new code
     temp = DMax("counter", "tblCounter1") + 1
     Me![txtExpenseID] = strMonth & strYear & Format$(temp, "000")
        
       
     rsb.Edit
     rsb![counter] = rsb![counter] + 1
     rsb.Update
    Else
    End If
rsa.Close
rsb.Close
End Sub

 

[scriverb]

Create a Form level variable that will hold the identification of the last user:

Dim vCurrUser as String

Private Sub txtExpenseID_Enter()

'This procedure generates the Expense ID with the month and
'the last two digits of the current year and increments
'each time.  The year counter does roll over at the end of
'the year.

Dim rsa As Recordset
Dim rsb As Recordset
Dim DateNow
Dim strMonth As String
Dim strYear As String
Dim temp As Integer

Set rsta = CurrentDb()
Set rstb = CurrentDb()
Set rsa = rsta.OpenRecordset("tblCurrentYear1", dbOpenDynaset)
Set rsb = rstb.OpenRecordset("tblCounter1", dbOpenDynaset)
rsa.Edit
rsb.Edit

DateNow = date
strYear = Right(DatePart("yyyy", Now()), 2)
strMonth = Right(DatePart("m", Now()), 2)
    If IsNull(txtExpenseID.Value) Then
        If DateNow > rsa![year] + 365 Then
            rsa![year] = rsa![year] + 365
            rsb![counter] = 0
            rsb.Update
            rsa.Update
        Else
        End If
     
     'new code
     temp = DMax("counter", "tblCounter1") + [red]IIF(CurrentUser() = vCurrUser, 0, 1)[/red]
     Me![txtExpenseID] = strMonth & strYear & Format$(temp, "000")
        
       
     rsb.Edit
     rsb![counter] = [red]temp[/red]
     rsb.Update
    Else
    End If
rsa.Close
rsb.Close
[red]vCurrUser = CurrentUser()[/red]
End Sub

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

Thanks scriverb, that change is keeping the same Expense ID however its not saving the different days. im also getting the following message after saving. im not sure what it means because all my relationships are correct. have you ever seen this before?

"The current field must match the join key 'ExpenseID' in the table that serves as the 'one' side of the one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry withe the desired join key in the 'many-only' table."


im not sure if i mentioned how i have the tables for this set up. i have three tables, one called "tblExpenses" and another "tblExpenseDetails". the PK in "tblExpenses" is "ExpenseID" and also is the FK in "tblExpenseDetails".
"tblExpenseDetails" has a PK of ID which is an autonumber field. there is a one-to-many relationship between the two conntected by "ExpenseID". also, i have "tblEmployee" with the employee info, connected to "tblExpenses" via PK and FK "EmployeeID". That is also a one-to-many relationship.

 

[scriverb]

Please explain the form setup. RecordSource, subforms, subform recordsources, LinkChild and LinkMaster fields, etc. It sounds like you have a master form with a subform. the subform has the details of the expense report.

You said:

however its not saving the different days.

What are you referring to here?

When the save occurs are the first days records being saved? And, then is a new master record being created with the same ExpenseID as the first one. Are you using this ExpenseID as the link in the subform to match to the subform recordsource.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

The first form "frmExpenses" is using SQL queries to draw the necessary info. i have it set up with a subform to display all expense reports for a particular user. the user selects a name from the combo box(employee id) and the rest fills in. On this form's subform the information should display the reports just like its shown in the picture below.

This is all the code behind this form.

Option Compare Database

'If no employee is selected or if selected employee doesnt match current
'user then the button to create a new expense report is disabled.

Private Sub cboEmployeeID_AfterUpdate()
If IsNull(cboEmployeeID) Or Me.cboEmployeeID.Column(0) <> Me.txtCurrentUser Then
    Me.cmdNewExpRpt.Enabled = False
Else
    Me.cmdNewExpRpt.Enabled = True
End If
End Sub

'On form load the create new expense report is disabled.
Private Sub Form_Load()

    Me.cmdNewExpRpt.Enabled = False
    Me.Undo


End Sub

'Closes form.
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

'Refreshes information on current form.
Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cmdRefresh_Click:
    Exit Sub

Err_cmdRefresh_Click:
    MsgBox Err.Description
    Resume Exit_cmdRefresh_Click
    
End Sub


'Opens up the detailed expenses form and transfers the current user to opened form.
Private Sub cmdNewExpRpt_Click()
Dim stDocName As String
Dim strEmployeeID As String
Dim strmainEmpID As String
Dim strCurrentUser As String

    stDocName = "practiceEmpExpenses"
    
    strEmployeeID = Me.cboEmployeeID.Column(0)
    strCurrentUser = Me!txtCurrentUser
    
 
    DoCmd.OpenForm stDocName, , , , acFormEdit, , strEmployeeID


    If strmainEmpID <> strEmployeeID Then

        DoCmd.GoToRecord , , acNewRec
        Forms!practiceEmpExpenses!txtSubmittedBy = strEmployeeID
        
        
 
    End If
    
End Sub

 

[Dherrera]

On the second form, frmExpDetails, there is no subform. when the form loads the ExpenseID is generated. the user selects a date from the calendar at the top and fills in all the necessary information for that date. the user then clicks save and the form clears, prompts the user to select a new date. each new date has an id that is supposed to be saved to tblExpenseDetails. for example, i am creating a new report and generate ExpenseID 604001, the current record is given and ID# of 1(this is PK in tblExpenseDetails). i click save after entering my info and select a new date, the ID# is now at 2 for the next date. each one of those should be saved to tblExpenseDetails but it is not saving them at all.

here is a screen shot of this form.

and here is all the code behind this form.

  Option Compare Database

Dim vCurrUser As String


'This procedure generates the Quote ID with the last two digits of the current year
'and increments each time.  The year counter does roll over at the end of the year.
Private Sub txtExpenseID_Enter()

Dim rsa As Recordset
Dim rsb As Recordset
Dim DateNow
Dim strMonth As String
Dim strYear As String
Dim temp As Integer

Set rsta = CurrentDb()
Set rstb = CurrentDb()
Set rsa = rsta.OpenRecordset("tblCurrentYear1", dbOpenDynaset)
Set rsb = rstb.OpenRecordset("tblCounter1", dbOpenDynaset)
rsa.Edit
rsb.Edit

DateNow = date
strYear = Right(DatePart("yyyy", Now()), 2)
strMonth = Right(DatePart("m", Now()), 2)
    If IsNull(txtExpenseID.Value) Then
        If DateNow > rsa![year] + 365 Then
            rsa![year] = rsa![year] + 365
            rsb![counter] = 0
            rsb.Update
            rsa.Update
        Else
        End If
     
     'new code
     'temp = DMax("counter", "tblCounter1") + 1
     temp = DMax("counter", "tblCounter1") + IIf(CurrentUser() = vCurrUser, 0, 1)
     Me![txtExpenseID] = strMonth & strYear & Format$(temp, "000")
        
       
     rsb.Edit
     rsb![counter] = temp
     'rsb![counter] = rsb![counter] + 1
     rsb.Update
    Else
    End If
rsa.Close
rsb.Close
vCurrUser = CurrentUser()
End Sub


'Saves current record to tblExpenseDetails.
Private Sub cmdSave_Click()
       
On Error GoTo Err_cmdSave_Click
    
    DoCmd.Save
    Me.Undo
    MsgBox "Please select a new date.", vbOKOnly + vbInformation, "Change Date"
    Me.txtExpenseID.SetFocus
    
Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

'Closes form
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

'The next three procedures calculate the total for meals

Private Sub txtBreakfast_LostFocus()
    Dim meal1 As String
    
    If IsNull(Me.txtBreakfast) Then
        'do nothing
    Else
        Me.txtMealTotal = Me.txtBreakfast
    End If
    
End Sub
Private Sub txtLunch_LostFocus()
    Dim meal2 As String
    
    If IsNull(Me.txtLunch) Then
        'do nothing
    Else
        Me.txtMealTotal = Me.txtLunch + Me.txtMealTotal
    End If
    
End Sub
Private Sub txtDinner_LostFocus()
    Dim meal3 As String
    
    If IsNull(Me.txtDinner) Then
        'do nothing
    Else
        Me.txtMealTotal = Me.txtDinner + Me.txtMealTotal
    End If
    
End Sub

 

[Dherrera]

any ideas scriverb?

 

[scriverb]

I have a lot to digest here. My question still is what do you mean when it is not saving the days? Please explain. Please explain the part that is not working now.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

what i mean by "not saving the days" is that user enters information in "frmExpDetails" for one day and clicks save, then enters another day of information, etc. each one of those days is a seprate record in tblExpenseDetails and has an ID#(the PK in that table). So ExpenseID# 604001 could have several related records(one for each day).

Let me try to draw a diagram here.


Expense ID# 604001
|
|
---------------------------------------------
| | | | |
| | | | |
Day 1 Day 2 Day 3 Day 4 Day 5
(ID# 1) (ID# 2) (ID# 3) (ID# 4) (ID# 5)



when i click the save button for day 1(ID# 1) it should save that, when i click save for day 2(ID# 2) it should save that as well, however, its not saving the seperate days in tblExpDetails.

 

[scriverb]

Boy, I don't see where changing the Function that we originally were working on would change this SAVE process. Especially without an error message being presented. When you say these records are not being saved are you actually checking the table to see if they are not there. If you form is being populated by a form the criteria for the form may be eliminating them.

If you can put together a small database with the tbl structure, and pertitent forms, and code you can send it to me and I will take a look. See my profile.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

ok, scriverb, i sent you the database

 

[scriverb]

I don't see a form named "frmExpDetails" as referred to in your last post. Also, the only form with a SAVE button is frmEmpExpenses. What form should I be looking at here. And where is the SAVE button. I don't see it in the mainform with the subform control.

Also, emails back to you are being rejected.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

sorry, i typed in the wrong form name on my previous post. there is no save on the main form, frmViewExpenses. the saving is done on frmEmpExpenses, that is where the info is input by the user.

 

[scriverb]

Dherrera: I have reviewed the foms now and your form used in the subform control that is not saving the days information added has its properties: Allow Edit, Deletion, Additions, and Data Entry all set to NO. This will prevent you from making any change of any kind to the tables records. If this form is for Additions only then set the Allow Additions and Data Entry to Yes.

This should solve your problem as it is stated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

scriverb,
i really appreciate you help with all this.
i tried what you suggested but that didnt work. however, i did find a way to make it work. what i did was changed the layout of the frmEmpExpense. i put in subform on that form that will contain all the fields for the information on "tblExpenseDetails" and the info from "tblExpenses" is placed on the main form. this worked out fine, it keeps the ExpenseID and it saves each new day that is entered.

thanks again.

 

[scriverb]

Did you set up the Link Child and Link Master fields in the subform control so that the linked records will automatically be displayed and when new records added the foreign key link field is updated properly. Make sure that the properties of the subform form are set to allows additions. This is the reason the records were not being saved in your previous setup.

This is the accepted way to update a one-to-many relationship between two tables.

Good luck

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

yeah, i set up all the links correctly and it is set to allow additions. it is working perfectly.


thanks for your help scriver.