Auto Complete Form from Two tables outputting to third table?

[timbo]

Fixed it. Thanks for all your help!

[pbaldy]

Happy to help!

[timbo]

One more really simple question. On the form, I have for example, the label "Client Name" and next to this the input box which also says "Client Name" (in design view). Is it possible for the text within the box (currently blank in form view) to display "Surname, Names" so that the user is reminded the order in which to input the data?

And now, more importantly I have added a combo box to my client input form which takes records from a table I have created named Lawyer, and this has caused all of the fields to disappear in form view.

SQL on form Record Source is

SELECT Clients.ID, Clients.[Client Name], Clients.[E Mail], Clients.Telephone, Clients.Nationality, Clients.Source, Clients.[1st Contact Date], Clients.[1st Contact person], Clients.[Notes & History], Lawyer.Telephone AS Telephone_Lawyer, Lawyer.Mobile, Lawyer.[E Mail] AS [E Mail_Lawyer] FROM Clients INNER JOIN Lawyer ON Clients.Lawyer = Lawyer.[Lawyer Name];

All of the other settings are the same as on the vendor form (which I have not touched yet) and this displays correctly.

I hae attached the db again in case I am not clear. Sorry this is like pulling teeth. I am trying to learn as I go!

[timbo]

I have just been playing around with the database and realised that the auto complete isn't actually working at all. It is just working like a normal combo box!

The other values don't change!

What idiotic mistake am I making? db attached in previous post to give an example.

Just to be clear, I am trying to build forms that will pull in the data from various tables, and auto-complete/display & save this data in the form then export it all to another table.

On the db attached I am attempting to create within the client input form, a section for clients with simple combo boxes from other tables(done), a section below this showing/auto-completing all fields from the lawyer table (problem), then I wish to emulate this in the vendors form, and subsequently have the ability to create a sales in progress form that will auto-complete various fields from their respective tables(problem), plus allow me to add a couple more to the sales in progress table (should be easy).

It sounds so simple when I write it down like this but I have spent loads of time trying to figure this out and keep messing it up.

[timbo]

Could it be that I need to use AfterUpdate so that all of the linked data is always displayed in the form?

[pbaldy]

Don't want you to think I've deserted you, but I only have 2010 on a VM at work, so it will be tomorrow before I can look at the sample.

[timbo]

Will it help if I build all of the tables first?

[pbaldy]

Not having looked at the sample yet I can't say, but it can't hurt. I'll be at work in an hour or so.

[pbaldy]

What specifically is the problem? There is no code in the after update event of the lawyer combo, which would be required if you wanted to save the related info. If you just want to display it, use the first recommended technique from that link.

[timbo]

I want to display and save the info. Via the sales in progress form, I want to "copy" the data from the clients and vendors tables, along with all of their respective lawyers details, and then add new data (completion date, commission and agreed price) which I already know how to do, exactly the same as I have done for the previous tables.

Then I want to add a button which will delete the record from "Sales in Progress" and paste it to "Sales Completed" at the bottom of this form.

I have added the two tables and uploaded a copy with this message. The column names are a bit wordy to hopefully explain what I am trying to achieve.

How does AfterUpdate work exactly? Will it change the displayed phone number etc. of the lawyer as soon as I click off the combo box?

[pbaldy]

Let's fix one thing at a time. As I mentioned, if you want to save the data, you need the code from that link in the after update event of the combo where you choose the lawyer. You can code it any way you want, but as it is on the link it will put whatever is in the lawyer table into the form when you make a selection from the combo.

[timbo]

This is where I struggle. I don't know code at all (need to learn!). What code do I need to use that will give the desired result of displaying the data from the lawyers table in the form?

Sorry!!

[pbaldy]

The code from the link I gave you:

Me.TextboxName = Me.ComboName.Column(2)

If you don't know where to put VBA:

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

[timbo]

ok, so this is the code I have put on the textbox.

Option Compare Database

Private Sub cmdFind_Click()

Private Sub Lawyer_Name_AfterUpdate()


Me.Lawyer_Telephone = Me.Combo41.Column(4)


End Sub

Private Sub Lawyer_Telephone_AfterUpdate()

End Sub

What's wrong with it?

[timbo]

Think I just cracked it by using the control source expression builder instead of going in through VBA. Is it ok to do it this way?