making Combobox SQL
---
Hi Sarah,
another option is to use a couple unbound combos to "drill-down" to the
records you want to pick from... as Marsh suggested, you should
eliminate duplicates and this would do it.
'~~~
private function SetSQL()
on error goto Proc_err
dim s as string, mWhere as long
s = "SELECT SomeID, SomeName " _
& " FROM SomeTable "
mFilter = ""
If not IsNull(me.text_controlname) Then
mWhere = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if
If not IsNull(me.date_controlname) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if
If not IsNull(me.numeric_controlname) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "[NumericFieldname]= " _
& me.controlname_for_number
end if
if if len(mWhere) > 0 then
s = s & " WHERE " & mwhere
end if
s = s & " ORDER BY SomeName;"
'remove next line after debugged
debug.print s
me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery
Proc_Exit:
exit function
Proc_err:
msgbox err.description _
,,"ERROR " & err.number & " SetSQL"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume
Resume Proc_Exit
End function
'~~~~~~~~~~
on the gotFocus event of the data combo, you would limit choices
on the LostFocus event of the data combo, you would show all
so, we would modify the above code to take a parameter...
'~~~
private function SetSQL(pBooCriteria as boolean)
on error goto Proc_err
dim s as string, mWhere as long
s = "SELECT SomeID, SomeName " _
& " FROM SomeTable"
'`````````````````````````````````````
if pBooCriteria then
mFilter = ""
If not IsNull(me.text_controlname ) Then
mWhere = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if
If not IsNull(me.date_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if
If not IsNull(me.numeric_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "[NumericFieldname]= " _
& me.controlname_for_number
end if
if len(mWhere) > 0 then
s = s & " WHERE " & mwhere
end if
end if
'`````````````````````````````````````
s = s & " ORDER BY SomeName;"
'remove next line after debugged
debug.print s
me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery
Proc_Exit:
exit function
Proc_err:
msgbox err.description _
,,"ERROR " & err.number & " SetSQL"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume
Resume Proc_Exit
End function
'~~~~~~~~~~
on the gotFocus event of the Colllection combobox, assign this:
=SetSQL(true)
on the lostFocus event of the Colllection combobox, assign this:
=SetSQL(false)
** 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
*
Marshall said:
Sarah wrote:
I have a continuous form with hundres of records. One of the fields is a
combo box showing PartNumberID. Typically there will be many identical
consecutive entries like xj6768-a12, followed by many more identical
consecutive entries like xJ6788-a15, and so on.
It would really help our efficiency if the FIRST entry in each grouping of
identical entries was hilighted by color, or had text that was bolded. In
other words, the first would be hilighted as would any PartNumberID
different than the one 'above' it.
Not with any Access capability. Wouldn't it be better to
not show the duplicates?
happy to help