ACCESS FEED: RE: Re: [MS_AccessPros] Limit combo box in continuous form

Sabtu, 24 Oktober 2015

RE: Re: [MS_AccessPros] Limit combo box in continuous form

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

From: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]
Date: 2015-10-25 08:26
To: MS_Access_Professionals
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