Automatically fill Additional related Form Fields

[Telestar]

I have a form that fills a table. There are 3 fields that are from another table. Firstname, LastName and Grade. The Firstname and lastname have been setup as lookup fields in their table of origin.

I can go to the lastname field in the form and start typing to get the name or pick it from a list of 1250 names (my pick shows last and first).

What I want to happen, is have the firstname and grade belonging to that lastname in the original entry to auto fill in their respective fields in the form without having to go there.

This would really help me out, I have checked all of my materials and databases, and cannot find an example of this anywhere.

Thanks,
Telestar

 

[AndrewRW]

Hi,
I got this from somewhere, can't remember where...
Basically, lastname is based on a query (or lookup) with 3 columns. Then, after update, insert this code as an event procedure(substituting your own control names and the number of columns - in your case, it would be 2 (lastname, grade)). The controls for lastname and grade are named, for instance, student1 and student2. Might not have this exactly right, you may need to play with the numbers to get the right columns into the controls (but basically the number at the end of the control should match the column...)
Good luck...hope this helps...

Private Sub Lastname_AfterUpdate()
Dim intZ As Integer
For intZ = 1 To 2
Me.Controls("student" & intZ) = Lastname.Column(intZ)
Next intZ
End Sub

 

[AndrewRW]

" in your case, it would be 2 (lastname, grade)). "

or 3...lastname, firstname, grade..can't remember how I got this to work, but it does...

 

[willir]

Telestar

There is a much easier way to do this...

First please realize that you do not need to grab the last + first name from the related table. You already have the name; you just need to grab the primary key from the original contact or student table.

Instead of...
LastName, FirstName, Grade

You really need
StudentID, Grade
...where the StudentID points to the student table which has the name of the student.

This is a fundemental concept to relational databases.

Next, you can use a single combo box to grab the information you need.

For a combo box called, cmbStudentID
RowSouce: SELECT StudentID, LastName, FirstName from tblStudent

You can now access all three columns. Works as follows...

End user selects a student.
- The StudentID is the bound column and is stored in the Grade table.
- The combo box is tweaked to not display the StudentID, so the end user sees the LastName + FirstName when they select the student, and only the LastName is displayed in the combox field.
- Simple code is used to display the FirstName in an unbound text field.

See following link for details...

http://www.tek-tips.com/viewthread.cfm?SQID=877985&SPID=702&newpid=702&page=1

Richard

 

[Telestar]

Richard:
You were right about the theory; I was aware of that. The link you gave me shoots me back to the forums though and nothing in specific. Is my answer there?

I actually discovered the following:

I seems that I had to remove lookup properties in the firstname and grade fields in the table before this would work.

I created an event after update in the Lastname combo box

'Fills First_Name and Grade fields when Student's last name is picked
Private Sub fLast_Name_AfterUpdate()
Me.fFirst_Name = Me.fLast_Name.Column(2)
Me.fGrade = Me.fLast_Name.Column(3)

End Sub

It's slick, it's simple and the target database is filled.

To make things even better, I took the two fields out of the tab order. I did not go as far as locking out the two fields for changes.

I am thinking about it though. If there are changes to be made, they should be doing it in the source not the target.

Thanks for responding to my call for help!

Cheers
Telestar

 

[willir]

Great job Telestar

There is more than one way of achieving things in Access, and your solution is great. Using the other fields from a combo box is pretty effecient. (...and is the reason why I gave you the link. The link was to a topic called "Populating Textboxes from Combobox selection" where I provided a similar solution to your problem on July 12 )

 

[domino3]

Hi

I've tried doing what is described above to fill in fields on my database but it doesn't seem to work.

I have a combo box with three fields. The combo box is called combo_names and the fields are id_no, firstname and surname. The cmbo box allows me to choose names from a list and it works OK. What I then want to do is fill in the surname from the combo box onto a surname1 field on the form as an after update event. I've put in the follwing commands

Private Sub combo_Name_AfterUpdate()
Me.Surname1 = Me.Surname.Column(3)
End Sub

but the surname1 box doesn't fill in. What am I doing wrong, please?

 

[PHV]

Replace this:
Me.Surname1 = Me.Surname.Column(3)
By this:
Me.Surname1 = Me.Surname.Column(2)
The first column number is 0, so the 3rd is 2.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[domino3]

It still doesn't work. I've tried putting all the numbers in from 1 through to 4 with no success. I've recreated the combo box and called it combo_74. The combo box row source is:

SELECT DISTINCTROW [Attending].[attending_ID], [Attending].[Firstname], [Attending].[Surname], [Attending].[Event], [Attending].[Surname1] FROM [Attending];

As I say, I've tried different numbers for the column and nothing helps.

 

[PHV]

If combo_74.RowSource = "SELECT DISTINCTROW [Attending].[attending_ID], [Attending].[Firstname], [Attending].[Surname], [Attending].[Event], [Attending].[Surname1] FROM [Attending];"
and if a row is selected, then:
combo_74.Column(0) is the selected attending_ID
combo_74.Column(1) is the selected Firstname
combo_74.Column(2) is the selected Surname
combo_74.Column(3) is the selected Event
combo_74.Column(4) is the selected Surname1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[domino3]

Sorry to make such heavy weather of this, but....

I'm getting an error message which says "Compile error. Method or data member not found"

The combo box is called combo66 and has the recordset

SELECT DISTINCTROW Attending.Surname, Attending.Firstname, Attending.Event, Attending.attending_ID FROM Attending ORDER BY Attending.Surname, Attending.Firstname, Attending.Event DESC;

The afterupdate event reads

Sub combo66_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[attending_ID] = " & Me![Combo66]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Surname = Me.Surname.Column(0)
End Sub

I've tried different numbers for the column, but none work. But I can't work out what I'm doing wrong.

 

[PHV]

"Compile error. Method or data member not found"
Any line of code highlighted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[domino3]

The section with the column number is highlighted.

 

[PHV]

Which kind of control is now Surname ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[domino3]

A text box

 

[PHV]

Me.Surname = Me.Surname.Column(0)
No TextBox has the Column property.
Perhaps you wanted this ?
Me.Surname = Me.combo66.Column(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[domino3]

Thanks. Putting combo66 in has solved the problem.

One more question, though. Is it possible to get this type of fill in to happen in a subform.

I have a table of names and addresses which appear on a main form. I also have a table of subsriptions which appear on a sub form to the main form. They both work fine.

Is it possible to autofill the postcode which appears on the main form against any name from the table of names against any subscription paid by that person on the sub form to appear on the table of subscriptions. I am currently inputting the postcode against any subscription payment manually and would love it if there was some way of automating the process.

 

[domino3]

I've worked out a way to do this now. I created an unbound control in the subform to lookup data from the main form, then used an after update in one field in the subform to copy the data over from the unbound control box.

Not the neatest way to do it, but it works!

 

[domino3]

Hi there.

The unbound boxes I set up before are working fine on my subform. However, Im having further problems.

I Have set up a form which allows me to select from a combo box of names, and then allows me to further select from particular events any individual person attends. These work fine.

What I now am trying to do is set up automatically filled fields for name and address on each event.

So, for example, my first combo box allows me select Mr Smith from a list, and then combo box 2 allows me to pick event B for Mr Smith, then I would like to automatically fill Mr Smith's firstname and surname in the field boxes for that event. All I keep getting is a runtime error which tells me there is a problem and that I am trying to fill the box with the wrong type of data. Help.

Thanks.