Manipulate records of 2 tables

HI,

How to manipulate records of 2 different tables on a form. Can anyone help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another table?

Thanks
Praveen

You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField would be the
name of a field in each table that matches a unique value in the current
record. txtIDField is a textbox on the form that holds this unique value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can anyone help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another table?

Thanks
Praveen

Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm getting a
bit confused in it.
I will explain you the full scenario here, so that you may be able to solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I want the
users of this application, be able to select the Names from a combo box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter the values
in the First Name combo. ( I tried so many ways to do this, but I failed all
the times)
2. And when the First Name Combo got selected, I want the SSN textbox field
which is empty, should populate the correct SSN value from the first table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote in message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField would be
the
name of a field in each table that matches a unique value in the current
record. txtIDField is a textbox on the form that holds this unique value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another
table?

Thanks
Praveen

Ok, this explanation is definately different than what I first understood.
First, using last name and first name combined as a primary key can cause a
problem, what if you have two John Smiths? From the way you worded the
question compared to how you listed the tables, I'm not sure which way you
went, but I believe you are actually using the ID field as the primary key.

Now, to help the users use the combo boxes. For the first combo box (last
name) set its Row Source to a query that will return just ONE of each last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that relies on the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox & """"
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of columns to 3,
the column widths to 0", 1", 1" (adjust the last two as desired to show the
data without cutting it off), and set the Bound Column to 1. The Limit to
List option will automatically be set to Yes. For the SSN textbox, set its
control source to =cboCombo2.Column(2). The Column value is 0 based, so 2 is
the 3rd column.

You will need the SSN showing when you drop down the list to make the
selection becuase you will need to be able to distinguish between two people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be able to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I want the
users of this application, be able to select the Names from a combo box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter the values
in the First Name combo. ( I tried so many ways to do this, but I failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN textbox
field
which is empty, should populate the correct SSN value from the first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField would be
the
name of a field in each table that matches a unique value in the current
record. txtIDField is a textbox on the form that holds this unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another
table?

Thanks
Praveen

Hi Morgan,

Thank you very much for your reply. You are excellent and it works too good.

But there is one problem, when I selected the Last Name combo, went to First
name Combo .. it is showing no records (the first name combo is blank =,
when I selected the Last Name combo). Do you know what might be the problem?

Thanks
Praveen Manne



"Wayne Morgan" wrote in message
...
Ok, this explanation is definately different than what I first understood.
First, using last name and first name combined as a primary key can cause
a
problem, what if you have two John Smiths? From the way you worded the
question compared to how you listed the tables, I'm not sure which way you
went, but I believe you are actually using the ID field as the primary
key.

Now, to help the users use the combo boxes. For the first combo box (last
name) set its Row Source to a query that will return just ONE of each last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that relies on the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox & """"
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of columns to
3,
the column widths to 0", 1", 1" (adjust the last two as desired to show
the
data without cutting it off), and set the Bound Column to 1. The Limit to
List option will automatically be set to Yes. For the SSN textbox, set its
control source to =cboCombo2.Column(2). The Column value is 0 based, so 2
is
the 3rd column.

