Making a list of all persons in a table in a corresponding form.

Hi all,

In a database of mine I have a table containing persons' name, address and 
so on. In the corresponding form I have of course all the fields where the 
users put in the data. In this form I want to have a list showing all the 
persons in the table as well, so that the users can click the right person in 
the list and have his or her data filled into the fields. I have tried to 
make a table form from the same table and include it as a subform. This, 
however, was not successful because the subform only showed the person in 
focus in the form, not all the persons in the table. Also, I do not think it 
would be possible to click a person in the subform and  get the data filled 
into the fields, even if I a got all the persons in the table.

If you have a good idea how to make this, please let me know.

Thank you very much in advance.

Jo
0
Utf
12/11/2007 10:10:00 AM
access.forms 6864 articles. 2 followers. Follow

5 Replies
617 Views

Similar Articles

[PageSpeed] 41

Jo,

You don't need another table.   You need a unbound listbox or unbound 
combobox with rowsource being populated from the table where your employee 
info resides.  In the AfterUpdate event fill in the "fields" by addressing 
the columns on the listbox or textbox like this:

[EmpFirstName] = [cboEmployee}.Column(0)
[EmpLastNamr]= [cboEmployee}.Column(1)

Etc.

God Bless,

Mark A. Sam



"Jo Gjessing" <JoGjessing@discussions.microsoft.com> wrote in message 
news:FFD033C0-F446-45AB-8275-DCE4C56140CC@microsoft.com...
> Hi all,
>
> In a database of mine I have a table containing persons' name, address and
> so on. In the corresponding form I have of course all the fields where the
> users put in the data. In this form I want to have a list showing all the
> persons in the table as well, so that the users can click the right person 
> in
> the list and have his or her data filled into the fields. I have tried to
> make a table form from the same table and include it as a subform. This,
> however, was not successful because the subform only showed the person in
> focus in the form, not all the persons in the table. Also, I do not think 
> it
> would be possible to click a person in the subform and  get the data 
> filled
> into the fields, even if I a got all the persons in the table.
>
> If you have a good idea how to make this, please let me know.
>
> Thank you very much in advance.
>
> Jo 


0
Mark
12/11/2007 12:50:48 PM
Mark,
Your suggestion will not move to the record, it will overwrite what is in 
the current record.  The normal way to do this is the use the unmbound 
combo's After Update event to reposition to the selected record.  The combo's 
Bound Column should contain the primary key or a unique field in the table.  
You can use other columns to display than may be more user friendly.

Private Sub MyCombo_AfterUpdate()

    With Me.RecordsetClone
        .FindFirst "[TableKeyField] = " & Me.MyCombo
        If Not .Nomatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

End Sub
-- 
Dave Hargis, Microsoft Access MVP


"Mark A. Sam" wrote:

> Jo,
> 
> You don't need another table.   You need a unbound listbox or unbound 
> combobox with rowsource being populated from the table where your employee 
> info resides.  In the AfterUpdate event fill in the "fields" by addressing 
> the columns on the listbox or textbox like this:
> 
> [EmpFirstName] = [cboEmployee}.Column(0)
> [EmpLastNamr]= [cboEmployee}.Column(1)
> 
> Etc.
> 
> God Bless,
> 
> Mark A. Sam
> 
> 
> 
> "Jo Gjessing" <JoGjessing@discussions.microsoft.com> wrote in message 
> news:FFD033C0-F446-45AB-8275-DCE4C56140CC@microsoft.com...
> > Hi all,
> >
> > In a database of mine I have a table containing persons' name, address and
> > so on. In the corresponding form I have of course all the fields where the
> > users put in the data. In this form I want to have a list showing all the
> > persons in the table as well, so that the users can click the right person 
> > in
> > the list and have his or her data filled into the fields. I have tried to
> > make a table form from the same table and include it as a subform. This,
> > however, was not successful because the subform only showed the person in
> > focus in the form, not all the persons in the table. Also, I do not think 
> > it
> > would be possible to click a person in the subform and  get the data 
> > filled
> > into the fields, even if I a got all the persons in the table.
> >
> > If you have a good idea how to make this, please let me know.
> >
> > Thank you very much in advance.
> >
> > Jo 
> 
> 
> 
0
Utf
12/11/2007 7:18:02 PM
Dave.

