Combo box values based on the contents of another

D

Duncs

My form has two combo boxes. One lists a company code, whilst the
other lists order numbers. As there isn't much data held, all the
information is held in the one table rather than being normalised and
split over several ones. (My apologies for this)

What I want to do, is select the company name from the first combo,
and then change the contents of the second combo, to only show those
orders that were made by the company in the first combo!

Hope this makes sense to you!

Can anyone assist?

TIA

Duncs
 
R

R S TOMAR, EAR-3

u can visit databsedev.co.uk and there they have some access samples
describing some common scenarios as urs.
 
D

Duncs

u can visit databsedev.co.uk and there they have some access samples
describing some common scenarios as urs.
Click to expand...

I've visited the site and tried the suggestion. However, when I
access the form and I select the company code from the first combo,
moving to the second and accessing the combo prompts me for a
parameter value, with the caption of the Company Code selected in the
first combo.

So, I have the following:

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()

Dim sSQL As String

sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
" [tbl_All_UTR_20090629].[CRN]," & _
" [tbl_All_UTR_20090629].[AMT]," & _
" [tbl_All_UTR_20090629].[ProperDate], " & _
" [tbl_All_UTR_20090629].[MSN], " & _
" [tbl_All_UTR_20090629].[MSN2], " & _
" [tbl_All_UTR_20090629].[SCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value

Me.cboCRN.RowSource = sSQL
Me.cboCRN.Requery

End Sub

cboCRN
RowSource - SELECT tbl_All_UTR_20090629.CRN, tbl_All_UTR_20090629.AMT,
tbl_All_UTR_20090629.ProperDate, tbl_All_UTR_20090629.MSN,
tbl_All_UTR_20090629.MSN2, tbl_All_UTR_20090629.SCNO FROM
tbl_All_UTR_20090629;

I'm at a loss as to what's wrong so would appreciate some help.

TIA

Duncs
 
R

R S TOMAR, EAR-3

After u set ur first combo to get the lookup values of company code.
Go to its after update property and try the following code.

Private Sub cboCompanyCode_AfterUpdate()

Dim sOrderNumberSource As String

OrderNumberSource = "SELECT [tblOrderNumber].[IngOrderID],
[tblOrderNumber].[lngCompanyID], [tblOrderNumber].[strOrderName] " & _
"FROM tblOrderNumber " & _
"WHERE [lngCompanyID] = " &
Me.cboCompanyCode.Value
Me.cboOrderNumber.RowSource = sOrderNumberSource
Me.cboOrderNumber.Requery

End Sub
 
R

R S TOMAR, EAR-3

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
Click to expand...
----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.

AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()

Dim sSQL As String

    sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
            " [tbl_All_UTR_20090629].[CRN]," & _
            " [tbl_All_UTR_20090629].[AMT]," & _
            " [tbl_All_UTR_20090629].[ProperDate], " & _
            " [tbl_All_UTR_20090629].[MSN], " & _
            " [tbl_All_UTR_20090629].[MSN2], " & _
            " [tbl_All_UTR_20090629].[SCNO] " & _
            "FROM tbl_All_UTR_20090629 " & _
            "WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
Click to expand...
------------------------------ Is it cboPPMIP or cboCompany as said
in ur first line of code...
 
D

Duncs

Nope, it's still not working.

I've got the Bound column set to 1. But, like before, when I select a
company code from cboPPMIP and activate the cboCRN dropdown, a
parameter window appears with a title of the company code I selected
prior to activating it. If I then enter the code that I previously
selected, it then displays the CRNs associated with the company code.

Duncs

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
Click to expand...

----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()
Click to expand...
Dim sSQL As String
Click to expand...
    sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
            " [tbl_All_UTR_20090629].[CRN]," & _
            " [tbl_All_UTR_20090629].[AMT]," & _
            " [tbl_All_UTR_20090629].[ProperDate], " & _
            " [tbl_All_UTR_20090629].[MSN], " & _
            " [tbl_All_UTR_20090629].[MSN2], " & _
            " [tbl_All_UTR_20090629].[SCNO] " & _
            "FROM tbl_All_UTR_20090629 " & _
            "WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
Click to expand...

------------------------------  Is it cboPPMIP or  cboCompany as said
in ur first line of code...




    Me.cboCRN.RowSource = sSQL
    Me.cboCRN.Requery
Click to expand...
End Sub
cboCRN
RowSource - SELECT tbl_All_UTR_20090629.CRN, tbl_All_UTR_20090629.AMT,
tbl_All_UTR_20090629.ProperDate, tbl_All_UTR_20090629.MSN,
tbl_All_UTR_20090629.MSN2, tbl_All_UTR_20090629.SCNO FROM
tbl_All_UTR_20090629;
Click to expand...
I'm at a loss as to what's wrong so would appreciate some help.