You will need the SSN showing when you drop down the list to make the
selection becuase you will need to be able to distinguish between two
people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm
getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be able to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I want
the
users of this application, be able to select the Names from a combo box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter the
values
in the First Name combo. ( I tried so many ways to do this, but I failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN textbox
field
which is empty, should populate the correct SSN value from the first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField would
be
the
name of a field in each table that matches a unique value in the
current
record. txtIDField is a textbox on the form that holds this unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can
anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another
table?

Thanks
Praveen

Sorry,

In the AfterUpdate event of the first combo, requery the second one.

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thank you very much for your reply. You are excellent and it works too
good.

But there is one problem, when I selected the Last Name combo, went to
First
name Combo .. it is showing no records (the first name combo is blank =,
when I selected the Last Name combo). Do you know what might be the
problem?

Thanks
Praveen Manne



"Wayne Morgan" wrote in
message
...
Ok, this explanation is definately different than what I first
understood.
First, using last name and first name combined as a primary key can
cause
a
problem, what if you have two John Smiths? From the way you worded the
question compared to how you listed the tables, I'm not sure which way
you
went, but I believe you are actually using the ID field as the primary
key.

Now, to help the users use the combo boxes. For the first combo box
(last
name) set its Row Source to a query that will return just ONE of each
last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that relies on
the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox & """"
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of columns to
3,
the column widths to 0", 1", 1" (adjust the last two as desired to show
the
data without cutting it off), and set the Bound Column to 1. The Limit
to
List option will automatically be set to Yes. For the SSN textbox, set
its
control source to =cboCombo2.Column(2). The Column value is 0 based, so
2
is
the 3rd column.

You will need the SSN showing when you drop down the list to make the
selection becuase you will need to be able to distinguish between two
people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm
getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be able to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I want
the
users of this application, be able to select the Names from a combo
box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter the
values
in the First Name combo. ( I tried so many ways to do this, but I
failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN textbox
field
which is empty, should populate the correct SSN value from the first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField
would
be
the
name of a field in each table that matches a unique value in the
current
record. txtIDField is a textbox on the form that holds this unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can
anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to another
table?

Thanks
Praveen

Hi Morgan,

Sorry to bother you. I think I'm doing a silly mistake in this. The query
I'm using for the 2nd combo is

SELECT tblPhys.SSN, tblPhys.FN, tblPhys.Ord
FROM tblPhys
WHERE tblPhys.LN = """" & Forms!frmTransGrid!LN & """"
ORDER BY tblPhys.LN;


table name is tblPhys (I changed it a bit now)
fields are SSN, LN, FN, Ord
Last Name Combo name is LN
First Name Combo name is FN

The requery statement is Me.FN.Requery

Could you please help me?

Thanks
Praveen Manne

"Wayne Morgan" wrote in message
...
Sorry,

In the AfterUpdate event of the first combo, requery the second one.

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thank you very much for your reply. You are excellent and it works too
good.

But there is one problem, when I selected the Last Name combo, went to
First
name Combo .. it is showing no records (the first name combo is blank =,
when I selected the Last Name combo). Do you know what might be the
problem?

Thanks
Praveen Manne



"Wayne Morgan" wrote in
message
...
Ok, this explanation is definately different than what I first
understood.
First, using last name and first name combined as a primary key can
cause
a
problem, what if you have two John Smiths? From the way you worded the
question compared to how you listed the tables, I'm not sure which way
you
went, but I believe you are actually using the ID field as the primary
key.

Now, to help the users use the combo boxes. For the first combo box
(last
name) set its Row Source to a query that will return just ONE of each
last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that relies on
the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox & """"
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of columns
to
3,
the column widths to 0", 1", 1" (adjust the last two as desired to
show
the
data without cutting it off), and set the Bound Column to 1. The Limit
to
List option will automatically be set to Yes. For the SSN textbox, set
its
control source to =cboCombo2.Column(2). The Column value is 0 based,
so
2
is
the 3rd column.

You will need the SSN showing when you drop down the list to make the
selection becuase you will need to be able to distinguish between two
people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm
getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be able
to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is
the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I
want
the
users of this application, be able to select the Names from a combo
box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter the
values
in the First Name combo. ( I tried so many ways to do this, but I
failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN
textbox
field
which is empty, should populate the correct SSN value from the first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField) +
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField
would
be
the
name of a field in each table that matches a unique value in the
current
record. txtIDField is a textbox on the form that holds this unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can
anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to
another
table?

Thanks
Praveen

You don't say what problem you're having, so I'll take some guesses.

1) It appears that your combo boxes have the same name as the fields. Let's
change the names of the comboboxes to cboLN and cboFN so that we eliminate
any possible conflicts with names.

2) The order of items you have listed in the query is different from the
order I gave you based on the field names in your previous message. You will
need to adjust the column widths and the Column(#) statement accordingly.

3) Where did you put the requery statement? It looks correct (adjust it for
the change in #1). Did you put it in the Event Procedure in the VBA editor
or just straight into the box in the Properties sheet?

4) Since the FN combo will only have values from ONE last name, the one
selected in the LN combo, then ordering by LN won't do much. Change the
Order By clause to read FN.

5) I just tried the query and, while it worked with the quotes, it
rearranged some items to do so. They actually aren't needed, this seemed to
work just fine.

SELECT tblPhys.SSN, tblPhys.FN, tblPhys.ORD
FROM tblPhys
WHERE (((tblPhys.LN)=Forms!frmTransGrid!LN))
ORDER BY tblPhys.FN;


--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Sorry to bother you. I think I'm doing a silly mistake in this. The query
I'm using for the 2nd combo is

SELECT tblPhys.SSN, tblPhys.FN, tblPhys.Ord
FROM tblPhys
WHERE tblPhys.LN = """" & Forms!frmTransGrid!LN & """"
ORDER BY tblPhys.LN;


