I am trying to set up a subform that has two tables.
Table 1: ID DESCRIPTION UNIT PRICE (ID=Primary Key)
This table is fix price, information will never change. "Description" is a look up box.
Table 2: IDqty quantity ID (IDqty=PK)
The relationship is one to many. Table 1 "ID" being one to Table 2 "ID" being many.
The problem I am having is that when I try out my query or form it is pulling the information I select back into Table 1. Like in my form I choose a "description" from the lookup box, the price and unit information should appear, but it doesnt. What happens is it is adding the same information back into table 1. Right now I have 82 records in table 1. It will add record 83 and store the same "description" I selected back into the table. NO Records should be added back into Table 1, it is a fix rate table.
What I want to do is when I create my form, I want to select "description" pick which record I want from the combo box. The unit and price should appear for each description I pick. Then I want to be able to enter a quantity.
I have tried changing the relationships, queries and forms.
Any suggestions?
5 2177
Hi, there.
You should link the form's combobox to Table2.ID (I guess that is FK).
What do you mean? The combo box is set up as a lookup box from the table. The row source is coming from qryequipmentprices (query of table 1). How do I link table 2 to it?
What do you mean? The combo box is set up as a lookup box from the table. The row source is coming from qryequipmentprices (query of table 1). How do I link table 2 to it?
First of all I would like to ask you to provide more information. - Post your tables metadata.
Here is an example of how to post table MetaData : Table Name=tblStudent - Field; Type; IndexInfo
-
StudentID; AutoNumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
Mark; Numeric
-
LastAttendance; Date/Time
- Post your query SQL.
Second - it should be obvious that entering data to a form control bound to a field in a table will cause this data to save in that field of that table.
Though you havn't specified what is the controlsource of the combobox you are mentioning, I've made a guess that the controlsource is a field in [Table1] as soon as symptoms are quite clear.
If you want a more helpful advice, then you should take more care on formulating your question.
Kind regards,
Fish
Table 1 Name= tblequipmentprice
1. Description; Text
(Combo Box with rowsource "qryequipprice"
2. ID; autonumber - PK
3. Unit; text
4. Price; currency
Table 2 Name= tblinstallqty
1. Idqty; autonumber - pk
2. quantity; number
3. ID; number
one to many relationship; ID from table 1 is one to ID table 2 is many.
query SQL
SELECT qryequipprice.DESCRIPTION, qryequipprice.UNIT, qryequipprice.PRICE, qryequipprice.ID, tblinstallqty.IDqty, tblinstallqty.quantity
FROM qryequipprice INNER JOIN tblinstallqty ON qryequipprice.ID=tblinstallqty.ID;
My goal is to have a subform that will let you select a description name, the prices and units will appear and I can enter a quantity and get a total.
Ok.
As I've already said, you need to retrieve field tblinstallquantity.ID in your query and link it to form control.
Just to make a sense of how it is working run the query and enter some value in tblinstallquantity.ID field.
And out of curiosity why don't you want to use form/subform interface?
Regards,
Fish
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alcyone Oliveira |
last post by:
------=_NextPart_000_000D_01C36572.F6480CE0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_000E_01C36572.F6480CE0"
------=_NextPart_001_000E_01C36572.F6480CE0
Content-Type:...
|
by: H Cohen |
last post by:
Hi,
I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.
It is possible, but unwieldy, to establish a relationship for each
table...
|
by: Steve |
last post by:
How do I delete a relationship between tables in SQL Server 7.0?
I previously had a relationship between two tables, but I renamed the field
and table of one of the tables and the old...
|
by: Andante.in.Blue |
last post by:
Hello, I have just inherited a legacy Access 97 database. While going
through it, I noticed something strange... its Relationships window
(the one accessed by Tools --> Relationships) is almost...
|
by: Marco Simone |
last post by:
Hi,
I have 4 tables, tblCompany, tblA, tblB and tblC.
tblA, tblB and tblC contain same type of data, they should be in one table,
but since there are many fields, I split it into 3 tables.
Each...
|
by: BrianDP |
last post by:
This is a strange one--
I have an Access 2K database with a split front end/back end. There
are two tables in the back end - RFile and RLine. There is a one to
many relationship from...
|
by: Richard |
last post by:
Hi folks, thanks for taking the time to read this (and hopefully point
our where I'm going wrong).
The scenario:
I have a local Access2007 database which links in several read only
mySql...
|
by: RobertJohn |
last post by:
Hi all
I am using Access 2007 to start a small home library application, and so far it has two tables.
1. Books, with fields Book_ID (Primary Key) and Title, and
2. Authors, with fields...
|
by: Phil Stanton |
last post by:
Having spent ages sorting out the layout of my relationship window with
about 60 tables and getting it all tidy and with enough of the tables
visible to show the PK an FK relaionshipsOn a subseqent...
|
by: NicoleCartrette |
last post by:
Going back to school is easier said than done..
This was posted to an older thread earlier but I don't think it got any attention. Your help is appreciated
Professor requires we create a...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
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...
|
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....
|
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
| |