Help on Fill Fields automatically

[sunflower]

Help on Fill Fields automatically

tblCONTACTS
FieldName Data Type
ContactID AutoNumber
Name Text
Phone Text
DepartmentNo Number

tblDEPTS
FieldName Data Type
DEPTID AutoNumber
DepartmentName Text
DepartmentNo Number

tblWORKORDER
FieldName Data Type
WOID AutoNumber
DEPTID Number
Requestor Text
Phone Text

On a form, on a Combo box control named “Requester”
I have the following event procedure:

Sub Requestor_Exit(Cancel As Integer)
Dim varPhone, varDEPTID As Variant
varPhone = DLookup("Phone", "tblCONTACTS", "Phone =[Phone] ")
varDEPTID = DLookup("DEPTID ", "tblDEPTS", "DEPTID =[ DEPTID] ")
If (Not IsNull(varPhone)) Then Me![Phone] = varPhone
If (Not IsNull(varDEPTID)) Then Me![DEPTID] = varDEPTID
End Sub


On exit, it fills both the Text boxes “Phone” and “DEPTID”…
WITH THE FIRST RECORD OF THE TABLES!!!

How do I get it to match the Requestor record?

any help is grealy appreciated

 

[Ken Snell [MVP]]

See this article at The ACCESS Web for how to let your combo box be your
assistant in this:
http://www.mvps.org/access/forms/frm0058.htm

 

[sunflower]

That worked so much easier...
Thank you so much

 

[Rob]

**ok this is what I got from following the link below. What I need to know
is where do I put this. Thanks in **advance for your patience.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

(Q) How do I have a textbox automatically fill in with a value after I
select an item from a combo box on a form (e.g., select a person's ID in a
combo box, and have the person's last name automatically display in a
textbox)?

(A) The way to do this depends upon whether the textbox is bound to a field
in the form's Record Source or not.

Unbound Textbox
In this example, we'll assume that your desired information is in a table
named tblPersons, and that this table contains four fields: ID (the person's
ID); FirstName (the person's first name); MiddleName (the person's middle
name); and LastName (the person's last name).

Set the Row Source Type property of the combo box (assume it's named
cboPersonID) to "Table/Query". Set the Row Source to a query that is based
on tblPersons and that selects the ID and LastName fields:



' ***** Code Start *****
SELECT ID, LastName
FROM tblPersons
ORDER BY ID;
' ***** Code End *****
Set the Bound Column property of cboPersonID to 1.
Set the Column Count property to 2.
Set the Column Widths property to this expression: 1";0".

Set the Control Source of a textbox (assume it's named txtPersonName) to
this expression:

=[cboPersonID].[Column](1)

Note that the Column property of a combo box is "zero-based," meaning that
the first column (field) in the combo box's Row Source is column 0, the
second column is column 1, etc.

When you make a selection of a person's ID in the combo box, the textbox
will automatically display the person's name. You can extend this example to
include more textboxes by adding more fields to the combo box's Row Source
query and setting the Control Source of each textbox to the appropriate
column number of the combo box.


Bound Textbox
We'll use the same setup for the combo box as described in the "Unbound
Textbox" example above. The difference in this situation is that an
expression cannot be used as the Control Source for the textbox. Instead, we
use VBA code on the AfterUpdate event of the cboPersonID combo box to place
the desired value into the txtPersonName textbox.


' ***** Code Start *****
Private Sub cboPersonID_AfterUpdate()
Me.txtPersonName.Value = Me.cboPersonID.Column(1)
End Sub
' ***** Code End *****


When you make a selection of a person's ID in the combo box, the textbox
will automatically display the person's name. You can extend this example to
include more textboxes by adding more fields to the combo box's Row Source
query and adding additional steps to the code so that each textbox receives
a value from the appropriate column number of the combo box.




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 

[Ken Snell [MVP]]

Where do you put what?

There are two examples in that article -- to which are you referring? What
have you tried to do?

--

Ken Snell
<MS ACCESS MVP>

**ok this is what I got from following the link below. What I need to
know is where do I put this. Thanks in **advance for your patience.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

(Q) How do I have a textbox automatically fill in with a value after I
select an item from a combo box on a form (e.g., select a person's ID in a
combo box, and have the person's last name automatically display in a
textbox)?

(A) The way to do this depends upon whether the textbox is bound to a
field in the form's Record Source or not.

Unbound Textbox
In this example, we'll assume that your desired information is in a table
named tblPersons, and that this table contains four fields: ID (the
person's ID); FirstName (the person's first name); MiddleName (the
person's middle name); and LastName (the person's last name).

Set the Row Source Type property of the combo box (assume it's named
cboPersonID) to "Table/Query". Set the Row Source to a query that is based
on tblPersons and that selects the ID and LastName fields:



' ***** Code Start *****
SELECT ID, LastName
FROM tblPersons
ORDER BY ID;
' ***** Code End *****
Set the Bound Column property of cboPersonID to 1.
Set the Column Count property to 2.
Set the Column Widths property to this expression: 1";0".

Set the Control Source of a textbox (assume it's named txtPersonName) to
this expression:

=[cboPersonID].[Column](1)

Note that the Column property of a combo box is "zero-based," meaning that
the first column (field) in the combo box's Row Source is column 0, the
second column is column 1, etc.

When you make a selection of a person's ID in the combo box, the textbox
will automatically display the person's name. You can extend this example
to include more textboxes by adding more fields to the combo box's Row
Source query and setting the Control Source of each textbox to the
appropriate column number of the combo box.


Bound Textbox
We'll use the same setup for the combo box as described in the "Unbound
Textbox" example above. The difference in this situation is that an
expression cannot be used as the Control Source for the textbox. Instead,
we use VBA code on the AfterUpdate event of the cboPersonID combo box to
place the desired value into the txtPersonName textbox.


' ***** Code Start *****
Private Sub cboPersonID_AfterUpdate()
Me.txtPersonName.Value = Me.cboPersonID.Column(1)
End Sub
' ***** Code End *****


When you make a selection of a person's ID in the combo box, the textbox
will automatically display the person's name. You can extend this example
to include more textboxes by adding more fields to the combo box's Row
Source query and adding additional steps to the code so that each textbox
receives a value from the appropriate column number of the combo box.




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

That worked so much easier...
Thank you so much