table name is tblPhys (I changed it a bit now)
fields are SSN, LN, FN, Ord
Last Name Combo name is LN
First Name Combo name is FN

The requery statement is Me.FN.Requery

Could you please help me?

Thanks
Praveen Manne

"Wayne Morgan" wrote in
message
...
Sorry,

In the AfterUpdate event of the first combo, requery the second one.

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thank you very much for your reply. You are excellent and it works too
good.

But there is one problem, when I selected the Last Name combo, went to
First
name Combo .. it is showing no records (the first name combo is blank
=,
when I selected the Last Name combo). Do you know what might be the
problem?

Thanks
Praveen Manne



"Wayne Morgan" wrote in
message
...
Ok, this explanation is definately different than what I first
understood.
First, using last name and first name combined as a primary key can
cause
a
problem, what if you have two John Smiths? From the way you worded
the
question compared to how you listed the tables, I'm not sure which
way
you
went, but I believe you are actually using the ID field as the
primary
key.

Now, to help the users use the combo boxes. For the first combo box
(last
name) set its Row Source to a query that will return just ONE of
each
last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that relies
on
the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox & """"
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of
columns
to
3,
the column widths to 0", 1", 1" (adjust the last two as desired to
show
the
data without cutting it off), and set the Bound Column to 1. The
Limit
to
List option will automatically be set to Yes. For the SSN textbox,
set
its
control source to =cboCombo2.Column(2). The Column value is 0 based,
so
2
is
the 3rd column.

You will need the SSN showing when you drop down the list to make
the
selection becuase you will need to be able to distinguish between
two
people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So I'm
getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be able
to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID is
the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I
want
the
users of this application, be able to select the Names from a
combo
box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter
the
values
in the First Name combo. ( I tried so many ways to do this, but I
failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN
textbox
field
which is empty, should populate the correct SSN value from the
first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote
in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" & txtIDField)
+
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table. IDField
would
be
the
name of a field in each table that matches a unique value in the
current
record. txtIDField is a textbox on the form that holds this
unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form. Can
anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to
another
table?

Thanks
Praveen

Hi,

I'm sorry that I didn't tell you the problem in my last email.

As you said ..

1. I corrected the names of combo boxes to cboLN and cboFN
2. I corrected the order of items, Column widths and Column(#) accordingly
3. I put the requery statement in the VBA editor. (Me.cboFN.Requery)
4. I chaged the query statment (the one without quotes)

and I did all the things you said in your previous emails.

The SSN is populating into the textbox accordingly (I chaged the control
source for testing to =LN.Column(0))

The problem is after I select the Last Name combo , the First Name combo is
supposed to filter the First Names according to my selection in the Last
Name.
But the First Name combo is showing no records, after I select a value from
the Last Name combo.

Please Help Me

Thank you very much for your prompt responses. I really appreciate that.

Thanks
Praveen Manne



"Wayne Morgan" wrote in message
...
You don't say what problem you're having, so I'll take some guesses.

1) It appears that your combo boxes have the same name as the fields.
Let's
change the names of the comboboxes to cboLN and cboFN so that we eliminate
any possible conflicts with names.

2) The order of items you have listed in the query is different from the
order I gave you based on the field names in your previous message. You
will
need to adjust the column widths and the Column(#) statement accordingly.

3) Where did you put the requery statement? It looks correct (adjust it
for
the change in #1). Did you put it in the Event Procedure in the VBA editor
or just straight into the box in the Properties sheet?

4) Since the FN combo will only have values from ONE last name, the one
selected in the LN combo, then ordering by LN won't do much. Change the
Order By clause to read FN.

5) I just tried the query and, while it worked with the quotes, it
rearranged some items to do so. They actually aren't needed, this seemed
to
work just fine.

SELECT tblPhys.SSN, tblPhys.FN, tblPhys.ORD
FROM tblPhys
WHERE (((tblPhys.LN)=Forms!frmTransGrid!LN))
ORDER BY tblPhys.FN;


--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Sorry to bother you. I think I'm doing a silly mistake in this. The
query
I'm using for the 2nd combo is

SELECT tblPhys.SSN, tblPhys.FN, tblPhys.Ord
FROM tblPhys
WHERE tblPhys.LN = """" & Forms!frmTransGrid!LN & """"
ORDER BY tblPhys.LN;


table name is tblPhys (I changed it a bit now)
fields are SSN, LN, FN, Ord
Last Name Combo name is LN
First Name Combo name is FN

The requery statement is Me.FN.Requery

Could you please help me?

Thanks
Praveen Manne

"Wayne Morgan" wrote in
message
...
Sorry,

In the AfterUpdate event of the first combo, requery the second one.

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thank you very much for your reply. You are excellent and it works
too
good.

But there is one problem, when I selected the Last Name combo, went
to
First
name Combo .. it is showing no records (the first name combo is
blank
=,
when I selected the Last Name combo). Do you know what might be the
problem?

Thanks
Praveen Manne



"Wayne Morgan" wrote in
message
...
Ok, this explanation is definately different than what I first
understood.
First, using last name and first name combined as a primary key
can
cause
a
problem, what if you have two John Smiths? From the way you worded
the
question compared to how you listed the tables, I'm not sure which
way
you
went, but I believe you are actually using the ID field as the
primary
key.

Now, to help the users use the combo boxes. For the first combo
box
(last
name) set its Row Source to a query that will return just ONE of
each
last
name.

Example:
SELECT DISTINCT Demo.LastName
FROM Demo
ORDER BY Demo.LastName;

For the second combo box, set its Row Source to a query that
relies
on
the
value in the first combo box.

Example:
SELECT Demo.ID, Demo.FirstName, Demo.SSN
FROM Demo
WHERE Demo.LastName = """" & Forms!frmMyForm!cboFirstCombobox &
""""
ORDER BY Demo.FirstName

In the second combo box's Properties sheet, set the number of
columns
to
3,
the column widths to 0", 1", 1" (adjust the last two as desired to
show
the
data without cutting it off), and set the Bound Column to 1. The
Limit
to
List option will automatically be set to Yes. For the SSN textbox,
set
its
control source to =cboCombo2.Column(2). The Column value is 0
based,
so
2
is
the 3rd column.

You will need the SSN showing when you drop down the list to make
the
selection becuase you will need to be able to distinguish between
two
people
with the same name.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi Morgan,

Thanks for your prompt reply. I'm not that good in access, So
I'm
getting
a
bit confused in it.
I will explain you the full scenario here, so that you may be
able
to
solve
my problem ...

I have 2 tables;
1. Demo: ID, LastName, FirstName (fields in the table Demo) ID
is
the
Primary key.
2. Orders: Last Name, First Name, NoOfOrders .......

I chose Last Name and First Name as the primary fields because I
want
the
users of this application, be able to select the Names from a
combo
box
instead of selecting their ID's.

Here I have 2 problems,

1. when I select Last Name in the first combo, it should filter
the
values
in the First Name combo. ( I tried so many ways to do this, but
I
failed
all
the times)
2. And when the First Name Combo got selected, I want the SSN
textbox
field
which is empty, should populate the correct SSN value from the
first
table.

Please Help

Thanks
Praveen Manne








"Wayne Morgan" wrote
in
message
...
You could use the DLookup function in a calculated control.

Example control source for textbox:
=DLookup("[FieldName]", "[Table 1]", "[IDField]=" &
txtIDField)
+
DLookup("[FieldName]", "[Table 2]", "[IDField]=" & txtIDField)

FieldName would be the name of the field in each table.
IDField
would
be
the
name of a field in each table that matches a unique value in
the
current
record. txtIDField is a textbox on the form that holds this
unique
value.

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
HI,

How to manipulate records of 2 different tables on a form.
Can
anyone
help
me in the correct syntax?

for eg: 23 is in table 1
and 75 is in table 2

how to add these two numbers on a form which is bounded to
another
table?

Thanks
Praveen

The SSN is going in because you changed the order in the query. The first
visible column will go into the combobox after the selection is made.
Reverse SSN and FN in the query to reverse the columns and the name will go
into the combo box after you make your selection.

You say the first name combo is showing no records after selecting a last
name in the last name combo. If that is the case, then where is the SSN
coming from since it is being filled in by the selection in the first name
combo box?

--
Wayne Morgan
MS Access MVP


"Praveen Manne" wrote in message
...
Hi,

I'm sorry that I didn't tell you the problem in my last email.

As you said ..

1. I corrected the names of combo boxes to cboLN and cboFN
2. I corrected the order of items, Column widths and Column(#) accordingly
3. I put the requery statement in the VBA editor. (Me.cboFN.Requery)
4. I chaged the query statment (the one without quotes)

and I did all the things you said in your previous emails.

The SSN is populating into the textbox accordingly (I chaged the control
source for testing to =LN.Column(0))

The problem is after I select the Last Name combo , the First Name combo
is
supposed to filter the First Names according to my selection in the Last
Name.
But the First Name combo is showing no records, after I select a value
from
the Last Name combo.

Please Help Me