Oh dear!!!!!!
I started working happily through my records updating the combo's, after
about a dozen records, Access seized & crashed. There appeared to be one dud
record in [Main Table], full of #####, which i've deleted and then compacted.
All looks Ok except my [Main Input Form] is completely done for, Access wont
even acknowledge it's existance ?
Do I delete it and start again ?
Is it my Combo code that's done it, or was that just a coincidence ?
Regards
:
I think I may finally have got there!! The following appears to be working,
I'll try it out for real and see what happens.
Many Thanks again for your advice.
Private Sub cboResource_AfterUpdate()
On Error Resume Next
Select Case Me.cboResource
Case "System Engineering Services"
cboCapability.RowSource = "SESPrimaryCapability"
Case "Management Services"
cboCapability.RowSource = "MSPrimaryCapability"
Case "Specialist Services"
cboCapability.RowSource = "SSPrimaryCapability"
End Select
End Sub
:
Hi, Thanks again. I’ve edited the code as below and removed the apostrophes,
and the quotation marks, deleted the old code and rebuilt it as below. It is
still not populating the ‘Row Source’ Property of the [cboCapability], though
I do now get the error message, “File doesn’t exist†or similar, despite
copying & pasting the table names back in to ensure no typo’s.
By entering a table in the Row Source Property of [cboCapability] I can get
it to display in the Control, but only the once!
Again, should I be altering some of the other Properties of [cboCapability]
e.g. ‘Row Source Type’ ?
Private Sub cboResource_AfterUpdate()
On Error Resume Next
Select Case Me.cboResource
Case "System Engineering Services"
cboCapability.RowSource = [SESPrimaryCapability]
Case "Management Services"
cboCapability.RowSource = [MSPrimaryCapability]
Case "Specialist Services"
cboCapability.RowSource = [SSPrimaryCapability]
End Select
End Sub
Thanks again for your attention.
:
First, this line:
Select Case "[cboResource]"
Should be:
Select Case Me.cboResource
All the lines where you are wanting to set the rowsource are commented out.
It is not necessary to requery the combo after assigning a rowsource.
--
Dave Hargis, Microsoft Access MVP
:
Hi, I think I have my combobox1 [cboResource] working as it should, so now
onto combobox2 [cboCapability] which should display values sourced from three
separate tables, depending on the selection made in [cboResource].
[cboCapability] has it’s ‘Control Source’ Property as ‘Capability’ Field in
[Main Table]
What should the ‘Row Source Type’ Property be set to, Table/Query, Value
List or Field List ?
The ‘Row Source’ Property is currently empty, but should get it’s value from
the ‘After Update’ Property of [cboResource], but clearly isn’t!
The code I have in the ‘After Update’ Property of [CboResource] is as
follows :
Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case "[cboResource]"
Case "System Engineering Services"
'cboCapability'.RowSource = "[SESPrimaryCapability]"
Case "Management Services"
'cboCapability'.RowSource = "[MSPrimaryCapability]"
Case "Specialist Services"
'cboCapability'.RowSource = "[SSPrimaryCapability]"
End Select
Me.cboCapability.Requery
End Sub
This is intended to pick up each of the three options in [cboResource] and
refer the Row Source of [cboCapability] to the relevant separate table, e.g.
[SESPrimaryCapability].
At the moment this doesn’t appear to be populating the Row Source Property
as it ought ?
Any suggestions much appreciated.
:
Many Thanks again, I'll look at the file again tomorrow, and if all OK will
move onto the second combo!!
:
Don't confuse Control Source with Row Source.
The Control Source property identifies to which form recordset field the
control is bound. It will display the current value of the field for the
current record and will update they underlying table when changed using the
combo box.
The row source property identifies what values will be displayed in the
combo.
In your case, the Control Source would be the Resource field in the main
table. The row source would be based on the tblResources.
Limit to List is your choice. If you don't want the user to enter new
Resource values from which they may select in the future, then Limit to List
= Yes is correct.
Locked = No is also correct. If Locked = Yes, then you wont be able to
select from the combo.
--
Dave Hargis, Microsoft Access MVP
:
Thank you for your reply. By making sure each step is correct, I hope to get
there in the end.
My one query is regards the ‘Control Source’ Property of [cboResource].
If I click on the down arrow on the property, I get a list of Fields in
[Main Table] only, If I click on ‘Resource’ Field the combobox appears to
work OK ? If instead I click the Expression Builder and navigate to the
‘Resource’ Field of [tblResource], giving me the expression
=[tblResource]![Resource] then although I still get the drop down list in
the combo, I can’t select a value from it. A solid black box remains in the
control window. Something is obviously wrong here ?
I have also set ‘Limit To List’ to ‘Yes’, & ‘Locked’ to ‘No’ Should they be
OK ?
I am aware of the ‘After Update’ event, but I’ll come to that once I’ve got
the above sorted, I want to do the ‘easy’ bits first!!
Many Thanks again.
:
First, a bit about naming. You are doing the correct thing. cboResource is
a very good name for the combo. It is best to have recordset field names and
form control names different. That way, neither Access nor you get confused
over which object is being addressed. Label captions mean nothing to Access.
They are only for us humans.
The control source of cboResource should be the field named resource in
tblResource.
Table/Query is correct for the row source type.
There is not enough info to tell you what other properties or methods should
be used in cboResource, with one exception. That is, when you get set up to
use the other combo, you will need to requery cboCapability in the After
Update event of cboResource.
Now, that is part of the technique for "cascading combos". The other part
is to filter cboCapability based on the value in cboResource; however, you
don't say how those relate, so I can't offer a suggestion on the code to do
that.
--
Dave Hargis, Microsoft Access MVP
:
Hi, Still having problems with this despite all previous Help/Demo’s, So back
to basics.
I have a [Main Table] and an [Input Form] based on that [Main Table].
A [Main Table] field named ‘Resource’ which is formatted as a bound combo
obtains its four values from a separate table [tblResource].This field
operates as it should, with a drop down list that you can select one value
from, which goes into the field.
The [Input Form] has a Control labeled as ‘Resource’, also formatted as a
bound combo, which in the Properties I have renamed [cboResource] to
distinguish it from the ‘Resource’ field in [Main Table] & [tblResource].
Is it OK to leave the label as ‘Resource’, but rename the Property as
[cboResource] ?
What should the ‘Control Source’ Property of this [cboResource] be ? Is it
the ‘Resource’ field in [Main Table] or is it direct from [tblResource] ?
The ‘Row Source Type’ Property for [cboResource] is set as ‘Table/Query’, Is
this correct ?
What should the ‘Row Source’ Property of [cboResource] be, again, is it the
‘Resource’ field of [Main table] or direct from [tblResource] ?
Do any other Properties have to be altered/set to make this combo work
correctly ?
Once this combo is working correctly I want to set a second combo
[cboCapability] displaying it’s values depending on the selection in
[cboResource], and taking those values from a separate table. But I will come
back to that once I know the first combo is correctly setup.
Many Thanks
