urgent - combo box row source

[JulieD]

hi all

i know that i can filter the elements in one combo box based on a selection
in another combo box but i can't seem to get this one to work:

combobox1 has lists of contracts - "A", "B", "C", "D", "E" etc
combobox2 has lists of aircraft - "plane1", "plane2", "plane3" etc

in the aircraft table there is a contract field,
plane1 is contract "A"
plane2 is contract "A"
plane3 has no contract assigned
plane4 is contract "B"
plane5 has no contract assigned
"C", "D" & "E" have not been used yet in the aircraft table

what i would like to do is in combobox2 display plane1 & plane2 if "A" is
selected (in combobox1), and plane4 if "B" is selected. However, if "C",
"D" or "E" is selected then i would like plane3 & plane5 displayed.

I really need help with this urgently because once this is done my database
is finished (yay!).

Cheers
JulieD

 

[JulieD]

here's my code to date:

Private Sub ContractID_AfterUpdate()
'select the aircraft that match the contract type
Set dbs = CurrentDb

strInput = Me![ContractID]
strSQL = "SELECT TBL_Aircraft.[AircraftID], TBL_Aircraft.[CompanyName] &
"" "" & TBL_Aircraft.[AircraftName] AS Aircraft,
TBL_Aircraft.[ContractType] from TBL_Aircraft where ContractType = '" &
strInput & "'"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF = True And rst.BOF = True Then
strSQL = "SELECT AircraftID, [CompanyName] & ' ' & [AircraftName] AS
Aircraft, ContractType from TBL_Aircraft"
Set rst = dbs.OpenRecordset(strSQL)
End If


End Sub

now all i need to do is to get the aircraftid combobox to use the strSQL as
its rowsource
i've tried

me![aircraftid].rowsource = strSQL

but i get an "object doesn't support this property or method"

Cheers
JulieD

 

[tina]

not sure why you're creating a recordset. all you need for
the combobox rowsource is the SQL statement. i tried a
simple test myself in VBA, assigning a string variable as
the rowsource for a combobox, just as you did below (your
last line of code). my string variable contained a simple
SQL statement. worked fine, so your idea apparently is
possible to do. (i'm using Win2kPro and A2k.)
perhaps your SQL statement has an error in it. remember
SQL statements in VBA are not checked for proper syntax,
etc, before runtime.
have you tried pasting your SQL statement into a new
query, in SQL view - and then running the query to see if
it works?

-----Original Message-----
here's my code to date:

Private Sub ContractID_AfterUpdate()
'select the aircraft that match the contract type
Set dbs = CurrentDb

strInput = Me![ContractID]
strSQL = "SELECT TBL_Aircraft.[AircraftID], TBL_Aircraft.[CompanyName] &
"" "" & TBL_Aircraft.[AircraftName] AS Aircraft,
TBL_Aircraft.[ContractType] from TBL_Aircraft where ContractType = '" &
strInput & "'"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF = True And rst.BOF = True Then
strSQL = "SELECT AircraftID, [CompanyName] & ' ' & [AircraftName] AS
Aircraft, ContractType from TBL_Aircraft"
Set rst = dbs.OpenRecordset(strSQL)
End If


End Sub

now all i need to do is to get the aircraftid combobox to use the strSQL as
its rowsource
i've tried

me![aircraftid].rowsource = strSQL

but i get an "object doesn't support this property or method"

Cheers
JulieD

hi all

i know that i can filter the elements in one combo box

based on a
selection

in another combo box but i can't seem to get this one to work:

combobox1 has lists of contracts - "A", "B", "C", "D", "E" etc
combobox2 has lists of aircraft - "plane1", "plane2", "plane3" etc

in the aircraft table there is a contract field,
plane1 is contract "A"
plane2 is contract "A"
plane3 has no contract assigned
plane4 is contract "B"
plane5 has no contract assigned
"C", "D" & "E" have not been used yet in the aircraft table

what i would like to do is in combobox2 display plane1 & plane2 if "A" is
selected (in combobox1), and plane4 if "B" is selected. However, if "C",
"D" or "E" is selected then i would like plane3 & plane5 displayed.

I really need help with this urgently because once this

is done my
database

is finished (yay!).

Cheers
JulieD

.

 

[JulieD]

Hi Tina

thanks for the reply - i still couldn't get your suggestion to work and then
i discovered that i was using the wrong control name - ooops

it's fine now, thanks

Cheers
Julie

not sure why you're creating a recordset. all you need for
the combobox rowsource is the SQL statement. i tried a
simple test myself in VBA, assigning a string variable as
the rowsource for a combobox, just as you did below (your
last line of code). my string variable contained a simple
SQL statement. worked fine, so your idea apparently is
possible to do. (i'm using Win2kPro and A2k.)
perhaps your SQL statement has an error in it. remember
SQL statements in VBA are not checked for proper syntax,
etc, before runtime.
have you tried pasting your SQL statement into a new
query, in SQL view - and then running the query to see if
it works?

-----Original Message-----
here's my code to date:

Private Sub ContractID_AfterUpdate()
'select the aircraft that match the contract type
Set dbs = CurrentDb

strInput = Me![ContractID]
strSQL = "SELECT TBL_Aircraft.[AircraftID], TBL_Aircraft.[CompanyName] &
"" "" & TBL_Aircraft.[AircraftName] AS Aircraft,
TBL_Aircraft.[ContractType] from TBL_Aircraft where ContractType = '" &
strInput & "'"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF = True And rst.BOF = True Then
strSQL = "SELECT AircraftID, [CompanyName] & ' ' & [AircraftName] AS
Aircraft, ContractType from TBL_Aircraft"
Set rst = dbs.OpenRecordset(strSQL)
End If


End Sub

now all i need to do is to get the aircraftid combobox to use the strSQL as
its rowsource
i've tried

me![aircraftid].rowsource = strSQL

but i get an "object doesn't support this property or method"

Cheers
JulieD

hi all

i know that i can filter the elements in one combo box

based on a
selection

in another combo box but i can't seem to get this one to work:

combobox1 has lists of contracts - "A", "B", "C", "D", "E" etc
combobox2 has lists of aircraft - "plane1", "plane2", "plane3" etc

in the aircraft table there is a contract field,
plane1 is contract "A"
plane2 is contract "A"
plane3 has no contract assigned
plane4 is contract "B"
plane5 has no contract assigned
"C", "D" & "E" have not been used yet in the aircraft table

what i would like to do is in combobox2 display plane1 & plane2 if "A" is
selected (in combobox1), and plane4 if "B" is selected. However, if "C",
"D" or "E" is selected then i would like plane3 & plane5 displayed.

I really need help with this urgently because once this

is done my
database

is finished (yay!).

Cheers
JulieD

.