Linking Combo Box to Bound Sub Form

N

Nanette

I have 3 cascading combo boxes that need to be bound to a sub form linked to
the values selected in the combo boxes.

Any suggestions or examples would be appreciated.
 
P

Perry

One option is through VBA by
changing the recordsource of the subform according to the combo's selections

simplified
tblCust is the subform's initial recordsource, and cbo1 through cbo3 are the
combo's
and sfrmCust is yr subform:
sSQL = "select * from tblCust where " & _
"and custName = " & me!cbo1 & _
"and custResid = " & me!cbo2 & _
"and custCountry = " & me!cbo3
me!sfrmCust.Form.RecordSource = sSQL

Note: you will have 6 (ie. 3*2*1) options for sSQL to cover.

Krgrds,
Perry
 
N

Nanette

Hi Perry,

I tried the following and it's not working, the subform does not change.
Could you tell me what I'm doing wrong.

Private Sub cboLine_AfterUpdate()
sSQL = "SELECT * FROM LineItem WHERE " & _
"and RFQNo = " & Me!cboRFQ & _
"and Change = " & Me!cboChange & _
"and LineItem = " & Me!cboLine & _
Me!sfrmLineItem.Form.RecordSource = sSQL

End Sub
 
P

Perry

Hi Nanette

Replace
sSQL = "SELECT * FROM LineItem WHERE " & _
"and RFQNo = " & Me!cboRFQ & _
Click to expand...
by
sSQL = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Me!cboRFQ & _

;-)

Krgrds,
Perry
 
N

Nanette

Hi Perry,

It still didn't work. Can you think of what I'm missing? Should I be
changing another parameter in the properties somewhere?
 
P

Perry

We're not giving in here ;-)

All Text, hmm

Private Sub cboLine_AfterUpdate()

sSQL = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Me!cboRFQ & Chr(39) & _
" and Change = " & Chr(39)& Me!cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Me!cboLine & Chr(39)

Me!sfrmLineItem.Form.RecordSource = sSQL

End Sub

Krgrds,
Perry
 
N

Nanette

That's awesome Perry. It works!!!

When I open the form, all the fields in the subform have #Name in them. When
I select what I want, all the fields fill in.

Is there a way to stop the subform fields from showing #Name?

Could you explain what the Chr(39)s mean?
 
P

Perry

Could you explain what the Chr(39)s mean?
Gruesome hey? haha

It's a textqualifier, and actually refers to a single quote.
One could also use a double quote, which is also a valid text qualifier
Chr(34)
In these newsgroups, when examplifying SQL statements (like for you) I tend
to use the Chr(39) or Chr(34) as opposed to using the single quote ' mark
for a better readibility for the poster.

MS Access needs this to be able to distinguish beginning of a text string
and ending of such.

Glad I could be of help.

Krgrds,
Perry
 
C

c.kremm

Gruesome hey? haha

It's a textqualifier, and actually refers to a single quote.
One could also use a double quote, which is also a valid text qualifier
Chr(34)
In these newsgroups, when examplifying SQL statements (like for you) I tend
to use the Chr(39) or Chr(34) as opposed to using the single quote ' mark
for a better readibility for the poster.

MS Access needs this to be able to distinguish beginning of a text string
and ending of such.

Glad I could be of help.

Krgrds,
Perry

"Nanette" <[email protected]> schreef in bericht







- Show quoted text -
Click to expand...

what next is this o.k
 
N

Nanette

Thanks again Perry.

I'm learning so much from all you experts. And, you make it easy.

One final question (for now), how can I get the #Name not to appear in all
the subform fields before I make the choices in the combo boxes? I just know
the folks here will freak out if they see all these :))
 
N

Nanette

Thanks again Perry.

I'm learning so much from all you experts. And, you make it easy.

One final question (for now), how can I get the #Name not to appear in all
the subform fields before I make the choices in the combo boxes? I just know
the folks here will freak out if they see all these :))
 
P

Perry

Set the recordsource of the form (in designview from the properties pane) to
read:
sSQL = "SELECT * FROM LineItem"

Krgrds,
Perry
 
N

Nanette

I tried putting the code in both the subform and the main form and I get a
message that "The record source 'sSQL = "SELECT * FROM LineItem" specifice on
this form or report does not exist.
 
P

Perry

My fault.
It's getting a bit late here ...

Set the recordsource of the form (in designview from the properties pane) to
read:
"SELECT * FROM LineItem"

sorry for that,
Krgrds,
Perry
 
N

Nanette

No worries about your fault. You've helped me a bunch.

Still getting the same message though.
 
N

Nanette

Hi Perry,

The #Name is no longer showing up in the fields when I open the form.
Somehow there was some code hidden in the Resource Code field that showed up
this morning. Once I cut it out, the form is working great.

Thanks again for all your help
 
Top