I didn't see that she wanted to go to another record.  I think he/she was 
just looking to populate the fields.

God Bless,

Mark

"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
news:891C7E1D-D1CE-494D-A78B-A7CF478C6470@microsoft.com...
> Mark,
> Your suggestion will not move to the record, it will overwrite what is in
> the current record.  The normal way to do this is the use the unmbound
> combo's After Update event to reposition to the selected record.  The 
> combo's
> Bound Column should contain the primary key or a unique field in the 
> table.
> You can use other columns to display than may be more user friendly.
>
> Private Sub MyCombo_AfterUpdate()
>
>    With Me.RecordsetClone
>        .FindFirst "[TableKeyField] = " & Me.MyCombo
>        If Not .Nomatch Then
>            Me.Bookmark = .Bookmark
>        End If
>    End With
>
> End Sub
> -- 
> Dave Hargis, Microsoft Access MVP
>
>
> "Mark A. Sam" wrote:
>
>> Jo,
>>
>> You don't need another table.   You need a unbound listbox or unbound
>> combobox with rowsource being populated from the table where your 
>> employee
>> info resides.  In the AfterUpdate event fill in the "fields" by 
>> addressing
>> the columns on the listbox or textbox like this:
>>
>> [EmpFirstName] = [cboEmployee}.Column(0)
>> [EmpLastNamr]= [cboEmployee}.Column(1)
>>
>> Etc.
>>
>> God Bless,
>>
>> Mark A. Sam
>>
>>
>>
>> "Jo Gjessing" <JoGjessing@discussions.microsoft.com> wrote in message
>> news:FFD033C0-F446-45AB-8275-DCE4C56140CC@microsoft.com...
>> > Hi all,
>> >
>> > In a database of mine I have a table containing persons' name, address 
>> > and
>> > so on. In the corresponding form I have of course all the fields where 
>> > the
>> > users put in the data. In this form I want to have a list showing all 
>> > the
>> > persons in the table as well, so that the users can click the right 
>> > person
>> > in
>> > the list and have his or her data filled into the fields. I have tried 
>> > to
>> > make a table form from the same table and include it as a subform. 
>> > This,
>> > however, was not successful because the subform only showed the person 
>> > in
>> > focus in the form, not all the persons in the table. Also, I do not 
>> > think
>> > it
>> > would be possible to click a person in the subform and  get the data
>> > filled
>> > into the fields, even if I a got all the persons in the table.
>> >
>> > If you have a good idea how to make this, please let me know.
>> >
>> > Thank you very much in advance.
>> >
>> > Jo
>>
>>
>> 


0
Mark
12/11/2007 8:00:39 PM
If that is what the OP wants, then your solution is correct.  I read it 
differently.
-- 
Dave Hargis, Microsoft Access MVP


"Mark A. Sam" wrote:

