SQl and VBA

[carl6885]

Hi

I have a table for logging errors, in this table I have two fields, TeamCaused1, TeamCaused2 and I have the individuals name.

I need to be able to pull all the errors that the user has logged as well all those caused by the team the individual belongs to. For example: All breaches logged by Carl Law who belongs to the Support team. I also need to pull in all the errors assigned to that team.

Once I have this information it needs to populate a listbox.

I initially thought this should be a recordset however I am unsure how you would write the SQL to populate it to then pull into the listbox.

Only other thing I thought of was a simple select statement to get the errors logged by the individual, another that pulls the ones caused by the same team and add these to an array/collection and then populate the listbox???

However I have no idea how to do this.

It would be great if someone could point me in the right direction with maybe an example?

Thanks

Carl

 

[spikepl]

A listbox has a rowsource. The rowsource can be a saved query.

Go to the query designer, and there make each query you want and save it.

The query designer also has an SQL view. There you can see what the SQL of the query you just made in the design grid looks like. Useful for copying and pasting into code, if you ever get to a point where that is needed (not in the current case, as far as I can see)

 

[carl6885]

Hi - thanks for the reply but this does not answer my query....

I know the listbox has a rowsource - it is the SQL to populate it that I am having issues....

I need the one list box to show:

* Errors logged by the user looking.
* Errors logged against the team the user belongs to

I know I can do these seperately however it would be good if one statement would show them all.... just not sure how.

Thanks

Carl

 

[Privateer]

Carl,
Using the employee table you pull all the Employee IDs in column0, the name in column1 and the DepartmentID in column2 as the row source for the Breachercombo box. When you select Carl Law, you get both IDs and you use them as criteria for two list boxes. If you want one list box, the criteria would be "WHERE (([EmployeeID]=" & BreacherCombo.value & ") OR ([DepartmentID]="& BreacherCombo.column2 & "));" The key here is the OR value, you will get all of Carl's breaches and all of the Support department breaches. Hope that helps.
Privateer

 

[carl6885]

Hi

Thanks for your response - I had worked out how to do it with trial and error but it is slightly different to the answer above however I will add this to my examples as this might work better than my version.

Thanks

Carl