Getting data from a table into a textbox

[bvdahl]

fito Hello,

I am a newbie at this, so I need some help on a very fundamental level.

I have two tables: orders and customers which have a relation in the field
CustomerID. I also have a form called salesorders. The first field in this
form, is the customerID field. There is also a text box called customername.

What I want to do, is to fill this textbox with the customers name when I
enter the customerID in my form, but I cant figure out how.

Hope this is clear, and that someone can help me.

Thx

Baard

 

[John Vinson]

fito Hello,

I am a newbie at this, so I need some help on a very fundamental level.

I have two tables: orders and customers which have a relation in the field
CustomerID. I also have a form called salesorders. The first field in this
form, is the customerID field. There is also a text box called customername.

What I want to do, is to fill this textbox with the customers name when I
enter the customerID in my form, but I cant figure out how.

Hope this is clear, and that someone can help me.

It's certainly NOT necessary, or wise, to store the customername in
any other table.

Why expose the customerID's *at all*? You could put a Combo Box on the
form, based on the Customer table; the combo wizard will walk you
through this, and it will let you *store* the CustomerID while
*displaying* the customer name.

Even if the customer ID's are meaningful to the user (i.e. not just an
autonumber), you can still use a combo box; base it on a query
selecting both the ID and the name, and set the textbox's Control
Source to

=comboboxname.Column(n)

where (n) is the zero based index of the field you want to see; e.g.
if your combo's query is something like

SELECT CustomerID, LastName & ", " & FirstName, Phone, Address
FROM Customers
ORDER BY LastName, FirstName;

you could use Column(1) to get the "Doe, John" name displayed.

John W. Vinson[MVP]

 

[bvdahl]

I get your point, and maybe my example was a bad one, but I would really like
to know how to do this. I can use in other places in my project as well.

Thx

Baard

 

[John Vinson]

I get your point, and maybe my example was a bad one, but I would really like
to know how to do this. I can use in other places in my project as well.

<shrug>

Ok, if you insist.

The simplest way is to use VBA code in the afterupdate event of the ID
textbox. Frex:

Private Sub txtID_AfterUpdate()
Me!txtName = DLookUp("[Namefield]", "[Customers]", "[ID] = " _
& Me!txtID)
End Sub

In other words, use DLookUp to extract the value from one table and
set a bound control on the form to that value.

John W. Vinson[MVP]

 

[bvdahl]

Thank you very much...and I WILL put your first advise to good use as well

Baard

I get your point, and maybe my example was a bad one, but I would really like
to know how to do this. I can use in other places in my project as well.

<shrug>

Ok, if you insist.

The simplest way is to use VBA code in the afterupdate event of the ID
textbox. Frex:

Private Sub txtID_AfterUpdate()
Me!txtName = DLookUp("[Namefield]", "[Customers]", "[ID] = " _
& Me!txtID)
End Sub

In other words, use DLookUp to extract the value from one table and
set a bound control on the form to that value.

John W. Vinson[MVP]