Selection Criteria for Combination Box in DataSheet View

[Guest]

HELP!! I have a subform shown in DataSheet View based on query "A" where I
am trying to code among other data elements, a combination box on the subform
based on a different query (call it "B"). Query "B" uses data from Query "A"
for criteria purposes. In another words, I only want to see certain
selectable selections based on information from Query "A".. What is
happening is, I am only getting the values from the first record of the
datasheet for all of the combination boxes for the remaing rows in the
datasheet. I am referencing the control field in Query "A" as follows
[Form]!ProjectID in the criteria section of Query "B".. How can I get it the
combination box to only show the selected records based on the value in
control field in Query "A"?????

 

[strive4peace]

limit combobox when you enter, show all when you leave

Instead of limiting the combobox ALL the time for criteria
on a particular record, just limit it when you enter it. In
other words, use the GotFocus event to limit the records and
the Lostfocus event to show them all. When you save the
form, have the combobox show all.

Here is an analogy for you:

'~~~~~~~~

on the gotFocus event of the Product combobox, assign this:

=SetProductSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetProductSource(false)


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

'~~~~~~~~

private function SetProductSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mSupplierID as long

s = "SELECT ProductID, Product, PartNumber" _
& " FROM Products "

if pBooCriteria then
mSupplierID = nz(me.SupplierID_controlname)
if mSupplierID <> 0 then
s = s & " WHERE (SupplierID =" _
& mSupplierID & ") "
end if
end if

s = s & "ORDER BY Product;"

debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

exit function

Err_proc:
msgbox err.description,,"ERROR " _
& err.number & " SetProductSource"
'press F8 to step through code and fix problem
Stop
Resume
End function

'~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Thank YOU for the Reply! I will give this a try, and let you know how it
worked!

limit combobox when you enter, show all when you leave

Instead of limiting the combobox ALL the time for criteria
on a particular record, just limit it when you enter it. In
other words, use the GotFocus event to limit the records and
the Lostfocus event to show them all. When you save the
form, have the combobox show all.

Here is an analogy for you:

'~~~~~~~~

on the gotFocus event of the Product combobox, assign this:

=SetProductSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetProductSource(false)


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

'~~~~~~~~

private function SetProductSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mSupplierID as long

s = "SELECT ProductID, Product, PartNumber" _
& " FROM Products "

if pBooCriteria then
mSupplierID = nz(me.SupplierID_controlname)
if mSupplierID <> 0 then
s = s & " WHERE (SupplierID =" _
& mSupplierID & ") "
end if
end if

s = s & "ORDER BY Product;"

debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

exit function

Err_proc:
msgbox err.description,,"ERROR " _
& err.number & " SetProductSource"
'press F8 to step through code and fix problem
Stop
Resume
End function

'~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

HELP!! I have a subform shown in DataSheet View based on query "A" where I
am trying to code among other data elements, a combination box on the subform
based on a different query (call it "B"). Query "B" uses data from Query "A"
for criteria purposes. In another words, I only want to see certain
selectable selections based on information from Query "A".. What is
happening is, I am only getting the values from the first record of the
datasheet for all of the combination boxes for the remaing rows in the
datasheet. I am referencing the control field in Query "A" as follows
[Form]!ProjectID in the criteria section of Query "B".. How can I get it the
combination box to only show the selected records based on the value in
control field in Query "A"?????

 

[strive4peace]

you're welcome happy to help

I wish you success!

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Thank YOU for the Reply! I will give this a try, and let you know how it
worked!

:

limit combobox when you enter, show all when you leave

Instead of limiting the combobox ALL the time for criteria
on a particular record, just limit it when you enter it. In
other words, use the GotFocus event to limit the records and
the Lostfocus event to show them all. When you save the
form, have the combobox show all.

Here is an analogy for you:

'~~~~~~~~

on the gotFocus event of the Product combobox, assign this:

=SetProductSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetProductSource(false)


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

'~~~~~~~~

private function SetProductSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mSupplierID as long

s = "SELECT ProductID, Product, PartNumber" _
& " FROM Products "

if pBooCriteria then
mSupplierID = nz(me.SupplierID_controlname)
if mSupplierID <> 0 then
s = s & " WHERE (SupplierID =" _
& mSupplierID & ") "
end if
end if

s = s & "ORDER BY Product;"

debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

exit function

Err_proc:
msgbox err.description,,"ERROR " _
& err.number & " SetProductSource"
'press F8 to step through code and fix problem
Stop
Resume
End function

'~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

HELP!! I have a subform shown in DataSheet View based on query "A" where I
am trying to code among other data elements, a combination box on the subform
based on a different query (call it "B"). Query "B" uses data from Query "A"
for criteria purposes. In another words, I only want to see certain
selectable selections based on information from Query "A".. What is
happening is, I am only getting the values from the first record of the
datasheet for all of the combination boxes for the remaing rows in the
datasheet. I am referencing the control field in Query "A" as follows
[Form]!ProjectID in the criteria section of Query "B".. How can I get it the
combination box to only show the selected records based on the value in
control field in Query "A"?????