I want a form to autofill certain fields based on other fields within the same form

[Pedigreeman]

Please consider the following situation.

customer_ID
1
2
3

Customer_name
Bob
Henry
Jo

On the form I have a box for both the ID and the name. I know them by name rather than ID, so when I type 'Bob' I want the form to autofill '1' into the customer_ID box.

Any help would be much appreciated. If possible I want this form to correspond to one table only. (Ideally I do not want customer names and customer ID to be on a different table)

Many thanks,

Mark

 

[vbaInet]

Naturally you work the other way round, type ID to get the Name. The ID is there to be a unique identifier and two or more people can have the same name so it just won't work.

 

[Pedigreeman]

Naturally you work the other way round, type ID to get the Name. The ID is there to be a unique identifier and two or more people can have the same name so it just won't work.

Thanks for your response. Even if I change the ID, the Name does not automatically fill in itself either. I should have said, I am new to Access so need a bit more information - I am aware that it may have something to do with relationships (I have not set any self joins up for my table). In this particular case, although the ID is the unique identifier, I would like to be able to just enter the name, rather than the ID. I do not want to look up what ID number 'John Smith' on a list of 1000 numbers and names, when I could just type in his name in the first place. (actually I have the names on a drop down list).

 

[DCrake]

Contrary to what VbaINet says you are most likely to know the persons name than remembering their ID. So I suggest youuse a combo box with two colulmns ID (Hidden) and name. Then in the after update event of the combo

Me.TxtID = Me.Combo.Column(0)

 

[vbaInet]

Contrary to what VbaINet says you are most likely to know the persons name than remembering their ID.

I didn't say you're more likely to know the ID, I said you would normally type an ID to lookup the matching value. This is what the OP (#1) is about - looking up a value.

If you have two people named Bob, one with an ID of 1 and the other with an ID of 2, typing in Bob will return 1 as the ID (depending on how the recordset is sorted), but which Bob are you talking about? Did you really want Bob - 1 or Bob - 2?

 

[boblarson]

How about a combo box to select the name. The combo can be bound to the ID field and have the ID either showing or hidden. I prefer to give someone that instead of a text box to put in the name.

 

[Pedigreeman]

Contrary to what VbaINet says you are most likely to know the persons name than remembering their ID. So I suggest youuse a combo box with two colulmns ID (Hidden) and name. Then in the after update event of the combo

Me.TxtID = Me.Combo.Column(0)

Thanks for your suggestion.

so I created a 2 columned combo box with hidden ID and Name (called combobox140). I tried having the control source as 'Name'. This resulted in a dropdown list which, on selecting the desired name, makes a note of the corresponding ID in the Name field and does not store the Name anywhere.

So then I tried having the control source as 'ID'. On selection of the desired name, this resulted in the correct ID being stored in the correct field, but the Name field was not filled in.

Not sure what to do and I would appreciate your help. If it does need to involve some coding, please treat me like an idiot, when explaining

Many thanks

 

[Pedigreeman]

Basically I am asking how you get different columns within your combobox connected to their respective fields, so that they will update all the fields on selection of one particular value within the combobox. E.g. When selecting 'John Smith' from combobox list, it updates ID field, Name field, and for example, Age field

 

[vbaInet]

Do you mean it updates the fields or it filters the fields to only show records related to John Smith?

 

[boblarson]

it updates ID field, Name field, and for example, Age field

1. If the name field is in your table where the combo box gets it, you do NOT store it in the other table too. You just store ID.

2. You should not store Age. You should store birthdate (in the table with the person's information, not in this second table) and calculate age whenever you need it.

 

[Adam Caramon]

E.g. When selecting 'John Smith' from combobox list, it updates ID field, Name field, and for example, Age field

So, you have a table already with all of this information in it. Then, you have a form with a combo box. On this same form you have several other fields that pertain to the person in the combo box.

And your desired goal is to be able to select a name with the combo box, and then the other fields that pertain to that person selected will populate with the correct information from the table where this information is stored.

Am I understanding this correctly?

 

[Pedigreeman]

So, you have a table already with all of this information in it. Then, you have a form with a combo box. On this same form you have several other fields that pertain to the person in the combo box.

And your desired goal is to be able to select a name with the combo box, and then the other fields that pertain to that person selected will populate with the correct information from the table where this information is stored.

Am I understanding this correctly?

Adam, yes you have the situation exactly right, but it might clear things up if I explain what I am actually trying to do. I am trying to create a family tree. I have the columns in the table: ID, Name, Father_ID and Father_Name. I create a form for this table, which includes these 4 fields. I want to create a new entry which is the son of one of the fathers, so I load up the form. I enter the new name manually, which allocates an ID# automatically. Now I want to put the fathers name and ID into the form. So I select the Father_Name combobox on the form which opens up a list of ALL the names in the Name field. I chose the correct one, and then I want it to look up the corresponding ID of that particular name and put it into the form (and in doing so the underlying table) within the Father_ID box.

I know that this is made more difficult by the fact that the table is refering to itself, but if you have any suggestions I would be very grateful.

 

[Pedigreeman]

any ideas??

 

[Adam Caramon]

I don't have any experience working with this sort of a database. I'm thinking that there is probably better software out there already to handle this kind of thing.

That being said, take a look at the attached DB. It is incredibly simple, but maybe it will give you some ideas on 1 way that could possibly work. I don't claim it is the best way, but maybe it will work for you (or at least show you a way you hadn't considered).

This attachment is 2003 version.

 

[shall]

is it possible to have mulitple fields to choose which you want filled out and it would auto populate all of them regardless?

also is there a way to make the DB u created above to work on 2007 i tried the =cboFatherID.Column(2) but it keeps putting it in brackets

 

[Pedigreeman]

I don't have any experience working with this sort of a database. I'm thinking that there is probably better software out there already to handle this kind of thing.

That being said, take a look at the attached DB. It is incredibly simple, but maybe it will give you some ideas on 1 way that could possibly work. I don't claim it is the best way, but maybe it will work for you (or at least show you a way you hadn't considered).

This attachment is 2003 version.

Adam, thanks very much for taking the time to do that - that cleared things up for me and you have achieved what I am trying to do. My DB is slightly different in that there is only one field for the name (not first and last names) My combobox has 2 columns (FatherID and FatherName) I select the name, and it records the correct ID in the form and underlying table, however the name is not recorded in the underlying table - it is simply shown in the combobox. How do I get this display in the combobox to project itself onto the underlying table? The combobox's control source is already taken up with the FatherID field. I have attached an example of my problem. If you or anyone else can shed any light on this I would really appreciate it.

 

[Pedigreeman]

here it is

 

[Adam Caramon]

is it possible to have mulitple fields to choose which you want filled out and it would auto populate all of them regardless?

I'm not sure what you mean by this.

also is there a way to make the DB u created above to work on 2007 i tried the =cboFatherID.Column(2) but it keeps putting it in brackets

Are you typing it directly into the record source? The only reason I can think for brackets would be if you're using the Expression builder it may add those.

Adam, thanks very much for taking the time to do that - that cleared things up for me and you have achieved what I am trying to do.

You're welcome, and I'm glad it helped.

How do I get this display in the combobox to project itself onto the underlying table?

You don't, and you don't want to. Having a separate field for "Father Name" creates data redundancy. FatherID serves as a pointer to tblPeople.

Is there a particular reason you were trying to store Father Name in the table? You should be able to access it in queries and reports via FatherID.

 

[Pedigreeman]

Is there a particular reason you were trying to store Father Name in the table? You should be able to access it in queries and reports via FatherID.

Yes point taken and understood. Many thanks again!