Filter Combo Box

[Djblois]

I am creating a Combobox that is filtered but it should show the Second Column - instead it is showing the Primary Key column. Here is the SQl i Have attached:

SELECT tblSalesPeople.SalesPerson FROM tblSalesPeople WHERE (((tblSalesPeople.Company_ID)=1)) ORDER BY tblSalesPeople.SalesPerson;

 

[vbaInet]

Before filtering does it show the right number of columns?

 

[Djblois]

I only want it to show the Salesperson column but it should be bound by the slsprsn_ID column. Right now I can either get it to show the slsprsn_ID column or if it does show the Salesperson column then it is not bound by the slsprsn_ID. I can get it to work perfectly if I do not filter also.

 

[vbaInet]

When it gets filtered and shows the ID, click on the combo box to drop down and see if the related item is highlighted in the drop-down.

Let's see the code that does the filtering of your combo box.

 

[anski]

this is my problem: i have a combo box that looks up values from a query. i filtered the query to show ACTIVE accounts only. when i click on the drop-down arrow, i only see the ACTIVE accounts, which is correct. my problem is when an account becomes inactive. i go back to the old entries where an account (that was previously active) was used. the account that has become inactive no longer appears in the entry (the form, not the drop-down list), but the account is still in the underlying table. so when i print reports, the inactive account is still there. it's only in the FORM that i don't see the account anymore.

Example: in january, cash #01 account was still active. so users were still able to pull up the cash #01 account from the combo box to make their accounting entries. in february, cash #01 became an inactive account. users could no longer see this account in the drop-down list beginning february, which is correct. when i check the entries made in january (using the entry form), i see a blank field for the cash #01 account but the other fields for that same row are still there (amount, description, etc.). the account name has just become invisible but the data is in the table. when the users see this, they might think that they have forgotten to enter an account and they might try to enter an account therefore changing the original entry.

what should i do? i want the combo box to show only active accounts (which i am able to do) but the inactive accounts should still be visible in entries if they have been used prior to the account becoming inactive.

 

[Phan]

I have the same issue and haven't gotten any replies.

 

[vbaInet]

this is my problem: i have a combo box that looks up values from a query. i filtered the query to show ACTIVE accounts only. when i click on the drop-down arrow, i only see the ACTIVE accounts, which is correct. my problem is when an account becomes inactive. i go back to the old entries where an account (that was previously active) was used. the account that has become inactive no longer appears in the entry (the form, not the drop-down list), but the account is still in the underlying table. so when i print reports, the inactive account is still there. it's only in the FORM that i don't see the account anymore.

Example: in january, cash #01 account was still active. so users were still able to pull up the cash #01 account from the combo box to make their accounting entries. in february, cash #01 became an inactive account. users could no longer see this account in the drop-down list beginning february, which is correct. when i check the entries made in january (using the entry form), i see a blank field for the cash #01 account but the other fields for that same row are still there (amount, description, etc.). the account name has just become invisible but the data is in the table. when the users see this, they might think that they have forgotten to enter an account and they might try to enter an account therefore changing the original entry.

what should i do? i want the combo box to show only active accounts (which i am able to do) but the inactive accounts should still be visible in entries if they have been used prior to the account becoming inactive.

Your combo box has a ROW SOURCE property which defines the set of records that are generated for display. I would imagine the record source of your report is different from the record source of your form hence, there's no comparison.

Look in the row source and remove the Criteria that's filtering the records.

 

[anski]

to vbinet: i have no problem with the report. i also have no problem with the filtering of the combo box. when i click on the drop-down arrow of the combo box, i see only the ACTIVE accounts because i filtered the query to show only active accounts.

my problem is with the form (not the report). when an account that was previously active becomes inactive and this account was used in entries, when i look at the entries using the form, i no longer see the inactive account in the row but the other details of the row are still present. take a look at my attachment so you can clearly see my dilemma.

 

[vbaInet]

I see. How is the subform linked to the main form? Are you using code or is it via Master-Child links? Have you had a look at the query of your subformto ensure that it's set to filter out all the inactive entries.

Try putting a Me.Requery on the After_Update event of the subform.

 

[anski]

subform is linked via master-child links. there is no problem with the query. whenever a user clicks on the dropdown arrow, only the ACTIVE accounts are shown, which is definitely correct.

