Forms

[dr223]

Hallo,

I have a form (Qual_hist) with a field named Institution. Also, I have a table with all the institutions around 490 universities/colleges with their respective codes. The table has two field names 1) school_code 2) school

What I want to do is create a look-up table for the field (institution) on the Qual_hist form. Secondly, ONLY the code of the School i.e., school_code be populated on the field.

For example, school_code: 5463 represents Liverpool University. So when you click the university the code is populated on the field Institution.

Any help on this or there is a better way of doing it.. Any helping tutorials or sample models will be appreciated..

Thanks

 

[neileg]

Use a combo box. Display the school name but bind the school code to the Institution field.

 

[dr223]

Thanks for your response, but how do u bind the school_code to the field.
An example will do..

Regards

 

[rsmonkey]

well obviously you have a table which refers each School to its corresponding code. Ok then build your combo-box so it looks up the schools.

Then create an After Update event for the combo-box and in this event create an SQL statement which looks up the code from the school selected. Something along these lines

Private Sub cboSchoolName_AfterUpdate()

Dim strSQL as String
   
Set Db as CurrentDB

   strSchool = Me.cboSchoolName.Value
   
   strSQL = "SELECT DISTINCT School_Code FROM table1 WHERE table1.School = '" & strSchool & "'
CurrentDB.Execute (strSQL)

txtSchoolCode = strSQL
txtSchoolCode.Requery

End Sub

etc etc... blah blah you get the picture just play around with it!

 

[dr223]

Thanks you for the code,

I have updated the code and followed the procedure, however, whenever I select the School such as Liverpool university it is populated on the field, instead of the code...

The code looks something like so...

Private Sub cboSchoolName_AfterUpdate()

Dim strSQL As String

Set Db as CurrentDB

strschool_college = Me.cboSchoolName.Value

strSQL = "SELECT DISTINCT schocoll_code FROM institution WHERE institution.school_college = '" & strschool_college & "'"
CurrentDb.Execute (strSQL)

txtschocoll_code = strSQL
txtschocoll_code.Requery

End Sub




For your information, table called institution has two field-names for the codes: schocoll_code and for the universities/colleges: school_college. I named the combo box SchoolName

Thanks

 

[rsmonkey]

yer sorry bout that i just realised that their is a much much easier/simpler/quicker/better way of doing this.. its in the db attached!

 

[dr223]

Thank you very much for your quickest response. I have sorted it out..

Much appreciated


Regards

 

[dr223]

One last thing, when I have selected the institution for example Birmingham. The code is populated on the field successfully.

What I want to do is also, retain the Birmingham on the combo box when I view it next time.

Is it possible...

Thanks

 

[neileg]

Any time you view a record, the combo box should show the saved value. Is this not happening for you?

 

[dr223]

Thats the problem, I have created a text box whereby it will be updated with the field selected at the combo box, e.g Liverpool University when clicked will be populated on another field. This works fine!!!

But next time when I open the record it is not there. This is mainly because it is not saved on a respective table. I opened the table to store the data qual_hist, added a field called inst_name. When I go to control source on the text box on the form (in design mode) and look for inst_name, I can't see it.

Why is this happening, unless I can set the inst_name on the control source the field will not be saved and hence not retrieved for the future...

any help...

I had a query I also added inst_name... but still it is not coming up on the control source

Thanks

 

[dr223]

I have sorted it finally, thank you guys