Solved Using Lookup Table names instead of ID's

[JamieRhysEdwards]

Hi All,

In other languages, it's easier and better to be able to use named identifiers instead of their ID's, for example, in Java, you have an Enum:

public enum AssetType {
    Desktop = 0,
    Laptop = 1,
    MobilePhone = 2,
    ...
};

How would I achieve this with a Lookup table within my code? For example, I'm looking to grab the first two letters of an Asset Tag and if they match that in my look up tables AssetTypePrefix column, I then update my AssetType combo box. However, being new to Access, I'm unsure how to achieve this.

To give a clearer example:

Dim AssetTagText As String
AssetTagText = Me.TextAssetTag.Text

If AssetTagText.SubString(0, 2) = AssetType.AssetTypePrefix.LAPTOP Then
    ' Update AssetType combo box to "Laptop"
End If

I know the above code is not quite valid but I hope this gives a clearer picture of what I'm looking for.

EDIT:

Just to make this a little clearer (I'm really sorry about this, and I'm not sure if this is possible without making two copies of data). I have a look up table structured like so:

ID	AssetType	AssetTypePrefix
1	Desktop	DT
2	Laptop	LT
3	Mobile Phone	MP
4	Monitor	M
5	Printer	P
6	Router	R
7	Ethernet Switch	ES

I'd like to get the data from the table and then create some form of VBA type lookup where in I can use a AssetType.AssetTypePrefix.LAPTOP/Laptop type convention which would return ID 2.

If this is not something that can be done then I don't mind creating an enum with the titles AssetType and AssetTypePrefix, I'll just have to maintain them if I add further AssetTypes.

 

[Gasman]

You would use Left(Me.AssetTag,2) and use that as criteria for the combo rowsource.

 

[theDBguy]

Or, you can use the Like operator as well.

Me.Combobox.RowSource = "SELECT ID, AssetType WHERE AssetType Like '" & Me.Textbox.Text & "*'"

 

[GPGeorge]

You could create a public enum in VBA to do that as well, if that floats your boat....

 

[The_Doc_Man]

You can do the Enum that GPGeorge suggested. Here is a link:

Enum statement (VBA)

Office VBA reference topic

docs.microsoft.com

NOTE, however, that because Access has TWO environments, that certain things won't work quite like the more language-oriented environments such as C variants.

You could enumerate things to define DESKTOP as 1, but there is no simple way to have a text name match up to the Enum'd value. You would still need some kind of translation table to match up a user's text input to an internal code value. Further, it would not be visible in SQL statements unless you built a string and did a concatenation to the string before actually using it. Like all other constants in VBA context, it would be invisible to SQL context.

 

[JamieRhysEdwards]

Hi all, I'm really sorry about this but I've updated OP which should make things a little clearer.

 

[theDBguy]

I'd like to get the data from the table and then create some form of VBA type lookup where in I can use a AssetType.AssetTypePrefix.LAPTOP/Laptop type convention which would return ID 2.

In Access/vba, you can use DLookup() for something like that. I don't know Java, but that syntax looks like you'll have to type every item in the enum just to find the matching value. Is that right?

 

[JamieRhysEdwards]

In Access/vba, you can use DLookup() for something like that. I don't know Java, but that syntax looks like you'll have to type every item in the enum just to find the matching value. Is that right?

How would I be able to use that with something like this? If I can refrain from needing to make duplicates it's a win-win as creating an enum would mean I would have to type each option essentially twice.

 

[Mike Krailo]

How would I achieve this with a Lookup table within my code? For example, I'm looking to grab the first two letters of an Asset Tag and if they match that in my look up tables AssetTypePrefix column, I then update my AssetType combo box. However, being new to Access, I'm unsure how to achieve this.

I think I see what your asking. The Prefix letters are not a good thing to go by. You want to pick out the unique ID numbers ideally.

I then update my AssetType combo box

Me.TextAssetTag.Text, is this just an unbound control on your form. Where does the text in this control come from? I mean are you trying to add items to the combo box list? For that, you can setup a form based on the AssetType table and then on the combo box properties set the form name on the "List Items Edit Form" property.