it is only when an active account becomes inactive that i have a problem. when an active account is used in an entry and then later on this same account becomes inactive, the account no longer is visible in the form but the underlying table still has the correct data (which is definitely correct). kindly view the picture i attached so you have an idea. i don't know how to explain it clearly.

 

[vbaInet]

I actually did look at your screenshot before commenting . Did you try requerying the subform (as mentioned in my last post)? If you're changing the active status of an entry from a different form then you would need to reference your subform accordingly:

Forms!Parent_Form_Name!Subform_Name.Form.Requery

That should do it.

 

[anski]

thanks, vbainet. the requery still did not work. i'm just wondering why you are suggesting a requery? the query which the combobox is based on is working perfectly. users only see all the ACTIVE accounts when they make accounting entries.

here is another example: this morning, i make the following entry:

cash#01 $1500
cash#02 $1500

at the end of the day, the administrator makes cash#01 inactive. starting tomorrow, users will no longer be able to see this account in the combo box (which is correct).

the following day, i open the same form where i used to enter my entries and i review the entry i prepared yesterday (entry above). when i look at the entry, it appears this way:

$1500
cash#02 $1500

i no longer see the name cash#01 in the form but in the underlying table, i see cash#01. it's just in the form that i don't see it anymore. i'm concluding that the form is behaving this way because the account cash#01 was made inactive.

 

[jdraw]

thanks, vbainet. the requery still did not work. i'm just wondering why you are suggesting a requery? the query which the combobox is based on is working perfectly. users only see all the ACTIVE accounts when they make accounting entries.

here is another example: this morning, i make the following entry:

cash#01 $1500
cash#02 $1500

at the end of the day, the administrator makes cash#01 inactive. starting tomorrow, users will no longer be able to see this account in the combo box (which is correct).

the following day, i open the same form where i used to enter my entries and i review the entry i prepared yesterday (entry above). when i look at the entry, it appears this way:

$1500
cash#02 $1500

i no longer see the name cash#01 in the form but in the underlying table, i see cash#01. it's just in the form that i don't see it anymore. i'm concluding that the form is behaving this way because the account cash#01 was made inactive.

Reading this set of posts leaves me a little confused.
You said the combo box is working correctly - only active accounts are available.

The form's behaviour " i'm concluding that the form is behaving this way because the account cash#01 was made inactive. ".

If your conclusion is correct, and I believe it is, has the issue been resolved or do you want something different to show on the form?

 

[vbaInet]

Reading this set of posts leaves me a little confused.
You said the combo box is working correctly - only active accounts are available.

The form's behaviour " i'm concluding that the form is behaving this way because the account cash#01 was made inactive. ".

If your conclusion is correct, and I believe it is, has the issue been resolved or do you want something different to show on the form?

Have a look at the screenshot in post #8. The OP wants to hide an entry that was made inactive.

I would have thought it was a requery so as to get the records refreshed, but I'm now thinking it's the query.

Are you able to post the db with some dummy data?

 

[jdraw]

Have a look at the screenshot in post #8. The OP wants to hide an entry that was made inactive.

I would have thought it was a requery so as to get the records refreshed, but I'm now thinking it's the query.

Are you able to post the db with some dummy data?

Or put a filter on the recordsource for the form

where not Inactive
using whatever the proper syntax and rules to determine how Inactive is identified.

 

[anski]

If your conclusion is correct, and I believe it is, has the issue been resolved or do you want something different to show on the form?

i want the form to show the original entry the users made, all accounts that they used regardless whether the account was active or inactive AT THE TIME OF VIEWING. the underlying table of the subform contains all the accounts that they used. its only in the form that the inactive accounts don't appear anymore. in the picture i attached, there is a blank field. that blank field belongs to an inactive account. when i change that particular account to active, that account becomes visible in that field.

i have attached our database. i have deleted unnecessary tables. when you open the file, go to JV# 2 (record #2). you will notice a blank field in the entry. if you take a look at the underlying table of the subform (gl_sub), AR-Erwin account is in the table. this should be the account that should appear in that blank field. at the time this entry was made, AR-Erwin was an active account. now, this account is inactive. you can see all the accounts in frm_account. i hope my problem is clear to you now.