Control used to fill another control

[Gkirkup]

I have a subform on which an item number is entered in one control. I have a query which looks up the description, and would like to insert that description into the next control on the subform. I can't see a 'clean and simple' way to do that. What should I do?

 

[HiTechCoach]

I have a subform on which an item number is entered in one control. I have a query which looks up the description, and would like to insert that description into the next control on the subform. I can't see a 'clean and simple' way to do that. What should I do?

If you are using a combo box control to enter the item number then this is easy to do.

If you have the item number in the first column ( index = 0) and the description n the second column (index = 1) then you can use this in the control source of a text box:

=cboItemNumber.Column(1)

You will need to substitute the cboItemNumber with the name of you control.

This method also works well on a form in continuous view mode.


Another advantage od using a combo box is that you can validate the item numkber by limiting the input the list of vaild item numbers.

 

[missinglinq]

Or you could use DLookUp() function. Syntax would depend on whether the item number was an actual number or text:
For a numeric ItemNumber:

Private Sub FormItemNumber_Numeric_AfterUpdate()
 If Not IsNull(Me.FormItemNumber_Numeric) Then
   Me.FormDescriptionField = DLookup("DescriptionField", "YourDescriptionTable", "[ItemNumber_Numeric] = " & Me.FormItemNumber_Numeric)
 Else
   Me.FormDescriptionField = ""
 End If
End Sub

For a Text ItemNumber:

Private Sub FormItemNumber_Text_AfterUpdate()
If Not IsNull(Me.FormItemNumber_Text) Then
   Me.FormDescriptionField = DLookup("DescriptionField", "YourDescriptionTable", "[ItemNumber_Text] = '" & Me.FormItemNumber_Text & "'")
 Else
   Me.FormDescriptionField = ""
 End If
End Sub

As Coach says, if these ItemNumbers are complex, or many in numbers, the combobx would be a good idea to avoid mistakes in entering them.

 

[Gkirkup]

Coach: I often have a problem with combo boxes, and that happened again here. I selected my query from the combo box wizard, then my two fields. But in the 'adjust column width' screen, both columns were shown with '#Name?'. Any idea what I might be doing wrong?

Robert

 

[HiTechCoach]

Coach: I often have a problem with combo boxes, and that happened again here. I selected my query from the combo box wizard, then my two fields. But in the 'adjust column width' screen, both columns were shown with '#Name?'. Any idea what I might be doing wrong?

Robert

I have never had any issues like that with a combo box. I even use this wizard a lot.

I use then a lots. Just about every form I create has at least one combo box.

Is it possible that you could post a sample of your form, query, and the tables that contains some sample data?

 

[Gkirkup]

Coach: OK, I fixed it - I had a reference to a form control in my query which was not needed.
My only remaining problem is that =cboItemNumber.Column(1) always returns the result of the first line in the combo box display, even if you select an item further down the list. Is there any way to return the Column(1) item actually selected?

Robert

 

[HiTechCoach]

Coach: OK, I fixed it - I had a reference to a form control in my query which was not needed.
My only remaining problem is that =cboItemNumber.Column(1) always returns the result of the first line in the combo box display, even if you select an item further down the list. Is there any way to return the Column(1) item actually selected?

Robert

Sure can. Are you setting the control source of a text box?

Text Box Control Source:
=cboItemNumber.Column(1)

 

[Gkirkup]

Coach: The control source of the text box is a field from a table. I tried setting the control source to cboItemNumber.Column(1) but of course I lose the link to the table, and existing data is not displayed.
I have set the 'on click' property of the combo box to update the text box. That works, but always sends over the first item from the combo list, not the selected item.

Robert

 

[HiTechCoach]

Coach: The control source of the text box is a field from a table. I tried setting the control source to cboItemNumber.Column(1) but of course I lose the link to the table, and existing data is not displayed.
I have set the 'on click' property of the combo box to update the text box. That works, but always sends over the first item from the combo list, not the selected item.

Robert

Robert,

I would then use the After update event of the combo box to set the value in to the text box.

Something like:

Me.txtControlName = Me.cboItemNumber.Column(1)

 

[Gkirkup]

Coach: I used after update, but I still always get the first item in the list returned, not the item that was selected.

Robert