Access SQl

[Mark O'Brien]

Ok, I think I've got to grips with relational databases. I can understand SQL or at least can work out what I want to do and how I'm going to do it.

I have a question about queries though, since I am not skilled in Access.

I'll present a trivial example.

Suppose, for example, I have a combobox on a VBA userform and I want the combobox to contain the result of a query e.g.:

SELECT DISTINCT tblPlantUnit.PLANT
FROM tblPlantUnit;

Now, this query is contained "Row Source" property of the combobox itself, it is not a separate query.

What I would like an opinion on is if I should make this a separate query? e.g. put it into a query called qryListPlants.

I do not think that I will need to use this query in any other control, which would be the obvious advantage of creating a separate query.

Would I gain any advantage by explicitly creating all of my queries in this manner? I would imagine it would be easier to modify and maintain my DB at a later stage if I didn't have to go hunting in all of my userforms for these queries.

Thanks

PS. I'm not really "with it" on the Access speak, so if anyone can correct my terminology I'd appreciate it.

EDIT:: Also, would another advantage for keeping my queries all in one place would be if I want to execute some queries from VBA? e.g. suppose on my combobox above, I want to populate another combobox based on the text selected in the first combobox.

 

[Russell Hauf]

Hi Mark,

I have found that I like to save the queries behind combo/list boxes only if they are filtering on something. So in your example, if you are just wanting every PLANT, then personally I would not save this as a query. However if I wanted all plants in the UK (for example), then I would save it. This is just personal preference after years of trial and error.

As for the last bit of your post, you don't always need VBA to limit the contents of a combo/list box, so I would have to say no to that specific case. But again, if you have a query that filters a table (or another query), and you could use it somewhere in your code, then obviously it would be easier to refer to the query name than typing the SQL in your code.

HTH,

Russell

 

[Mark O'Brien]

Thanks for the input Russell. I appreciate it. I think I'll follow your rule of thumb.

As a follow up to the second part. I'll use a trivial example again.
Suppose I had a query called qryListUnits that looked like this:

SELECT [UNIT NUMBER]
FROM tblPlantUnit
WHERE PLANT=[];

What is the syntax in vba for passing the PLANT argument to the query from VBA, I've got this far:

Me.cboUnit.RowSource = "qryListUnits" 'how do I pass an argument?

Thanks.

(again this is a trivial example and it's more for my reference only right now.)

 

[Russell Hauf]

In the case you have presented, it would actually be better to use the SQL in the code instead of basing the combo box off of a parameter query.

Me.cboUnit.RowSource = "SELECT [UNIT NUMBER] FROM tblPlantUnit WHERE PLANT=" & varSomething

or

Me.cboUnit.RowSource = "SELECT [UNIT NUMBER] FROM tblPlantUnit WHERE PLANT='" & varSomething & "'"

(where the first case PLANT is numeric and the second, text).

However, you could also put something like:

SELECT [UNIT NUMBER]
FROM tblPlantUnit
WHERE PLANT=Forms!frmMain!cboPlants

as your query, and then maybe put in a Me.cboUnitNums.Requery in the change or update event of cboPlants...if that makes sense. Of course, this query would only work while your form (frmMain) is open.

Clear as mud?

 

[Mark O'Brien]

Thanks again.

I was missing the single quotes in the first syntax and the "Forms!" part of the second. I hate Access.