4 Replies - 960 Views - Last Post: 03 November 2015 - 02:00 PM

#1 lboyce72  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 18-August 15

Combo Box items based on query of another Combo Box

Posted 03 November 2015 - 10:10 AM

I have 2 combo boxes that contain the same information. The first cboA lists data from tblA. The second cboB queries tblA based on criteria from cboA and filters the selected item in cboA. Here is my query

SELECT tblA.SystemA
FROM tblA
WHERE (((tblA.SystemA)<>"[Forms]![frm_Select]![cboA.Column(1)]"));



With this query listed here, it still shows the item selected from cboA (example: dog) as an option in cboB.

However, if I replace Column(1) with the name of an item from cboA (dog), it works properly and "dog" is not in cboB.

I'm sure it is something simple, but I am stumped.

Thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: Combo Box items based on query of another Combo Box

#2 lboyce72  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 18-August 15

Re: Combo Box items based on query of another Combo Box

Posted 03 November 2015 - 01:19 PM

I am open to suggestions for other options to accomplish this as well.

LB
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Combo Box items based on query of another Combo Box

Posted 03 November 2015 - 01:45 PM

How are you executing that statement?

The double quotes won't be correct because it won't be looking for "dog" it'll be looking for "[Forms]![frm_Select]![cboA.Column(1)]".

From an Access Query the statement would be like this:
SELECT tblStaff.FirstName
FROM tblStaff
WHERE (((tblStaff.FirstName)<>[Forms]![frmSwitchboard]![cboA]))
ORDER BY tblStaff.FirstName;

Note that it doesn't use Column which is a VBA property. Just referring to the combobox returns the text that is displayed.

Executing from VBA you can use Column, although note that the first column is Column(0).

If executing a SQL statement from VBA you'll need to concatenate the form value, something like:
sqlStmt = "SELECT FirstName FROM tblStaff " & _
    "WHERE FirstName <>" & Forms!frmSwitchboard!cboA.Column(1)

although you can use Me in VBA to refer to the current form:
sqlStmt = "SELECT FirstName FROM tblStaff " & _
    "WHERE FirstName <>" & Me.cboA.Column(1)

Again, you shouldn't need to use Column if there is only one column.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Combo Box items based on query of another Combo Box

Posted 03 November 2015 - 01:50 PM

You also need to surround a text value with single quotes in VBA:
Private Sub cboA_AfterUpdate()
    cboB.RowSourceType = "Table/Query"
    cboB.RowSource = "SELECT FirstName FROM tblStaff " & _
        "WHERE FirstName <>'" & Me.cboA.Column(0) & "'"
End Sub

Doing this from an Access Query hides this requirement.

This post has been edited by andrewsw: 03 November 2015 - 01:51 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Combo Box items based on query of another Combo Box

Posted 03 November 2015 - 02:00 PM

If you wanted to avoid VBA, but still refer to the Column property, then you need an additional TextBox whose Control Source uses the Column property:
=[Forms]![frmSwitchboard]![cboA].[column](0)

The query that feeds the second ComboBox can then refer to this TextBox rather than directly to the first ComboBox's Column property.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1