lookup (1 Viewer)

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
hi ,
I have a form called input table on that form I have a suppliercode field this is a dropdown box field that looks at the suppliers table

i also have a feild called suppliername in my suppliers table

what i would like to do is when i select the suppliercode it puts the supplier name in my form input table automatically

not sure how to do this

thanks for your help

steve
 

Ranman256

Well-known member
Local time
Today, 11:18
Joined
Apr 9, 2015
Messages
4,339
in the combo drop box, have it use 2 columns:
CODE, SUPPLIERNAME

then in the cboBox afterupdate event, just fill in from the 2nd column.
NOTE: in vb, columns begin with zero

Code:
sub cboBox_Afterupdate()
txtName = cboBox.column(1)   'which is column 2
end sub
 

June7

AWF VIP
Local time
Today, 07:18
Joined
Mar 9, 2014
Messages
5,423
Why duplicate the supplier name into the input table? Just save the suppliercode.
 

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
hi

I was reminded on this board that I was wrong with my approach because there was more than one supplier name but with different codes . so i changed the links etc to use the supplier code. but i have been asked if the form , reports, etc could show the supplier name when selecting the code

thanks
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:18
Joined
Jul 26, 2013
Messages
10,354
They can certainly display it and it's a very common requirement, use a text control set to the second combo column as per Ranman's suggestion.

Just don't store it.
 

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
I am not sure how to do that

I have a field called suppliercode , with the 2 coulmns in . as suggested I have attached that code to the drop down box after update
but sorry for being stupid . but where does the result show?

it says object required

thanks
steve
 

Minty

AWF VIP
Local time
Today, 15:18
Joined
Jul 26, 2013
Messages
10,354
Assume you have created a txtbox called txtSuppName and you combo is called cmbSuppId

In the control source for txtSuppName simply put
Code:
= cmbSuppId.Column(1)

And it should all work swimmingly.
 

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
thank you all very much , I have tried many ways and failed but now working ok

steve
 

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
can I ask , for example. how you would do it without the combo box
got a field called suppliercode and I typed the code in , and just a text box


steve
 

Cronk

Registered User.
Local time
Tomorrow, 02:18
Joined
Jul 4, 2013
Messages
2,770
how you would do it without the combo box
I don;t know why you would want to do that (a combo is easiest), just the same as I don't know how you get to have suppliers with multiple codes (see #4)

However you could use dlookup to populate a text box with the supplier name. Set the control source of the text box to

Code:
=dlookup("SupplierName","tblSuppliers","SupplierCode=" & me.txtSupplierCode)
or if the supplier code is alphanumeric
Code:
=dlookup("SupplierName","tblSuppliers","SupplierCode='" & me.txtSupplierCode & "'")
 

rainbows

Registered User.
Local time
Today, 08:18
Joined
Apr 21, 2017
Messages
425
thank you

just for ref you could have A company called ATB which has 3 branches in the uk so they have different codes

steve
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:18
Joined
Feb 28, 2001
Messages
26,999
just for ref you could have A company called ATB which has 3 branches in the uk so they have different codes

Steve, when you have the setup that you mention, then you have three ATB entries that must include the branch indicator somehow. But if you do that, YES you can have three branches with different codes if that is your goal.

The only thing you shouldn't do in that case is to have three different codes that ALL resolve to exactly the same company information and use them interchangeably.

That is because from your description the codes are being used like prime keys (maybe even ARE prime keys) and the rule is simple: The PK must uniquely identify or perhaps associate with whatever is being identified in the remainder of the record.

Stated another way, there is an EXPLICIT uniqueness relationship between the PK and the rest of the record. It SHOULD be a two-way relationship in that; if you have ATB company (unqualified), you should always get the same exact choice of PK each time; and if you have the same PK each time, you must select the same record each time. This is a theoretical design-level issue that novices don't always understand. There is (or should be) a 1-to-1 relationship between a PK and the logical entity identified by that PK.

However, IF you have "ATB (Essex)", "ATB (London)", and "ATB (Brighton)" with three different PKs, one for each case, you would be doing exactly what you wanted AND NEEDED to do to keep things straight.

It would also be acceptable if your branch designation was a different field from the company name in that case. The point being that the RECORD shoudl be unique but it is OK for individual fields to match content of other records where applicable.

I should also clarify that this point is THEORETICAL. If in practice you had three records with three different PKs and the only other field was a text string containing "ATB" it would work - but something else down the road would go off the rails, like being unable to make a GROUP BY work exactly right, or having a SORT not keep things in a decent order. This advice is designed to prevent LATER catastrophes.

Hope that clarifies the issue of your question.
 
Last edited:

Users who are viewing this thread

Top Bottom