Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2003, 08:58 AM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access SubForm Problem

Greetings

I think that the answer to this question is "No", but I'll ask anyway.

On a subform, I would like to have a combobox for each record on the subform that allows me to edit a value in the record. The problem is that ... if you had 10 records in your subform, you would have 10 different combo boxes, but the problem is when you change the value in one of the combo boxes, it changes in the other 9 boxes too! Basically, any change that you make a record on a subform, seems to apply to them all! If you select the colour blue in a combobox for one record, blue will be displayed for all other records in the subform.

Am I doing something wrong?

Cheers

Joe
 
Old June 5th, 2003, 09:18 AM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure I understand what you are trying to do with the combo boxes on the subform, but you should make sure that your sub form is in continuous forms.

HTH,
Mike
 
Old June 5th, 2003, 09:25 AM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Firstly, thanks for the reply!

The subform is a continuous form. I'll try to explain it a bit better. Imagine that you are looking at a subform and it has 10 records in it. There is also a combobox that appears on each line of the subform i.e. one for each record. The combobox contains a list of colours. The problem is that if I go to any individual record, and select a different colour in the combo box for that record, the same colour is also selected on the other 9 combo boxes too!
 
Old June 5th, 2003, 11:00 AM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Make sure that the combo box is bound to a field in your subform. From the behaviour you describe, tt sounds as if it is not.

Regards,
Mike
 
Old June 6th, 2003, 07:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I think I know what you're trying to do. Correct me if I'm wrong. You have a subform that's a continuous form. This subform has a combobox that the user can choose a value. When a value is chosen, you want the subform's backcolor property to change. However, when one changes, they ALL change. Is that right?

To fix this, you need to fool the user.

1) Create an unbound textbox. Delete or hide its label.

2) Stretch this textbox so its height and width are the same size as the subform.

3) Make the textbox's forecolor = backcolor.

4) Also set the textbox placement, i.e. FORMAT / SEND TO BACK.

By doing steps 1 to 4, you're making a "fake" background.

5) Put conditional formatting on this textbox. If its value = X then set the backcolor and forecolor to red; if it's = Y, set them to blue; etc.

6) On the combobox's AfterUpdate event, set the textbox's value = to the combobox's.

What will happen is that when you select a value from the combobox, the textbox will equal its value. When that happens, the conditional formatting on the textbox will trigger the textbox's backcolor and forecolor to change. But because you set the textbox as big as the subform (i.e. a "fake" background) it will appear to the user that the subform's backcolor changed. If done correctly, only ONE part of the subform in the continuous subform will change. The rest will not.

Messy, but it works.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old June 6th, 2003, 07:50 AM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Cheers Greg, I'll give that a go!!
 
Old June 9th, 2003, 01:05 AM
Authorized User
 
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to maha
Default

Hi Joe,

I agree to Mike. Just make that combo box bound to a field in your subform. That will work. It simple and easy. I work with a lot of subforms. I came across this issue many times and I just do the same trick and it works.

Greg, I agree your solution works. However, it is messy work.

Maha Arupputhan Pappan
IT Specialist
Nacap Asia Pacific Sdn Bhd - Malaysia
 
Old April 20th, 2005, 05:44 PM
Registered User
 
Join Date: Apr 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to NiceHandTick
Default

New Member here....

I'm struggling with the same problem with a subform on a form......

I'm using the conditional formatting for background colors that are affected by the values in a payment received and payment due field, continuous forms.....

It seems to work fine, except when I try to advance to another form using a search box, it crashes on me......but the conditional formatting seems to work okay, if you're not using a search box and the bookmark property.

I have Access XP developer and I sure wish I could figure out why it's crashing on me.... I need to send the program out the door.

---------------------
Some days you eat
the bear...some days
the bear eats you.
---------------------
 
Old May 13th, 2005, 07:04 PM
Registered User
 
Join Date: May 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Greg or any VBA expert looking in,
I'm trying to use FormatConditions and your excellent advice on creating a fake background with a text box, but my code isn't working. I've modified something I found elsewhere on the web. I'm trying to color different records in a continuous form based on a value in a field called Status. The values are strings: New, Active, Chgd or Inactive. Here's the code and also what I tried before commented out. I tried eliminating the variable from the previous code with no luck and know that I probably need to keep it for null values in the new code when it's done. I've also replaced formal declarations with Me with no change. Nothing happens when I run this code. If I comment out the FormatConditions.Delete line and have a conditional format that I've created using the GUI, the code stops at the first Set objFrc statement stating something to the effect that the collection index is greater than what's available. I'm thinking my objects are't getting created, but I'm not really sure. Do you have any idea what the problem might be?

