Row Source Formatting

[Guest]

In Access 2000, I have a project (Citrus.adp) where I'm setting the row
source for the TripCode field in VB. I'm using a SQL statment that joins 2
tables (Trip and Trip_Payment) and gets fields from both. Trip_Payment is
the form's unique table.

The control source for the Boxes field is "=[TripCode].[column](2)". I set
it in the form where I've formatted the Boxes field, as well as many other
fields, as #,###. That format is working for all my unique table
(Trip_Payment) fields, but not for Boxes. In fact, none of my formatting is
working for any of my non-unique (Trip) table fields. I thought I might be
able to do the formatting using VB, but I can't find a way to do that.

Is there a way I can format those fields? Thanks in advance.

Mary Fetsch

 

[OfficeDev18 via AccessMonster.com]

Mary,

Use the controls' Property Sheets to control display. See the Format property
in the help file. Use this property in conjunction with the 'Decimal Places'
property to tweak your display.

Hope this helps,

Sam

In Access 2000, I have a project (Citrus.adp) where I'm setting the row
source for the TripCode field in VB. I'm using a SQL statment that joins 2
tables (Trip and Trip_Payment) and gets fields from both. Trip_Payment is
the form's unique table.

The control source for the Boxes field is "=[TripCode].[column](2)". I set
it in the form where I've formatted the Boxes field, as well as many other
fields, as #,###. That format is working for all my unique table
(Trip_Payment) fields, but not for Boxes. In fact, none of my formatting is
working for any of my non-unique (Trip) table fields. I thought I might be
able to do the formatting using VB, but I can't find a way to do that.

Is there a way I can format those fields? Thanks in advance.

Mary Fetsch

 

[Guest]

Doesn't work for fields that aren't in the unique table. In fact, when I
debug.print the Boxes field (debug.print Boxes.value), it tells me it's null,
but it displays 1500 on the screen.

Mary,

Use the controls' Property Sheets to control display. See the Format property
in the help file. Use this property in conjunction with the 'Decimal Places'
property to tweak your display.

Hope this helps,

Sam

In Access 2000, I have a project (Citrus.adp) where I'm setting the row
source for the TripCode field in VB. I'm using a SQL statment that joins 2
tables (Trip and Trip_Payment) and gets fields from both. Trip_Payment is
the form's unique table.

The control source for the Boxes field is "=[TripCode].[column](2)". I set
it in the form where I've formatted the Boxes field, as well as many other
fields, as #,###. That format is working for all my unique table
(Trip_Payment) fields, but not for Boxes. In fact, none of my formatting is
working for any of my non-unique (Trip) table fields. I thought I might be
able to do the formatting using VB, but I can't find a way to do that.

Is there a way I can format those fields? Thanks in advance.

Mary Fetsch

 

[OfficeDev18 via AccessMonster.com]

Mary,

Sorry. Let me back up. You say you initialize the Boxes field to

"=[TripCode].[column](2)".

What is TripCode? Is it a combo box? How many columns are in the combo box?

Sam

Doesn't work for fields that aren't in the unique table. In fact, when I
debug.print the Boxes field (debug.print Boxes.value), it tells me it's null,
but it displays 1500 on the screen.

Mary,

[quoted text clipped - 21 lines]

 

[Guest]

Sam,

Thanks for taking time with this. TripCode is a combo box, and its row
source is a SQL statement that selects TripCode, PkoCode, Boxes, and 2 other
fields. So there are 5 columns in the combo box, but I only show TripCode
when displaying the list of TripCodes. The control source for Boxes is
"=[TripCode].[column](2)".

Mary,

Sorry. Let me back up. You say you initialize the Boxes field to

"=[TripCode].[column](2)".

What is TripCode? Is it a combo box? How many columns are in the combo box?

Sam

Doesn't work for fields that aren't in the unique table. In fact, when I
debug.print the Boxes field (debug.print Boxes.value), it tells me it's null,
but it displays 1500 on the screen.

Mary,

[quoted text clipped - 21 lines]

Mary Fetsch

 

[OfficeDev18 via AccessMonster.com]

Mary,

According to the help file, when doing things this way you must include a
complete address, as in

=Forms!YourFormName!TripCode.Column(2).

See the Column property in the Help file for details.

By the way, if I were you I would make the text box unbound, and in the combo
box's Update event I would set the textbox value. Also, set the textbox's
Locked property to True so you won't inadvertently try to make an entry there.


Ergo, in the combo's Update event you would have this line of code:

Me.Boxes=Me.TripCode.Column(2)

(The keyword Me can be used as a shortcut for the current object - in this
case your form - in VBA, but not in a control's property sheet.)

Hope this helps,

Sam

Sam,

Thanks for taking time with this. TripCode is a combo box, and its row
source is a SQL statement that selects TripCode, PkoCode, Boxes, and 2 other
fields. So there are 5 columns in the combo box, but I only show TripCode
when displaying the list of TripCodes. The control source for Boxes is
"=[TripCode].[column](2)".

Mary,

[quoted text clipped - 15 lines]

 

[Guest]

Hi Sam,

I can't seem to get the complete address to work. I'm setting the Control
Source in the field itself, not in VB. I have an idea I don't need the
complete address because the format I use works in several forms.

As you suggested, I had already locked the field.

I don't think I want to make the field unbound because I want to see it when
I'm not updating, i.e., when I pull up an existing record. There may be a
way to do that with the field unbound, but I'm at a point where I can't spend
any more time on this because I've got a deadline looming. At this point,
it's not a big deal if the field's not formatted. I may experiment some more
with it at a later date, so if you have any more ideas, I'd like to hear
them. I appreciate alll your help with this.

Mary

Mary,

According to the help file, when doing things this way you must include a
complete address, as in

=Forms!YourFormName!TripCode.Column(2).

See the Column property in the Help file for details.

By the way, if I were you I would make the text box unbound, and in the combo
box's Update event I would set the textbox value. Also, set the textbox's
Locked property to True so you won't inadvertently try to make an entry there.


Ergo, in the combo's Update event you would have this line of code:

Me.Boxes=Me.TripCode.Column(2)

(The keyword Me can be used as a shortcut for the current object - in this
case your form - in VBA, but not in a control's property sheet.)

Hope this helps,

Sam

Sam,

Thanks for taking time with this. TripCode is a combo box, and its row
source is a SQL statement that selects TripCode, PkoCode, Boxes, and 2 other
fields. So there are 5 columns in the combo box, but I only show TripCode
when displaying the list of TripCodes. The control source for Boxes is
"=[TripCode].[column](2)".

Mary,

[quoted text clipped - 15 lines]

Mary Fetsch