How to combine the two fields in a lookup

[rtcary]

The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?



Note: I just did it in the Lookup query. However I need help with the next 2 questions.

Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.

And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?

Sorry for beginner's question, but after 25 years working with Delphi, the Access UI is completely new for me. Fortunately there are a lot of similar concepts.

With great appreciation,

Todd

[ItsMe]

Many folks here avoid using Lookup fields at the table level. Is there a reason you chose to use a Lookup field?

[Ruegen]

See No. 2

http://access.mvps.org/access/tencommandments.htm

There may be another way to do the same things - perhaps we can help in that way?

[June7]

Why would you store the name and not ID?

Changing those properties of field in table will not automatically be reflected on already existing control. Often will be prompted to determine if you want changes to cascade out to controls bound to field but I am not sure what circumstances will provoke that prompt.

Delete the label and then paste it back - this will break link with data control.

[rtcary]

I would label that "beginner's ignorance". And I am beginning to understand why doing it this way is NOT good.

Todd

[Ruegen]

I would label that "beginner's ignorance". And I am beginning to understand why doing it this way is NOT good.

Todd

Concantination can be done in a query using the two fields - that way you don't store anything and save space.

In the table (table B) you want to store the id of the named person (table A - let's call it users) and not the name. That way you can change the name in the table A and the id number stays the same.

A lookup won't do this.

What you want in table B is a reference to table A's id. If table A's primary key is userID then you want a field called userID in table B too (this is called a foreign key field)

In userID field in table B you store the number of table user's id

You can do this on by making a form that has a combo box with table b as the form's recordset and tie the combo box to userid field

In the combo box you have the names pulled from table A (users) and when you select the name you want - it will store that id in the field (control source) that is tied to the control box.

[rtcary]

See No. 2

http://access.mvps.org/access/tencommandments.htm

There may be another way to do the same things - perhaps we can help in that way?

Yes, I do understand Dr. Dodd's rules. My problem is learning how to use the Access tools to setup an application with a normalized data structure for a local organization. Usually I would do this in Delphi using SQL Server, but that would tie me to the organization.

The AdTbl has three fields:
AdID: int (autoincrementing primary key)
AdDescription: text (30)
AdAmount: int (currency)

ClientTbl
ClID: int (autoincrementing primary key)
ClCompany: text (35)
ClContact: text (30)
ClEmail: text (35)
ClPhone: text (14)
AdID: (link to AdTbl)
MemID: (link to MemberTbl)
ClDatePaid: date/time
ClDateModified: date/time

What I am trying to learn is how, with the Access tools, to have a ComboBox on the Form so the user selects the Description/Amount and have those values show on the Form. Of course, the link to the AdTbl is stored in the ClientTbl (AdID)

Is there a tutorial on how this is done?

Todd

[June7]

The AdTbl info can be displayed in combobox dropdown list as multiple columns, however, only one column can be displayed in the box.

http://www.datapigtechnologies.com/f...combobox3.html

You can concatenate description and amount fields and this can be a column in the dropdown list and also display in the box.

It both cases it is the AdID that is saved.

With this table arrangement, each client can have only one ad - is that what you want?

[rtcary]

The AdTbl info can be displayed in combobox dropdown list as multiple columns, however, only one column can be displayed in the box.

http://www.datapigtechnologies.com/f...combobox3.html

You can concatenate description and amount fields and this can be a column in the dropdown list and also display in the box.

It both cases it is the AdID that is saved.

With this table arrangement, each client can have only one ad - is that what you want?

Thank you! That was the help I needed.

Todd