Disappearing/Changing Combo Box Selections

First post, so be gentle. Here goes...
In Access 2003, I have a subform (headerdeductcodes), with the recordsource
being a table, connected to the main form by a ParentID. Within the subform,
I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
and Deduction_Description, which is populated based on the option selected
from Deduction_Type. 

The After Update code for Deduction_Type reads:

Private Sub Deduction_Type_AfterUpdate()

   Me.Deduction_Description = Null
   Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
DeductDescTable.DeductDesc, DeductDescTable.DescripID, DeductDescTable.Rate
FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
[HeaderDeductCodes]![tableid]));"
Me.Deduction_Description.Value = 0

End Sub

The problem arises when the subform loses focus, some of the selected options
from Deduction_Description either disappear or change values. The actual
values written to the table don’t change, but, to the user, the selected
values are no longer visible or changed. Any ideas on how to keep the
Deduction_Description selected options on the form after focus is lost?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200704/1

0
Rklein
4/4/2007 2:49:54 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
2698 Views

Similar Articles

[PageSpeed] 7

Okay, this is a fairly common problem. Access does not have a different 
RowSource for every row of the continuous form/datasheet. So if you modify 
the RowSource so that some values are not in the list, the rows that use 
those values have nothing to show, and so the combo goes blank on those 
rows.

Solutions:
=======

a) The most obvious workaround is not to limit the combo's RowSource like 
that.

b) Another solution is to make the combo's bound column visible. If it is 
not a hidden column, Access has the value to show. Typically you do this by 
dropping the autonumber, and storing the text value as the primary key of 
the combo's lookup table.

c) If those 2 choices don't give you what you need, the next option is to 
create a query that uses the main table and also the combo's lookup table. 
The query outputs all fields from the main table, and the description field 
from the lookup table. That means you can add a text box to your form to 
show the description, and it stlll does so, even when the combo's RowSource 
doesn't have the desired record.

The trick is to move make the text box's width 0.2" less than the combo, and 
place it on top of the combo (Format menu.) Then use its GotFocus event to 
SetFocus to the combo, so that as soon as the user clicks there, the combo 
jumps in front and the use can choose the desired value. By setting the 
combo's TabStop to No, it takes focus and passes focus to the combo, and so 
the user doesn't even know there is a text box on top of the combo.

Post back if that's not clear.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rklein via AccessMonster.com" <u33012@uwe> wrote in message
news:70342b37713dd@uwe...
> First post, so be gentle. Here goes...
> In Access 2003, I have a subform (headerdeductcodes), with the 
> recordsource
> being a table, connected to the main form by a ParentID. Within the 
> subform,
> I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
> DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
> and Deduction_Description, which is populated based on the option selected
> from Deduction_Type.
>
> The After Update code for Deduction_Type reads:
>
> Private Sub Deduction_Type_AfterUpdate()
>
>   Me.Deduction_Description = Null
>   Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
> DeductDescTable.DeductDesc, DeductDescTable.DescripID, 
> DeductDescTable.Rate
> FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
> [HeaderDeductCodes]![tableid]));"
> Me.Deduction_Description.Value = 0
>
> End Sub
>
> The problem arises when the subform loses focus, some of the selected 
> options
> from Deduction_Description either disappear or change values. The actual
> values written to the table don’t change, but, to the user, the selected
> values are no longer visible or changed. Any ideas on how to keep the
> Deduction_Description selected options on the form after focus is lost? 

0
Allen
4/4/2007 3:13:04 PM
Hi Allen
re your option A.  how else can I limit the rowsource?
thanks


"Allen Browne" wrote:

