Pulling together data on a form for combo box

[spudracer]

I'm trying to establish a combo box pulled from data being collected on the same form.

I know this can be done, I just think I'm messing something up somewhere.

So far, I've got the table (tblPersonnelData) established for the form (frmPersonnelData) and another table (tblSupervisor) established to combine certain fields from the form together for a combo box with a lookup wizard established in the initial table

On the form where I want the combo box, the control source is identified as [tblSupervisor:SUPV_NAME]. Only one field from the four that it's supposed to collect will show up.

Due to the classification of the database, I am restricted from publishing a sample of it to the web.

 

[dkinley]

This link gives some pretty good samples of combo boxes ...

http://www.techonthenet.com/access/comboboxes/index.php

I think what you are after on how to format it to show what you want.

Just as statement, it seems that you are storing the supervisor name into the employee table. Why not store the primary key of the supervisor instead? The reason is what happens if you have two supervisors of the same name. Even though you assign them correctly when you are inputting data, if you ever go to run a report and your only link between the two tables is a name field, both employees will show up under the first supervisor found. If you store the primary key (and through the format of the combo box show the name) then later on when you are assembling the data in reports you will have the correct supervisor every time.

-dK

 

[spudracer]

Thanks for that info. I will play around with it and see if I can get it to work the way I want it to. I've been out of the Access training loop for a little while so I'm a bit rusty on everything.

 

[dkinley]

No problemo ... the link can explain much better than I and it comes with pictures!

Post back if you have any problems.

-dK

 

[spudracer]

I'm hitting a roadblock now. I'm looking at the site you posted a link to and everything is coming off as it was a foreign language.

Also, one thing I forgot to mention, all four columns of information need to be able to be displayed in a report.

 

[dkinley]

Strange, worked for me .. here is the home link for it - see if that works. Go down to the combo box section.

http://www.techonthenet.com/access/index.php

Seeing four columns in the form is no problem with the right formatting. Seeing all four columns in a report shouldn't be problem either (that link will help you get to some report building, too.) If you are pulling two tables together for the report that is what I was talking about that could be critical on how you are storing and thus linking the supervisor with the employee. Use an autonumber ID field as the primary key in each table. Then in the employee table, store the supervisor primary key so that it acts like a foreign key. Then when you build your reports, the link is through a non-duplicated key that will guarentee data integrity every time.

-dK

 

[spudracer]

I wasn't meaning the website was in a foreign language, but to me, getting re-familiarized with Access is like trying to understand a foreign language.

 

[dkinley]

Hehehe ... ahhhhhhhhh

It's a good site - plenty of pictures and written much better than I could here. Give it a whirl and if you can't blast thru the roadblock, post up your db and it can get modified. Perhaps dissecting the modification might help you out better.

-dK

 

[spudracer]

I tried something different to see if maybe it would work.

I've got a Yes/No box set up for people who will be supervisors. Then I've got a query established to filter out who is in fact a supervisor. From that, I've got a combo box pulling the information off of that query. Everything works, but when I go to display the info in the combo box, only one field of the four is displaying.

 

[dkinley]

I would think that it is a combo box formatting problem. In the properties of the combo box, there should be a column count and a format widths property. Make sure the column count is 4 and in the format widths is 1";1";2";1". I put the 2" in there so you can see that the third column of the box would be formatted in a 2 inch column. Change the size as necessary.

For your learning curve. You could put 0" in a column width. This would effectively hide the column from presentation but still make it accessible to the application. For instance, suppose the ID field was the first column of the underlying query of the combo box row source. The format was 0";1";1";1". Here, the user will not see the ID field but in the bound column property you listed column 1. Thus, the ID field would be stored in the table (the data source of the combo box - not row source) and the user would never know it.

Also, you are creating a recursive table (recursive because it has to call back on itself in order to populate certain fields). This is not an issue, but it could cause problems in grouping and sorting in your reports depending on what your vision for the application is. You can do searches on google for recursive tables for a better feel. There are non-programatic work arounds that do are not completly normalised - your application is. Said all of that just in case you wanted to know.

If you are still stymied by this issue, you can post a copy of the db and I can take a look at it for you.

-dK

EDIT: The yes/no approach is fine; however what if you wanted to list a manager, a vice-president, etc, so you could then put that person in the manager's manager box?

 

[spudracer]

I'm always trying to simplify something before making the underlying program more difficult. So that's why I'm trying everything I know before going for the obvious fixes.

As for the Yes/No box, as this is a military database, the yes/no option will suffice for anyone as we have no manager, vice-president positions.

 

[spudracer]

Hopefully you get the basic idea from what I've attached. The combo box can be found under the command tab. The combo box is pulling part of the correct information, but not all of it.

 

[dkinley]

Here ya go. You were almost there - you had the control source of the combo box incorrect along with a design flaw.

I went ahead and made a couple of changes after looking at it. The design flaw was in the table design you had the SUPERVISOR field designated as a look up field. This is a no-no because of the problems with look-ups. You can searches on this site where it is discussed in-depth. I removed it and made it a regular field. I didn't check, but you should go through your tables and rectify this sort of error. Since you are using forms, what is in the design of the fields really doesn't matter much anyhow.

I also took the liberty of concantenating the query source of the combo box to enhance the drop down look. Just an option, you can change it back if you want - just wanted to demonstrate the storage of the ID and presenting the user with usable information. Just in case you wanted to use something like that now or in the future.

I noticed that none of your tables have a primary key. So, to demonstrate, I added a primary key (autonumber) to the personnel table. Now, when you click on the supervisor, the personellID is stored in that field. To verify, look at the supervisor field in the table, it will have Joe's primary key in there. In terms of data, think of this way. Suppose you had 10,000 entries in the database - it takes up alot less space to store a number than whatever it was you were going to store. This type of thought should be applied across the board for the best results.

Now, when you go to create your queries and you want details of the supervisor, you can just add another copy of the personnel table to the query - link (a left-join) the primary key to the supervisor field (of the copy) since it is now acting as a foreign key. At this point you can pull any field you want about the supervisor into the query for use in a report or what-have-you.

Hope that helps.

-dK

 

[spudracer]

Thank you!!!!

 

[dkinley]

I've attached what I meant by copying the table for the supervisor bit.

Check the query, qrySample. I've also used a column to concantenate to give you an idea of what you can do to prep the query for the report.

-dK

 

[spudracer]

As soon as I saw the query the lightbulb went off. I was drawing a blank...thanks again!

 

[dkinley]

You betcha.

-dK

 

[spudracer]

I've got a problem. I posted a question early today, but deleted it when I thought I figured it out.

I've got another database I'm working on where I'm using the similiar data to make a combo box much like what was discussed earlier in this thread. Everything works fine, names show up like they're supposed to, however, when I go to select a name I get an error that says "The value you entered isn't valid for this field." I checked the settings of the other database I used this in against this one and all the settings are the same (where they need to be, anyway).

Any ideas on this? Due to the location of the file, I cannot post an example. If needed, I can try and create a dummy file and post that. I will also backtrack and see if something is checked or marked as something different than what it's supposed to be.