473,324 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

subform will not allow information not found in table

I have a warranty claim form with a part subform that pulls description and
pricing information from a linked table. The database will not let me enter
a part number that is not in the linked table. I need to, on certain
occassions, be able to enter a part number that is not in this table.
However, I would like to store it in another seperate table.

Here is the scenerio:

We have two factories that produce two different products. I have access to
the parts database tables at one of these factories. I process warranty
claims for both. Therefore, if I get a claim on a machine from the other
factory, I need to be able to keep track of part numbers from that factory,
even if that means I have to enter the pricing information and description
manually.

Any suggestions?

Thanks for the help.

Shannan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200603/1
Mar 14 '06 #1
3 1319
"socasteel21 via AccessMonster.com" <u11033@uwe> wrote in
news:5d426e713ccea@uwe:
I have a warranty claim form with a part subform that pulls
description and pricing information from a linked table. The
database will not let me enter a part number that is not in
the linked table. I need to, on certain occassions, be able
to enter a part number that is not in this table. However, I
would like to store it in another seperate table.

Here is the scenerio:

We have two factories that produce two different products. I
have access to the parts database tables at one of these
factories. I process warranty claims for both. Therefore, if
I get a claim on a machine from the other factory, I need to
be able to keep track of part numbers from that factory, even
if that means I have to enter the pricing information and
description manually.

Any suggestions?
First step: change the relationship between the subform's table
and the lookup table from an inner join to a left join.

Second step: add the new table to the query and create a left
join relatinship to the new table.

3rd step: modify the query to use the price and description from
the new table when not in the original linked table.

Fourth step, build a form to enter the new description and price
when you encounter a new part to be added.

Thanks for the help.

ur Welcome.
Shannan


--
Bob Quintal

PA is y I've altered my email address.
Mar 14 '06 #2
This sounds like a referenctial integrity issue. The table must be
related to the other table. What you need to do is to add the part
number to the other table. It sounds like the constraint is that if the
part number does not exist in your parts table, then you can't add it
to/use it in the main table.

The only way to get around this is to either modify the
relationship/constraint or remove it altogether. The downside of doing
that is that there are probably several inter-related relationships in
your system. If you change one thing without considering the impact on
the other relationships you will bring down the system.

If you can't add the part number to your parts table, then I would see
about getting a backup copy of the system (the front end and the back
end) and start experimenting by removing relationships and see what
happens. Only do that to the backup because that way you can restore
the backup when it quits working. If this were sql server you could use
an InsteadOf trigger to get around the constraint.

HTh

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Mar 14 '06 #3
1. Enter the data into the linked table first; if you decide to enter
data into your warranty table that is not in your parts table you will
destroy the integrity of your database; from a theoretical point of
view you will no longer have a relational database. It will be ...
think of a four letter word beginning with S.

2. Get on your IT department, DB Administrator or whomever to meld the
two tables and keep them up to date; depending on your circumstances
you may wish to have location as a new field in the combined table.

Did I mention, "Don't put orphans into your warranty table!"? Not sure?
.... OK ... One More Time: DON'T PUT ORPHANS INTO YOUR WARRANTY TABLE!

Mar 14 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Ray | last post by:
I have a main form with a subform. The main form has a combo box which lists all the clients which in turn are displayed in the subform. The subform is bound to the combo box and all work well....
0
by: Alienz | last post by:
hiya! I wanted to make a multiple selection drop down list and I found the easiest way to do this was by creating a subform linked to a table with 2 values, the 1st value links to the main form...
4
by: Mason | last post by:
This is probably an incredibly newbie-ish question. I just haven't had the cause to use many subforms before, so I'm pretty sure I just don't understand it correctly (even after reading up on it)....
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: whitc26 | last post by:
Let me preface: I'm a novice, and have no programming experience. I have created an access database and have a few tables in it. I have created a form called "clients" This form opens up and...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
4
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form...
2
by: Craig | last post by:
I am trying to create a form that shows quote info, but also has a subform that lists all keywords and allows you to select multiple keywords to associate with that quote. I have never done a...
7
by: TinyTom | last post by:
I really need help with this code. I have made a pretty complicated subform that automatically updates filter criteria based off of inputs in various fields within the search form. What I need to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.