X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Limiting A Query To A Dropdown Value., Access 2013    
tina t
post Jul 18 2017, 02:47 PM

Posts: 5,051
Joined: 11-November 10
From: SoCal, USA

This is my dropdown select:
SELECT [dbo__Active_w/Action].pkLog_No, [dbo__Active_w/Action].fkSiteID FROM [dbo__Active_w/Action] ORDER BY [dbo__Active_w/Action].fkSiteID;


Bound column = 1

okay, hon, the RowSource returns the following two fields, in order:


because the Bound column = 1, the value of the combobox control is the first column: pkLog_No. so in your query criteria,


the form reference is returning whatever value is in the first column of the combo: pkLog_No.

if you need the value from column fkSiteID instead, then you have several options. you can

1) change the BoundColumn property value from 1 to 2, or
2) change the order of the columns in the combobox control's RowSource, so that field fkSiteID is listed first, instead of second
(note that if you do this, you may have to change the ColumnWidths property setting, too, so that the combo's droplist shows what you want it to), or
3) leave the combobox control's settings as is, and add a textbox control to the form (you can set its' Visible property to False if you wish), i'll call it txtSiteID, and set its' ControlSource property to


note that column references in combo- and listbox controls can be confusing. the BoundColumn property is straightforward: 1st field in the RowSource is column 1, 2nd field in the RowSource is column 2, etc. but when referring to a column as in the expression above, you're referring to the column's Index property. and in Access, comboboxes' and listboxes' column index property is zero-based, so the first column index is (0), the second column index is (1), etc.

the reason the third option above includes adding a textbox control is because you can't refer to a combobox control's Column property in a query (with the usual caveat: that may have changed in newer versions of Access). so you would change the query reference to


whew! that's a lot to get through. suggest you read it again, slowly, making sure you understand each step as i take you through it, and pay attention to what column numbers, column index numbers, and field names i'm talking about, at each step.


"you can't take the sky from me"
Go to the top of the page
Doug Galayda
post Jul 18 2017, 03:00 PM

Posts: 174
Joined: 19-May 10
From: Springfield, Illinois

1)The Like '%' is because the first column is pkLogNo, it has hyphens in the number,
each number is a number after the year, 2017-136, 2017-137

2)I checked in sqlserver
Both the pklogno and the fkSiteNo are nvarchar(8) + (10) respectively.
The pklogno is Not allow to be null, but
The fkSiteNo is allowed to be null, therefore I am keeping the nz in play as Null.
I did not add the Nz until theDBGuy suggested it this morning:
“How about something like”?
...AND ((dbo_tblPermits.fkSiteID)=Nz(Forms!FrmActionReport.ComboActionSiteID,0)))
--But he did not know both columns were nvarchar.

I will put you code in a sub and run it and let you know the value

Below is the current query for the report
SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.fkSiteID, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV
FROM dbo_tblPermits WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=Nz([Forms]![FrmHistoryReport].[ComboHistorySiteId],Null)));

This is the select for the drop down
SELECT [dbo__Active_w/Action].pkLog_No, [dbo__Active_w/Action].fkSiteID FROM [dbo__Active_w/Action] ORDER BY [dbo__Active_w/Action].fkSiteID;

This post has been edited by Doug Galayda: Jul 18 2017, 03:33 PM
Go to the top of the page
Doug Galayda
post Jul 18 2017, 07:44 PM

Posts: 174
Joined: 19-May 10
From: Springfield, Illinois

Got the solution! 730pm Tuesday/7/17/2017
Got the drop down not work by stating Select fkSiteId, fkSiteId from ,,,,twice in the select
tried it and it worked. (not perfect but I can tweak it when I get back Saturday)
selected site id 0050050001 and now the report generates with data
and the site_id is In the title of the report.

Many thanks to all of you a UA for all of your help :-)
Now I can put a site_id drop down on each (of 3) form report drivers.
the users will appreciate it!
This post has been edited by Doug Galayda: Jul 18 2017, 07:52 PM
Attached File(s)
Attached File  Snippet_7_17_2017_737pm.PNG ( 50.31K )Number of downloads: 0
Go to the top of the page
post Jul 18 2017, 08:21 PM

UtterAccess VIP
Posts: 8,239
Joined: 10-February 04
From: South Charleston, WV


I think I gave some misinformation earlier. When you refer to the first column (from the left) of a combo box in VBA code you use 0. For example, Me.Comboname.Column(0). But in the properties of the combo box the Bound Column for the leftmost column is specified as 1. The Bound Column is the one that provides the value when you don't specify a column, such as Forms.Formname.Comboname.
If the row source of your combo is Select fkSiteId, fkSiteId you should be able to use 1 or 2 as the Bound Column and get fkSiteId. I think I mistated earlier that you should use 0.

Robert Crouser
Go to the top of the page
2 Pages V < 1 2

Custom Search
RSSSearch   Top   Lo-Fi    26th July 2017 - 11:49 AM