Combobox autocomplete taking data from within subform only?

[jw5et]

I have a form which you can enter comments on individuals. There is a text box (Informant), where the same data was entered over & over. To make data entry faster, I've made Informant into combo box, where the query selects itself as the row source & autocomplete is on.

I would like to have the query select out a subset of all the Informant where Individual is the current record I'm working with. Right now, when I move from Record1 to Record2, it populates the combo box options of Informant with every possiblity in both Record 1 & Record2. The RowSource of Informant reads as: SELECT DISTINCT [Comment Table].[Informant] FROM [Comment Table];.

How do I limit the list to just those within Record 1? I'm fairly certain I have to do something with the CaseNumber variable, which links the the Case table with the Comment Table, but I'm not sure what.

I've tried setting the Row Source to: SELECT DISTINCT [Comment Table].[Informants / Reference] FROM [Comment Table] WHERE ((([Comment Table].[CaseNumber])=[Me]![CaseNumber]));. But it just asks me to enter the parameter for [Me]![CaseNumber].

In case any of that was unclear, Case Table contains the information on all the individuals, and Comment Table contains all the comments made. CaseNumber the one->many relationship link.

any hints? thanks!

[puppydogbuddy]

I have a form which you can enter comments on individuals. There is a text box (Informant), where the same data was entered over & over. To make data entry faster, I've made Informant into combo box, where the query selects itself as the row source & autocomplete is on.

I would like to have the query select out a subset of all the Informant where Individual is the current record I'm working with. Right now, when I move from Record1 to Record2, it populates the combo box options of Informant with every possiblity in both Record 1 & Record2. The RowSource of Informant reads as: SELECT DISTINCT [Comment Table].[Informant] FROM [Comment Table];.

How do I limit the list to just those within Record 1? I'm fairly certain I have to do something with the CaseNumber variable, which links the the Case table with the Comment Table, but I'm not sure what.

I've tried setting the Row Source to: SELECT DISTINCT [Comment Table].[Informants / Reference] FROM [Comment Table] WHERE ((([Comment Table].[CaseNumber])=[Me]![CaseNumber]));. But it just asks me to enter the parameter for [Me]![CaseNumber].

In case any of that was unclear, Case Table contains the information on all the individuals, and Comment Table contains all the comments made. CaseNumber the one->many relationship link.

any hints? thanks!

If case number is a column in your combobox, you can use the column index method as shown below. I am assuming that the reason that you are using the keyword "Distinct" is because the Informant combobox references the informant more than once using different case numbers.

Keep in mind that In Access, column indexes start with 0 for the first column on the left.....In the statement below, I am assuming that Case number is the third column in your combobox and therefore, has an index# of 2.

SELECT DISTINCT [Comment Table].[Informants / Reference] FROM [Comment Table] WHERE ((([Comment Table].[CaseNumber])=[Me]![Informant].Column(2)));

If the above does not work, then provide more detail as to what is contained in the combobox and where on the form is the Case Number referenced.

[jw5et]

If case number is a column in your combobox, you can use the column index method as shown below. I am assuming that the reason that you are using the keyword "Distinct" is because the Informant combobox references the informant more than once using different case numbers.

Keep in mind that In Access, column indexes start with 0 for the first column on the left.....In the statement below, I am assuming that Case number is the third column in your combobox and therefore, has an index# of 2.

SELECT DISTINCT [Comment Table].[Informants / Reference] FROM [Comment Table] WHERE ((([Comment Table].[CaseNumber])=[Me]![Informant].Column(2)));

If the above does not work, then provide more detail as to what is contained in the combobox and where on the form is the Case Number referenced.

I tried typing in directly what you mentioned and got the following error, "Undefined function 'Me!Informant.Column' in expression.

I'm not sure what you mean by Case Number being the 3rd column in my combobox. I curently only have 1 column in the combobox. Let me try to describe what I am trying to accomplish a little more.

I have 2 tables (for our purposes). 1) Case Table has each Case's information: case num, name, date, data entry info, etc. 2) Comment table has information on each comment that is tied to each case: comment num, case num, comment, info on each comment including the field in question (informant/reference). Comment Table is related to Case Table in many->one relationship.

Each informant can make multiple comments related to each case. Informant A can state 4, 5, or 10 comments for Case A. For each case, there is typically a different set of informants, and there may be 4 or 5 different informants for each case.

Right now, instead of typing Informant A over & over & over, I made [Informant / Reference] into a combo box that autofills by selecting disctinct records from itself which speeds the entry process considerably. However, instead of picking only the informants of the current case, the combobox allows you to select informants from any case. With thousands of cases and thousands of informants, the autofill in feature is potentially hazardous especially since many of the informants may have similar names.

What I would like to do, is filter the [Informant / Reference] combo box down to just those entries that have the same Case number. Right now, the form looks as follows:
-Case stuff-
-Comment subform (with Case number not on the form)-

[jw5et]

I tried something similar to what you mentioned, and it almost works.

