diplaying one field in pick list but storing another (1 Viewer)

[mk0920]

I am just learning Access and creating a database for my company. I added a combo box to a form for state selection. I copied code from an example in a tutorial. The row source code reads like this SELECT DISTINCT STATELOOKUP.CONCSTATE, [CONCSTATE] AS STATE FROM STATELOOKUP ORDER BY [CONCSTATE];

concstate is the column in my state lookup table that I want to display in the pick list for the form. It shows the state abbreviation and the state name. However, I would like just the abbreviation to store in the table. How would I do this?

 

[dkinley]

Hello and welcome to the forums!

What is stored is based on the "bound column" of the combo box. You can find this under the rowsource in the combo box properties.

I would consider using a numeric based (autonumber) primary key for this table using an autonumber scheme and storing this in the field. Note, that this is not required - but it may save some headaches further down the road.

-dK

 

[dkinley]

Posting again for some good links for you since you are new to Access ...

http://www.accessmvp.com/strive4peace/
http://www.vtc.com/products/Microsof...-tutorials.htm
http://fisher.osu.edu/~muhanna_1/837/MSAccess/
http://www.functionx.com/
http://knol.google.com/k/data-entry-in-microsoft-access#
http://www.sienaheights.edu/personal...Access2007.htm

HTH,
-dK

 

[mk0920]

Thanks for the quick reply. Right now bound column is 1. If I add the abbr column to the row source (making it the 3rd column) and change bound to 3, nothing displays in the pick list. If I change bound column to 2, the pick list displays, but when I go back to the table, the concatenated column info (abbr and state name) is stored in the state field.

I saw the info about storing a numeric field, but that seemed to cause problems when I was trying to do queries or tables. For instance customers by state, it would display the code # of the state, not the abbreviation.

This is not a huge database and there are less than 10 people who will access it. Really just an customer lead database.

 

[dkinley]

Okay .. the second part of this is how you display the combo box.

One aspect that makes combo boxes so great for data integrity is that even though you bound column n, you can have it display column m.

Look one tab over (Format) in the combo box properties. There will be a property called "Column Widths". You can use this to present the information you want to the user and hide the data you don't want presented.

For instance, suppose I had a query driving the combo box that consisted of 3 columns. The first column stored the primary key - so I set the Bound Column to 1. In the format property, I would use 0";1";1". This would not show the first column (size set to 0"), but display the other two for the user interaction (size 1" and 1"). These can all be set to what you specify.

Per the state code #, yes, that is how it works. What you would do is add the table of the State codes and perform a left join from the foreign key (the one showing #) to the primary key of the State table. So no matter where in your database you needed a state name, you would simply reference this table in this manner - thus saving data space (normalization).

The links I posted should help you along those lines of creating these queries and so on and so forth.

HTH,
-dK

 

[mk0920]

thanks for that info. it really helps. Now I have a new problem!

Looking for an easy way to launch a particular form based on user group login. Would also like to know how to link a custom menu/toolbar based on user group login in and how to hide buttons that are not applicable. For instance the delete record button. Right now it shows up for all users. If a user doesn't have permission to delete and they click on the button, a message comes up saying basically, you can't do this. I would like buttons to be greyed out if user cannot use them. I am not a programmer, so you need to make your answer as simple as possible. Thanks!

 

[vbaInet]

If you say you're not good in programming then your easiest option would be to create menu/toolbars for EACH user group. So if you have three different levels, you create 3 of the same menu bars but with some buttons disabled on one and enabled on the other. On the LOAD event of the form, check the group and set the MenuBar.

 

[dkinley]

I am not sure what version you are using. If you are using 2003, I am not the best to offer advice if you are using the built-in security for the users and groups (all I know how to do is grab the windows login name).

If you are using 2003 or 2007 and built your own log-in functions, then I can offer some advice - however it will require some VBA knowledge to accomplish what you want.

For the launching aspect, you can have code behind your login screen to store the user name in a global variable. Then in the code sequence, you can evaluate the global variable to open the appropriate form ...

Dim sFormName as String
 
If gbl_sUserName = "Joe" Then
     sFormName = "Form1"
Else
     sFormName = "Form2"
End If
 
DoCmd.OpenForm sFormName

You would essentially do the same on a forms Open event ....

If gbl_sUserName = "Joe" Then
     Me.cmdCommandButtonName.Enable = True
Else
     Me.cmdCommandButtonName.Enable = False
End If

HTH,
-dK

 

[mk0920]

I am using 2003. Set up user groups using security wizard. To show you how little I understand...where would I put this code? In the macros area? I use a bit of Fox Pro here at my job, so the syntax of the code does make some sense. The login screen was automatically created by the wizard. I don't see a table that holds the users and their group/login info. I guess I'm in over my head!

 

[vbaInet]

When you said menubars I knew it had to be pre 2007 which is why I suggested the method in post #7. With respect to code, what you should be checking is the User Group and not the Username. Search the forum for code on how to check user group.

 

[dkinley]

Ah ... vbaInet posted prior to me and thus I answered unknowingly and held my tongue. I have '03 on an old machine which I hardly boot up hence I am not much help because my memory is not that reliable.

You should be able to do a search such as vbaInet suggested and find exactly what you are looking for.

-dK

 

[mk0920]

thanks for your help. I hope I find it soon!

 

[vbaInet]

Here's a thread that might help:

http://www.access-programmers.co.uk/forums/showthread.php?t=188395&highlight=check+user+group

And here's another you would find useful:
http://www.access-programmers.co.uk/forums/showthread.php?t=97578

Let us know how you get on.