Help Storing text value of combobox not ID

[scottemotive]

Hi,

I have a db (fiel too big to attach i can email if required)
which has three comboboxs which are all related to their relevent tables.
i.e Employee combobox links to employee table etc...

The question:

the three comboboxes output to a table calls CMS Compliance Table, (which is where i want to store my records)
however the data stored in their is the "ID" from the tables and not the text values of the fields.
I want to be able to store the text values in the table
for example i select (the numbers are the ID column which is hidden)

cbo 1: Joe Bloggs, 24
cbo 2: Joes Manager, 2
cbo 3: Joes Dept, 31

I want the text value stored in the table and not the ID
can anyone help me on this please?

thank you for any assistance

Scott.

 

[Uncle Gizmo]

Why?????????????????????????????????????

 

[scottemotive]

becuase i export the table into a excel pivot table where i report and compare results of my data. which is the reason i need text values

thank you

 

[livvie]

becuase i export the table into a excel pivot table where i report and compare results of my data. which is the reason i need text values

thank you

Why not use the Access Pivot Charts - saving you the hassle of exporting.

Have you tried me.txtbox = me.cboCombo.colum(1) in the afterupdate event of your combo box

 

[Uncle Gizmo]

That's a perfectly good reason! However it does mean that you are breaking one of the basic rules of database construction in that you are storing more information than necessary.

An alternative method may be too generate the table you require either as a query as shown in the attached example, or alternatively use a make table query at the time you need the text information.

 

[scottemotive]

i like the sound of the make table query, how would i format this?

sorry to be a complete newbie pain.

thanks

scott

 

[Uncle Gizmo]

The open the query in the example "qryTableToExport" In design mode, select "Query" from the query menu and then choose the option >>> Make-Table query.

this will convert the query into a make table query, you will also need to give the name of the table you want to generate in the popup form.

 

[scottemotive]

ill try it thanks

 

[riggsd]

I'm trying to get this to work for me and it creates the new table, but it is blank. The query works fine until I add the POC table that contains the linked information.

See attached image to see the relationships I need. I've had to blank out some of the information on the image.

Here is the SQL code it generates:

SELECT tbl_RDFVersions.CName, tbl_RDFVersions.AID, tbl_RDFVersions.RDFVersion, tbl_RDFVersions.RDFName, tbl_PPLInstances.LFile, tbl_PPLInstances.RDFFile, tbl_PPLInstances.Instance, tbl_PPLInstances.Description, tbl_PPLInstances.Type, tbl_PPLInstances.PFile, tbl_RDFVersions.PClass, tbl_PPLDetails.SOR, tbl_PPLInstances.PN, tbl_PPLInstances.FF, tbl_PPLInstances.LF, tbl_PPLInstances.EventID, tbl_PPLDetails.LIFL, tbl_PPLDetails.VMth, tbl_PPLDetails.RUpdated, tbl_PPLDetails.RActual, tbl_PPLDetails.EVersion, tbl_PPLDetails.EActual, tbl_PPLDetails.FComplete, tbl_PPLDetails.FActual, tbl_PPLDetails.SComplete, tbl_PPLDetails.SActual, tbl_PPLDetails.Concurrence, tbl_PPLDetails.Version, tbl_PPLDetails.EML, tbl_PPLDetails.SML, tbl_RDFVersions.tbl_Subsystem_ID, tbl_RDFVersions.BPFM, tbl_RDFVersions.BOwner, tbl_RDFVersions.BManager, tbl_PPLInstances.PProducer, tbl_RDFVersions.NPFM, tbl_RDFVersions.NOwner, tbl_RDFVersions.NManager, tbl_RDFVersions.MPOC, tbl_PPLInstances.Purpose, tbl_PPLInstances.InstallationScenario, tbl_PPLInstances.Constraint, tbl_PPLInstances.OIFL, tbl_PPLDetails.SCR, tbl_PPLDetails.SIF, tbl_PPLDetails.SDN
FROM tbl_POC INNER JOIN (tbl_RDFVersions INNER JOIN (tbl_PPLInstances INNER JOIN tbl_PPLDetails ON tbl_PPLInstances.InstanceUID = tbl_PPLDetails.PPL_ID_Lookup) ON tbl_RDFVersions.RDFUID = tbl_PPLInstances.tbl_RDF_ID) ON (tbl_POC.POCUID = tbl_RDFVersions.MPOC) AND (tbl_POC.POCUID = tbl_RDFVersions.NManager) AND (tbl_POC.POCUID = tbl_RDFVersions.NOwner) AND (tbl_POC.POCUID = tbl_RDFVersions.BManager) AND (tbl_POC.POCUID = tbl_RDFVersions.BOwner) AND (tbl_POC.POCUID = tbl_RDFVersions.NPFM) AND (tbl_POC.POCUID = tbl_RDFVersions.BPFM) AND (tbl_POC.POCUID = tbl_PPLInstances.PProducer)
WHERE (((tbl_PPLInstances.Obsolete)=0))
ORDER BY tbl_RDFVersions.CName, tbl_RDFVersions.AID, tbl_RDFVersions.RDFVersion, tbl_PPLInstances.Instance;

