Can a record in a table have a default value from another table?

G

gtslabs

I have 2 tables joined to make a Many to Many 3rd table. I want my
3rd table to use some of the fields from the first table as default
values so they can be overwritten if needed. I copied the fields from
the first table to the 3rd so I have the same field type but the first
table has the suffix "default" in the field name.

I am in a form trying to use this formula to fill in my 3rd table
field (as a default value) based on a combobox:
=DLookUp([OMCdefault],[tbl-TestCodes]," [TestCode] = '" & [cbo_Tests]
& "'")
It is not working. The values returned are strings.
The field returns nothing and the newrecord line in the record
selector says #Name?
 
B

Beetle

The 3rd table should hold the PK values from the two parent tables, along
with fields for any attributes that are unique to the *relationship* (in other
words, things that are not attributes of either of the two parent tables
individually).

It should not, however, redundantly store data that is already stored
in one of the parent tables.

If you can describe more about what it is you are trying to accomplish
(and why you think you need to do it like you are attempting), someone
may be able to point you in the right direction.
 
G

gtslabs

The 3rd table should hold the PK values from the two parent tables, along
with fields for any attributes that are unique to the *relationship* (in other
words, things that are not attributes of either of the two parent tables
individually).

It should not, however, redundantly store data that is already stored
in one of the parent tables.

If you can describe more about what it is you are trying to accomplish
(and why you think you need to do it like you are attempting), someone
may be able to point you in the right direction.
--
_________

Sean Bailey



gtslabs said:
I have 2 tables joined to make a Many to Many 3rd table.  I want my
3rd table to use some of the fields from the first table as default
values so they can be overwritten if needed.  I copied the fields from
the first table to the 3rd so I have the same field type but the first
table has the suffix "default" in the field name.
I am in a form trying to use this formula to fill in my 3rd table
field  (as a default value) based on a combobox:
=DLookUp([OMCdefault],[tbl-TestCodes]," [TestCode]  = '" & [cbo_Tests]
& "'")
It is not working. The values returned are strings.
The field returns nothing and the newrecord line in the record
selector says #Name?- Hide quoted text -

- Show quoted text -

My Tables are as such:
tbl-TestCodes (Table 1)

TestCode (PK)
TestName
TestMethod
Matrix
OMCdefault
Prerequisite1default
Prerequisite2default
Prerequisite3default

tbl-Samples (Table 2)

LabSampleID (PK)
ProjectNumber
Location1
Location2
Location3


Tbl-Samples-TestCodes (Table 3)
LabSampleID (FK)
TestCode (FK)
OMC
Prerequisite1
Prerequisite2
Prerequisite3

I am trying to get the default values from Table1 into this Table 3 so
they can be modified as needed and saved with the relevant
LabSampleID.

(I guess this is simlar to an invoice schema where you would bring in
the current fees but be able to modify the prices as needed)
 
G

gtslabs

The 3rd table should hold the PK values from the two parent tables, along
with fields for any attributes that are unique to the *relationship* (in other
words, things that are not attributes of either of the two parent tables
individually).
It should not, however, redundantly store data that is already stored
in one of the parent tables.
If you can describe more about what it is you are trying to accomplish
(and why you think you need to do it like you are attempting), someone
may be able to point you in the right direction.
Sean Bailey
gtslabs said:
I have 2 tables joined to make a Many to Many 3rd table.  I want my
3rd table to use some of the fields from the first table as default
values so they can be overwritten if needed.  I copied the fields from
the first table to the 3rd so I have the same field type but the first
table has the suffix "default" in the field name.
I am in a form trying to use this formula to fill in my 3rd table
field  (as a default value) based on a combobox:
=DLookUp([OMCdefault],[tbl-TestCodes]," [TestCode]  = '" & [cbo_Tests]
& "'")
It is not working. The values returned are strings.
The field returns nothing and the newrecord line in the record
selector says #Name?- Hide quoted text -
- Show quoted text -

My Tables are as such:
tbl-TestCodes (Table 1)

TestCode (PK)
TestName
TestMethod
Matrix
OMCdefault
Prerequisite1default
Prerequisite2default
Prerequisite3default

tbl-Samples (Table 2)

LabSampleID (PK)
ProjectNumber
Location1
Location2
Location3

Tbl-Samples-TestCodes  (Table 3)
LabSampleID (FK)
TestCode (FK)
OMC
Prerequisite1
Prerequisite2
Prerequisite3

I am trying to get the default values from Table1 into this Table 3 so
they can be modified as needed and saved with the relevant
LabSampleID.

(I guess this is simlar to an invoice schema where you would bring in
the current fees but be able to modify the prices as needed)- Hide quotedtext -

- Show quoted text -

I think I may have a problem with the Dlookup becuase when I create
the new record the combo box has not been updated yet.
And the default value does not know what record is selcted in the
combo box.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top