prevent clicking on sub-form trying to update 'master' form

[tmc]

Hello,

I wonder if anyone can please help?

I have created a form with employee id numbers (among other fields) and within it a subform that is linked to the ID number form and will display the employee's name when the ID number is input.

So far so good! However i would like to disable being able to click on and select the sub form, because when i do access thinks i am moving on from the 'master' form and asks if i want to update the record.

This could get confusing for those who will be using the form and although i have used the subform propertied to prevent ammending, adding or deleting records in the sub-form, it is still possible to select it andget this update record infobox.

Any ideas please?

Many thanks, Tmc.

 

[Galaxiom]

Why is the employee's name field not part of the mainform records?
Sounds like it could be included in the main form's record source query rather than using a subform.

 

[tmc]

It's not included on the form because the form only records the employees id number, the location and the date they work.

The id number is linked to the employee name in another table.

This way when you input the id number on the 'main' form, the employees name matching that number comes up instantly on the screen via the small sub-form and helps prevent errors and looks quite good.

Couldn't think of how to get that information to come up instantly any other way, as it would only happen after i had updated the record, which kinda defeated the purpose of having the employee name entered at all.

Hope this makes sense and you'll have to appreciate i'm a real beginer at this!

Hope you can help me further and many thanks,

Tmc.

 

[wiklendt]

a better way is to have all the relevant employee details in one table (tblEmployees) and displayed in one form (frmEmployees).

then, put a listbox on your form whose record source is a query pulling all the employees with minimal info (eg: ID, GivenName, Surname, EmployeeNum).

on the "after update" event of the listbox, put code in there so that the record on the main form displays the selected employee.

similar set up to show you how this impacts visually:

code for this is quite minor (oops, i've left off error handling... naughty me!):

Private Sub lstItems_AfterUpdate()
    
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[[COLOR=Red]ItemID[/COLOR]] = " & Str(Nz(Me![[COLOR=Red]lstItems[/COLOR]], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

the bits in red you need to change to suit your setup.

 

[Galaxiom]

It's not included on the form because the form only records the employees id number, the location and the date they work.

I think you have your form and subform reversed to normal.
Normally the main form would hold the employee data and the subform would be used to enter the work data for the currently selected employee on the main form.

The required employee would be found on the main form using something like Wiklendt had posted.

 

[wiklendt]

I think you have your form and subform reversed to normal.
Normally the main form would hold the employee data and the subform would be used to enter the work data for the currently selected employee on the main form.

The required employee would be found on the main form using something like Wiklendt had posted.

ah, yup. forgot to add that in my post: tmc, you need to do what Galaxiom has said. the employee details go into employee table, then their own table (say, tblWorking) working hours and location - which is the source of the subform (this allows one employee to work in many different locations in different hours - called a one to many relationship).

 

[wiklendt]

in the same database that i posted that picture before, here is the "orders" tab from the same form.

this data, the ordering data, is in a one to many relationship to the item. the items are all in their own table. the orders have also their own table, and when we want to put in a new order, we select in our order form which item we're ordering.

that is, our lab requires a constant supply of certain items, so we order them multiple times throughout the year. so, instead of putting the item in multiple times, we just put in multiple orders for any one item...

so, in your scenario, employee are the equivalent of my items. your working hours/locations is equivalent to my ordering data.

kapish?