Any assistance is appreciated.

 

[Uncle Gizmo]

it creates the new table, but it is blank..

Instead of a make table query, if you change it to a select query does it return any results?

 

[riggsd]

No, that doesn't make a difference.

What I'm thinking I may have to do is run the query and append it to a blank table, then use VBA to go through the six columns, get each lookup id, go to the appropriate lookup table, find that id, then get the real data and put it in the table to replace the lookup id.

Now I just have to figure out how to do that.

 

[Uncle Gizmo]

go to the appropriate lookup table, find that id, then get the real data

If you look at the example previously posted in this thread and examined the query "qryTableToExport" you will see that this query does what you suggest .

 

[riggsd]

It may, but I can't get it to work with my database. The example has the id and data in two different fields, not in a lookup field.

I'll have to upload an example database later this evening to my personal site since its larger than the allowed attachment size and I can't upload to my site until I get home. It will have the problem tables/queries with a sample of two tables. I actually have over 250,000 attributes between the tables spread out over 44 fields, although I only included three fields.

The query, qry_GetMaturity is what I need but if that is exported, then the maturities are replaced by numbers because those fields are lookup fields.

The query, qry_ForExport, is the same basic query as the GetMaturity one, but it creates a new table. If you run it, you'll see that there are numbers in the maturity fields instead of text. I need the text not the numbers.

dB is Access2K.

 

[Uncle Gizmo]

I'll have to upload an example database later this evening

I have just suggested that you do this in the other thread you have running. I would also suggest that it is not a good idea to start multiple threads on a particular problem. Not only does it cause people to waste time answering your questions twice, but it is also very annoying!

 

[riggsd]

I didn't feel they were exactly the same since I was trying to do something different so I started a new thread.

Anyway, here's the example dB:

http://www.fignations.com/_storage/riggsd_db.zip

 

[Uncle Gizmo]

Well, the first thing is that it is considered good practice not to use lookup tables in a table. They cause you lots of confusion and the only use they have really his if you’re user looks at the table they see what the data represents, and not the real data. But you should never build a database where the user can look at it’s tables anyway. So there’s no point in using look up tables in a table. It’s one of those things Microsoft has put into the program that is really a sales gimmick other than a useful function.

What I found was that because you were using the same table as a lookup table for two different fields in your master table then this caused a conflict in the SQL behind the query.

To so what I’ve done is made two tables from one table to get around the problem. Hopefully somebody else may have experienced this problem before and come up with a better way.

 

[riggsd]

I understand what you're saying and see how the way the database is set up is causing problems but not sure I can split up the tables like this since its not the only one. There are about four with one that is referenced by 7 other fields, six in one table and one in another. This second table has about 100 records and lists points of contact, of which there are seven types, but one POC can be assigned several roles. One of the reasons I used the lookups is because the database is already 8 MB in size and the tables are used by the database admin to maintain the large amounts of data. It's a lot easier to paste 500 cells at once in a table than one at time in a form. So the admin needs to see the actual text and not numbers. The users don't see these tables, just forms and reports, and they don't modify the data either.

However, I can now take this and see if there are ways to modify the database design to get what I need.

Thank you for your assistance.