Unbound text boxes in a Sub Form

[John Pangallo]

I have a subform which has a default view of "Continuous forms". On this
form I have several unbound text boxes to display data from other tables.
This data does not need to be saved to the sub form record source as it is
for display purposes only. The data is used to determine an appropriate sell
price for a product. The table that the sub form is linked to is an SQL
Server table.
The unbound text boxes work well when there is only one record. As soon as
I add a second record and update these unbound text boxes the values become
the same on all records (rows). I need each row to show information relating
to the record.

Hope this makes sense.

TIA

John Pangallo

 

[Allen Browne]

Access has no way to handle the unbound controls differently on different
rows of a continuous form. They will always show the same values.

Alternatives:
- Place the unbound controls in the Form Footer section, so they do not
appear on every row.

- Instead of using unbound controls, if the data depends on other controls
in the record, you could bind the controls to an expression that calculates
the correct value for the row.

 

[Peter Hibbs]

John,

A Flex Grid Control will probably do what you want. Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

for some examples.

Peter Hibbs.

On Mon, 9 Jul 2007 22:56:02 -0700, John Pangallo <John

 

[John Pangallo]

Allen

Thank you for your help.
But I cannot put the values in the footer as the values are different for
every row.

I have tried to bind the controls to an expression. But because the data is
to be inserted into SQL server as soon as I bind the form to an expression
and not a table directly I cannot insert records. This is why I tried
unbound text boxes.

Regrads

John Pangallo

 

[John Pangallo]

Peter

I think it may come to this. I was trying to avoid a flex grid as I am a
contractor developing this product and when I leave people who know very
little about development want to maintain the product. My resources are very
limited.

Thank you

John Pangallo

 

[Allen Browne]

I don't understand that reply.

If you place the text box in the form footer, it displays only once - for
the current row. You can't see the value for the other rows. (Perhaps you
need ot.)

Creating an expression (calculated field) should not make the query
non-updatable. If it does (such as using a subquery), it may be posssible to
use an alternative expression (such as a DLookup().)

 

[John Pangallo]

Allen

I think the best solution would be the DLookup function.

But I cannot get the thing to work. My Dlookup function relies on the users
selection in a combo box. In need the value from the third column in the
combo box.

I have tried = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
cboPartNo.Column(2)). But Access does not like the .Column part. I am not
sure how to retrieve this value from the combo box. The SOSID is dependent
on the Part that the user selects.

I have also tried to put this into an event with code like the following

Me.txtSOS.ControlSource = "= DLookup(" & Chr(34) & "[SOSDescription]" &
Chr(34) & _
", " & Chr(34) & "tlbSOS" & Chr(34) & ", " & _
Chr(34) & "[SOSID] = " & Me.cboPartNo.Column(2) & Chr(34) & ")"

But this returns an #Error.

I am willing to give anything a gTIA.

John Pangallo

 

[Allen Browne]

Column is a zero-based property (i.e. the first column is 0, the 2nd is 1,
etc), so Column(2) would be the 3rd column.

Is that what you intend?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

I think the best solution would be the DLookup function.

But I cannot get the thing to work. My Dlookup function relies on the
users
selection in a combo box. In need the value from the third column in the
combo box.

I have tried = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
cboPartNo.Column(2)). But Access does not like the .Column part. I am
not
sure how to retrieve this value from the combo box. The SOSID is
dependent
on the Part that the user selects.

I have also tried to put this into an event with code like the following

Me.txtSOS.ControlSource = "= DLookup(" & Chr(34) & "[SOSDescription]" &
Chr(34) & _
", " & Chr(34) & "tlbSOS" & Chr(34) & ", " & _
Chr(34) & "[SOSID] = " & Me.cboPartNo.Column(2) & Chr(34) &
")"

But this returns an #Error.

I am willing to give anything a gTIA.

John Pangallo

I don't understand that reply.

If you place the text box in the form footer, it displays only once - for
the current row. You can't see the value for the other rows. (Perhaps you
need ot.)

Creating an expression (calculated field) should not make the query
non-updatable. If it does (such as using a subquery), it may be posssible
to
use an alternative expression (such as a DLookup().)

 

[John Pangallo]

Allen

I know Column is a zero based property. Access does not like the column
property when I place the formula into the Control Source property of the
text box at design time.

e.g. = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " & cboPartNo.Column(2))

When I place this formula into the control Source property Access places
square brackets around the word "column". So the formula looks like
= Dlookup("[SOSDescription]", "tblSOS", "SOSID = " & [cboPartNo].[Column](2))
And when I load the form the value in the text box is #Name.
I need to know how to reference the combo box columns at design time.

Many thanks for all your help.

John Pangallo

Column is a zero-based property (i.e. the first column is 0, the 2nd is 1,
etc), so Column(2) would be the 3rd column.

Is that what you intend?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

I think the best solution would be the DLookup function.

But I cannot get the thing to work. My Dlookup function relies on the
users
selection in a combo box. In need the value from the third column in the
combo box.

I have tried = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
cboPartNo.Column(2)). But Access does not like the .Column part. I am
not
sure how to retrieve this value from the combo box. The SOSID is
dependent
on the Part that the user selects.

I have also tried to put this into an event with code like the following

