Opening a form by clicking on invididual records in a continuous form/report

[vman]

Hi,

I am fairly new to MS Access and don't have much understanding on VBA code as well. However, for the sake of learning and to replace and old excel based solution used to capture details of various team members in our team - I am trying to create a database in MS Access.

So far, I have created a "Main" (frmMain) form which has a button to open up a "summarized" report (rptEmployeeSummary) of various people in the team. I wan't to be able to click on an individual's name such that it opens up the record for that employee from the main database in another form (frmEmployeeDetails). That form would then have an update button and a cancel button to allow the users to come back to the main report or to update any values in the main database.

I got the first part working, that is, I am using a macro to open up the required record into a new form when the user clicks on that particular record, however, the problem is that once the user closes the "sub-form" (frmEmployeeDetails), and clicks on the record for some other employee, I get an error:

The table "tblEmployeeDetails" is already open exclusively by another user, or it is already opened and cannot be manipulated programmatically. What am I missing here?

Let me know if I haven't made my question clear or if any more information is needed.

Thanks
vman

 

[tad0075]

vman -

I just put together something like this. My method was to set an "on click" event for the record you want to search (in you case, employee name or ID number) to open your form filtered by the employee name or ID number.

To accomplish this, select whichever field you want to be clickable in rptEmployeeSummary, go to the properties window under the Events tab, and find "on click." You can build your event either as a macro or as VBA code. If you're more comfortable using a macro, set an "OpenForm" command. Then, you'll want to include your filter under Where Condition. Or if you want to try some VBA, open up the Code Builder and try it out. You can copy in the code from my thread "VBA finds string field in detail of report, not in group header" and modify it to include your form name and your filtering fields. Make sure you use the first code, since I'm still trying to make the 2nd one work

I hope this helps!

-Tyler

 

[vman]

Thanks, Tyler.

Sorry, if I did not make my question clear. I am able to code the macro and open up the form. My problem is that it works only once.

For example, if I generate the report which has 5 employees in it, and I click on any one of them - it would open up the frmEmployeeDetails, as I want it to do for that employee. However, when I close the form frmEmployeeDetails and comeback to my original report (which is still open), and click on another employee to display its details - it gives me the error that the underlying table is already open in exclusive mode so I can't view the details for the next employee.

I hope that explains my scenario better, else I'll try and include some screenshots on the error I am getting and some sample report.

vman

 

[tad0075]

vman -

Thanks for the clarification. I assume nobody else is editing the same tables and forms you're working with while you're in there? Assuming that's not the issue, you might try putting a Close command into your macro right before OpenForm to ensure it gets closed completely.

Also, I don't get this kind of error when doing this in a report instead of a form. If you don't need to edit employee information through this process, you might try that instead.

Beyond this, I'm out of ideas

-Tyler

 

[vman]

Thanks for trying Tyler, and for the idea for using a "close" before Opening. I am new to access and macros/VBA so I wasn't aware that a "close" clause exists.

However, Unfortunately that did not work either I tried a close both before and after the openform clause, but to no avail.

To give you a brief summary of why I have these forms and reports in place - from the main form - I have just one button to load the employee details. I originally designed that as a continuous form. However, with that I thought that someone may actually edit few fields in the form while just viewing the details of the employees. So, to avoid that kind of an error propping up. I decided that from the main form, if someone clicks on the button "Team details", it would load the details of employees in a report - which could be edited - if that's really the intent.

So the UI looks something like this:

Main Form
-Team Details
- Other options (which I'll work on later)

Team Detail loads the report - rptEmployeeDetails

Click on any record in rptEmployeeDetails to load the employee's details in the form if you really to edit/update the details.

Close the "Update" form. Come back to the report.

Click on another employee details (that you want to update) - this is what's not working.

And, NO, there are no other users. I am the only one - as I am still developing the whole database

 

[vman]

For anyone who might face the same problem - the issue was resolved by changing the "Record Locks" property for the form "frmEmployeeDetails". Good Luck!