> Dave.
> 
> I didn't see that she wanted to go to another record.  I think he/she was 
> just looking to populate the fields.
> 
> God Bless,
> 
> Mark
> 
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
> news:891C7E1D-D1CE-494D-A78B-A7CF478C6470@microsoft.com...
> > Mark,
> > Your suggestion will not move to the record, it will overwrite what is in
> > the current record.  The normal way to do this is the use the unmbound
> > combo's After Update event to reposition to the selected record.  The 
> > combo's
> > Bound Column should contain the primary key or a unique field in the 
> > table.
> > You can use other columns to display than may be more user friendly.
> >
> > Private Sub MyCombo_AfterUpdate()
> >
> >    With Me.RecordsetClone
> >        .FindFirst "[TableKeyField] = " & Me.MyCombo
> >        If Not .Nomatch Then
> >            Me.Bookmark = .Bookmark
> >        End If
> >    End With
> >
> > End Sub
> > -- 
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Mark A. Sam" wrote:
> >
> >> Jo,
> >>
> >> You don't need another table.   You need a unbound listbox or unbound
> >> combobox with rowsource being populated from the table where your 
> >> employee
> >> info resides.  In the AfterUpdate event fill in the "fields" by 
> >> addressing
> >> the columns on the listbox or textbox like this:
> >>
> >> [EmpFirstName] = [cboEmployee}.Column(0)
> >> [EmpLastNamr]= [cboEmployee}.Column(1)
> >>
> >> Etc.
> >>
> >> God Bless,
> >>
> >> Mark A. Sam
> >>
> >>
> >>
> >> "Jo Gjessing" <JoGjessing@discussions.microsoft.com> wrote in message
> >> news:FFD033C0-F446-45AB-8275-DCE4C56140CC@microsoft.com...
> >> > Hi all,
> >> >
> >> > In a database of mine I have a table containing persons' name, address 
> >> > and
> >> > so on. In the corresponding form I have of course all the fields where 
> >> > the
> >> > users put in the data. In this form I want to have a list showing all 
> >> > the
> >> > persons in the table as well, so that the users can click the right 
> >> > person
> >> > in
> >> > the list and have his or her data filled into the fields. I have tried 
> >> > to
> >> > make a table form from the same table and include it as a subform. 
> >> > This,
> >> > however, was not successful because the subform only showed the person 
> >> > in
> >> > focus in the form, not all the persons in the table. Also, I do not 
> >> > think
> >> > it
> >> > would be possible to click a person in the subform and  get the data
> >> > filled
> >> > into the fields, even if I a got all the persons in the table.
> >> >
> >> > If you have a good idea how to make this, please let me know.
> >> >
> >> > Thank you very much in advance.
> >> >
> >> > Jo
> >>
> >>
> >> 
> 
> 
> 
0
Utf
12/11/2007 8:25:03 PM
Thank you very much, both of you. Dave read my unsuccessful English as I 
meant it. I shall therefore try your solution, Dave, and I am looking forward 
to see how it works. Thank you again.

Klatuu skrev:

> If that is what the OP wants, then your solution is correct.  I read it 
> differently.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Mark A. Sam" wrote:
> 
> > Dave.
> > 
> > I didn't see that she wanted to go to another record.  I think he/she was 
> > just looking to populate the fields.
> > 
> > God Bless,
> > 
> > Mark
> > 
> > "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
> > news:891C7E1D-D1CE-494D-A78B-A7CF478C6470@microsoft.com...
> > > Mark,
> > > Your suggestion will not move to the record, it will overwrite what is in
> > > the current record.  The normal way to do this is the use the unmbound
> > > combo's After Update event to reposition to the selected record.  The 
> > > combo's
> > > Bound Column should contain the primary key or a unique field in the 
> > > table.
> > > You can use other columns to display than may be more user friendly.
> > >
> > > Private Sub MyCombo_AfterUpdate()
> > >
> > >    With Me.RecordsetClone
> > >        .FindFirst "[TableKeyField] = " & Me.MyCombo
> > >        If Not .Nomatch Then
> > >            Me.Bookmark = .Bookmark
> > >        End If
> > >    End With
> > >
> > > End Sub
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Mark A. Sam" wrote:
> > >
> > >> Jo,
> > >>
> > >> You don't need another table.   You need a unbound listbox or unbound
> > >> combobox with rowsource being populated from the table where your 
> > >> employee
> > >> info resides.  In the AfterUpdate event fill in the "fields" by 
> > >> addressing
> > >> the columns on the listbox or textbox like this:
> > >>
> > >> [EmpFirstName] = [cboEmployee}.Column(0)
> > >> [EmpLastNamr]= [cboEmployee}.Column(1)
> > >>
> > >> Etc.
> > >>
> > >> God Bless,
> > >>
> > >> Mark A. Sam
> > >>
> > >>
> > >>
> > >> "Jo Gjessing" <JoGjessing@discussions.microsoft.com> wrote in message
> > >> news:FFD033C0-F446-45AB-8275-DCE4C56140CC@microsoft.com...
> > >> > Hi all,
> > >> >
> > >> > In a database of mine I have a table containing persons' name, address 
> > >> > and
> > >> > so on. In the corresponding form I have of course all the fields where 
> > >> > the
> > >> > users put in the data. In this form I want to have a list showing all 
> > >> > the
> > >> > persons in the table as well, so that the users can click the right 
> > >> > person
> > >> > in
> > >> > the list and have his or her data filled into the fields. I have tried 
> > >> > to
> > >> > make a table form from the same table and include it as a subform. 
> > >> > This,
> > >> > however, was not successful because the subform only showed the person 
> > >> > in
> > >> > focus in the form, not all the persons in the table. Also, I do not 
> > >> > think
> > >> > it
> > >> > would be possible to click a person in the subform and  get the data
> > >> > filled
> > >> > into the fields, even if I a got all the persons in the table.
> > >> >
> > >> > If you have a good idea how to make this, please let me know.
> > >> >
> > >> > Thank you very much in advance.
> > >> >
> > >> > Jo
> > >>
> > >>
> > >> 
> > 
> > 
> > 
0
Utf
12/12/2007 10:21:00 AM
Reply:

Similar Artilces:

Hiding records on reports by checkbox on form
Hi, I'm in the proccess of buliding a new database. Its purpose is to store various organisation contact details and then to produce reports based on this that can be printed onto sticky labels. This much or basically finished. However, we naturally don't want to print the whole database every time, so I'm using a checkbox on the interface form that will filter the report so that only checked records are prepared for printing. Can anyone tell what code I need to use to do this, and where? I've tried various methods, but nothing seems to work. I'm using accesss 2003 by the w...

Make a pivot table from an existing pivot table
I'm trying to make a completely new pivot table out of an existing pivot table where the existing table is actually the source data (not the original data table). The problem I have is that this intermediate pivot table has 3 columns of unique values (with over 1000 rows) before we get to the data. I can't just copy it and make a pivot table, or create a mirror sheet that isn't a pivot table because of the blank cells underneath each change in value. I have this: Source Name Attempts Data Mail Joe 1 654 2 700 ...

PDF Table to Excel Spreadsheet
This has been asked in numerous places all over the web but I have not found a workable solution. We have a mainframe system that generates PDF reports for billing. However, few people in our organization these days speak mainframe. We need Excel to do calculations on the data. I began working on a method for converting using UEdit Macros and an Excel macro because the fields are fixed-width. My attempts have been unsuccessful. Here is what I want to accomplish: -Copy a region of text from a PDF -Paste into Excel -Break the values out into the appropriate cells. http://www.google.com/sea...

How can I access a public calendar from a custom form
I am trying to create a custom form that would allow a employee to request a vactation time frame. This form would then be mailed to a supervisor who could approve or disapprove the request. I am having difficulty getting the response posted to a public calendar vs. the managers private calendar. Help! -Jeff ...

Best way to design tables for cascading on my form
My company inspects damaged cars for insurance purposes. Up to now, (relating to this issue) I've had tblDamageArea populate a list box on my form where the user can select the various parts of the car that were damaged. I've had this list include parts for multiple kinds of cars and I'd like to make this list cascade to include only those parts relevant to the car type inspected. I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc. which populates a cbo on my form where the user will select the type which will then be used as the crite...

Missing something basic
Working with Access 2003. I have a table of users, their phone numbers, and job classes called tblUsers. A query extracts that information into qryUsers. I have a form that uses the query as the record source called frmUsers. The form only contains a single combobox so someone can select the user for which they wish to view data in other forms. It works fine for the first user on the list in the combobox. Selecting any other user in the combobox causes the first record in the table to change to that user's name. For example: The table and the query both contain: Timmy Bobby ...

disable MDI window list menu
I created a MDI application. When I create a new child window, it automatically adds window name to the 'Window' menu, i.e., it adds 'Doc1', 'Doc2'... How do I make it NOT do that? Thanks. WJ You can change the default name of your documents (in the string table) or you could remove the Window menu from the top menu, or, I think, you can just hook into OnInitMenuPopup() for the mainframe and remove the entries starting with AFX_IDM_FIRST_MDICHILD. This article might help you get started. In this one they remove all those items and replace them with a popup...