Duncs- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
D

Douglas J. Steele

I'm guessing that PPMIP is a text field. If that's the case, you need quotes
around the value:

"WHERE [tbl_All_UTR_20090629].[PPMIP] = '" & Me.cboPPMIP & "'"

Exagerated for clarity, that's

"WHERE [tbl_All_UTR_20090629].[PPMIP] = ' " & Me.cboPPMIP & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nope, it's still not working.

I've got the Bound column set to 1. But, like before, when I select a
company code from cboPPMIP and activate the cboCRN dropdown, a
parameter window appears with a title of the company code I selected
prior to activating it. If I then enter the code that I previously
selected, it then displays the CRNs associated with the company code.

Duncs

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
Click to expand...

----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()
Click to expand...
Dim sSQL As String
Click to expand...
sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
" [tbl_All_UTR_20090629].[CRN]," & _
" [tbl_All_UTR_20090629].[AMT]," & _
" [tbl_All_UTR_20090629].[ProperDate], " & _
" [tbl_All_UTR_20090629].[MSN], " & _
" [tbl_All_UTR_20090629].[MSN2], " & _
" [tbl_All_UTR_20090629].[SCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
Click to expand...

------------------------------ Is it cboPPMIP or cboCompany as said
in ur first line of code...




Me.cboCRN.RowSource = sSQL
Me.cboCRN.Requery
Click to expand...
End Sub
cboCRN
RowSource - SELECT tbl_All_UTR_20090629.CRN, tbl_All_UTR_20090629.AMT,
tbl_All_UTR_20090629.ProperDate, tbl_All_UTR_20090629.MSN,
tbl_All_UTR_20090629.MSN2, tbl_All_UTR_20090629.SCNO FROM
tbl_All_UTR_20090629;
Click to expand...
I'm at a loss as to what's wrong so would appreciate some help.

Duncs- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
D

Duncs

Aaaaagggggghhhhhhh!

I'm now at a loss as to what's happening.

I've renamed some fields for clarity and here's what I have, based on
the entries here:

My form has a RecordSource of tbl_All_UTR_20090629
PPMIP has a RowSource of:

SELECT tbl_All_UTR_20090629.fldPPMIP
FROM tbl_All_UTR_20090629
GROUP BY tbl_All_UTR_20090629.fldPPMIP;

BoundColumn = 1
ControlSource = fldPPMIP

It has an AfterUpdate event as follows, after following Dougs
suggestion:

Private Sub PPMIP_AfterUpdate()

Dim sSQL As String

sSQL = "SELECT [fldPPMIP]," & _
" [fldCRN]," & _
" [fldAMT]," & _
" [fldProperDate], " & _
" [fldMSN], " & _
" [fldMSN2], " & _
" [fldSCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [fldPPMIP] = '" & Me.PPMIP.Value & "'"

Me.CRN.RowSource = sSQL
Me.CRN.Requery

End Sub

CRN has a ControlSource of fldCRN and no RowSource.

Now, when I run the form and select a value form the PPMIP field,
moving to the CRN field and accessing the combo, shows me a list
containing the PPMIP that was selected in the previous field. This
value would, I am guessing, appear for as many times as there is a CRN
against the particular PPMIP. Tracing through the VBA for the
AfterUpdate, gives the following for the sSQL value:

"SELECT [fldPPMIP], [fldCRN], [fldAMT], [fldProperDate], [fldMSN],
[fldMSN2], [fldSCNO] FROM tbl_All_UTR_20090629 WHERE [fldPPMIP] =
'TMIDE'"

Which all looks fine, considering I selected TMIDE as the PPMIP value.

Can someone advise as to what's going wrong with this thing, as it's
driving me mental!!!

Many, many TIA.

Duncs


I'm guessing that PPMIP is a text field. If that's the case, you need quotes
around the value:

"WHERE [tbl_All_UTR_20090629].[PPMIP] = '" & Me.cboPPMIP & "'"

Exagerated for clarity, that's

"WHERE [tbl_All_UTR_20090629].[PPMIP] = ' " & Me.cboPPMIP & " ' "

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Nope, it's still not working.

I've got the Bound column set to 1.  But, like before, when I select a
company code from cboPPMIP and activate the cboCRN dropdown, a
parameter window appears with a title of the company code I selected
prior to activating it.  If I then enter the code that I previously
selected, it then displays the CRNs associated with the company code.

