UtterAccess.com
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
Post#21



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


QUOTE
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:

pkLog_No
fkSiteID


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

dbo_tblPermits.fkSiteID=Nz(Forms!FrmHistoryReport.ComboHistorySiteId,0)

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

=ComboHistorySiteID.Column(1)

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

dbo_tblPermits.fkSiteID=Nz(Forms!FrmHistoryReport.txtSiteID,0)

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.

hth
tina

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



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


Robert:
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
Thanks!

Below is the current query for the report
SQL
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
SQL
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
Post#23



Posts: 206
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
 
projecttoday
post Jul 18 2017, 08:21 PM
Post#24


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


Congratulations!

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

My company's website
Go to the top of the page
 
Doug Galayda
post Aug 8 2017, 05:42 PM
Post#25



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


its cool, thanks for you input, Its working, thanks for your kindness!
Go to the top of the page
 
projecttoday
post Aug 8 2017, 06:14 PM
Post#26


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


You're welcome. Glad you got it working.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 09:45 PM