Linking Combo Box to Bound Sub Form

[Guest]

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.

 

[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

 

[Guest]

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

 

[Perry]

Hi Nanette

Replace

sSQL = "SELECT * FROM LineItem WHERE " & _
"and RFQNo = " & Me!cboRFQ & _

by
sSQL = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Me!cboRFQ & _

;-)

Krgrds,
Perry

 

[Guest]

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?

 

[Perry]

Ok, what data types are below fields?

RFQNo
Change
LineItem

Krgrds,
Perry

 

[Guest]

They are all text.

Ok, what data types are below fields?

RFQNo
Change
LineItem

Krgrds,
Perry

 

[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

 

[Guest]

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?

 

[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.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 -

what next is this o.k

 

[Guest]

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 )

 

[Guest]

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 )

 

[Perry]

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

Krgrds,
Perry

 

[Guest]

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.

 

[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

 

[Guest]

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

Still getting the same message though.

 

[Guest]

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