New record created when I click on subform

[Rated047]

I have a form with a subform. The form is bound to a table.


I have made a search button that will filter records in the subform according to the date range entered in the main form.
However after filtering when I click on the subform, the new record gets created and saved.

How can not saved this new record (undo or delete)?

[pbaldy]

What is your code? A record should not be created unless you're populating it.

[Edgar]

You might be storing the date entered to search, if that's the case, then I assume it's a textbox bound to a table. Make sure your search textbox is unbound.

[Rated047]

You might be storing the date entered to search, if that's the case, then I assume it's a textbox bound to a table. Make sure your search textbox is unbound.

The form is a employee leave form which I use to enter daily leaves of employees. I have a subform in the form that shows the leaves recorded for the current date and also a search button where you can search for leaves using at least of the following criteria (date range (i.e. start date & end date), EmpID, LeaveType).

So, I use the form mainly to enter leaves hence all textboxes are bound but I also want to make it so we can search for leaves and edit them if needed. Also, my biggest issue currently is that when I try to click on the subform within the main form, the EmpID returns error since its empty. (error reads "The Microsoft Access database engine cannot find a record in the table 'Employees' with key matching field(s) 'EmpID'.")

File: Employee Leave Management.zip

Is there a way to do this?

[Bob Fitz]

I think you need a Main form, bound to the Employees table. A sub form, bound to the EmpLeaves table.

In the header section of the main form whatever controls you need to find a particular employee. These controls would be unbond. Perhaps just a combo which lists the employees would suffice. You could use the wizard to create this and it would create a macro that would do the search for you. The macro can be converted to vba code.

I would adopt a naming convention for all your db objects. ((You could search this forum or google for more on this)
I would avoid the use of spaces and special characters (e.g. #134/|\<> etc) in those names and I would minimise those names as much as possible. So, SDate rather than Start Date etc.
I would create a one to many relationship between the Employees table and the EmpLeaves table.

[Edgar]

You got the error you described because the query of the form was setup in a way that required EmpID, I did the following changes:

Employee Leave form:
Changed query source to the EmpLeaves table
Changed name of Designation textbox to txtDesignation, same for Type
Changed Emp_ID recordset to add Designation and Type
Added to Emp_ID an after_update event to populate Designation and Type
Changed query source of the subform to a query that would show all employee leaves + type of leave and employee name

You can take it from here. Reconfigure your search to filter by those fields.

Let me know how it goes.

[Bob Fitz]

Sample db of my earlier suggestions in post #5

[Rated047]

You got the error you described because the query of the form was setup in a way that required EmpID, I did the following changes:

Employee Leave form:
Changed query source to the EmpLeaves table
Changed name of Designation textbox to txtDesignation, same for Type
Changed Emp_ID recordset to add Designation and Type
Added to Emp_ID an after_update event to populate Designation and Type
Changed query source of the subform to a query that would show all employee leaves + type of leave and employee name

You can take it from here. Reconfigure your search to filter by those fields.

Let me know how it goes.

Thanks! This really helped, but I also want to add a vba code so that if I click on the subform in the middle of adding a leave, it deletes/undo the current record (the incomplete record).
I don't know which event I should use. I tried using Subform 'On Enter' event, but undo didn't work since it was already saved when i clicked on the subform and delete record deletes the record on the subform not the incomplete record.

[mike60smart]

A simple Command Button with Me.Undo should sort that ?

[Rated047]

A simple Command Button with Me.Undo should sort that ?

On which event should I use the undo action? Using it in Subform 'On Enter' does not work since it is already saved.

[mike60smart]

Hi
You would create the Command Button in the Detail area of the Subform and it is just a straightforward OnClick Event

[Rated047]

I don't want to create a Button to Undo, I want a way to automatically trigger the code when I click on the Subform within the Main form.
For e.g. I already have a similar code to undo any unsaved changes on Form Close event.

[mike60smart]

Not following your Logic

If you click on a Subform then all of the records will have been saved.

When you start entering a New Record in the Subform and you want to cancel, then the easiest Method is just Press Escape which cancels the current Record.

[Bob Fitz]

You could try some code in the Before Update event of the form but of course that would fire each time you leave an edited record.

[Rated047]

You could try some code in the Before Update event of the form but of course that would fire each time you leave an edited record.

Yes that works thank you. I just defined a new variable that keeps track on whether I pressed the save button or not.