Re: Not connecting to records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,




strSQL = "SELECT [Cash Part No], (various other fields)...[Special
Notes] "
strSQL = strSQL & "FROM [Query - Special Pricing Main & Individual] "
strSQL = strSQL & "WHERE (Cust_Cust_ID = '" & strCustomerNumber & "')"


if( 0 <> len(AcctNo & vbNullString) ) then
strSQL=strSQL & " AND [Acct_no] = " & AcctNo
end if


strSQL = strSQL & "ORDER BY [Cash Part No];"




which, basically, check to see if the there something in the control AcctNo
and if so, add the extra condition to the SQL string.


Hoping it may help,
Vanderghast, Access MVP

"ztyco" <ztyco@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86F6859A-0751-4894-9EAD-93B2F4731F09@xxxxxxxxxxxxxxxx
Thank you so much Michel, I've gotten that working. However, after going
in
circles I have another problem and I believe I've figured out I can't "get
there from here". Could I ask for your guidance on one more question
please.
I "think" I may be making this harder than it should be and I'm just
tangled
up.

First of all...what I'm trying to do.

I have several account numbers from which I want to use the same price
sheet
(another assigned account number). So, I've created a query that works
just
fine and does that for me.

However, when I try to fill the list box it pulls based upon the below
code.

strSQL = "SELECT [Cash Part No], (various other fields)...[Special
Notes] "
strSQL = strSQL & "FROM [Query - Special Pricing Main & Individual] "
strSQL = strSQL & "WHERE (Cust_Cust_ID = '" & strCustomerNumber & "')"
strSQL = strSQL & "ORDER BY [Cash Part No];"

So, what it does is pull the special pricing based upon the Cust_Cust_ID
field. However, I need it to pull from the secondary field that has the
special pricing account number in it.

For instance.
Acct No Product Price List Acct Number
1 A 2
2 B 2
3 B 2

It pulls the account number data (matches "cust_cust_ID) and I want it to
pull all data that matches Price Llist Acct Number when I select any of
Acct
No 1, 2 or 3.

I hope this makes sense.

Thank ever so much.

Cindy
"Michel Walsh" wrote:

Hi,


In the "customer number" after update event, try something like:


strSQL= "SELECT ... " ' your query that list everything

If IsNull(Me.CustomerNumber) then
' do nothing
Else
strSQL = strSQL & " WHERE
coding=FORMS!FormNameHere!CustomerNumber "
End if

Me.ListBoxName.RowSource = strSQL



where I assumed you have a field name coding that must be filtered with
the
value supplied in the control CustomerNumber. You probably have to make
some
adaptation for your particular case, but the idea is just to write an SQL
statement, as a string, and to supply it to the RowSource property of
your
listbox. Just be sure your statement, supplied as a string, is correct
(such
as having spaces between key words, etc.


Hoping it may help,
Vanderghast, Access MVP

"ztyco" <ztyco@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C804877-7136-40D8-B0FE-F143D4964162@xxxxxxxxxxxxxxxx
This is what I am trying to do....

Pull data from a query that is working beautifully -- to populate a
listbox.

When I double click a customer number and their coding is "SPC"
(special),
then I want it to populate the list box from the query with only those
records of the customer number selected. I have a customer number
field
on
the actual form and the list box in on the same form but a different
window.

Right now, what is occuring is that it pulls all records- it does not
filter
by the customer number. So, I get all special pricing for all
customers.
I
had this working in a prior version and have compared and compared and
compared and cannot seem to discern what has changed. Does anyone have
any
insight on this form?

Thank you every so much in advance!





.

Relevant Pages

  • Re: Not connecting to records
    ... I have several account numbers from which I want to use the same price sheet ... what it does is pull the special pricing based upon the Cust_Cust_ID ... Acct No Product Price List Acct Number ... When I double click a customer number and their coding is "SPC", ...
    (microsoft.public.access.forms)
  • Re: Not connecting to records
    ... I'm confused by the account number references -- no doubt. ... is matching the Customer ID, which is giving me the result below. ... what it does is pull the special pricing based upon the Cust_Cust_ID ... Acct No Product Price List Acct Number ...
    (microsoft.public.access.forms)
  • RE: Forms help for beginner
    ... You will need a customer table containing customer name, address, phone, etc. ... A database table is not at all the same as a spreadsheet row. ... Account Details ... > contact, acct due date, name of person contacted, primary phone number, ...
    (microsoft.public.access.gettingstarted)
  • Re: Subreport Problem
    ... report is sorting the account numbers. ... more if you think it's possible a customer could have more than 24 accounts. ...  I have a subreport in the detail section of the "receipt" ...
    (microsoft.public.access.reports)
  • Re: Administrator account disabled in SBS 2008 but password is now kn
    ... demonstrating to a very high value customer. ... passwords for both my user account and the Administrator account. ... The Server service is disabled. ... extension of a trial installation which expired due to non-use when there ...
    (microsoft.public.windows.server.sbs)