Concatentation challenge! 1

[BullHalseyUSN]

Greetings experts!

I have a form that takes various pieces of information about employees.

The three pieces I am interested in are:

specialty - grade - name

E.G: GS (specialty) - 12 (grade) - Smith.

GS12 Smith

I am working on a form called FrmtblEmployee. This is based on tblEmployee. Both specialty, which is entered by a combo called RankOrRateCmb and grade, which is entered through Combo165, actually stored in the table as codes.

The actual names are stored in, in the former case a table tblRankorRate (let's say a code of 3 is defined in the table tblRankorRate as an 'MS') and grade is in tblRate.

I have the following:

Private Sub SocialSecurityNumber_AfterUpdate()
[SALUTATION]= [tblEmployees.Name] & " " & Me![RankorRateCmb] & " " & Me![Combo165]
Me.Refresh
End Sub

Because those Combos store values instead of terms I get, say:

3 4 Smith

Insted of GS12 Smith (3 corresponds to GS and 4 to 12 in my tables (why 4 = 12 would take a long explanation, but basically, some government positions have numbers some do not)

Can anyone brainstorm how I can get at the values in other tables, or if I can "teach" VBA which values correspond to which values?

Thanks! BH

 

[jrbarnett]

Hi,

Use the DLookup function to retrieve the full name from the description, based upon the entered data.

The syntax for the DLookup function is:

DLookup ("Field name", "Table Name", "Where clause&quot

eg

DLookup ("Code", "Tablename", "Code=" & Me![Combo165])

Just build them up in several strings from these.

John

 

[Eupher]

Use column notation. I'm assuming the combos display the specialty name and Rank/Rate info in the 2nd column of the combo, and the hidden first column has the actual bound data. But it looks to me like the order of your fields in your example is reversed - you say that this:

Private Sub SocialSecurityNumber_AfterUpdate()
[SALUTATION]= [tblEmployees.Name] & " " & Me![RankorRateCmb] & " " & Me![Combo165]
Me.Refresh
End Sub

Yields this?:

3 4 Smith

At any rate, to get the value from a non-bound column in your combo, refer to the appropriate column, i.e.:

Private Sub SocialSecurityNumber_AfterUpdate()
[SALUTATION]= [tblEmployees.Name] & " " & Me![RankorRateCmb]

.Column(1)

 & " " & Me![Combo165]

.Column(1)

Me.Refresh
End Sub

Remember that column notatin is zero-based, so the first column is column 0, the second column is 1, and so forth.

HTH...

Ken S.

 

[BullHalseyUSN]

John, or another expert:

Dlookup is obviously the tool I need. Thanks! Now I am struggling to learn the usage.

What I am trying to say with the code is "tell me the information in FIELD RankorRate in TABLE tblRankorRate for the value clicked in COMBO RankOrRateCMB on this form." I believe I should be able to execute this with one statement, as opposed to writing a statement for each value clicked on the Combo?

Dlookup ("RankorRate","tblRankorRate", Me![RankOrRateCmb]= ["])

is my lamish attempt! Help please.

BH

Ken -

You're right. The example was backwards. The code would give me "Smith 3 4" not 3 4 Smith

 

[KenReay]

Hi

Dlookup is definately NOT the tool you need, using this causes another lookup which in turn means more disk activity and will therefore be slower than using Eupher's solution.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BullHalseyUSN]

In that case, KenR, I may misunderstand Eupher's post.

The way my combo works is exactly this way:

Row Source Type: Table/Query
Row Source: SELECT [tblRankorRate].[RankorRateID], [tblRankorRate].[RankorRate] FROM tblRankorRate;

The values that the user sees are grabbed from a table that matches the RankorRateID to its plain English value.

Example: the user will see "Ensign" (a rank used in the US). The ID is "9" and is what's stored in the table associate with the form.

Thanks for any guidance this may inspire! BH

 

[KenReay]

Hi

You are selection two columns from the table of Ranks (tblRankorRate)

the user (presumably 'sees' RankOrRate, but you store RankOrRateId

If you look at the columns property of your combo box it will read 2.

The columns of a combo box are a zero based array,

so cboName.Column(0) is the first column (RankOrRateId in your case), cboName.Column(1) is the second column etc

The user sees the text becuase the Id is hidden, but becuase the bound column is column 1 (for some reason, to confuse Microsoft have numbered the array columns 0-n, but the columns to declare the bound column are numbered 1-n, I haev no idea why).

So as Eupher says you can 'get at' the text values by refering to cboName.Column, the exact value of n will depend on the columns SELECTED in the SQL statement, reading from the left, just rember the first column is 0, the next column is 1 and so on

Does that help?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BullHalseyUSN]

KenR,

You're right! Column count is 2. Just wanted to say thanks to you and the other respondents (or is it responders?). I will attempt to implement the solution this evening.

I hadn't thought of the columns in this fashion, but I suppose it makes a certain amount of sense.

I imagine this will work just fine, then. I will still teach myself Dlookup as it seems a handy little tool.

BH

 

[BullHalseyUSN]

Thanks fellows!

It worked.

I have another challenge related to this same little element, but this has been a great help.