combo box, control source, and sql statements

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

Guest

Hi all.
I am trying to create a simple invoice database with MS Access (2003).
I think I have an adequate knowledge of SQL to finish this project. And I
did use Visual Basic before to create a mySQL-based database.
But the problem is with combo box, I have a hard time executing SQL
statements.
I have a table called products and it has fields such as Name, Price,
Category and so on. Now I want my combo box to choose products by their names
so I set the row source of cboName to something like this:

=SELECT [products].Name FROM [products];

And it does exactly what I want. Now I also want a text box named tbPrice to
show the price of the selected products automatically when I choose a product
in the combo box. This is where I am having a problem. I set the control
source of tbPrice to something like this and I get a syntax error:

=SELECT [products].Price FROM [products] WHERE ([products].Name =
[Forms].[Order].[cboName]);

After searching for answers everywhere, I did learn that there was a way
around to solve this problem. I could make the combo box to have 2 columns so
it has Name and Price fields attached- thus getting the price from the column
(=[cboName].[Column](1)). Or I could use a function called DLookup.
But I don't really like these methods- I want to use SQL to access to the
database. I wouldn't mind having to code in Visual Basic with code builder,
but I don't know how to access to this Access database with Visual Basic -
and I can't find that information anywhere (maybe I'm not googling the right
way).

So my questions are these:

Could I use an SQL statement with the control source property? If so, how?

How do you manipulate records in Access database with Visual Basic? Maybe a
website address that gives plenty of information on that one?

Thank you very much for being patient and reading this; even if you don't
have a 100% answer, I would still appreciate an input from all of you.
 
Embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[snip]
So my questions are these:

Could I use an SQL statement with the control source property? If so, how?
Click to expand...

No. The Control Source is a field name based on the fields available in the
Record Source of the form or an expression based on those fields. It cannot
be a SQL statement.
How do you manipulate records in Access database with Visual Basic? Maybe
a
website address that gives plenty of information on that one?
Click to expand...

Yes. You can build your own VBA function to OpenRecordset and retrieve the
desired value. Of course, that's exactly what DLookup() does for you, but if
you want to learn how to achieve the same result, see:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
 
Back
Top