Okay, good. now one other question and I think I have an idea.
Is cboLocName a bound control?
Are the controls you mentioned on the form already just hidden, or are they
on a popuf form?
--
Dave Hargis, Microsoft Access MVP
:
If the "Other" option is chosen, it would be a one-time entry. To put it in
context for you, these are the locations that we will do our inspections. We
inspect cars so often times the inspection takes place at a body shop, which
would be on the list, or the owner's residence, for which the address was
already input under owner info. But sometimes the car will be w/ a relative
or at the owner's workplace. In which case, the entry would be something
like "Uncle's House" or "AT&T Office" and then I'll have the LocAddress,
LocCity, LocState, etc. become visible for the complete address entry.
Obviously these entries would be only applicable to this one inspection for
this one person. Thanks for any other ideas you have!
:
Oh, yeah, I remember seeing that once before, but I don't remember what the
cure was.
There is one thing I am curious about. If you are wanting to put something
in the combo that is not in the list, which is a query on a table, how are
you going to have the other columns data available?
It may be you can change the order of what you are doing, that is set the
row source to "" first, then set the limit to list. If That doesn't work,
you might try a dummied up row source.
There also may be another idea. What are you going to put in the second
combo if Other is selected in the first?
--
Dave Hargis, Microsoft Access MVP
:
Yes, that is exactly what I want, but here's the error I'm getting:
Microsoft Office Access can't set the LimitToList property to No right now.
The first visible column, which is determined by the ColumnWidths property,
isn't equal to the bound column. Adjust the ColumnWidths property first,
then set the LimitToList property. Any suggestions for a workaround? I
really want to keep the box to showing the Name vs. the ID which is what this
error is referring to . Right now, my column widths are 0";1";1" so the
RepairerID isn't visible but it IS the bound column. Again, continued
thanks!
:
Maybe I am not totally understanding what it is you want to do. Are you
saying that if you choose Other from the Location Type, then you want to be
able to use a Location Name that is not in your location name combo?
If that is the case, then you would use the After Upate event of the
Location type to set the location name limit to list property. So, you would
use the filtering I poseted earlier if it is not Other and if it is, set the
location name limit to list to no.
If Me.cboLocType = "Other" Then
With Me.cboLocName
.LimitToList = False
.RowSource = vbNullString
End With
Else
Me.cboLocaName.Requery
End If
Note, the above is untested air code, so may take some tweeking.
--
Dave Hargis, Microsoft Access MVP
:
Thanks for that code. I input it as you said but the 2nd combo isn't
propagating - it's blank for all options. I was sure to add the
Me.cboLocName.Requery to the AfterUpdate event of the cboLocType too. Also,
is this going to allow the LimitToList to change to No so that should the
"Other" option in cboLocType be chosen, the user can add a not in list item?
Thanks!!
:
It isn't necessary to change entire row source. All you need to do is filter
the row source of the second combo on the value of the first:
SELECT [tblRepairer].[RepairerID], [tblRepairer].[RepairerName],
[tblRepairer].[RepairerAddress], [tblRepairer].[RepairerCity] FROM
[tblRepairer] WHERE [tblRepairer].[RepairerType] = """ & Me.cboLocType &
""";"
Then in the After Update event of cboLocType
Me.cboLocName.Requery
--
Dave Hargis, Microsoft Access MVP
:
I think, perhaps, I wasn't clear on the problem scenario. Here is my code:
Private Sub cboLocType_AfterUpdate()
On Error Resume Next
Select Case cboLocType.Value
Case "Body Shop"
cboLocName.RowSource = "SELECT [tblRepairer].[RepairerID],
[tblRepairer].[RepairerName], [tblRepairer].[RepairerAddress],
[tblRepairer].[RepairerCity] FROM [tblRepairer] WHERE
[tblRepairer].[RepairerType] = ""Body Shop"";"
Case "Other Residence"
cboLocName.LimitToList = False
End Select
End Sub
In order for the LimitToList to change, I have to change this box so that,
for instance, RepairerID is the displayed field vs. RepairerName should "Body
Shop" be selected from the 1st combo. It's my preference to have the ID
field be bound but the name displayed-- doing this by setting the column
count to 2 and the column sizes to 0";1" but this setting won't allow the
limit to list feature to change. Thanks for your continued help.
Pamela
:
It is too late in the After Update event. The Not In List event will fire
before any of the other events that would normally fire when you make a
change to the combo box's value.
You will need to deal with the exception in that event or you could set the
Limit To List property to No and deal with it in the After Update or Before
update, which ever would be appropriate.
--
Dave Hargis, Microsoft Access MVP
:
I am using Select Case on the AfterUpdate event of cboLocType to then filter
cboLocName based on a query with a Where clause for several different cases.
For example: cboLocType = "Body Shop", cboLocName would then show a list of
Body Shops which works fine. I also have an "Other" case that would be a
one-time entry for a location name. I now need the ability to have the
LimitToList feature change based on cboLocType and it seems that if I'm only
displaying the Name in the box while the ID is bound, it won't work...any
ideas on how to work around this???? Thanks so much!!