Me.txtSOS.ControlSource = "= DLookup(" & Chr(34) & "[SOSDescription]" &
Chr(34) & _
", " & Chr(34) & "tlbSOS" & Chr(34) & ", " & _
Chr(34) & "[SOSID] = " & Me.cboPartNo.Column(2) & Chr(34) &
")"

But this returns an #Error.

I am willing to give anything a gTIA.

John Pangallo

I don't understand that reply.

If you place the text box in the form footer, it displays only once - for
the current row. You can't see the value for the other rows. (Perhaps you
need ot.)

Creating an expression (calculated field) should not make the query
non-updatable. If it does (such as using a subquery), it may be posssible
to
use an alternative expression (such as a DLookup().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thank you for your help.
But I cannot put the values in the footer as the values are different
for
every row.

I have tried to bind the controls to an expression. But because the
data
is
to be inserted into SQL server as soon as I bind the form to an
expression
and not a table directly I cannot insert records. This is why I tried
unbound text boxes.

Regrads

John Pangallo

:

Access has no way to handle the unbound controls differently on
different
rows of a continuous form. They will always show the same values.

Alternatives:
- Place the unbound controls in the Form Footer section, so they do
not
appear on every row.

- Instead of using unbound controls, if the data depends on other
controls
in the record, you could bind the controls to an expression that
calculates
the correct value for the row.

message
I have a subform which has a default view of "Continuous forms". On
this
form I have several unbound text boxes to display data from other
tables.
This data does not need to be saved to the sub form record source as
it
is
for display purposes only. The data is used to determine an
appropriate
sell
price for a product. The table that the sub form is linked to is an
SQL
Server table.
The unbound text boxes work well when there is only one record. As
soon
as
I add a second record and update these unbound text boxes the values
become
the same on all records (rows). I need each row to show information
relating
to the record.

 

[Allen Browne]

The square brackets are added. They are not the problem.

The #Name must have another cause. Make sure the Name of this text box is
not the same as the name of any field in the form's RecordSource.

Beyond that, you could use the Immediate Window to debug the expression. You
will need to use the full refernece to the combo, e.g.:
? [Forms]![MyMain].[MySub].[Form]![cboPartNo].Column(2)

If that gives the expected result, you can then try:
? Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
[Forms]![MyMain].[MySub].[Form]![cboPartNo].Column(2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

I know Column is a zero based property. Access does not like the column
property when I place the formula into the Control Source property of the
text box at design time.

e.g. = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
cboPartNo.Column(2))

When I place this formula into the control Source property Access places
square brackets around the word "column". So the formula looks like
= Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
[cboPartNo].[Column](2))
And when I load the form the value in the text box is #Name.
I need to know how to reference the combo box columns at design time.

Many thanks for all your help.

John Pangallo

Column is a zero-based property (i.e. the first column is 0, the 2nd is
1,
etc), so Column(2) would be the 3rd column.

Is that what you intend?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

I think the best solution would be the DLookup function.

But I cannot get the thing to work. My Dlookup function relies on the
users
selection in a combo box. In need the value from the third column in
the
combo box.

I have tried = Dlookup("[SOSDescription]", "tblSOS", "SOSID = " &
cboPartNo.Column(2)). But Access does not like the .Column part. I am
not
sure how to retrieve this value from the combo box. The SOSID is
dependent
on the Part that the user selects.

I have also tried to put this into an event with code like the
following

Me.txtSOS.ControlSource = "= DLookup(" & Chr(34) & "[SOSDescription]" &
Chr(34) & _
", " & Chr(34) & "tlbSOS" & Chr(34) & ", " & _
Chr(34) & "[SOSID] = " & Me.cboPartNo.Column(2) & Chr(34) &
")"

But this returns an #Error.

I am willing to give anything a gTIA.

John Pangallo


:

I don't understand that reply.

If you place the text box in the form footer, it displays only once -
for
the current row. You can't see the value for the other rows. (Perhaps
you
need ot.)

Creating an expression (calculated field) should not make the query
non-updatable. If it does (such as using a subquery), it may be
posssible
to
use an alternative expression (such as a DLookup().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Allen

Thank you for your help.
But I cannot put the values in the footer as the values are
different
for
every row.

I have tried to bind the controls to an expression. But because the
data
is
to be inserted into SQL server as soon as I bind the form to an
expression
and not a table directly I cannot insert records. This is why I
tried
unbound text boxes.

Regrads

John Pangallo

:

Access has no way to handle the unbound controls differently on
different
rows of a continuous form. They will always show the same values.

Alternatives:
- Place the unbound controls in the Form Footer section, so they do
not
appear on every row.

- Instead of using unbound controls, if the data depends on other
controls
in the record, you could bind the controls to an expression that
calculates
the correct value for the row.

message
I have a subform which has a default view of "Continuous forms".
On
this
form I have several unbound text boxes to display data from other
tables.
This data does not need to be saved to the sub form record source
as
it
is
for display purposes only. The data is used to determine an
appropriate
sell
price for a product. The table that the sub form is linked to is
an
SQL
Server table.
The unbound text boxes work well when there is only one record.
As
soon
as
I add a second record and update these unbound text boxes the
values
become
the same on all records (rows). I need each row to show
information
relating
to the record.