0

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?

Mike
  • 1,853
  • 3
  • 45
  • 75
  • 2
    Why not just set the listbox rowsource to the query directly? You can include parent form and sub-form references in the queries. – Minty Sep 11 '17 at 13:42
  • @Minty I tried `select pc.Value Permit from pmdb.Project_Permit pp left join pmdb.PicklistChild pc on pc.Id = pp.Permit where Project = Forms![DATASHEET - CAF2]![Project_Permit].Form!Permits.Project and ProjectType = Forms![DATASHEET - CAF2]![Project_Permit].Form!Permits.Project_Type and GroupId = Forms![DATASHEET - CAF2]![Project_Permit].Form!Permits.GroupID`, which did not work. Am I referencing the form and sub form incorrectly? – Mike Sep 11 '17 at 14:14
  • 1
    Try building the query in the query builder and use the expression builder to pick the form names for you to get the exact syntax correct. The link @Andre provided will also assist. – Minty Sep 11 '17 at 14:50

1 Answers1

1

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

Andre
  • 26,751
  • 7
  • 36
  • 80
  • I tried this. It did not populate the Listbox. Should this be in the `Form_Load` for the main form or the subform? – Mike Sep 11 '17 at 14:15
  • I get an error: `Syntax error (missing operator) in query expression 'pc.Value Permit'.` in both forms. – Mike Sep 11 '17 at 14:17
  • Value is a reserved word, ideally you'll need to rename it or at least surround it in square brackets. – Minty Sep 11 '17 at 14:43
  • @Minty I cannot rename it. I have tried surrounding it with `[]`. Though it did not work either. – Mike Sep 11 '17 at 14:47
  • @Mike sounds like you haven't yet changed `pc.Value Permit` to `pc.Value, Permit`. You need to make that change, else you get exactly this error. – Erik A Sep 11 '17 at 15:16
  • @ErikvonAsmuth the `Permit` is the alias for `pc.Value`. I added `as` so that it is not `pc.[Value] as Permit` and even tried just `pc.[Value]` still, doesn't work. – Mike Sep 11 '17 at 15:27
  • 1
    @Mike Please be explicit about what you're doing, because that's a terrible idea if you're using the name of a column already assigned to a different column, that gets used in a join (`left join pmdb.PicklistChild pc on pc.Id = pp.Permit`). Please start with the bare minimum (e.g. `select Permit from pmdb.Project_Permit`), and add on that until you find the error. If you don't get the SQL to work, ask a separate question about it, because you're doing some weird things here. – Erik A Sep 11 '17 at 15:33
  • And *"doesn't work"* isn't really helpful for us to find an error. But as Erik wrote, debugging your SQL should be your first step. @Mike – Andre Sep 11 '17 at 17:12
  • @ErikvonAsmuth I am working on debugging my SQL. I understand what you are saying. I've probably got something in it that isn't working on top of whatever else is wrong. I will simplify and continue from there. Where I'm starting is with: `Me!Permits.RowSource = "Select Permit from pmdb.Project_Permit"` and see what that gets me. – Mike Sep 11 '17 at 17:19
  • @Andre when I say *doesn't work* without an error message, It's because there is no error message. There is also no difference made by putting whatever update in. I will try to be more clear in my comments so that what happens (or doesn't happen) will be more apparent. – Mike Sep 11 '17 at 17:22
  • I got it to work, mostly. It's showing the wrong value, but it is showing a value. I'll figure out why it's showing the wrong one. Thanks for the help! – Mike Sep 11 '17 at 19:08