Combo Box issue

[Guest]

I'm using one combo box (cmbo1) to limit the results of another combo box
(cmbo2). The record being generated is freight claim.
cmbo1.rowsource = "select projid, projname from projects_table"
cmbo2.rowsource = "select contactid, contactname from projects_contact_table
where projid = " & me!cmbo1

private sub cmbo1_afterupdate()
me!cmbo2.requery
end sub

id fields are the bound columns

Frghtclaim1 was created fine. AddNew. Frghtclaim2 - after selecting the
projid in cmbo1 i go to cmbo2 and select the contactid. go back to
frghtclaim1 and the cmbo2 field is blank as well as the drop down - i know
that records exist for this project. close frghtclaim form. open form.
frghtclaim1 now shows the correct information. go to frghtclaim2 - cmbo2 is
blank and the drop down now gives me the record for frghtclaim1 only.

took out the where clause. go back to frghtclaim1 - correct info.
frghtclaim2 - correct info. AddNew. frghtclaim 3 - cmbo2 shows all records
that exist.

changed the cmbo1_afterupdate to set the recordsource for cmbo2. AddNew.
only option is the one correct one. save record. page through previous
records - combo2 is blank and only option listed is the one for the new
record.

any ideas?? thanks.
working in Access 2007 with a database as 2003 format.

 

[strive4peace]

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, 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
if not IsNull(me.cmbo1) then
strSQL = strSQL _
& " WHERE projid = " & me.cmbo1
end if
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

'~~~~~~~~

WHERE
- SomeID is the name of the first field of the combo RowSource (contactid)
- SomeName is the name of the second field of the combo RowSource
(contactname)
- Tablename is the table name to get the data from (projects_contact_table)
- combobox_controlname is the NAME property of the control you are
settnig the RowSource for (cmbo2)

** 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
*

 

[Guest]

pretty close. when I use the lostfocus event my selection is gone and is not
saved.

 

[strive4peace]

"when I use the lostfocus event my selection is gone and is not
saved."

then you are not showing ALL records on the LostFocus... doubt the
selection is gone -- it probably just doesn't show anymore


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