Not connecting to records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
:

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

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!
Click to expand...
Click to expand...
Click to expand...
 
Hello, Michel,

Well, still no luck. I'll copy my code below .. jus tin case you can spot
something.

With the below code, it does not do the filllist.

If I remove the conditions you've suggested , it pull data associated with
the "CUST_CUST_ID" and not the 'PRIMARYPRICELISTACCOUNT".

If I flip-flop the two fields in discussion here, bit then pulls from a
totally different customer.

Also, something I saw that was strange, but at some point, it said that it
could not find PrimaryPriceListAccount. I searched several times and checked
the table number and it is there.

One other thing...should this be a factor. This is a form based upon a
table that is different than the information it locates for the filllist.

I'm trying here, but still love. I'm so sorry.

Private Sub FillList()

Dim strSQL As String
Dim strCustomerNumber As String

strCustomerNumber = Me.txtCustNumber.Value

strSQL = "SELECT tblSpecPrcModelFamily.[Model Family],
tblSpecPrcModelFamily.Multiplier "
strSQL = strSQL & "FROM tblSpecPrcModelFamily "
strSQL = strSQL & "WHERE (((tblSpecPrcModelFamily.[Customer Number]) =
'" & strCustomerNumber & "'))"
strSQL = strSQL & "ORDER BY tblSpecPrcModelFamily.[Model Family];"



strSQL = "SELECT [Cash Part No], [Set Pressure], [Cust Part No], [Model
Family], [Inlet Connection Size], "
strSQL = strSQL & "Service, [Special Requirement], Price, ListPrice,
[Single Model Multiplier], [Commission Rate], [Special Notes] "
strSQL = strSQL & "FROM [Query - Special Pricing Main & Individual] "
strSQL = strSQL & "WHERE (Cust_Cust_ID = '" & strCustomerNumber & "')"

If (0 <> Len(CUST_CUST_ID & vbNullString)) Then
strSQL = strSQL & "AND PrimaryPriceListAccount = " & CUST_CUST_ID
End If

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

Me!lstIndividualParts.RowSource = strSQL
Me!lstIndividualParts.Requery

End Sub
 
Hi,



You should add a space before the O of ORDER BY. If you debug.Print the
strSQL string, just before using it in

Me!lstIndividualParts.RowSource = strSQL


you could have spotted:


SELECT ... WHERE ... AND PrimaryPriceListAccount = 1234ORDER BY [Cash Part
No];

(assuming 1234=CUST_CUST_ID)

So, try (note the extra spaces before the O ):

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


and now, you will get (at least) a space each side of the keyword ORDER. You
can also add a space before the A of AND,

strSQL = strSQL & " AND PrimaryPriceListAccount = " & CUST_CUST_ID


but there, less of a problem, since the preceding character is a close
parenthesis.



Your first four lines with strSQL built a statement you don't seem to use.
Is that ok?



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top