CODEWITHSUNDEEP
sql-serverms-accessvbams-access-2010
Mike
Mike

Reputation: 1938

How to fill a List box from a SQL query in Access?

I have a subform as part of my main form in my Access DB. All the data is in linked tables that are linked to a SQL Server DB. I am not able to figure out how to correctly fill the list box based on a query of on one of the linked tables. I thought I had it working only to find that I don't. I'm not sure if I have the code in the correct place or if I have the code even correct. Here is what I have:

Forms![DATASHEET - CAF2]![Project_Permit]!Permits!RowSource = "select pc.Value Permit from pmdb.Project_Permit pp" _
    & " left join pmdb.PicklistChild pc on pc.Id = pp.Permit " _
    & " where Project = '" & ProjectNum & "' and ProjectType = " & ProjectTypenum _
    & " and GroupID = '" & GROUPID & "';"

This is in the Form_Load() sub for the main form.

Should this be in the subform?

Or is my code wrong?

Or both?

Upvotes: 0

Views: 2061

Answers (1)

Andre
Andre

Reputation: 27644

Refer to Form and Subform properties and controls

This is the Forms!Mainform!Subform1.Form!ControlName.Enabled case.

You probably need

Forms![DATASHEET - CAF2]![Project_Permit].Form!Permits.RowSource = "..."

If you can put the code into the subform, it simplifies to

Me!Permits.RowSource = "..."

As for the SQL, that is indeed invalid syntax.

Qualify each column, and separate columns by comma, e.g. 

select pc.Value, pp.Permit from ...

To debug your SQL, see: How to debug dynamic SQL in VBA

Upvotes: 1

Related Questions