Replacing Normal Queries With VBA Code & SQL

[marky_dp]

Hi all,

In some of my forms i would like things such as combo boxes to retrieve their values using VBA code rather than them just being based upon an access query. My aim is replace all my access queries with just VBA. So far I am able to retrive values from the DB and put them into text boxes but am unsure of how i should i apply this to a combo box.

Say for example I wanted to select all the employees from my employee table and display them all in a combo box for the user to choose whichever one he/she wanted, would i go about it by doing this:

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySQL As String

mySQL = "SELECT EmployeeID, EmployeeFirstName, EmployeeLastName"
mySQL = mySQL + " FROM EmployeeTBL"

myRecordSet.Open mySQL

After this though I'm pretty unsure as to how i'd get the values into a combo box. If any one could help me here it would be very much appreciated,

Cheers

 

[llkhoutx]

Don't use the method you are proposing, merely use the SQL string as the row source for your combo box, i.e.

Select EmployeeID, EmployeeLastName & ", " & EmployeeFirstName AS EmployeeName FROM EmployeeTBL ORDER BY EmployeeName

with

Column Count as 2
Bound Column as 1
Column Width as 0;1.5

This will display sorted employee names, last name first.

 

[Bat17]

Why do you want to get rid of your queries?

Peter

 

[Summerwind]

Select EmployeeID, EmployeeLastName & ", " & EmployeeFirstName AS EmployeeName FROM EmployeeTBL ORDER BY EmployeeName

with

Column Count as 2
Bound Column as 1
Column Width as 0;1.5

This will display sorted employee names, last name first.

Might be some code missing here. Try:

strSource = "Select EmployeeID, EmployeeLastName & ", " & EmployeeFirstName AS EmployeeName FROM EmployeeTBL ORDER BY EmployeeName"

MyCombo.RowSource = strSource

You can usually set the column details at design time.

I very rarely, if ever, use queries for sourcing combos, list boxes etc. Just clutters the place up and you still have to code the parameters in may cases. I think that the SQL will run quicker too, but that's just my theory

 

[Bat17]

I think in older verions queries ran faster because they were saved optimised, I seem to recall that in newerversions Access also optimoses Sql in forms but I would not swear to it!
Queries have the advantage in a secured database that you can use them "with owners permissions"

Peter

 

[Summerwind]

Why would you ever do this? Do you think you can do it better?

Let me ask you a question - How many comboboxes would you expect in a medium sized, user-friendly application?

If they all have a separate query to feed them, that equates to quite a lot (In my case), if not sheds full.

Writing a couple of lines of SQL code makes the App a lot smaller and whatever you say, I cannot belive that the micro-seconds of speed difference, even if there is one?????, makes not a scrap of difference the the user's perception.

 

[Summerwind]

So tell me then Pat, what is the difference between writing the SQL string in the row source of the combo and writing it in code?

I'll tell you - When its written in code, its easier to see for debugging purposes and easier to apply parameters.

RE your PS (Hey, Hey - I'm talking in code), I've seen the arguments relating to DAO v ADO. Having gone to all of the effort and trouble of converting to the latter and knowing that I need the latter to write code for SQL server back ends, I'll stick with ADO if its all the same to you.

My brain hurts now!