Lookup Field in form from subform

  • Thread starter Thread starter Signe B via AccessMonster.com
  • Start date Start date
S

Signe B via AccessMonster.com

Hi
I have a subform A (with a main form B) and another form C. I want to look up
a field in Form C and add it to a field in subform A. The to forms (A and C)
are linked in a one to many relationsship.
The problem is that I only get the value from the current record in form C in
all records on subform A and not the one matching the record.
Can anyone help?

I have been trying everything (but the right thing).
 
You need to look at the contents of TableC, not FormC (where TableC is the
underlying record source for FormC). A text box on FormC only has the value
of that field for the current record. Here is a simple example as a lookup -
you can write a simple update query using the criteria portion of this:

Control1_AfterUpdate()
Control2 = DLookup ("[Field2]","[TableC]","[Field1] = " &
[Forms]![FormA]![Control1])
End Sub

When you select or enter something in Control1, this will look up the
content of Field2 in TableC for the TableC record whose Field1 is equal to
the contents of the Control1 that you just populated on FormA, and then
assign that value to Control2 (on FormA).
 
Hi
I have a subform A (with a main form B) and another form C. I want to look up
a field in Form C and add it to a field in subform A. The to forms (A and C)
are linked in a one to many relationsship.
Click to expand...

Whoa. Stop right there.

Forms are JUST WINDOWS. They do not contain data. You cannot "look up
a field" in Form C - you can reference the value of a Control on form
C to see the value of a current record. Forms are NOT "in a one to
many relationship" - only Tables can participate in relationships!

The data is not stored in form C; it's stored in a Table, whatever
table you're using as Form C's Recordsource. If you want data, get it
from the Table, not from the form; DLookUp is one way to do so:

DLookUp("[fieldname]", "[tablename]", "[IDfield] = " & [txtID])

will look up the value of fieldname from tablename where the value of
IDfield in the table is equal to the value in textbox txtID on the
current form.

John W. Vinson[MVP]
 
Hi John
Sorry about the confusion, off course it is the tables that have a
relationship. I have tried Dlookup (with the table names) but the field then
just stays blank.
Do you have an idea why?

The tables have the same name as the forms, could that be a problem?

Signe

John said:
Hi
I have a subform A (with a main form B) and another form C. I want to look up
a field in Form C and add it to a field in subform A. The to forms (A and C)
are linked in a one to many relationsship.
Click to expand...

Whoa. Stop right there.

Forms are JUST WINDOWS. They do not contain data. You cannot "look up
a field" in Form C - you can reference the value of a Control on form
C to see the value of a current record. Forms are NOT "in a one to
many relationship" - only Tables can participate in relationships!

The data is not stored in form C; it's stored in a Table, whatever
table you're using as Form C's Recordsource. If you want data, get it
from the Table, not from the form; DLookUp is one way to do so:

DLookUp("[fieldname]", "[tablename]", "[IDfield] = " & [txtID])

will look up the value of fieldname from tablename where the value of
IDfield in the table is equal to the value in textbox txtID on the
current form.

John W. Vinson[MVP]
Click to expand...
 
Hi again
I better show the code used as well
I have tried in both Before and After Update

Private Sub TEST_BeforeUpdate()
Dim test1 As String
test1 = DLookup("[Eq/Mo/Fu/Others Validated EOD]", "Pricelist1", , "[Client
ID] = [Klient]")
[TEST] = test1 + 1
End Sub


Signe said:
Hi John
Sorry about the confusion, off course it is the tables that have a
relationship. I have tried Dlookup (with the table names) but the field then
just stays blank.
Do you have an idea why?

The tables have the same name as the forms, could that be a problem?

Signe
[quoted text clipped - 19 lines]
John W. Vinson[MVP]
Click to expand...
Click to expand...
 
I have solved it by inserting formC as an invisible subform
Properly not the best way, but it works
:-)

John said:
Hi
I have a subform A (with a main form B) and another form C. I want to look up
a field in Form C and add it to a field in subform A. The to forms (A and C)
are linked in a one to many relationsship.
Click to expand...

Whoa. Stop right there.

Forms are JUST WINDOWS. They do not contain data. You cannot "look up
a field" in Form C - you can reference the value of a Control on form
C to see the value of a current record. Forms are NOT "in a one to
many relationship" - only Tables can participate in relationships!

The data is not stored in form C; it's stored in a Table, whatever
table you're using as Form C's Recordsource. If you want data, get it
from the Table, not from the form; DLookUp is one way to do so:

DLookUp("[fieldname]", "[tablename]", "[IDfield] = " & [txtID])

will look up the value of fieldname from tablename where the value of
IDfield in the table is equal to the value in textbox txtID on the
current form.

John W. Vinson[MVP]
Click to expand...
 
Hi again
I better show the code used as well
I have tried in both Before and After Update

Private Sub TEST_BeforeUpdate()
Dim test1 As String
test1 = DLookup("[Eq/Mo/Fu/Others Validated EOD]", "Pricelist1", , "[Client
ID] = [Klient]")
[TEST] = test1 + 1
End Sub
Click to expand...

BeforeUpdate and AfterUpdate fire at the END of the process - if you
want the value incremented so the user can see it, use the
BeforeInsert event instead.

You've got an extra comma (which might have been from copy & paste).

And you've got the criterion [Klient] inside the quotes instead of
outside.

Try

test1 = DLookup("[Eq/Mo/Fu/Others Validated EOD]", "Pricelist1",
"[Client ID] = " & Me![Klient])

John W. Vinson[MVP]
 
Back
Top