> Okay, this is a fairly common problem. Access does not have a different 
> RowSource for every row of the continuous form/datasheet. So if you modify 
> the RowSource so that some values are not in the list, the rows that use 
> those values have nothing to show, and so the combo goes blank on those 
> rows.
> 
> Solutions:
> =======
> 
> a) The most obvious workaround is not to limit the combo's RowSource like 
> that.
> 
> b) Another solution is to make the combo's bound column visible. If it is 
> not a hidden column, Access has the value to show. Typically you do this by 
> dropping the autonumber, and storing the text value as the primary key of 
> the combo's lookup table.
> 
> c) If those 2 choices don't give you what you need, the next option is to 
> create a query that uses the main table and also the combo's lookup table. 
> The query outputs all fields from the main table, and the description field 
> from the lookup table. That means you can add a text box to your form to 
> show the description, and it stlll does so, even when the combo's RowSource 
> doesn't have the desired record.
> 
> The trick is to move make the text box's width 0.2" less than the combo, and 
> place it on top of the combo (Format menu.) Then use its GotFocus event to 
> SetFocus to the combo, so that as soon as the user clicks there, the combo 
> jumps in front and the use can choose the desired value. By setting the 
> combo's TabStop to No, it takes focus and passes focus to the combo, and so 
> the user doesn't even know there is a text box on top of the combo.
> 
> Post back if that's not clear.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Rklein via AccessMonster.com" <u33012@uwe> wrote in message
> news:70342b37713dd@uwe...
> > First post, so be gentle. Here goes...
> > In Access 2003, I have a subform (headerdeductcodes), with the 
> > recordsource
> > being a table, connected to the main form by a ParentID. Within the 
> > subform,
> > I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
> > DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
> > and Deduction_Description, which is populated based on the option selected
> > from Deduction_Type.
> >
> > The After Update code for Deduction_Type reads:
> >
> > Private Sub Deduction_Type_AfterUpdate()
> >
> >   Me.Deduction_Description = Null
> >   Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
> > DeductDescTable.DeductDesc, DeductDescTable.DescripID, 
> > DeductDescTable.Rate
> > FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
> > [HeaderDeductCodes]![tableid]));"
> > Me.Deduction_Description.Value = 0
> >
> > End Sub
> >
> > The problem arises when the subform loses focus, some of the selected 
> > options
> > from Deduction_Description either disappear or change values. The actual
> > values written to the table don’t change, but, to the user, the selected
> > values are no longer visible or changed. Any ideas on how to keep the
> > Deduction_Description selected options on the form after focus is lost? 
> 
> 
0
Utf
5/15/2007 5:03:05 AM
The alternative is to programmatically assign a SQL statement to the combo's 
RowSource in the Current event of the main form.

Again, Access has display problems with this approach if the bound column is 
zero-width.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aeg" <aeg@discussions.microsoft.com> wrote in message
news:8F220E23-863D-4B1E-82FA-B99F59C8A792@microsoft.com...
> Hi Allen
> re your option A.  how else can I limit the rowsource?
> thanks
>
>
> "Allen Browne" wrote:
>
>> Okay, this is a fairly common problem. Access does not have a different
>> RowSource for every row of the continuous form/datasheet. So if you 
>> modify
>> the RowSource so that some values are not in the list, the rows that use
>> those values have nothing to show, and so the combo goes blank on those
>> rows.
>>
>> Solutions:
>> =======
>>
>> a) The most obvious workaround is not to limit the combo's RowSource like
>> that.
>>
>> b) Another solution is to make the combo's bound column visible. If it is
>> not a hidden column, Access has the value to show. Typically you do this 
>> by
>> dropping the autonumber, and storing the text value as the primary key of
>> the combo's lookup table.
>>
>> c) If those 2 choices don't give you what you need, the next option is to
>> create a query that uses the main table and also the combo's lookup 
>> table.
>> The query outputs all fields from the main table, and the description 
>> field
>> from the lookup table. That means you can add a text box to your form to
>> show the description, and it stlll does so, even when the combo's 
>> RowSource
>> doesn't have the desired record.
>>
>> The trick is to move make the text box's width 0.2" less than the combo, 
>> and
>> place it on top of the combo (Format menu.) Then use its GotFocus event 
>> to
>> SetFocus to the combo, so that as soon as the user clicks there, the 
>> combo
>> jumps in front and the use can choose the desired value. By setting the
>> combo's TabStop to No, it takes focus and passes focus to the combo, and 
>> so
>> the user doesn't even know there is a text box on top of the combo.
>>
>> Post back if that's not clear.
>>
>> "Rklein via AccessMonster.com" <u33012@uwe> wrote in message
>> news:70342b37713dd@uwe...
>> > First post, so be gentle. Here goes...
>> > In Access 2003, I have a subform (headerdeductcodes), with the
>> > recordsource
>> > being a table, connected to the main form by a ParentID. Within the
>> > subform,
>> > I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
>> > DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
>> > and Deduction_Description, which is populated based on the option 
>> > selected
>> > from Deduction_Type.
>> >
>> > The After Update code for Deduction_Type reads:
>> >
>> > Private Sub Deduction_Type_AfterUpdate()
>> >
>> >   Me.Deduction_Description = Null
>> >   Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
>> > DeductDescTable.DeductDesc, DeductDescTable.DescripID,
>> > DeductDescTable.Rate
>> > FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
>> > [HeaderDeductCodes]![tableid]));"
>> > Me.Deduction_Description.Value = 0
>> >
>> > End Sub
>> >
>> > The problem arises when the subform loses focus, some of the selected
>> > options
>> > from Deduction_Description either disappear or change values. The 
>> > actual
>> > values written to the table don’t change, but, to the user, the 
>> > selected
>> > values are no longer visible or changed. Any ideas on how to keep the
>> > Deduction_Description selected options on the form after focus is lost? 

