Populating Subforms

[J.A.C.]

I need help!!!!! Please.

I have two tables - sub forms on a form ( source is two tables†Risk†and
“Loanâ€) is there any way that I can populate one of the forms (Table)if the
information on the other is a particular value?

For expamle – I have a field called “Risk Rating†and if the user enters a
specific number say 4 - I would like to have the other subform field
populated if it meets other criteria’s on that Loan form – As: On the loan
Form - If the value of Field “Total +/-“ is in a negative position I want
the Field titled “Exposure†to be populated with “Sâ€. - If it is a 5 then “Hâ€

At present once the loan table/Form has been completed - and is has to be in
this order - – the user then completes the Risk Form/Table – then returns to
the Loan Table to enter the “H†or “Sâ€. - it is time consuming and
sometimes overlooked.

Thank you.

 

[Arvin Meyer [MVP]]

The problem is that it is difficult, if not impossible, to ascertain which
record to write to on a subform located on another form. A subform only has
records when it has focus. Before then it is essentially a picture of
records. Rather than writing to the form, trying using a recordset or an
update query to write to the other subform's underlying table. You can only
do that if you have the key from the other table stored in your calling
form's underlying table.

 

[J.A.C.]

THANKS SO MUCH - I HAVE NOW BEEN ABLE TO DO THAT WITH AN UPDATE QUERY.
How do I acheive this using recorset?? I am fairly new at this.

jc

 

[Arvin Meyer [MVP]]

Something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset)

With rst
.AddNew
If Me.[Total +/-] < 0 Then
!Exposure = 4
ElseIf Me.[Tptal +/-] = 5 Then
!Exposure = 5
Else
!Exposure = 0
End If
.Update
End With
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[J.A.C.]

Thanks a bunch - I will give it a try.

--
Methals

Something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset)

With rst
.AddNew
If Me.[Total +/-] < 0 Then
!Exposure = 4
ElseIf Me.[Tptal +/-] = 5 Then
!Exposure = 5
Else
!Exposure = 0
End If
.Update
End With
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

THANKS SO MUCH - I HAVE NOW BEEN ABLE TO DO THAT WITH AN UPDATE QUERY.
How do I acheive this using recorset?? I am fairly new at this.

jc

 

[J.A.C.]

I Have a problem when I run the update query - When I enter the Criteria as
<0 it does not work.
For example - The Expression is as follows: Total +/-: ([Loan Value]-[O/S
amount])+[Net Wth] when I run it all the record show up - and then when I add
the criteria to filter out the unwanted records <0 the same records appear.

What am I doing wrong?
Methals

Thanks a bunch - I will give it a try.

--
Methals

Something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset)

With rst
.AddNew
If Me.[Total +/-] < 0 Then
!Exposure = 4
ElseIf Me.[Tptal +/-] = 5 Then
!Exposure = 5
Else
!Exposure = 0
End If
.Update
End With
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

THANKS SO MUCH - I HAVE NOW BEEN ABLE TO DO THAT WITH AN UPDATE QUERY.
How do I acheive this using recorset?? I am fairly new at this.

jc

--
Methals


:

The problem is that it is difficult, if not impossible, to ascertain
which
record to write to on a subform located on another form. A subform only
has
records when it has focus. Before then it is essentially a picture of
records. Rather than writing to the form, trying using a recordset or an
update query to write to the other subform's underlying table. You can
only
do that if you have the key from the other table stored in your calling
form's underlying table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I need help!!!!! Please.

I have two tables - sub forms on a form ( source is two tables" Risk"
and
"Loan") is there any way that I can populate one of the forms (Table)if
the
information on the other is a particular value?

For expamle - I have a field called "Risk Rating" and if the user
enters a
specific number say 4 - I would like to have the other subform field
populated if it meets other criteria's on that Loan form - As: On the
loan
Form - If the value of Field "Total +/-" is in a negative position I
want
the Field titled "Exposure" to be populated with "S". - If it is a 5
then
"H"

At present once the loan table/Form has been completed - and is has to
be
in
this order - - the user then completes the Risk Form/Table - then
returns
to
the Loan Table to enter the "H" or "S". - it is time consuming and
sometimes overlooked.

Thank you.

 

[Howard Burgman]

Thanks a bunch - I will give it a try.

--
Methals

Something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset)

With rst
.AddNew
If Me.[Total +/-] < 0 Then
!Exposure = 4
ElseIf Me.[Tptal +/-] = 5 Then
!Exposure = 5
Else
!Exposure = 0
End If
.Update
End With
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

THANKS SO MUCH - I HAVE NOW BEEN ABLE TO DO THAT WITH AN UPDATE QUERY.
How do I acheive this using recorset?? I am fairly new at this.

jc

--
Methals


:

The problem is that it is difficult, if not impossible, to ascertain
which
record to write to on a subform located on another form. A subform
only
has
records when it has focus. Before then it is essentially a picture of
records. Rather than writing to the form, trying using a recordset or
an
update query to write to the other subform's underlying table. You can
only
do that if you have the key from the other table stored in your
calling
form's underlying table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I need help!!!!! Please.

I have two tables - sub forms on a form ( source is two tables"
Risk"
and
"Loan") is there any way that I can populate one of the forms
(Table)if
the
information on the other is a particular value?

For expamle - I have a field called "Risk Rating" and if the user
enters a
specific number say 4 - I would like to have the other subform
field
populated if it meets other criteria's on that Loan form - As: On
the
loan
Form - If the value of Field "Total +/-" is in a negative position
I
want
the Field titled "Exposure" to be populated with "S". - If it is a
5
then
"H"

At present once the loan table/Form has been completed - and is has
to
be
in
this order - - the user then completes the Risk Form/Table - then
returns
to
the Loan Table to enter the "H" or "S". - it is time consuming and
sometimes overlooked.

Thank you.