'Private Sub Form_Current()
'Define the colors and receiving variable
'Dim LYellow As Long, LRed As Long, LBlack As Long, LWhite As Long
'Dim Status As String
'Set color values
'LRed = RGB(255, 0, 0)
'LBlack = RGB(0, 0, 0)
'LYellow = RGB(255, 255, 0)
'LWhite = RGB(255, 255, 255)
'Assign variable
'If Not IsNull(Forms!FTargets!Status.Value) Then
 ' Status = Forms!FTargets!Status.Value
'End If
'Test the Status value and set BackColor accordingly
'If Forms![FTargets]![Status].Value = "New" Then
' Forms!FTargets!txtRecord.BackColor = LYellow

    ' ElseIf Status = "Chgd" Then
    ' Forms!FTargets!txtRecord.BackColor = LRed

    'ElseIf Status = "Inactive" Then
     ' Forms!FTargets!txtRecord.BackColor = LBlack
 'This line actually works!
' Else
' Forms!FTargets!txtRecord.BackColor = LRed

'End If
'End Sub
'_________________________________________________ _____________________
'New attempt at same sub
'Try a different event, no difference
Private Sub SetUp_Click()
'Define the colors and FormattingConditions
Dim LYellow As Long, LRed As Long, LBlack As Long, LWhite As Long
Dim objFrc As FormatCondition
'Set color values
LRed = RGB(255, 0, 0)
LBlack = RGB(0, 0, 0)
LYellow = RGB(255, 255, 0)
LWhite = RGB(255, 255, 255)

'Remove any existing format conditions.
Forms!FTargets![txtRecord].FormatConditions.Delete

'Create first FormatCondition object index 0
Set objFrc = Forms!FTargets![txtRecord].FormatConditions.Add(acFieldValue, acEqual, "New")
'Create first FormatCondition object index 1
Set objFrc = Forms!FTargets![txtRecord].FormatConditions.Add(acFieldValue, acEqual, "Chgd")
'Create first FormatCondition object index 2
Set objFrc = Forms!FTargets![txtRecord].FormatConditions.Add(acFieldValue, acEqual, "Inactive")

'Assign properties to each FormatCondition object
With Forms!FTargets![txtRecord].FormatConditions(0)
    .BackColor = LRed
End With

With Forms!FTargets![txtRecord].FormatConditions(1)
    .BackColor = LYellow
End With

With Forms!FTargets![txtRecord].FormatConditions(2)
    .BackColor = LBlack
    .ForeColor = LWhite
End With

End Sub

Please help!

 
Old August 29th, 2005, 03:03 PM
Registered User
 
Join Date: Aug 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Greg,

I want to do somthing similar.

I have a continuous form showing records from a table.
The first column is a check box column which is linked to a boolean field in the database.

Now what I want to do is, highlight the entire record when user checks a record, and remove highlighting when it is unchecked.

How can this be done?

Your help is appreciated.

Thanks in advance

Vikram



Quote:
quote:Originally posted by SerranoG
 I think I know what you're trying to do. Correct me if I'm wrong. You have a subform that's a continuous form. This subform has a combobox that the user can choose a value. When a value is chosen, you want the subform's backcolor property to change. However, when one changes, they ALL change. Is that right?

To fix this, you need to fool the user.

1) Create an unbound textbox. Delete or hide its label.

2) Stretch this textbox so its height and width are the same size as the subform.

3) Make the textbox's forecolor = backcolor.

4) Also set the textbox placement, i.e. FORMAT / SEND TO BACK.

By doing steps 1 to 4, you're making a "fake" background.

5) Put conditional formatting on this textbox. If its value = X then set the backcolor and forecolor to red; if it's = Y, set them to blue; etc.

6) On the combobox's AfterUpdate event, set the textbox's value = to the combobox's.

What will happen is that when you select a value from the combobox, the textbox will equal its value. When that happens, the conditional formatting on the textbox will trigger the textbox's backcolor and forecolor to change. But because you set the textbox as big as the subform (i.e. a "fake" background) it will appear to the user that the subform's backcolor changed. If done correctly, only ONE part of the subform in the continuous subform will change. The rest will not.

Messy, but it works.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division





Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Subform RecordSource Issue llaurit Access 3 March 12th, 2008 01:04 PM
How to bind Recordset to an Access Subform in VB? fcortes BOOK: Access 2003 VBA Programmer's Reference 1 March 1st, 2008 08:39 PM
Access VBA passing to subform ayazhoda Access VBA 1 July 11th, 2007 07:45 AM
Passing a Variable to a SubForm in Access ritag Access 1 September 29th, 2004 03:53 PM
Access help, subform on a cont. form akwormy Access VBA 1 December 28th, 2003 05:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.