0
Allen
5/15/2007 1:39:24 PM
Reply:

Similar Artilces:

Filling drop down box
hi, I have a drop down box in a cell. Based on a value of anohter cell I have to fill my drop down box. what i mean is, say if GreeNPackage is "No" then I want the drop down box to fill a range of values. But if the GreeNPackage is "Yes" then I want the drop down box to fill another set of values. how can i do this? plenty of thanks See reply in .Functions Biff >-----Original Message----- >hi, > >I have a drop down box in a cell. Based on a value of >anohter cell I have to fill my drop down box. what i mean >is, say if >GreeNPackage is ...

data from a modeless dialog box to its parent
I need to post a text data from a modeless dialog box to its parent which is itself a dialog based application. Since it should be a large text data a simple Windows message won't be good. What is the right way to do that? Thanks Gil If you are sure the data will stay current you can post the address of the data (as a pointer) in a windows message to the parent. If you want to be really sure use SendMessage() which will wait until the parent gets the message before returning. You could call a function or copy the data to a variable in the parent dialog, but that could be dang...

Mail merge recipient dialog box
Why are the "edit" and "refresh" buttons at the bottom of the mail merge recipient dialog box grayed out? They were available in the last Word version but I cannot figure how to access them in 7 to add to or correct my mailing list. In Word 2007, the first thing you have to do is select the name of the data source in the box at the bottom left of the dialog box. Then, depending on the type of data source, the Edit and Refresh buttons may be ungreyed. [FWIW the same dialog is used by MS Publisher, where they allow multiple data sources, requiring you to ...

How to delete or permantly change logo in personal settings
When I was just learnng how to do publisher I created a logo and Iwant to change it in the permanant settings for the personal information. I cannot delete it or edit it. Also on the other 3 settings for secondary, home etc. it does not alowany logo at all. Anyone know? Locate the following files and delete them: Primary Business - biz1logo.jsp Secondary Business - biz2logo.jsp Other Organization - Orglogo.jsp Home/Family - Perslogo.jsp -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no ...

Selecting records within a date range
What would be the proper expression to display records only between a specific date range? <#12/31/2006# Or >#1/1/2008# in the criteria field isn't filtering the data. Between #12/31/2006# and #1/1/2008# That should return any records including those dates; however if the January 1, 2008 date also has any time stored in it, those records won't show. Something like below will work: Between #12/31/2006# and #1/1/2008# + .99999 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kristibaer" wrote: > What ...

Select multiple adjacent cells of multiple cells without selecting
Select multiple adjacent cells of multiple cells without selecting adjacent cells one by one. those cells looks like; |adjacent cells |cells| |some characters | A | |some characters | B | |some characters | A | |some characters | A | |some characters | B | I am trying to select adjacent cells of A cells without selecting adjacent cells one by one. Thank you for your help. Adjacent in this case, means? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgr...

The case of the disappearing signature
Can anybody confirm and/or explain this? 1. Start with Outlook 2007 with at least two Accounts and a signature. 2. Open a New message (in HTML or plain text format) 3. Type a message (not actually necessary) 4. Insert signature 5. Click on the Account icon and change to another account. The signature disappears !?! Mike Tordoff <tordoff@monell.org> wrote: > Can anybody confirm and/or explain this? > > 1. Start with Outlook 2007 with at least two Accounts and a signature. > 2. Open a New message (in HTML or plain text format) > 3. Type a message (not actually necessary...

Checkbox event to control other checkbox selections
I have been working for a couple of hours now trying different ways to solve this problem and I'm out of ideas (or tired). :-/ Here's what I'm trying to do: On a form I have a section with six checkboxes that represent 6 different mailing lists an organization can subscribe to: MailList01, MailList02, etc. I have a seventh checkbox named MailListOmit for orgs that want to opt out of mailings. If MailListOmit is checked, when the user attempts to click any one of the MailList checkboxes, I want to return a dialog box that says, "This organization has chosen to not receive ma...

Copying selected items from Listbox into Textbox #2
Thanks for the response Jim. Unfortunately I'm trying to copy the selected items from the listbox o uf2 and display them in a textbox on uf1, not another listbox. I'v tried tinkering with the code below to see if I could modify it to cop it directly to a textbox, but to no avail. Can you suggest how I woul modify the code to achieve this. Jim Rech Wrote: > I'd suggest that in second userform's module you have a sub like thi > run > from the button's click: > > Private Sub CommandButton1_Click() > CopySelectedItems > Unload Me > End Sub > >...

Orolus offers the widest selection of Hermes watches (www.trade8.cc)
Baume Mercier, Omega, Tag Heuer ...Shop for Gucci G Watches, Gucci 1500 Watch, Gucci 100g, Gucci 101 g Chronograph, Gucci Twirl, Men=92s 111 Watches and 107 Chain Woman=92s Diamond Watch at ,Shop for watches such as Omega, omega watch, Gucci, Tissot, Hamilton and other fine brand name watcheshermes man,hermes watch,Orolus offers the widest selection of Hermes watches (www.trade8.cc) ...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

Save a Copy/Overwrite changes dialog box
All of a sudden, this dialog box pops up every time I want to save something in Excel 2002. The box says, "The file '<file name>' may have been changed by another user since you last saved it. In that case, what do you want to do? __ Save a Copy __ Overwrite changes even though I'm the only user that's changed anything in the past 3 years. The shared workbook command has not been changed at all, so that can't be the problem. The settings are the same as the ones I have at home for personal spreadsheets that don't have this dialog box. Help! Than...

How to change GAL from outlook
Hello I posted this to the other group but this might be more suitable: Background: I'm now connected to our domain example.local. Domain controller is W2K and has Exchange 2000 installed. I have Outlook 2002 on my XP pro. Address book and mails are working fine. Problem: I need change my Outlook to connect to other Exchange (let's call it E2) outside of our domain. I have managed to do that but Outlook shows global address book from my domain instead of E2. I don't need to use old exchange anymore but there are users that are still connected to the old one so I cannot...

Change the icon on Microsoft apps files?
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01CA9CFE.DD0ACFE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can anyone tell me how to change the icon on a Microsoft file/shortcut = -- for example, an excel spreadsheet or MCE recording? ------=_NextPart_000_0012_01CA9CFE.DD0ACFE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META con...

combo box list updating
Hi - I would like my combo box list to change base on the value of A1. That is, I have its input range being B1:F1 - "Year 1", " Year 2" etc. Example: -If cell A1 is the value "10" then the combo box default list item would be "Year 1" (which is cell "B1") -If cell A1 is the value "20" then the combo box default list item would be "Year 2" (which is cell "C1") ETC... Thanks for your help - Jim A ...

"Find a contact box" problem
Hi. I�m having problems with the �Find a contact� box on the Standard toolbar. Previously, I would type in a name and the search would pull up name/s from the Outlook Contacts folder first then, if the name is not in the Contacts folder, it would pull up name/s from the Global Address List if any. Now, when I type in a name, the search only pulls up name/s from the Global Address List. The search does not include the Contacts folder although there are entries in the Contacts folder. The problem started after I transferred a couple of Contacts subfolders to another pst file. How do I fix the p...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

need help with list box?
Hi I have a huge table of about 20000 records. how can I restrict entry for a few columns. I am new to excel programming. For example. I have a list of colors (red, green, blue) that I want to go under the Color column. how can I allow the user to click on a pulldown or list box similar to ms access for each cell. thanks in advance. Hi you could use 'Data - Validation'. See: http://www.contextures.com/xlDataVal01.html >-----Original Message----- >Hi > >I have a huge table of about 20000 records. how can I restrict entry for a >few columns. >I am new to exc...

Check box question 04-23-07
I have a form with a check box used to indicate if a receipt is voided or not. What I'm trying to do is go to a new record after the checkbox is marked. I have the following code in place and it works. Kinda. The problem is that if I then go back and UNCHECK the check box, it sends me to a new record again. Here's the code: Private Sub Check43_AfterUpdate() If Ckeck43 = Yes Then DoCmd.GoToRecord , , acNewRec Thanks for any help! End Sub The following works fine for me. If Me.Check3 = True Then DoCmd.GoToRecord , , acNewRec so try If Ckeck43 = True Then DoCmd.GoToRecor...

Changing default address book from Global to Contacts
I would like to change the Outlook 2000 default of listing the Global Adress book from our internal network to opening our shared business contacts when you click the "To:" button when addressing a new email. Does anybody know how to accomplish this? Thank you in advance. Go to Tools > Options > Addressing Tab and choose which Contacts folder to show first. -- Russ Valentine [MVP-Outlook] "Brad Streeeer" <brad@aerospecialties.com> wrote in message news:0b2501c3c3f4$f69d8220$a501280a@phx.gbl... > I would like to change the Outlook 2000 default of listing &...

Getting back a deleted test frame after saving the changes
My son just deleted a text frame with all his work and started a new text frame and then when asked if he wanted to save his work he clicked yes. Is there any way to retrieve that missing text frame when it won't allow you to undo? On Sat, 27 Aug 2005 16:04:01 +0100, Denise wrote (in article <09056FDE-03FC-4988-8AB0-D1EA0896BFC7@microsoft.com>): > My son just deleted a text frame with all his work and started a new text > frame and then when asked if he wanted to save his work he clicked yes. Is > there any way to retrieve that missing text frame when it won't a...

SERIES function disappear
I can't use SERIES in Excel 2007 (err msg, input function not correct) even i cant find SERIES function at "Insert function" dialog i disable Marco in Excel 2007, is it related? Pls Help, Thank you What were you expecting SERIES to do for you as a worksheet function? In Excel 2003, SERIES didn't exist in a worksheet, but only in a chart. -- David Biddulph "Vimm" <Vimm@discussions.microsoft.com> wrote in message news:1FA6C85E-A9F2-471F-9629-8ED8B9901CB8@microsoft.com... >I can't use SERIES in Excel 2007 (err msg, input function not correc...

Some contacts don't show on drop down box when addressing email
I was using Outlook Express, my computer motherboard died, got new computer with Windows 7, trying to learn Outlook 2003. The computer tech from my husband's office put Outlook 2003 on the new computer and somehow transferred the addresses that I had in Outlook Express. I do not know what method he used to get the addresses into Outlook 2003. Some things carried over just fine, some things didn't, i.e. a group list of my subdivision property owners, so I had to re-make that distribution list. When I want to email my daughter, her name or email address is not s...

changing entry's quickly #4
I have aproximately 5000 entries for product codes. My problem is this: I need to get rid of the last two digits of each code quickly. For example: I need to change 0234600 to 02346 is there a simple wa to do this? It needs to be done in text format because of the zero a the start of each product. Thank -- cj2 ----------------------------------------------------------------------- cj21's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39095 ...

How to change caption on 'apply' button in a CPropertySheet-derived class?
Hi, Does anyone know how to change the caption on the 'apply' button in a CPropertySheet-derived class? I derived a class CPlotSettingsDialog1. Here is it's OnCreate. int CPlotSettingsDialog1::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CPropertySheet::OnCreate(lpCreateStruct) == -1) return -1; CRect WindowRect; GetWindowRect(&WindowRect); WindowRect.bottom = WindowRect.bottom + 60; MoveWindow(&WindowRect); return 0; } all the code does is make the bottom of the property sheet a bit longer. It still has the 3 default buttons. I need to either re-caption t...