Re: Not connecting to records

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

Hello, Me again...

I'm sorry but I tried this with no success. It just crashed on me or I
would have sent my code. I am confused and hope you might be able to help me
clarify this. I'm confused by the account number references -- no doubt.

This is what you said....

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

===============================================

Does "AcctNo" refer to the account that matches the CUST_CUST_ID field. It
is a field on my form that right now pulls the records.

OR does "AcctNo" refer to the field of the records that I want to pull?

Are you using "AcctNo" and "Acct_no" as two different fields?

Again, I am after this pull...(Price List Two (based upon a price list
field) for 1,2 and 3.

1 2
2 2
3 2

and not this one...(which I am now getting that matches the Cust_Cust_ID).
Whoops after proofing...let me clarify even a little more. My Price List
Fields all have "2" in them for each of account numbers 1, 2 and 3. But it
is matching the Customer ID, which is giving me the result below.

1 1
2 2
3 3

I'm just trying to be sure I am clear on what I want since I know my
terminology is a little difficult to follow.

And could I ask one more favor which may helpl me in the future from an
understanding perspective.

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

Would you mind translating this into understandable sentences?

Thanks again, and I can't wait to hear back from you. This will be sooooo
slick and will save us so much heartache if I can get it doing what I need
to.



Have a great weekend and thanks ever so much for your help! Cindy


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




"Michel Walsh" wrote:

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
    ... equal to the value actually in the control AcctNo. ... OR does "AcctNo" refer to the field of the records that I want to pull? ... is matching the Customer ID, which is giving me the result below. ...
    (microsoft.public.access.forms)
  • Re: Not connecting to records
    ... I have several account numbers from which I want to use the same price ... what it does is pull the special pricing based upon the Cust_Cust_ID ... Acct No Product Price List Acct Number ... In the "customer number" after update event, ...
    (microsoft.public.access.forms)
  • 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: Perl script to clean up file -- Dont know if it can be done
    ... > This is the problem I get a file that I must pull the pertanent data out. ... I need to pull out just the columns. ... Where did the extra CUSTOMER HAS line come from? ...
    (comp.lang.perl.misc)
  • 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)