vlookups in userform - selections to populate other fields

[Newport Count]

Hi

I am trying to to use vlookup on a userform so that when a value is entered in comboboxA, textbox1 is filled with data from another worksheet - is this possible?

many thanks

 

[Newport Count]

thanks both!

Norie - your code suggestion works great:

If cboContactName.Value <> "" And cboContactName.ListIndex <> -1 Then
... code for lookup End If

</PRE>
I can reset the form at all times and can also change the company names to different values without the error!
the only thing missing now is clearing the Tel number field when the company name changes (the company contact does clear)

could you advise how to solve this final part?


Also i will take a look into Mike's suggestion - i think it would make more sense to me to use as it looks clearer as to what the code is doing

many thanks

 

[Norie]

Definitely use Mike's.

 

[Norie]

Mike

I think the row idea is another Access thing.

In Access it would be the record ID and it could be used for other things as well, eg linking subforms, opening other forms.

 

[Newport Count]

sure i think i will have to spend some more time and go through Mike's solution....

any ideas on my current code - all i need is for the contact tel to clear to nothing when a new company name is chosen...just like company contact disappears when a new contact is chosen


really appreciate your help

thanks again

 

[Norie]

You can just reset the textboxes in the company combobox change event.

 

[Newport Count]

I thought it would be something like that as I already have:

cboContactName.Clear

as you suggested in the company combobox change event

however if it try the same with the below:

txtContactTel.Clear

then i receive the following error message?:

Compile error:
Method or data member not found

Any help would be appreciated!

 

[mikerickson]

Try

txtContatctTel.Text = vbNullString

 

[Newport Count]

thanks Mike - that works perfectly!

i think i have now got exactly what i need - so i have been trying to break the form!

when i enter a brand new name in the company name dropdown such as 'ff' (one that isnt listed)... there are entries to choose from in the contact name for the first company that begins with f!

any ideas how to fix? this is just a final touch i suppose - not really affecting the functionality of the form....but want to get everything correct!

thanks again - your help has been fantastic!

 

[mikerickson]

Fiddle with the .MatchEntry and .MatchRequired properties of the combo box until it acts the way that you want it to.