jamescol
05-31-2004, 09:41 PM
Here's another challenge I must tackle in my OL2K3 app. :dunno I need to obtain a result set from a SQL Server 2000 DB and use it to populate some of the custom form fields.
Is it possible to do this using VBA? :confused: I have searched Microsoft's support site, technet, MSDN, etc. but have not located a definitive answer. :mad:
If such a query is possible, here is the select statement I need to run, along with the datasource and sql driver name. It is not necessary to include authentication in the call, since the SQL server uses pass-through authentication from Windows. Can someone show me the code construct to use?
'DataSource = "Warehouse"
'Driver "SQL SERVER"
'This is the query:
SELECT
vwContract.ContractNumber,
vwContract.ContractStartDate,
vwContract.ContractExpireDate,
vwContract.ContractTitle,
vwEmployee.EmployeeFirstLastName
FROM vwContract, vwEmployee
Where
[OwnerHRPersonId]= <username> ' username is supplied by another procedure
AND
vwEmployee.EmployeeId = vwContract.AdministratorEmployeeId
Order by
[ContractTitle]
I then need to place the ContractTitle results in a combo-box control with the first row as the default and populate the other form fields with the data corresponding to the first row. When the user selects a different ContractTitle form teh combo-box, I will need to update the other form fields from the corresponding data in the result set.
What would be the best way to store the result set and populate/update the fields as the user changes selections from the combo box?
Thanks,
James
Is it possible to do this using VBA? :confused: I have searched Microsoft's support site, technet, MSDN, etc. but have not located a definitive answer. :mad:
If such a query is possible, here is the select statement I need to run, along with the datasource and sql driver name. It is not necessary to include authentication in the call, since the SQL server uses pass-through authentication from Windows. Can someone show me the code construct to use?
'DataSource = "Warehouse"
'Driver "SQL SERVER"
'This is the query:
SELECT
vwContract.ContractNumber,
vwContract.ContractStartDate,
vwContract.ContractExpireDate,
vwContract.ContractTitle,
vwEmployee.EmployeeFirstLastName
FROM vwContract, vwEmployee
Where
[OwnerHRPersonId]= <username> ' username is supplied by another procedure
AND
vwEmployee.EmployeeId = vwContract.AdministratorEmployeeId
Order by
[ContractTitle]
I then need to place the ContractTitle results in a combo-box control with the first row as the default and populate the other form fields with the data corresponding to the first row. When the user selects a different ContractTitle form teh combo-box, I will need to update the other form fields from the corresponding data in the result set.
What would be the best way to store the result set and populate/update the fields as the user changes selections from the combo box?
Thanks,
James