Summing cells in pivot tables
I have a problem summing cells in a pivot table. When I double-click on a cell that I want to add, the formula from that cell gets added. Eventually I run out of characters. Is there a way to switch how the cell gets picked up. I can manually enter the cell addresses that I am trying to sum but that takes forever. Thanks, Ted There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html Ted wrote: > I have a problem summing cells in a pivot table. When I double-cli...

Forms and Reports from CrossTab queries
Hi All, I have read a few postings in this area, but they all seem to be very specific to particular situations. Can someone please guide me towards the best solution to my dilema? I have a form with two list boxes which contain many variables (answers to specific questions from a survey). When two different questions are slected I run a cross tab query resulting in a dymanic result of rows and columns based on the selections. I now want to display the result as a datasheet on my form and in a similar fashion as a report. Any help is, of course, greatly appreciated. Regards, John D...

Hiding a field from a sub form.
I know that this looks easy (and it probably is) but this is what I need to do. I have this code that I can hide a field within the same form ' NI.Visible = Not NB And this works just fine. what I would like to do is Hide a Field from a Sub Form and I am hitting a wall. I tried this, so please tell me where I am brain dead. frmDateEntry!product.visible = not NB Thanks in advance, -- -The Novice Learn Today, Teach Tomorrow Great Success is ones ability to ask for Help. On Fri, 18 May 2007 14:50:00 -0700, TheNovice <TheNovice@discussions.microsoft.com> wrote: >I know tha...

Exporting distribution lists
Is thee any way to export distribution lists into something like a spreadsheet? Hello: Which version of Exchange are you using? In 5.5 it is easy to do. 200x is not as easy, but doable. Regards, Martin "Elbryyan" <elbryyan@hotmail.com> wrote in message news:483201c4a09c$dd0a5240$a301280a@phx.gbl... > Is thee any way to export distribution lists into > something like a spreadsheet? Do you keep all of your DGs in one OU? If so you can easily use LDIFDE or dsquery group. "Elbryyan" <elbryyan@hotmail.com> wrote in message news:483201c4a09c$dd0a524...

new to making forms on access
Ok I have a table whihc has column name title and some survey question. Now i want to create a form so that when i select Name ( I guess this will be combo box) I dont know how to do that even where i can select name ? but when i select name the person title auto populates and so does the relevant answers to the questions for example i choose John and the title should auto populate and question 1 should populate also and so on but if i pick adam then everything related to adam populates. Your help will be really appreciated The table look like this Name Title Qu...

is it possible to have datasheet smart tags appear in a form?
is it possible to have datasheet smart tags appear in a form? Thanks ...

Publish Form Changes without restarting IIS?
Is there another way to publish form changes without restarting IIS? We run a 24x7 IT shop and taking down a production system for every change is a pain. thank you. Mike Presumably as you are 24/7 you have multiple CRM servers, so you only need to IISRESET one CRM server at a time, thus users will not notice any loss of service "BWIT" <user1@bwinc.com> wrote in message news:ObSXFVvWFHA.4036@TK2MSFTNGP10.phx.gbl... > Is there another way to publish form changes without restarting IIS? We > run a 24x7 IT shop and taking down a production system for every chang...

Import Adobe Form into Access for Report
I am trying to import a PDF into Access to use as a report and populate the fields with a query I have created. I can not find any help in getting this to work. Can someone help me? -- Terry Foster Tax Trilogy ...

batch importing of mailing lists to Excel
I've got a mailing list of around 1000 names, stored in both Word and Entourage like this: First Name Last Name <e-mail address> Once I’ve eliminated the < >, do I then have to individually copy & paste (or type) all the info in three Excel columns? Or is there a simpler solution for batch importing of mailing lists to the Excel spreadsheet? "bgsignal" wrote: > I've got a mailing list of around 1000 names, stored in both Word and > Entourage like this: First Name Last Name <e-mail address> > Once I’ve eliminated the < >, do I then ...

