Populating a text box from a combo box 1

[MissTinSC]

I am having a problem and would love to have your help.

I have a MainDatafrm that is built from the MainDatatbl. The MainDatatbl has a field for LeadTeacher and also ClassName. I also have a Classtbl that serves as the list table that enables a data entry person to select only the classes included in the table. The Classtbl has a field for LeadTeacher and ClassName - populated by the db manager.

The problem is this:
I would like to have two fields on the MainDatafrm: one of them a combo box that allows the user to select a className from the Classtbl and a field that automatically fills with the name of the LeadTeacher (from the Classtbl). Making the selection with the combo box also needs to enter the selected information into the MainDatatbl.

I have used several suggestion from this forum's threads. Either I have not rightly understood the programming directions (quite likely) or my problem is unique to the ones that appeared similar... I am at my wit's end - I am pretty sure it can be done...
Thanks for any advice/help you can send my way.

MissTinSC

 

[TheAceMan1]

How are ya MissTinSC . . . . .

I'm detecting an ambiguity here. You need to post the tables, and the relationships as they appear in the [blue]Relationships Window[/blue] . . . . .

See Ya! . . . . . .

 

[MissTinSC]

These are, generally, the two tables involved:

Classtbl
ClassID (primary key)
LeadTeacher
Classroom
Assistant

MainDatatbl
StudentID (primary key)
FName
LName
Street
City
Phone
Teacher (lookup)
Class (lookup)
ClassIds (long integer)

Relationship is

MainDataTbl: ClassIds...(many)
Classtbl:ClassID...(one)

This is a Many-to-One relationship

I hope this clarifies.

Thanks,

MissTinSC

 

[KenReay]

Hi

Sounds like what you need is a two column combo box based on Classtbl, the columns would be ClassId and LeadTeacher, column widths (assuming you do NOT want to see LeadTeacher in the drop down list would be 1;0, say we call this combo box cboMyCombo

make a text box control on your form, set its datasource to =cboMyCombo.column(1)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MissTinSC]

Eureka! That did it, Ken. Thank you so much! Now I can move on! Yay!

MissTinSC

 

[MissTinSC]

Oops! The problem is not completely solved...The data now comes from the Classtbl and shows up beautifully on the form... BUT ...it does not update the information in the MainDatatbl. Is there some code that will do this (flush the choices from the Classtbl into the MainDatatbl)from the AfterUpdate event procedure?

Thanks again for your expertise in problem-solving.

MissTinSC

 

[KenReay]

Hi

You did not imply that it had to update the MainDataTbl

My first question would be why duplicate the teacher name in the MainDataTbl, when you can get it in a quey by joining on ClassId

But, if you must, then taking what I originally said, remove the =cboMyCombo.Column(1) from the source of the text box, instead bind the text box to the MainDatatbl TEacher column, in the after update event of cboMyCombo, put txtTeacherName = cboMyCombo.Column(1) - this is assuming the text box is called txtTeachername, assuming you do not want the user to be able to overtype this value you may wish to set the enabled property of this control to fale, and the locked property to true

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

I'm trying to do something similar to this in a form, but whenever a try to put "txtBox = cboMyCombo.Column(1)" in the After Update line of the combo box, Access adds extra syntax to the line and it doesn't work.

Any ideas?

Thanks.

 

[KenReay]

Hi

I might have if you showed mt the code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[realflava]

Hi Ken

Sorry to intterupt. I was trying to implement something very similar - your advice was very helpful, thanks for that.

If I know for example, what to LeadTeacher field which was set to =cboMyCombo.column(1) appear in a Report - how would you suggest I go about doing it?

Thanks

 

[realflava]

rephrase of post..!

Hi Ken

I just read what I wrote - didn't make sense to me, so here it is again..

If I now wanted to create a Report, and include LeadTeacher field which was set to =cboMyCombo.column(1) - how would you suggest I go about doing it?

Thanks

 

[KenReay]

Hi

With such scant information it is not easy to give a good answer,

perhaps you should base your report on a query including this column, and then it will be available as a bound column

or

if the form containing the combobox is open when the report runs then

=Forms!TheNameOfTheForm!cboMyCombo.column(1)

should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[realflava]

Thanks for that Ken - sorted it

 

[cswosu]

Ken,

Here are the details of my combo box:

Name - FIN
Control Source - FIN (in table Release Data Tbl)
Row Source - SELECT [Facility Common Names].[FIN], [Facility Common Names].[FCN] FROM [Facility Common Names]
Bound Column - 1

And the details of the text box I want to change

Name - Facility Common Name
Control Source - Facility Common Name (in table Release Data Tbl)

To summarize, the FIN combo box is used to select the FIN; it also shows the Facility Common Name to aid in the selection of the appropriate FIN (an undescriptive number). So when the user selects the FIN, I want the Facility Common Name text box to automatically update. I found this thread and entered the following in the "After Update" field of the "FIN" combo box:

Facility Common Name = FIN.Column(1)

When I go into the form and select a FIN to see if the Name field updates, I get the error "Database can't find the macro 'Facility Common Name = FIN.". So my question is, why is Access looking for a macro?

Thanks for your assistance.

 

[KenReay]

Hi

To begin, if you have control names with embedded spaces (in future dont!), then you need to enclose the control in [] so try

[Facility Common Name] = FIN.Column(1)


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

Still got the same macro error message.

 

[KenReay]

Hi

Paste the actual code from the event

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

Here is what I have in the After Update field of FIN

[Facility Common Name] = FIN.Column(1)

Thanks.

 

[cswosu]

Got it fixed now. Thanks for the help.