Auto Populate Form Field

[jcbhydro]

Good Evening,

I have an entry form for a table named [Group Members]. The form has several fields deriving data from 2 other tables [Mail List] and [Groups] I wish to automatically display a Group ID when the relevant Group Name is selected from a drop down list.
The Row Source Code is as follows;
SELECT [GROUPS].[GROUPS ID}, [GROUPS].[GROUP NAME FROM GROUPS].
The Bound Column is 1.
This code fails to display the [GROUPS ID] when a Group Name is entered.
Which other controls do I need to modify to achieve this simple matter?

I have similar code installed to achieve auto display of Member ID when Member Name is entered. That code is equally ineffective.

Any suggestions would be exxtremely welcome.

Regards,

jcbhydro

 

[pbaldy]

That can't be the actual row source, but is the what you're after?

http://www.baldyweb.com/Autofill.htm

 

[jcbhydro]

Many thanks for your response.
I have read and attempted to apply the principles you outline in your htm file, but without success.
The code which I listed previously is intalled in the 'row source' for the [Group Name] field. The control source for the [Group ID] text box previously contained [Group ID] and has been modified to contain [Geoup ID].Column(0). The result is the dreaded #Name? message in the the relevant Form field.
I am obviously misinterpreting your suggested methodology, but I don;t see where. Any further assistance would be gratefully received.

Regards,

jcbhydro

 

[pbaldy]

Did you precede the combo reference with "="?

 

[jcbhydro]

Paul,
Thanks again. Yes I had included an = sign.

However, I have concluded that your alternative solution might be more appropriate to my needs.

I detail below my interpretation of your suggested coding;

Property Sheet [Group ID] Text Box
{NB Group Members is the table associated to form}
Control Source =[Group Members].[Group ID]

Property Sheet [Group Name] Combo
Control Source [Group Name]
Row Source SELECT [Group ID],[Group Name] FROM GROUPS
After Update Me.[Group ID]=Me.[Group Name].Column(0)

Sadly, I am unable to get a satisfactory result wwith this arrangement either.
The message #Name? occurs in the ;Group ID] control as before.

I am obviously still misinterpreting your suggestion.

Regards,

jcbhydro

 

[pbaldy]

Can you post the db here?

 

[Galaxiom]

=[Group Members].[Group ID]

That is invalid as a ControlSource.

The control source must be a simple field name in the RecordSource.

Access will not support fully qualified names if these are generated for ambiguous fieldnames due to the same fieldname being found in multiple tables joined in the query.

The usual workaround is to alias the names in the RecordSource query though I think the full disambiguated name can be surrounded by square brackets if that is the name in the query.

 

[jcbhydro]

Paul,

I have been looking for a protocol for posting a cutdown database, but haven;t found one.
Obviously, I need to delete the bulf of the records and modify those that remain, for security reasons.
Where should I look for guidancee.

jcbhydro

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[jcbhydro]

Paul,
as requested, attached please find the db file.
I have reduced all but 75 of the records and have done some severe editing for reasons of security. This should't affect the structure or coding of the databas.

I confidently predict that you will note many aspects of the database which are not strictly in accordance with recommended procedures

jcbhydro

 

[pbaldy]

Which form? I don't see that error on any of them.

 

[jcbhydro]

Sorry Paul,

As the suggested solution failed I removed it.

I will reinstate it and do another upload tomorrow.
I apologize for wasting your time.

jcbhydro

 

[jcbhydro]

Paul,

I hope that I have managed the upload correctly this time.

jcbhydro

 

[pbaldy]

I still don't see a #Name error on any of the 3 forms. Perhaps I need to see the eye doctor?

 

[jcbhydro]

Nothing wrong with your eyes as I will explain.
The problem relates to the [Group Members Entry Form].
The #Name error occurred when I was applying your non-saving option of autofill. This occurs when the control source of the [Group ID] text box is written as [Group Name].Column(0).
The #Name error does not occur when the 'saving' option is applied as in the uploaded database.. In that case the control source is written as [Group ID] in the [Group ID] textbox. & the afterupdate of the [Group Name] combo is written as Me.[Group ID]=Me.[Group Name].Column(0).
When a new record is added auto fill does not occur and an error 'Access cannot find the object Me.

I just cannot fathom how I am failing to implement your very concise text.

Regards,

jcbhydro

 

[pbaldy]

Ah. What I posted is VBA code, which you incorrectly have directly in the properties window. It belongs here:

http://www.baldyweb.com/FirstVBA.htm

"Me" is a shortcut to refer to the object (form, report) containing the code, and is only valid in VBA code, thus the error regarding it.

 

[jcbhydro]

Many thanks Paul,

That works beautifully! You have been extremely patient & helpful.
Things are so much simpler when you know what you are doing, or more precisely when one is led by the hand.

I am puzzled as to why others on the forum have emphatically stated that such a procedure should not be implemented.

Regards,

jcbhydro

 

[pbaldy]

Happy to help!

 

[jcbhydro]

Paul,
I need to bother you again with a supplementary but related question.

Is it possible to add a second line of code in an afterupdate event procedure to display and save data to a second text box.
My first line reads;
Me.[Group ID]=Me.[Group Name].Column(1) and that works, but I wish to add;
Me.Leader=Me.[Group Name].Column(2)

I don't receive an error message but it doesn't produce the required result. Is there a further control required to cope with 2 requests?

Regards,

jcbhydro

 

[pbaldy]

That should work. Make sure the column count property of the combo is 3.