Manipulate records of 2 tables

[Praveen Manne]

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

 

[Wayne Morgan]

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.

 

[Praveen Manne]

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

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

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

 

[Wayne Morgan]

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

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

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

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

 

[Praveen Manne]

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

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

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

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


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

 

[Wayne Morgan]

Sorry,

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

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP

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

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

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








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

 

[Praveen Manne]

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

Sorry,

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

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP

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



cause to
3, show
the

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


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








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


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

 

[Wayne Morgan]

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

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

Sorry,

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

Me.cboCombo2.Requery

--
Wayne Morgan
MS Access MVP


way
you each
last on
the

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


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








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


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

 

[Praveen Manne]

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

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

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

blank

=,

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


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








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


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

 

[Wayne Morgan]

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?

 

[krishna krishnamaneni]

Hi Mr.Morgan

Sorry to bother you. I corrected the errors. But the problem is, When I
selected the Last Name combo, it is filtering the First Name Combo and is
giving the appropriate values. But When I selected the correct first name,
it is repeating the same name in all the rows of First Name column. How to
correct this error?

Please help

Thanks
Praveen Manne

 

[Wayne Morgan]

Ok, it sounds as if you are using a form in continuous view. The problem is
that even though you see multiple controls, it is really only one control
repeated multiple times. Changing the Row Source of the control will change
it for all rows on the form. The only way around this that I've seen is to
place a textbox on top of the combobox's textbox portion so that it looks as
if its not there. Set the textbox's Locked property to Yes and its Enabled
property to No. Set the textbox's Control Source to a DLookup statement that
will return the value you want to see. When the row doesn't have the focus,
the textbox will show with the calculated value. When the row has the focus
and you click in that control, since the textbox can't receive the focus,
the combobox works as expected. You will probably have to also use the
form's Current event to update the combobox's row source when you move from
record to record.