Query vs. VB

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

How can I convert a query into a VB statement?
I have a query that looks into a specific field in all the
records and returns all the customers that contains that
specific criteria.

The SQL statement would be:

SELECT CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
FROM CustomerDB
WHERE (((CustomerDB.SubscriptionCode1)="O1025")) OR
(((CustomerDB.SubscriptionCode2)="O1025")) OR
(((CustomerDB.SubscriptionCode3)="O1025")) OR
(((CustomerDB.SubscriptionCode4)="O1025")) OR
(((CustomerDB.SubscriptionCode5)="O1025")) OR
(((CustomerDB.SubscriptionCode6)="O1025")) OR
(((CustomerDB.SubscriptionCode7)="O1025")) OR
(((CustomerDB.SubscriptionCode8)="O1025")) OR
(((CustomerDB.SubscriptionCode9)="O1025")) OR
(((CustomerDB.SubscriptionCode10)="O1025")) OR
(((CustomerDB.SubscriptionCode11)="O1025")) OR
(((CustomerDB.SubscriptionCode12)="O1025")) OR
(((CustomerDB.SubscriptionCode13)="O1025")) OR
(((CustomerDB.SubscriptionCode14)="O1025")) OR
(((CustomerDB.SubscriptionCode15)="O1025")) OR
(((CustomerDB.SubscriptionCode16)="O1025")) OR
(((CustomerDB.SubscriptionCode17)="O1025")) OR
(((CustomerDB.SubscriptionCode18)="O1025")) OR
(((CustomerDB.SubscriptionCode19)="O1025")) OR
(((CustomerDB.SubscriptionCode20)="O1025")) OR
(((CustomerDB.SubscriptionCode21)="O1025")) OR
(((CustomerDB.SubscriptionCode22)="O1025")) OR
(((CustomerDB.SubscriptionCode23)="O1025")) OR
(((CustomerDB.SubscriptionCode24)="O1025")) OR
(((CustomerDB.SubscriptionCode25)="O1025")) OR
(((CustomerDB.SubscriptionCode26)="O1025")) OR
(((CustomerDB.SubscriptionCode27)="O1025")) OR
(((CustomerDB.SubscriptionCode28)="O1025")) OR
(((CustomerDB.SubscriptionCode29)="O1025")) OR
(((CustomerDB.SubscriptionCode30)="O1025")) OR
(((CustomerDB.SubscriptionCode31)="O1025")) OR
(((CustomerDB.SubscriptionCode32)="O1025")) OR
(((CustomerDB.SubscriptionCode33)="O1025")) OR
(((CustomerDB.SubscriptionCode34)="O1025"))
GROUP BY CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
HAVING (((CustomerDB.CompanyName) Is Not Null))
ORDER BY CustomerDB.CompanyName;
 
John,

thanks for your reply.

Here is what I am trying to do:

The query below is used to find out how many customers
subscribe to the product (O1025). So, the query searches
in the txtSubCode textbox through all the records.

Another form opens, which contains a subform that looks up
to the corresponding query and displays the results.

I have way too many specific queries and subforms that
look for the corresponding queries which I would like to
eliminate, allowing for a more generic search.

Hope this is clear enough.

Antonio
-----Original Message-----
Antonio,

You can't just make a "VB Statement" out of SQL - you can
run SQL from VB (docmd.runSQL - or the ADO equivalent)
however this is only really useful if your SQL does
something - yours is just a select statement.

