I have a datasheet subform. In one field I want to show text values
from a lookup table. I set the field to a combo box, row source,
column count, colum widths and it works fine however...

I want to show the current value as text but in the combo box for a new
record I only want to show values not yet in the table. How can I
display the values in the datasheet but limit the contents of the combo
box.

Thanks

You can use the GotFocus event of the combo to rebuild the SQL for the
RowSource to include criteria

Then, in the LostFocus event for the combo, rebuild the SQL for the
RowSource to show everything

here is an example you can pattern after:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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

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


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



(E-Mail Removed) wrote:
> I have a datasheet subform. In one field I want to show text values
> from a lookup table. I set the field to a combo box, row source,
> column count, colum widths and it works fine however...
>
> I want to show the current value as text but in the combo box for a new
> record I only want to show values not yet in the table. How can I
> display the values in the datasheet but limit the contents of the combo
> box.
>
> Thanks
>