Duncs

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
Click to expand...
Click to expand...
----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()
Dim sSQL As String
sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
" [tbl_All_UTR_20090629].[CRN]," & _
" [tbl_All_UTR_20090629].[AMT]," & _
" [tbl_All_UTR_20090629].[ProperDate], " & _
" [tbl_All_UTR_20090629].[MSN], " & _
" [tbl_All_UTR_20090629].[MSN2], " & _
" [tbl_All_UTR_20090629].[SCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
Click to expand...
Click to expand...
------------------------------ Is it cboPPMIP or cboCompany as said
in ur first line of code...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
D

Douglas J. Steele

Sorry, but I don't really understand what problem you're encountering! Is it
that you're seeing the PPMIP value in the CRN combo box, instead of the CRN
values? The RowSource for the CRN combo box has seven fields in it: have you
set the ColumnCount to 7?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Aaaaagggggghhhhhhh!

I'm now at a loss as to what's happening.

I've renamed some fields for clarity and here's what I have, based on
the entries here:

My form has a RecordSource of tbl_All_UTR_20090629
PPMIP has a RowSource of:

SELECT tbl_All_UTR_20090629.fldPPMIP
FROM tbl_All_UTR_20090629
GROUP BY tbl_All_UTR_20090629.fldPPMIP;

BoundColumn = 1
ControlSource = fldPPMIP

It has an AfterUpdate event as follows, after following Dougs
suggestion:

Private Sub PPMIP_AfterUpdate()

Dim sSQL As String

sSQL = "SELECT [fldPPMIP]," & _
" [fldCRN]," & _
" [fldAMT]," & _
" [fldProperDate], " & _
" [fldMSN], " & _
" [fldMSN2], " & _
" [fldSCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [fldPPMIP] = '" & Me.PPMIP.Value & "'"

Me.CRN.RowSource = sSQL
Me.CRN.Requery

End Sub

CRN has a ControlSource of fldCRN and no RowSource.

Now, when I run the form and select a value form the PPMIP field,
moving to the CRN field and accessing the combo, shows me a list
containing the PPMIP that was selected in the previous field. This
value would, I am guessing, appear for as many times as there is a CRN
against the particular PPMIP. Tracing through the VBA for the
AfterUpdate, gives the following for the sSQL value:

"SELECT [fldPPMIP], [fldCRN], [fldAMT], [fldProperDate], [fldMSN],
[fldMSN2], [fldSCNO] FROM tbl_All_UTR_20090629 WHERE [fldPPMIP] =
'TMIDE'"

Which all looks fine, considering I selected TMIDE as the PPMIP value.

Can someone advise as to what's going wrong with this thing, as it's
driving me mental!!!

Many, many TIA.

Duncs


I'm guessing that PPMIP is a text field. If that's the case, you need
quotes
around the value:

"WHERE [tbl_All_UTR_20090629].[PPMIP] = '" & Me.cboPPMIP & "'"

Exagerated for clarity, that's

"WHERE [tbl_All_UTR_20090629].[PPMIP] = ' " & Me.cboPPMIP & " ' "

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Nope, it's still not working.

I've got the Bound column set to 1. But, like before, when I select a
company code from cboPPMIP and activate the cboCRN dropdown, a
parameter window appears with a title of the company code I selected
prior to activating it. If I then enter the code that I previously
selected, it then displays the CRNs associated with the company code.

Duncs

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
Click to expand...
Click to expand...
----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()
Dim sSQL As String
sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
" [tbl_All_UTR_20090629].[CRN]," & _
" [tbl_All_UTR_20090629].[AMT]," & _
" [tbl_All_UTR_20090629].[ProperDate], " & _
" [tbl_All_UTR_20090629].[MSN], " & _
" [tbl_All_UTR_20090629].[MSN2], " & _
" [tbl_All_UTR_20090629].[SCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
Click to expand...
Click to expand...
------------------------------ Is it cboPPMIP or cboCompany as said
in ur first line of code...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
D

Duncs

Doug, that's what I had done wrong. The CRN combo was showing the
text that had been selected in the PPMIP combo. I'd set the
ColumnCount in the CRN combo to 1, so it was showing the PPMIP value
in the drop down. Changed it and all is now working fine!!

Many thanks to you and Raj for your support and advice with this one.

Rgds

Duncs

Sorry, but I don't really understand what problem you're encountering! Isit
that you're seeing the PPMIP value in the CRN combo box, instead of the CRN
values? The RowSource for the CRN combo box has seven fields in it: have you
set the ColumnCount to 7?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


Aaaaagggggghhhhhhh!

I'm now at a loss as to what's happening.

I've renamed some fields for clarity and here's what I have, based on
the entries here:

My form has a RecordSource of tbl_All_UTR_20090629
PPMIP has a RowSource of:

SELECT tbl_All_UTR_20090629.fldPPMIP
FROM tbl_All_UTR_20090629
GROUP BY tbl_All_UTR_20090629.fldPPMIP;

BoundColumn = 1
ControlSource = fldPPMIP

It has an AfterUpdate event as follows, after following Dougs
suggestion:

Private Sub PPMIP_AfterUpdate()

Dim sSQL As String

        sSQL = "SELECT [fldPPMIP]," & _
            " [fldCRN]," & _
            " [fldAMT]," & _
            " [fldProperDate], " & _
            " [fldMSN], " & _
            " [fldMSN2], " & _
            " [fldSCNO] " & _
            "FROM tbl_All_UTR_20090629 " & _
            "WHERE [fldPPMIP] = '" & Me.PPMIP.Value & "'"

    Me.CRN.RowSource = sSQL
    Me.CRN.Requery

End Sub

CRN has a ControlSource of fldCRN and no RowSource.

Now, when I run the form and select a value form the PPMIP field,
moving to the CRN field and accessing the combo, shows me a list
containing the PPMIP that was selected in the previous field.  This
value would, I am guessing, appear for as many times as there is a CRN
against the particular PPMIP.  Tracing through the VBA for the
AfterUpdate, gives the following for the sSQL value:

"SELECT [fldPPMIP], [fldCRN], [fldAMT], [fldProperDate],  [fldMSN],
[fldMSN2],  [fldSCNO] FROM tbl_All_UTR_20090629 WHERE [fldPPMIP] =
'TMIDE'"

Which all looks fine, considering I selected TMIDE as the PPMIP value.

Can someone advise as to what's going wrong with this thing, as it's
driving me mental!!!

Many, many TIA.

Duncs

I'm guessing that PPMIP is a text field. If that's the case, you need
quotes
around the value:
Click to expand...
"WHERE [tbl_All_UTR_20090629].[PPMIP] = '" & Me.cboPPMIP & "'"
Click to expand...
Exagerated for clarity, that's
Click to expand...
"WHERE [tbl_All_UTR_20090629].[PPMIP] = ' " & Me.cboPPMIP & " ' "
Click to expand...
"Duncs" <[email protected]> wrote in message
Click to expand...
Nope, it's still not working.
Click to expand...
I've got the Bound column set to 1. But, like before, when I select a
company code from cboPPMIP and activate the cboCRN dropdown, a
parameter window appears with a title of the company code I selected
prior to activating it. If I then enter the code that I previously
selected, it then displays the CRNs associated with the company code.
Duncs
Click to expand...

cboCompany
RowSource - SELECT tblPPMIP.PPMIP FROM tblPPMIP ORDER BY [PPMIP];
----------------------------Make Sure u have Bound column of
cboCompany set to 1 as there is only one column.
AfterUpdate -
Private Sub cboPPMIP_AfterUpdate()
Dim sSQL As String
sSQL = "SELECT [tbl_All_UTR_20090629].[PPMIP]," & _
" [tbl_All_UTR_20090629].[CRN]," & _
" [tbl_All_UTR_20090629].[AMT]," & _
" [tbl_All_UTR_20090629].[ProperDate], " & _
" [tbl_All_UTR_20090629].[MSN], " & _
" [tbl_All_UTR_20090629].[MSN2], " & _
" [tbl_All_UTR_20090629].[SCNO] " & _
"FROM tbl_All_UTR_20090629 " & _
"WHERE [tbl_All_UTR_20090629].[PPMIP] = " &
Me.cboPPMIP.Value
------------------------------ Is it cboPPMIP or cboCompany as said
in ur first line of code...
Me.cboCRN.RowSource = sSQL
Me.cboCRN.Requery
End Sub
cboCRN
RowSource - SELECT tbl_All_UTR_20090629.CRN, tbl_All_UTR_20090629.AMT,
tbl_All_UTR_20090629.ProperDate, tbl_All_UTR_20090629.MSN,
tbl_All_UTR_20090629.MSN2, tbl_All_UTR_20090629.SCNO FROM
tbl_All_UTR_20090629;
I'm at a loss as to what's wrong so would appreciate some help.
TIA
Duncs- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populating a combo box.... 1
Heirarchical Combo Boxes? 3
Problem Synchronizing Combo Boxes (of a Form based on a table) 2
Autofilling a form based on combo box selection 8
Combo Box QUESTION 5
How to filter one drop down combo box to another 10
Text Box values based on Combo Box value 1
Combo Box Values 2

Top