You can use it with ADO to make a new recordset and
search through it and populate (e.g.) a list box, or you
may just want to populate a form with the results of
it... in which case, you could just put your SQL in the
recordsource of the form, or you could apply the record
source from VB (Form_frmMyForm.Recordsource = "SELECT
etc")

I think if you want any further help, you need to tell us
exactly what you want to do - using VB might not even be
your answer.

Whatever the answer is - if you don't mind me saying so
it looks like you may be getting in to a rather sticky
mess judging by the SQL statement!

John C

-----Original Message-----
How can I convert a query into a VB statement?
I have a query that looks into a specific field in all the
records and returns all the customers that contains that
specific criteria.

The SQL statement would be:

SELECT CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
FROM CustomerDB
WHERE (((CustomerDB.SubscriptionCode1)="O1025")) OR
(((CustomerDB.SubscriptionCode2)="O1025")) OR
(((CustomerDB.SubscriptionCode3)="O1025")) OR
(((CustomerDB.SubscriptionCode4)="O1025")) OR
(((CustomerDB.SubscriptionCode5)="O1025")) OR
(((CustomerDB.SubscriptionCode6)="O1025")) OR
(((CustomerDB.SubscriptionCode7)="O1025")) OR
(((CustomerDB.SubscriptionCode8)="O1025")) OR
(((CustomerDB.SubscriptionCode9)="O1025")) OR
(((CustomerDB.SubscriptionCode10)="O1025")) OR
(((CustomerDB.SubscriptionCode11)="O1025")) OR
(((CustomerDB.SubscriptionCode12)="O1025")) OR
(((CustomerDB.SubscriptionCode13)="O1025")) OR
(((CustomerDB.SubscriptionCode14)="O1025")) OR
(((CustomerDB.SubscriptionCode15)="O1025")) OR
(((CustomerDB.SubscriptionCode16)="O1025")) OR
(((CustomerDB.SubscriptionCode17)="O1025")) OR
(((CustomerDB.SubscriptionCode18)="O1025")) OR
(((CustomerDB.SubscriptionCode19)="O1025")) OR
(((CustomerDB.SubscriptionCode20)="O1025")) OR
(((CustomerDB.SubscriptionCode21)="O1025")) OR
(((CustomerDB.SubscriptionCode22)="O1025")) OR
(((CustomerDB.SubscriptionCode23)="O1025")) OR
(((CustomerDB.SubscriptionCode24)="O1025")) OR
(((CustomerDB.SubscriptionCode25)="O1025")) OR
(((CustomerDB.SubscriptionCode26)="O1025")) OR
(((CustomerDB.SubscriptionCode27)="O1025")) OR
(((CustomerDB.SubscriptionCode28)="O1025")) OR
(((CustomerDB.SubscriptionCode29)="O1025")) OR
(((CustomerDB.SubscriptionCode30)="O1025")) OR
(((CustomerDB.SubscriptionCode31)="O1025")) OR
(((CustomerDB.SubscriptionCode32)="O1025")) OR
(((CustomerDB.SubscriptionCode33)="O1025")) OR
(((CustomerDB.SubscriptionCode34)="O1025"))
GROUP BY CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
HAVING (((CustomerDB.CompanyName) Is Not Null))
ORDER BY CustomerDB.CompanyName;
Click to expand...
Click to expand...
 
Antonio,

Like Sandra says - you need to have two different tables
with a one to many relationship.

You need a customers table to hold customer information,
and a unique ID (autonumber) for each one, and another
table for subscriptions.

You then need a Customer ID field in the subs table so
that you can link customers with subscriptions. This
needs to be a one to many relationship - then you can
have as many or few subscriptions as you want for each
customer. Having 34 combo boxes is a waste of space and
in some scenarios may actually be restrictive!

Don't worry about SQL for the moment - concentrate on
relationships and build your database correctly. This is
essential if it's to be of any use in the future.

John.

-----Original Message-----
John,

thanks for your reply.

Here is what I am trying to do:

The query below is used to find out how many customers
subscribe to the product (O1025). So, the query searches
in the txtSubCode textbox through all the records.

Another form opens, which contains a subform that looks up
to the corresponding query and displays the results.

I have way too many specific queries and subforms that
look for the corresponding queries which I would like to
eliminate, allowing for a more generic search.

Hope this is clear enough.

Antonio
-----Original Message-----
Antonio,

You can't just make a "VB Statement" out of SQL - you can
run SQL from VB (docmd.runSQL - or the ADO equivalent)
however this is only really useful if your SQL does
something - yours is just a select statement.

You can use it with ADO to make a new recordset and
search through it and populate (e.g.) a list box, or you
may just want to populate a form with the results of
it... in which case, you could just put your SQL in the
recordsource of the form, or you could apply the record
source from VB (Form_frmMyForm.Recordsource = "SELECT
etc")

I think if you want any further help, you need to tell us
exactly what you want to do - using VB might not even be
your answer.

Whatever the answer is - if you don't mind me saying so
it looks like you may be getting in to a rather sticky
mess judging by the SQL statement!

John C

-----Original Message-----
How can I convert a query into a VB statement?
I have a query that looks into a specific field in all the
records and returns all the customers that contains that
specific criteria.

The SQL statement would be:

SELECT CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
FROM CustomerDB
WHERE (((CustomerDB.SubscriptionCode1)="O1025")) OR
(((CustomerDB.SubscriptionCode2)="O1025")) OR
(((CustomerDB.SubscriptionCode3)="O1025")) OR
(((CustomerDB.SubscriptionCode4)="O1025")) OR
(((CustomerDB.SubscriptionCode5)="O1025")) OR
(((CustomerDB.SubscriptionCode6)="O1025")) OR
(((CustomerDB.SubscriptionCode7)="O1025")) OR
(((CustomerDB.SubscriptionCode8)="O1025")) OR
(((CustomerDB.SubscriptionCode9)="O1025")) OR
(((CustomerDB.SubscriptionCode10)="O1025")) OR
(((CustomerDB.SubscriptionCode11)="O1025")) OR
(((CustomerDB.SubscriptionCode12)="O1025")) OR
(((CustomerDB.SubscriptionCode13)="O1025")) OR
(((CustomerDB.SubscriptionCode14)="O1025")) OR
(((CustomerDB.SubscriptionCode15)="O1025")) OR
(((CustomerDB.SubscriptionCode16)="O1025")) OR
(((CustomerDB.SubscriptionCode17)="O1025")) OR
(((CustomerDB.SubscriptionCode18)="O1025")) OR
(((CustomerDB.SubscriptionCode19)="O1025")) OR
(((CustomerDB.SubscriptionCode20)="O1025")) OR
(((CustomerDB.SubscriptionCode21)="O1025")) OR
(((CustomerDB.SubscriptionCode22)="O1025")) OR
(((CustomerDB.SubscriptionCode23)="O1025")) OR
(((CustomerDB.SubscriptionCode24)="O1025")) OR
(((CustomerDB.SubscriptionCode25)="O1025")) OR
(((CustomerDB.SubscriptionCode26)="O1025")) OR
(((CustomerDB.SubscriptionCode27)="O1025")) OR
(((CustomerDB.SubscriptionCode28)="O1025")) OR
(((CustomerDB.SubscriptionCode29)="O1025")) OR
(((CustomerDB.SubscriptionCode30)="O1025")) OR
(((CustomerDB.SubscriptionCode31)="O1025")) OR
(((CustomerDB.SubscriptionCode32)="O1025")) OR
(((CustomerDB.SubscriptionCode33)="O1025")) OR
(((CustomerDB.SubscriptionCode34)="O1025"))
GROUP BY CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
HAVING (((CustomerDB.CompanyName) Is Not Null))
ORDER BY CustomerDB.CompanyName;
Click to expand...
Click to expand...

.
Click to expand...
 
John,

Most of the customers subscribe to 10+ products. I am
using the combo boxes to select the product and to display
the subscription.

If I am not using these combo boxes, I have to re-design
the whole tabbed page and at this moment I can only think
about One combobox to select the subscription and One
listbox to display the subscription?

Antonio
-----Original Message-----
Antonio,

Like Sandra says - you need to have two different tables
with a one to many relationship.

You need a customers table to hold customer information,
and a unique ID (autonumber) for each one, and another
table for subscriptions.

You then need a Customer ID field in the subs table so
that you can link customers with subscriptions. This
needs to be a one to many relationship - then you can
have as many or few subscriptions as you want for each
customer. Having 34 combo boxes is a waste of space and
in some scenarios may actually be restrictive!

Don't worry about SQL for the moment - concentrate on
relationships and build your database correctly. This is
essential if it's to be of any use in the future.

John.

-----Original Message-----
John,

thanks for your reply.

Here is what I am trying to do:

The query below is used to find out how many customers
subscribe to the product (O1025). So, the query searches
in the txtSubCode textbox through all the records.

Another form opens, which contains a subform that looks up
to the corresponding query and displays the results.

I have way too many specific queries and subforms that
look for the corresponding queries which I would like to
eliminate, allowing for a more generic search.

Hope this is clear enough.

Antonio
-----Original Message-----
Antonio,

You can't just make a "VB Statement" out of SQL - you can
run SQL from VB (docmd.runSQL - or the ADO equivalent)
however this is only really useful if your SQL does
something - yours is just a select statement.

You can use it with ADO to make a new recordset and
search through it and populate (e.g.) a list box, or you
may just want to populate a form with the results of
it... in which case, you could just put your SQL in the
recordsource of the form, or you could apply the record
source from VB (Form_frmMyForm.Recordsource = "SELECT
etc")

I think if you want any further help, you need to tell us
exactly what you want to do - using VB might not even be
your answer.

Whatever the answer is - if you don't mind me saying so
it looks like you may be getting in to a rather sticky
mess judging by the SQL statement!

John C


-----Original Message-----
How can I convert a query into a VB statement?
I have a query that looks into a specific field in all
the
records and returns all the customers that contains that
specific criteria.

The SQL statement would be:

SELECT CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
FROM CustomerDB
WHERE (((CustomerDB.SubscriptionCode1)="O1025")) OR
(((CustomerDB.SubscriptionCode2)="O1025")) OR
(((CustomerDB.SubscriptionCode3)="O1025")) OR
(((CustomerDB.SubscriptionCode4)="O1025")) OR
(((CustomerDB.SubscriptionCode5)="O1025")) OR
(((CustomerDB.SubscriptionCode6)="O1025")) OR
(((CustomerDB.SubscriptionCode7)="O1025")) OR
(((CustomerDB.SubscriptionCode8)="O1025")) OR
(((CustomerDB.SubscriptionCode9)="O1025")) OR
(((CustomerDB.SubscriptionCode10)="O1025")) OR
(((CustomerDB.SubscriptionCode11)="O1025")) OR
(((CustomerDB.SubscriptionCode12)="O1025")) OR
(((CustomerDB.SubscriptionCode13)="O1025")) OR
(((CustomerDB.SubscriptionCode14)="O1025")) OR
(((CustomerDB.SubscriptionCode15)="O1025")) OR
(((CustomerDB.SubscriptionCode16)="O1025")) OR
(((CustomerDB.SubscriptionCode17)="O1025")) OR
(((CustomerDB.SubscriptionCode18)="O1025")) OR
(((CustomerDB.SubscriptionCode19)="O1025")) OR
(((CustomerDB.SubscriptionCode20)="O1025")) OR
(((CustomerDB.SubscriptionCode21)="O1025")) OR
(((CustomerDB.SubscriptionCode22)="O1025")) OR
(((CustomerDB.SubscriptionCode23)="O1025")) OR
(((CustomerDB.SubscriptionCode24)="O1025")) OR
(((CustomerDB.SubscriptionCode25)="O1025")) OR
(((CustomerDB.SubscriptionCode26)="O1025")) OR
(((CustomerDB.SubscriptionCode27)="O1025")) OR
(((CustomerDB.SubscriptionCode28)="O1025")) OR
(((CustomerDB.SubscriptionCode29)="O1025")) OR
(((CustomerDB.SubscriptionCode30)="O1025")) OR
(((CustomerDB.SubscriptionCode31)="O1025")) OR
(((CustomerDB.SubscriptionCode32)="O1025")) OR
(((CustomerDB.SubscriptionCode33)="O1025")) OR
(((CustomerDB.SubscriptionCode34)="O1025"))
GROUP BY CustomerDB.CompanyName, CustomerDB.ContactName,
CustomerDB.City, CustomerDB.State, CustomerDB.Phone,
CustomerDB.[E-mail Address]
HAVING (((CustomerDB.CompanyName) Is Not Null))
ORDER BY CustomerDB.CompanyName;
Click to expand...

.
Click to expand...
.
Click to expand...
 
Antonio said:
Sandra,

Thank you for your reply.....

The reason I have 34 Subscription combo boxes is because
most of our customer subscribe to more than 10 products.
Click to expand...

If you followed Sandra's advice you would have two tables and this would allow for an
endless number of Subscriptions per Customer and when new Subscriptions types came
along it would not require any design changes.

Think of it like an Order Entry app. One main record for the header and many
sub-records for the items ordered and quantities. You wouldn't set up 20 columns in
the Order table for "items ordered" you would use a separate table so that you could
have as many (or as few) items per order as required.
 
Thank you all for your advice. I am already re-designing
the forms. I am creating only one combobox to select a
product, one listbox to display the selected product. The
combobox will be cleared after a selection has been made.
I already created another table (CustomersSubscriptions)
which includes a SubscriptionID and a SubscriptionTitle.

I am going to play a little with this. Should you have
other advices, I would be very grateful.

Thanks again,


Antonio
-----Original Message-----


If you followed Sandra's advice you would have two tables and this would allow for an
endless number of Subscriptions per Customer and when new Subscriptions types came
along it would not require any design changes.

Think of it like an Order Entry app. One main record for the header and many
sub-records for the items ordered and quantities. You wouldn't set up 20 columns in
the Order table for "items ordered" you would use a
Click to expand...
separate table so that you could
 
Antonio said:
Thank you all for your advice. I am already re-designing
the forms. I am creating only one combobox to select a
product, one listbox to display the selected product. The
combobox will be cleared after a selection has been made.
I already created another table (CustomersSubscriptions)
which includes a SubscriptionID and a SubscriptionTitle.

I am going to play a little with this. Should you have
other advices, I would be very grateful.

Thanks again,
Click to expand...

You should redesign and in any event running a query will always be faster
than looping through a recordset in code, then trying to find out how to
deal with it.
 
Back
Top