How do I pass the results of a select query to a VBA module and then to variables?

[Phred]

Access 2007 and SQL Server 2008 R2.

I have a query that runs fine. I would like to pass the results to VBA variables. Then I will write an update query in VB and update the Property table and then run a requery to update the screen.

SELECT dbo_TownshipLookup.TownshipName, dbo_TownshipLookup.TownshipNO,dbo_CountyLookup.Cou ntyName, dbo_TriCycleLookup.CycleName, dbo_VolumeLookup.Volume
FROM ((dbo_TownshipLookup INNER JOIN dbo_TriCycleLookup ONdbo_TownshipLookup.TricountyID = dbo_TriCycleLookup.TricountyID) INNER JOINdbo_CountyLookup ON dbo_TriCycleLookup.CountyID = dbo_CountyLookup.CountyID)INNER JOIN dbo_VolumeLookup ON dbo_TownshipLookup.TownshipName =dbo_VolumeLookup.Township;

The query runs fine and returns the proper results. What I don't know is how to pass the results of the query to the VBA module. I am ok with the syntax and variables involved, I just don't know how to create the methodology to capture the query results and place them into appropriate variables. I think I create a record set for the results but I'm not really sure.

Thanks


Phred

[June7]

Yes, open a recordset object.

Or build an UPDATE query object.

What do you need to update?

[Phred]

I have a Property screen with a combo box. The combo box is based on the query above and returns the Township Name as well as several other fields. See Pic 1.




When the person selects the Township it places the township in the combo box. See Pic 2.



But the query that is behind the combo box also contains data that could be written in the additional fields
Township No:
County:
Triennial Cycle:
Volume:

I need to do one of two things:

Find a way to have the combo box write the additional fields of information from the Query behind the combo box to the fields below the combo box. I've never seen a combo box write to additional fields like that. I'm not sure it's even possible.

Or

Use VB to pull the data from the combo box query into VB and have it update the table behind the Property screen and then requery the Property screen to update the data.

I have to get all the data to assist the end user select the correct township. I might as well use it to populate the additional fields below automatically.

Hope that makes sense.

Thanks,

Phred

[June7]

The other textboxes can have expression in ControlSource that references columns of combobox. Column index begins with 0. So if County is in column 2 it is index 1.

=[cbxTownship].[Column](1)

This will display the related info but will not save to table. However, should probably not save this related data anyway. That would be duplication of data.