table manipulation
Hi all my db has a single table, Table1. Table1 is as follows: AUTHOR;TITLE;PUBLISHER rows are not unique; i.e. I can have more than 1 row with the same author. What would be the SQL code to generate a table2 with the following structure: AUTHOR;TITLE-PUBLISHER;...;TITLE-PUBLISHER where every author now has only 1 row and titles and publishers for author are appended to the right as as may fields as necessary? thanks! hugh wrote: > Hi all > > my db has a single table, Table1. Table1 is as follows: > > > AUTHOR;TITLE;PUBLISHER > > rows are not unique; i.e....

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

Why won't contacts list out in address book
When I open my address book or select the To: or CC: options, my contacts are not listed out. I performed the steps at the below link. The address book appears to be linked to the contact folder since the address book shows the correct contact file in the very top and will search from it, but why aren't the contacts listed for easier selection like I've seen before? Thanks.. http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 To give further info, I am running Outlook 2007 and the only message in either the global address list or my sp...

Making cell change colour
Hi all I want to make a cell change colour 'on click'. Is there any way that this is possible? Help would be appreciated, Many thank darren --- Message posted from http://www.ExcelForum.com/ Hi this would require VBA / using an event procedure like Selection_change. Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany > Hi all > > I want to make a cell change colour 'on click'. > > Is there any way that this is possible? > > Help would be appreciated, > > Many thank > > darren > > > --- > Message posted f...

It's twins!!! Two sets of personal folders!!
I had to re-install outlook and reloaded my .pst file. My inbox got all the mail back but my contacts didnt come back. I imported the pst file and now i have double!! It wont let me get rid of either. What do I do now?? TIA Terri ...

Distribution List Limits
Hi, I could find an answer to this for Outlook 2002 but not for Outlook 2003. Is the answer the same? We are using SBS 2003. The error is: "The distribution list has reached the maximum size for your network e-mail server. The new member could not be added." List was at 132 members. Not an Outlook question. These limits are set by the server, not Outlook. If your sever hasn't changed, then I imagine the limit is still the same. -- Russ Valentine [MVP-Outlook] "Paddy Ryan" <paddy@fpmt.org> wrote in message news:uOKDg0vaGHA.4196@TK2MSFTNGP03.phx.gbl... ...

Custom Roll up and Drill down calculation for Excel Pivot table
Greetings to all - I am currently struggling with a method to apply a custom rollup function for an Excel pivot table. I am trying to calculate a sell through % which is calculated as Sales $/(Sales $ + Ending Inv $). I want to be able to calculate this % at the weekly, monthly, and yearly level. I can successfully add a calculated field which works perfectly when the user has WEEK on the pivot table. However, if the user wishes to remove week and see the Sell Thru % at the Monthly level, then Excel naturally sums the Sell Thru % for each week in the month. This is not an accurat...

List Box Change Event
Hi, I have a list box and have the following code in the worksheet module: Private Sub MyListBox_Change() Sheets("Report").Select End Sub My list box is called MyListBox but when I select it nothing happens. All I want is to go to another sheet after the change occurs. The only thing I can think is the name of my list box is incorrect but it does show as MyListBox in the cell name drop down but it doesnt appear in the defined names. Using 2003. Can anyone see what I am doing wrong? Thanks in advance Martin What sort of ListBox is it, from the Forms to...

Make input in one column determine dropdown list in another.
Is there any way I can make the info thats input in one column (selected by dropdown list) determine which (one of several) dropdown list is used in another column? Use a list formula of the type =OFFSET(IF(E1=1,rng1,rng2),0,0,(COUNTA(IF(E1=1,rng1,rng2))),1) where E1 is the first DD. -- HTH RP (remove nothere from the email address if mailing direct) "gettin-older" <gettin-older@discussions.microsoft.com> wrote in message news:8FF90B6B-F07F-402B-A042-E1B103C40EED@microsoft.com... > Is there any way I can make the info thats input in one column (selected by > dr...