Combo Box Issues

[tcb]

1) When a user selects an item from a combo box, tabs on to other
controls doing more data entry, then realizes that he/she did not want
an item in that combo box after all, what is the best method for
deleting the value that dropped into the table?

2) When there are values in a combo box that you no longer want a
viewer to choose from, via a Active_YN field = 0, but you want the
user to be able to see the historical value in previous entries, what
is the best way of achieving that?

 

[strive4peace]

1. DELETE * FROM tablename WHERE conditions

2. 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, assign this:

=SetRowSource(true)


on the lostFocus event of the combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource( _
pBooCriteria as boolean)

on error goto Err_proc

dim strSQL as string

strSQL = "SELECT SomeID, SomeName" _
& " FROM Tablename"

if pBooCriteria then

strSQL = strSQL _
& " WHERE (Active_YN = true)"

end if

strSQL = strSQL & "ORDER BY SomeName;"

debug.print strSQL

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery

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

'~~~~~~~~

** 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
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[tcb]

Crystal, I just tried your code for issue #2. Excellent. Now my day
is awesome. Thank you.

 

[strive4peace]

thank you!

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*