Hi there,
I'm something of a newcomer to MS Access so it's more than likely that my question is extremely elementary.
I have a form whose record source is a single table in my database. It's purpose is only to display in sequence,every record in this table. Trouble is, one particular field in this table (tblAttendees) is a foreign key in a foreign key - primary key relationship to a field in another table. The foreign key, is of datatype "number" and so it obviously corresponds with another number field in the parent table which is a primary key.
My problem is that rather than simply display the field's value for every record which would be simple enough and would of course simply be a integer, I want to be able to access the value assigned to another specific field in the particular record the foreign key points to in the parent table. Given that I don't know how to assign mutliple record sources to a given form or if it is even possible, I don't know a solution to my problem.
At the moment, I have a label box that in design view, has the following syntax: - ="Attendee:" & [intTitleTypeID] & " " & [txtAttendeeFirstName] & " " & [txtAttendeeSurname]
The [intTitleTypeID] field is the foreign key. Given that I can't access the parent table, I can't actually access the field name within the parent table either.
Any suggestions as to how I might solve this? Can you embed the result of a query of another table within a label box in a form for example?
5 2449 JustJim 407
Recognized Expert Contributor
Hi there,
I'm something of a newcomer to MS Access so it's more than likely that my question is extremely elementary.
I have a form whose record source is a single table in my database. It's purpose is only to display in sequence,every record in this table. Trouble is, one particular field in this table (tblAttendees) is a foreign key in a foreign key - primary key relationship to a field in another table. The foreign key, is of datatype "number" and so it obviously corresponds with another number field in the parent table which is a primary key.
My problem is that rather than simply display the field's value for every record which would be simple enough and would of course simply be a integer, I want to be able to access the value assigned to another specific field in the particular record the foreign key points to in the parent table. Given that I don't know how to assign mutliple record sources to a given form or if it is even possible, I don't know a solution to my problem.
At the moment, I have a label box that in design view, has the following syntax: - ="Attendee:" & [intTitleTypeID] & " " & [txtAttendeeFirstName] & " " & [txtAttendeeSurname]
The [intTitleTypeID] field is the foreign key. Given that I can't access the parent table, I can't actually access the field name within the parent table either.
Any suggestions as to how I might solve this? Can you embed the result of a query of another table within a label box in a form for example?
This is where relational databases become fun and addictive!
You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.
Now make that query the table's recordsource and drag fields to your heart's content.
Have fun but don't stay up too late!
Jim
This is where relational databases become fun and addictive!
You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.
Now make that query the table's recordsource and drag fields to your heart's content.
Have fun but don't stay up too late!
Jim
Jim,
Are you saying that the record source should be an entire query that would query both tables thereby giving me access to whatever fields I needed? If so, what you're saying would make sense.
This is where relational databases become fun and addictive!
You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.
Now make that query the table's recordsource and drag fields to your heart's content.
Have fun but don't stay up too late!
Jim
Jim,
I've done exactly what you suggested and it works perfectly. THANK YOU!!
Jim,
I've done exactly what you suggested and it works perfectly. THANK YOU!!
Search for DLookup in the help system, You will find help that documents another great way to display related information, using VBA code, great for just one off situations, and or conditional display. E.g. only display the information, if the criteria of a series of Dlookups is true or false etc etc.
Welcome to the disease ... As Jim pointed out, this is where relational Databases get addictive.
Hi all ... yes yes I know its been nearly a year in excile, but then its the crazy season again .... heh he
JustJim 407
Recognized Expert Contributor
Jim,
I've done exactly what you suggested and it works perfectly. THANK YOU!!
My pleasure. Sorry about missing your second question, being on the "Far Side" of the world in relation to most people on this list, means that I miss the busy period.
Enjoy
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mark ??;-\) |
last post by:
I would like to display a listing of files on a web page as follows:
If there is only one file: display the section name and then display the
current file.
If there is more than one file (for the first page): display the section
name, the current file and a few archive files.
If there is more than a page full (for each additional page):...
|
by: Matt K. |
last post by:
Hi there,
I have a form in an Access project that contains a subform which
displays the results of a query of the style "select * from
where = #a certain date#". In the main part of the form
the user can change the date, which will force a requery in the
subform to bring up records from the date selected.
My question is this... The...
|
by: John Baker |
last post by:
Hi:
Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I...
|
by: ED |
last post by:
I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql mode after
a certain point it give me the error message that the expression is to
complex. Below is the sql code that I am using (this works so far,...
|
by: Jon Weston |
last post by:
I'm setting up an Access2003 database with pictures. I put a bound ole
picture ctrl on a form that's source is the table that contains the pictures
and follow ALL the directions for embedding a picture. The ctrl in the form
only displays the filename of the picture. The underlying table, when
opened in table view, shows the word "package"...
| |
by: hafner |
last post by:
Hello,
I'm building a query in which a master set of data is linked to a
secondary table with the use of a unique identifier.
Basically, I'm looking to represent in the query whether or not the
line of data in the master table appears in the secondary table. I can
do that easily by showing all records in the master table and only
those...
|
by: Dorish3 |
last post by:
Hello. I have to apologize in advance but I am a newbie with Access.
I have a select query of which I would like the other select queries
that I create to be dependant on the first query....can any one tell
me how I would go about accomplishing this?
More or less I want the created queries to be dependant upon query
1. The first query...
|
by: esmith2112 |
last post by:
I have a query running on a federated database that takes the form
select col1, col2
from nickname1
where <conditions exist>
union all
select col1,col2
from nickname2
|
by: dba |
last post by:
Have been displaying data from database using html for some time but
just recently trying to display data back to "form". Can't find answer.
<form method="post" action="<?php echo $PHP_SELF;?>">
First Name:<input type="text" size="12" maxlength="12" name="Fname"><br >
Last Name:<input type="text" size="12" maxlength="36" name="Lname"><br />...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |