Crystal suggested:
"After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement"
What did you see when you did this?
Duane Hookom, MVP
MS Access
Sent from Mail for Windows 10
From: 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals]
Sent: Saturday, October 24, 2015 10:37 PM
To: MS_Access_Professionals
Subject: Re: Re: [MS_AccessPros] Limit combo box in continuous form
Crystal-
I put the code behind the subform, the result didn't limit the source. Please check what could be wrong with my code?
private function SetRowSource( _
pBooCriteria as boolean)
'Crystal, strive4peace
on error goto Err_proc
dim strSQL as string
dim strSQL2 as string
strSQL = "SELECT tblCompanyEmployee.CompanyEmployeeEnglishFirstName, tblServiceOperator.CompanyEmployeeID FROM tblServiceOperator INNER JOIN tblCompanyEmployee ON tblServiceOperator.CompanyEmployeeID = tblCompanyEmployee.CompanyEmployeeID"
if pBooCriteria then
if not IsNull(me.OperatorID) then
strSQL = strSQL _
& " WHERE fieldname = " & me.OperatorID
end if
end if
strSQL = strSQL & "ORDER BY CompanyEmployeeEnglishFirstName;"
debug.print strSQL
with me.ServiceID
.RowSource = strSQL
.Requery
end with
Exit_proc:
exit function
Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume
resume Exit_proc
End function
Looking forward to your reply. Thanks a lot.
Best Regards,
Kevin
Regards,
Kevin Zhao
Date: 2015-10-25 08:26
Subject: Re: [MS_AccessPros] Limit combo box in continuous form
Hi Kevin,
changing combobox SQL when entering data: use GotFocus, LostFocus event to set combo RowSource
---
limit the combobox to specific records when it gets the focus, show all records when you leave it
on the GotFocus event of the combobox, put this in the [EventProcedure] code:
'~~~~~~~~~~~~~~
SetRowSource true
'~~~~~~~~~~~~~~
on the LostFocus event of the combobox, put this in the [EventProcedure] code:
'~~~~~~~~~~~~~~
SetRowSource false
'~~~~~~~~~~~~~~
put this code behind the form/subform with the combobox -- and compile it before testing
'~~~~~~~~~~~
private function SetRowSource( _
pBooCriteria as boolean)
'Crystal, strive4peace
on error goto Err_proc
dim strSQL as string
strSQL = "SELECT SomeID, SomeName" _
& " FROM Tablename"
if pBooCriteria then
if not IsNull(me.controlname1) then
strSQL = strSQL _
& " WHERE fieldname = " & me.controlname1
end if
end if
strSQL = strSQL & "ORDER BY SomeName;"
debug.print strSQL
with me.controlname2
.RowSource = strSQL
.Requery
end with
Exit_proc:
exit function
Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume
resume Exit_proc
End function
'~~~~~~~~
WHERE
- controlname1 is the name of the first combo
- SomeID and SomeName are fieldnames for the RowSource
- Tablename is the table name to get the data from
- controlname2 is the NAME property of the control you are setting the RowSource for
** debug.print ***
debug.print strSQL
--> this prints a copy of the SQL statement to the debug window (CTRL-G)
After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement
If the SQL statement has an error
1. Make a new query (design view)
2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window
(select, CTRL-X)
4. paste into the SQL window of the Query
(CTRL-V)
5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL
'~~~~~~~~~~~~~~
the debug window, also called the immediate window, is another good resource. When you are executing code, you can query the value of any variable, field, control, ...
? pSQL
and then press ENTER
You can also use the debug window to get help on a topic -- type or paste a keyword into the window and press F1
'~~~~~~~~~~~~~~
warm regards,
crystal
Popup Calendar for Access
http://www.rogersaccesslibrary.com/forum/popup-calendar-for-access-2007-and-above_topic597.html
~ have an awesome day ~On 10/24/2015 5:35 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] wrote:
Crystal-
Thanks a lot for your quick reply. Would please give an example on the GotFocus and LostFocus event to limit the chose and re-show all choice? Thank you so much again.
Best Regards,
Kevin
在 "crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年10月25日 上午7:19写道：
Hi Kevin,
do not see your previous message, perhaps it did not go through. Be sure it is included before you click send. If using an interface that does not include the previous message, you will have to click on a link to do so -- thanks.
please realize that a combo box is the SAME on all records. So if you want to limit choices for information on a current record, I find it best to use the GotFocus event to set the RowSource limiting choices and then the LostFocus event to show all choices again. Realize that while you are picking, information in other records for the combo may be blank because of the criteria.
warm regards,
crystal
~ have an awesome day ~On 10/24/2015 5:05 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] wrote:
Dear All,
I posted this topic yesterday, haven't gotten feedback yet. Maybe the question wasn't too clear, so I rewrite it here. I want a combo box in a continuous form to be limited by another field in the current record (record with focus in continuous form). Can this be done? Thanks.
Best Regards,
Kevin
Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar