Differentiating names form and subform

Posted on 2015-02-16
Medium Priority
Last Modified: 2015-02-16
Hi all,

I am having difficulty distinguishing between two same surnamed individuals.

The sample I am attaching provides my dilemma.

I have two same surnamed individuals but when I highlight them in the form the same data appears in the subform.

Grateful if someone would assist.

Question by:PipMic
  • 3
  • 2
LVL 18

Expert Comment

ID: 40613020
It sounds like the master<=>child link between the form and subform is currently just on surname. You need to add another field to distinguish records with the same surname.

Edit: Actually I see that you're not using a subform, but a series of textboxes  populated by the on-click event of the list_staff listbox. For your current method you need to include the forename in the DLookup functions that you're using.

Author Comment

ID: 40613037
I have the following two criteria:-

What should I change?  Not too clear!

Private Sub list_staff_Click()
txt_Grade = DLookup("[Grade]", "tbl_Officer", "[Surname] = Forms![frm_Directory]![list_Staff]")
txt_Section = DLookup("[Section]", "tbl_Officer", "[Surname] = Forms![frm_Directory]![list_Staff]")
txt_Centrex = DLookup("[Centrex]", "tbl_Officer", "[Surname] = Forms![frm_Directory]![list_Staff]")
txt_directline = DLookup("[DirectLine]", "tbl_Officer", "[Surname] = Forms![frm_Directory]![list_Staff]")
txt_email = DLookup("[Email]", "tbl_Officer", "[Surname] = Forms![frm_Directory]![list_Staff]")

SELECT DISTINCTROW tbl_Officer.Surname, tbl_Officer.Forename FROM tbl_Officer ORDER BY tbl_Officer.Surname;
LVL 18

Accepted Solution

Simon earned 2000 total points
ID: 40613060
See how I've changed the DLookups. Tested and working.

Private Sub list_staff_Click()
txt_Grade = DLookup("[Grade]", "tbl_Officer", "[Surname] = '" & Me.list_staff.Column(0) & "' and [forename] = '" & Me.list_staff.Column(1) & "'")
txt_Section = DLookup("[Section]", "tbl_Officer", "[Surname] = '" & Me.list_staff.Column(0) & "' and [forename] = '" & Me.list_staff.Column(1) & "'")
txt_Centrex = DLookup("[Centrex]", "tbl_Officer", "[Surname] = '" & Me.list_staff.Column(0) & "' and [forename] = '" & Me.list_staff.Column(1) & "'")
txt_directline = DLookup("[DirectLine]", "tbl_Officer", "[Surname] = '" & Me.list_staff.Column(0) & "' and [forename] = '" & Me.list_staff.Column(1) & "'")
txt_email = DLookup("[Email]", "tbl_Officer", "[Surname] = '" & Me.list_staff.Column(0) & "' and [forename] = '" & Me.list_staff.Column(1) & "'")

Open in new window

Note: If your tbl_Officer has many rows, these multiple DLookups aren't going to be the best performing way of retrieving the data. If you want to stay with Dlookups, you could include the PID as a zero-width column in your listbox and use that as the criteria, and a faster method would be to create a recordset based on a query like 'select grade,section,centrex,directline,email from tbl_officer where PID =' & me.staff_list.column(x)
and then set your textboxes to the appropriate columns of the recordset.
You could also use a subform linked to the main form by PID.
However, the above code works and switching to recordset or subform is a larger change to your design and subject for another question if necessary.

Author Comment

ID: 40613084

grateful for your answer...
LVL 18

Expert Comment

ID: 40613087
No problem. I may have edited it since you saw it. I think our posts crossed.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question