What I did was under the comment subtable, I set a new textbox to display the Case Number and labeled it Case Num Text. The query for the combo box now reads as "SELECT DISTINCT [Comment Table].[Informants / Reference], [Comment Table].[Case Number] FROM [Comment Table] WHERE ((([Comment Table].[Case Number]) Like [Case Num Text]));"

The width 2nd column of the combobox is set to 0" so it is invisible. The first [Informant / Reference] I work with correctly pulls the data from the case I am currently working on. However, when I move on from the first case to another one, it continues to pull combobox data from the first case I was working with. I have to close the form & reopen it for it to use the correct [Case Num Text]. The [Case Num Text] does display the correct Case Number. It seems the query, however, does not use the updated [Case Num Text].

[puppydogbuddy]

I tried typing in directly what you mentioned and got the following error, "Undefined function 'Me!Informant.Column' in expression.

I'm not sure what you mean by Case Number being the 3rd column in my combobox. I curently only have 1 column in the combobox. Let me try to describe what I am trying to accomplish a little more.

I have 2 tables (for our purposes). 1) Case Table has each Case's information: case num, name, date, data entry info, etc. 2) Comment table has information on each comment that is tied to each case: comment num, case num, comment, info on each comment including the field in question (informant/reference). Comment Table is related to Case Table in many->one relationship.

Each informant can make multiple comments related to each case. Informant A can state 4, 5, or 10 comments for Case A. For each case, there is typically a different set of informants, and there may be 4 or 5 different informants for each case.

Right now, instead of typing Informant A over & over & over, I made [Informant / Reference] into a combo box that autofills by selecting disctinct records from itself which speeds the entry process considerably. However, instead of picking only the informants of the current case, the combobox allows you to select informants from any case. With thousands of cases and thousands of informants, the autofill in feature is potentially hazardous especially since many of the informants may have similar names.

What I would like to do, is filter the [Informant / Reference] combo box down to just those entries that have the same Case number. Right now, the form looks as follows:
-Case stuff-
-Comment subform (with Case number not on the form)-

OK all you need to do is bring up the query grid for your combobox's row source, drag case# to the grid (leave the show box unchecked if you don't want it to display in the combobox. In the criteria row of the case # column of the query grid, place the following: = nz(Forms!yourFormName![case num],0)
then click ok and test it to see how it is working. Note that I used nz function to provide for instances where case num not entered (IsNull).

[jw5et]

OK all you need to do is bring up the query grid for your combobox's row source, drag case# to the grid (leave the show box unchecked if you don't want it to display in the combobox. In the criteria row of the case # column of the query grid, place the following: = nz(Forms!yourFormName![case num],0)
then click ok and test it to see how it is working. Note that I used nz function to provide for instances where case num not entered (IsNull).

hmm.. this is very close to what I want. I think I'm messing up syntax somewhere. I put in, "=nz([Forms]![Comment Table Subform]![Case Number],0)" because I assume I should have changed a few things in what you mentioned above. When I enter into the field, it askes me what [Forms]![Comment Table Subform]![Case Number] is. When I manually type in the Case Number, it works. But of course, I'd rather not type it in each time.

*edit* nvm, I changed the form name to [Case Comments Entry] which is the mater form, and not the subform, and it worked! :) However, now I still have the same not updating problem as in the post #4 above. :(

[puppydogbuddy]

hmm.. this is very close to what I want. I think I'm messing up syntax somewhere. I put in, "=nz([Forms]![Comment Table Subform]![Case Number],0)" because I assume I should have changed a few things in what you mentioned above. When I enter into the field, it askes me what [Forms]![Comment Table Subform]![Case Number] is. When I manually type in the Case Number, it works. But of course, I'd rather not type it in each time.

The combo box and the textbox where you normally enter the case number are on the main form, aren't they? If so the reference should be :

nz(Forms!YourMainFormName![nameOfTextboxWhereYouEnterCaseNumber],0)

[jw5et]

Alright, I tried the following expression:
"Private Sub Informants___Reference_GotFocus()

Dim ctlCombo As Control

' Return Control object pointing to a combo box.
Set ctlCombo = Me![Informants / Reference]

' Requery source of data for list box.
ctlCombo.Requery

End Sub"

Everything seems to work great for my Design Master. However, with the working copy, I seem to be back to square one. It does not filter down the specific Case Number. However, if I remove the Event Procedure the properties, it does filter. The problem seems to be with the code itself.

[puppydogbuddy]

Alright, I tried the following expression:
"Private Sub Informants___Reference_GotFocus()

Dim ctlCombo As Control

' Return Control object pointing to a combo box.
Set ctlCombo = Me![Informants / Reference]

' Requery source of data for list box.
ctlCombo.Requery

End Sub"

Everything seems to work great for my Design Master. However, with the working copy, I seem to be back to square one. It does not filter down the specific Case Number. However, if I remove the Event Procedure the properties, it does filter. The problem seems to be with the code itself.

If your combo is named [Informants / Reference], here is all you need to do:

Private Sub {Informants / Reference]_AfterUpdate()
Me![Informants / Reference].Requery

End Sub