Hello,
I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.
So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.
I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.
Thanks
6  6015 
Hello All,
I'm bumping this because I'm ready to pull my hair out. I know there must be an easy fix and I'm just missing it (or I'm a complete moron)
Any help would make my day.
Thanks
 FishVal 2,653
Recognized Expert Specialist
Hello,
I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.
So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.
I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.
Thanks
Hi!
It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.
Hi!
It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.
The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.
Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.
Thanks so much for your help.
FishVal 2,653
Recognized Expert Specialist
The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.
Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.
Thanks so much for your help.
Hi!
Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.
Hi!
Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.
That's fantastic!! I knew it would be something easy like that. However, by changing the record source for the main form from a query to the claims table it has created another issue. As I mentioned above the query had two additional tables linked to the claim table. One of them is the Plant Table. The idea was that I could enter the Plant # on the main form and it would automatically populate the plant address. The other linked table was to operate in the same way. Now the Plant number still appears but the address fields all show "#Name?"
Is there something I can do about this?
I can't thank you enough for the help. It looks like I'll get to keep my job for at least another week.
HI,
Never mind my last question. I've figured it out on my own. I set the record source for the main form to a query that DID NOT include the part table but did include the plant and carrier table. Now everything populates automatically and I only get one record per claim number.
This has been a good Friday...so far.
FishVAl, thank you so much for the help.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Catherine Jo Morgan |
last post by:
Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?
Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA
|
by: 6thirty |
last post by:
Hi,
I've created a stocktaking database using Access XP. This is indexed by two
fields - part number and shelf location.
I am currently inputting all the data via a form. When I have entered a
record such as:
part number 202354-001
location C1-01
|
by: Charles Ledbetter |
last post by:
I'm missing the mistake.
I have a MainForm called frmResources.
I have a Second Form and related table called frmFootnotes. I am trying to
use it as a subform to the main form appearing in a tabctl in the main form.
The two are related with a one-to-many relationship. The PrimaryID field in
Resources is related to a non-primary field in Footnotes. At least that is
where it currently is. I've tried every combination I can think of and...
|
by: mavmavv |
last post by:
I have a Form where I have created a duplicate record button, no
problem...
The subform is where my problem lies. The subform displays data
matching the mainform's ID, these two values are linked. The subform
has no primary key, since there are multiple matching entries.
Basically the mainform displays Customer info + order totals, and the
subform displays the products.
When I goto duplicate the record, the ID from the duplicated...
|
by: claus |
last post by:
Hi,
I am not a programmer but tries anyway to program a feature in a form
where I am able to copy entries for the form and a subform. I have
tried follow the guide here http://allenbrowne.com/ser-57.html, but my
problem is that I have more than one keyfield in the join between the
two tables: Hovedoplysninger and Itemoplysninger. The tree fields are:
"Medarbejder", "Sagsnummer" and "Mandag". I have succeded creating the
new entry...
| | |
by: jwbird |
last post by:
I have a main form with multiple Subforms that are all displaying Datasheet information related to the Main form record. When I make changes/adds to the records(s) in the subform and tab to another subform, it saves those previous subform records fine, but when I make changes to the subform records and then change the mainform criteria, it doesn't save those subform records.
I can use an event to prompt for saving the records on the...
|
by: VinArt |
last post by:
MS Acc 2003, XP
Thank you in advance for any help.
I have tables called "Makeup" and "Lines". Each makeup can have multiple lines.
Goal is to create a new "makeup" with identical "lines" except with a new makeup id.
I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary...
|
by: 6afraidbecause789 |
last post by:
Kudos to anyone who can explain this one--how to duplicate a group of
continuous records in a subform for use in a new subform PK ID. The 2
entry fields (combo boxes) in the subform are RoleID and StaffID, and
the PK ID is StaffandIntsID. The subform's data is put into
jtblStaffandInts, which joins a Staff and Intervention tables:
So, here's what the data could look like in the table:
StaffandIntsID InterventionID StaffID RoleID
|
by: xraive |
last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful.
Current Design
Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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
| |