I'm not sure what you mean by typing each option twice. If all the asset types are in a table, that's the only place you need to reference them, right?

 

[theDBguy]

How would I be able to use that with something like this? If I can refrain from needing to make duplicates it's a win-win as creating an enum would mean I would have to type each option essentially twice.

Hi. I am not sure we're on the same page, but here's what I would do using Access.

1. Create a table to store the id, asset type, and prefix
2. In your code, use DLookup() to retrieve the id using the prefix
3. In queries, forms, and reports, you can join the lookup table to have all the lookup columns available

Using a table makes it flexible. You can update the lookup items without having to modify the code again.

 

[Mike Krailo]

@JamieRhysEdwards can you provide some sample data for the AssetTags. The problem is in your example is that there are not strictly two letter codes, there are one letter codes in there as well. If these single letter codes are butted directly up against another string of text, then there is no way to parse out the code effectively (unless I'm missing something). Each two letter code you grab from the AssetTag field must be unique to do what you are attempting to do. On the other hand, if the AssetTag data has a space or another special character between the 2 or 1 letter code, then we can make it work.

Usually combo boxes provide a way to link to other tables and by selecting your choice, then other fields (columns) in the combo box can fill out other fields automatically in a given form, but it's not as common to put in a text string into a text box and from that select a combo box. You can do it, it's just not a normal way to input data in general. It sounds like there is a redundancy issue with your tables in some way that could be causing this. I would look at making sure everything is properly normalized first or starting a new thread in the Tables section to check that out.

But it would be beneficial to everyone trying to help you if you had the AssetTag sample data which is probably in your Assets table. I'm going to assume there is a dash between the code and other part of the tag like this (CC-A765TVG484), then I'll attach a sample database for you to modify if it is not correct.

Since the two letter code must be unique, you might as well make that the primary key for the AssetType table. This means that you are limited to how many asset types you can have but maybe that is all you need, I don't know.

 

[JamieRhysEdwards]

@JamieRhysEdwards can you provide some sample data for the AssetTags. The problem is in your example is that there are not strictly two letter codes, there are one letter codes in there as well. If these single letter codes are butted directly up against another string of text, then there is no way to parse out the code effectively (unless I'm missing something). Each two letter code you grab from the AssetTag field must be unique to do what you are attempting to do. On the other hand, if the AssetTag data has a space or another special character between the 2 or 1 letter code, then we can make it work.

Usually combo boxes provide a way to link to other tables and by selecting your choice, then other fields (columns) in the combo box can fill out other fields automatically in a given form, but it's not as common to put in a text string into a text box and from that select a combo box. You can do it, it's just not a normal way to input data in general. It sounds like there is a redundancy issue with your tables in some way that could be causing this. I would look at making sure everything is properly normalized first or starting a new thread in the Tables section to check that out.

But it would be beneficial to everyone trying to help you if you had the AssetTag sample data which is probably in your Assets table. I'm going to assume there is a dash between the code and other part of the tag like this (CC-A765TVG484), then I'll attach a sample database for you to modify if it is not correct.

Since the two letter code must be unique, you might as well make that the primary key for the AssetType table. This means that you are limited to how many asset types you can have but maybe that is all you need, I don't know.

Unfortunately not... Our assets are for example LT1001, DT2202, R419, ES404 etc which means that it would be a little harder than I expected.

 

[Mike Krailo]

If all the characters after the single letter AssetTypePrefix are always numbers, then this would be easy. It is also possible if there is never another letter right after the M, P, or R asset types. There is a new function GetAssetTypePrefix that works with your tag data. See updated demo. I also added some simple validation to the form so an invalid AssetTag cannot be entered. It probably could use more validation though.

 

[JamieRhysEdwards]

@Mike Krailo Sorry for the very late reply, this works! I noticed another issue but I've fixed that (I think, will soon see, but it's irrelevant to this). Thanks!

 

[Mike Krailo]

@Mike Krailo Sorry for the very late reply, this works! I noticed another issue but I've fixed that (I think, will soon see, but it's irrelevant to this). Thanks!

You're welcome, and glad it is working for you now.