Help with Query showing Active/Inactive Customers

[egstatus]

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

 

[Andrzejek]

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.

 

[dhookom]

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

 

[Darrylles]

Then your combo rowsource should be of the ilk:

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.

 

[MajP]

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

 

[egstatus]

Thanks you all for the help.

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:

 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

 

[Darrylles]

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.