INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with Query showing Active/Inactive Customers

Help with Query showing Active/Inactive Customers

(OP)
Hi all,
I have an application form that contains many fields from few different tables.
One of those fields is a dropdown that list all active customers. I just added ‘Inactive’ field to the customer table today, this table is what is used as the rowsource for that dropdown box.

The Dropdown should hide all inactive customers UNLESS that customer is selected as the applicant on the current file.

I modified the rowsource of the dropdown to: ‘Select CustID, CustName from Customers where Inactive = false’ ; however if the current form has a customer that is inactive it does not show the CUSTID at all.

How do I get the dropdown to show me the CUSTID on the current form if that customer is inactive?

Thanks

EG

RE: Help with Query showing Active/Inactive Customers

"I just added ‘Inactive’ field the customer table"
Not to be picky, but I would name this new field 'Active' with True or False values.

Active True
Active False
makes sense to me, but:
Inactive True
Inactive False
that's a 'double-negative' in my book and it is always very confusing of what is what. Is 'Inactive False' an Active Customer or Inactive Customer? The same for: Inactive True

Or maybe it is just me...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Help with Query showing Active/Inactive Customers

Can you explain "UNLESS that customer is selected as the applicant on the current file"? Is this the "current record"?

Is this form continuous or single view?

Duane
Hook'D on Access
MS Access MVP

RE: Help with Query showing Active/Inactive Customers

Then your combo rowsource should be of the ilk:

CODE

SELECT CustID, 
       CustName 
FROM   Customers 
WHERE  Inactive = False
UNION
SELECT CustID, 
       CustName 
FROM   Customers 
WHERE  CustID = {formname}.ApplicantId
ORDER 
BY     Custname 

And, you'd have to requery the combo on each <On Current> event (because, it's content may change for each and every displayed record).

Although this setup seems strange - can the record 'customer' be changed at any time - to another customer via this combo?
If not - what DOES this combo signify - in the record?
Is the combo Customer the 'Applicant'?

It sounds (at the moment) like your business logic / the use of this combo is incorrect.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

RE: Help with Query showing Active/Inactive Customers

CODE -->

WHERE  CustID = {formname}.ApplicantId 
That syntax is incorrect when used in a query. You have to use bang notation and fully qualify the form.

Forms!FormName.ApplicantID

RE: Help with Query showing Active/Inactive Customers

(OP)
Thanks you all for the help.

Quote (dhookom)


Can you explain "UNLESS that customer is selected as the applicant on the current file"? Is this the "current record"?
Yes, this is the current Record.
Is this form continuous or single view?
This form is single

I was able to fix my issue with the following query:

CODE --> vba

Me.CustID.RowSource = "SELECT CUSTOMERS.CUSTID, CUSTOMERS.Name, From Customers where inactive=0 or CUSTOMERS.CUSTID=" & Me.CustID 

The Me.CustID is the ID of for the current record being displayed.

Thanks

RE: Help with Query showing Active/Inactive Customers

Majp

Oh dear, there IS no {} coding convention for VBA, in this context - wasn't it obvious that this was pseudo-codish?

I was expecting common sense to be applied and {formname} to be interpreted as:

{formname} = Forms!FormName

(Please don't now question my 'made-up' word of 'codish')


*Sigh*

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close