Re: Not connecting to records

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

Hi,


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


means: if the control, on the form that owns that code, AcctNo has
something in it (if the length of what it contains is not zero), then add
the criteria about the field (in the table) Acct_No, which we want as to be
equal to the value actually in the control AcctNo (of the form).


Before using the string strSQL, debug - print it:



Debug.Print strSQL



and take a look at it, in the Debug Immediate Window (Ctrl_G). Are there
spaces around the values and the keywords, and the fields name? There must
be at least one space between a value and a keyword. You can also cut and
paste what contains the strSQL variable, now printed in the immediate debug
window, into a new query, in its SQL view, to further see if it works fine.



Hoping it may help,
Vanderghast, Access MVP

"ztyco" <ztyco@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:26025259-1B83-4159-882D-EC67E35B81D4@xxxxxxxxxxxxxxxx
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: Audio / Video Distribution
    ... > I am in the process of putting all my audio and video equiptment into one ... > room of my house and distribute throughout the house from there. ... > control or wired control? ... The most common way to install such systems is to pull CAT5 to where you ...
    (comp.home.automation)
  • 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'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: 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: Circuit Design Thoghts wanted
    ... > I'm looking for a few suggestions for a circuit to control a hydraulic ... > My main concern lies with the limit switches. ... Pull up vs pull down probably won't make much difference at low speeds ...
    (sci.electronics.design)