Autofill of second field in a Form

[jcbhydro]

Good Evening,

I have an entry form with a drop down list of Members Names derived from a Members table. When I select a nameon the form from the drop down list I want the Member ID field to auto fill from the same table, where Member ID is a foreign key in the table associated with the form and PK in the Members table.

I have studied numerous references on the Forum to similar autofill problems but I am unable to achieve the required result.



Should it not be possible to use a simple code in the Property Sheet Afterupdate event to achieve autofill. If so how would the code read?

Any suggestions would be gratefully received

Regards,

jcbhydro

[pbaldy]

Typically the combo would contain both fields, and the ID field would be the bound column (it could be hidden). The combo wizard will typically do that by default. That way no code is required.

[Dal Jeanis]

First, load the dropdownbox with 2 columns, both the names and the memberid, but you can make the columnwidth for the member ID 0", so that nobody has to look at it.

If the combobox is bound to the field you want to contain the key, then you're done - no code at all. If it's unbound, then you'll want the afterupdate of the unbound control to set the value of the bound control to the appropriate value.

[jcbhydro]

Thank you PBaldy & Dal Jeanis for your rapid response to my query
Unfortunately my Access experience to date is somewhat limited and I do not understand how to implement your suggestions. Perhaps if I inform you of the current Property Sheet Row source reference for 'Member Name' you will be able to advise me further:
SELECT [Mail List].[Member ID], [Mail List].[Member Name], [Mail List].[Surname] FROM [Mail List] Order BY Surname;

With this arrangement is there an AfterUpdate expression I can use for the Member ID field which will achieve autofill.

[Dal Jeanis]

So, you already have it there. What are the values for BoundColumn and for ColumnWidths? What is the data source for the combo box?

You're almost done alrady, we just have to figure out which piece is missing.

[jcbhydro]

Thank you for your further comment.

The property sheet for the [Member Name] Field shows the following;
Bound Column Value 1 (presumably this means the 2nd column, ie [Member Name]
The column count is 3 (this presumably excludes the 'Order' expression which is unticked in the Combo Box. The 1st 3 columns are ticked.)
The column widths are :0, 2.54, 2.542 cms.
The data source for all 4 columns is the main table [Mail List]

Does this provide you with the required information?

Regards,

jcbhydro

[pbaldy]

The bound column is not zero based, so 1 is the ID field. Is the control source property set to the correct field?

[Dal Jeanis]

So, if the control source of the combobox is set as the memberid on the underlying table for the form, then you shouldn't have to do anything else. The only reason you'd have to have any code is if you were, say, selecting the combobox on the main form then entering data on a subform, and wanting that data to default to the value on the main form.

[jcbhydro]

I'm afraid that In my inexperience, I don't understand PBaldy's question.
However, I believe that I have a clue to the problem. When I input a Member Name on the Form, the Member ID field does not autofill, but if I then move to make a further record entry, I receive an error message which reads ; (The database engine cannot find a record in the table 'Mail List' with key matching field Member ID'). Does this message cast any light on the problem?

jcbhydro

[Dal Jeanis]

What is the form's record source? Is it a query or a table?

What is it that you are trying to enter? What table will the records go into, and what other tables does that record have to be linked to?

[jcbhydro]

Following your query as to the Form's Record Source, I have examined this on the Form Property and it certainly looks as though it includes a mass of expressions, probably most of which are incorrect. They were certainly not written there but have possibly been generated as a result of invalid procedures on my part.

The Record Source states:
SELECT GroupMembers.*, [Mail List].[Member Name] AS [Member Name_Mail List], [Mail List].[Member Name] AS [Member Name_Mail List1] FROM [Mail List] LEFT JOIN GroupMembers ON [Mail List].[Member ID] = GroupMembers.[Member ID];

There are 3 tables; [Mail List]. [Groups] & [Group Members]
The PK's are [Member ID] and [Group ID] respectively, each of which is a FK in the 3rd table.
The Entry Form incorporates selection from a dropdown list of Member Names and I need to autofill the relevant Member ID field.
The entry form also incorporates a dropdown list of Group Names for selection and permits entry of a Group ID.
The data from the entry form is stored in the [Group Members] table.

I hope the above answers your query and that the Form Record Source can be modified to achieve my requirements.

Regards,

jcbhydro