Combo Field Values

[andysgirl8800]

I'm really hoping this is just a little bit of code here, but here's what I'm trying to do, without success so far...

I have a cascading combo box field called [Name], which is linked to a table that only has 3 columns, ID, Name, and Number. The combo box displays the Name column, with the other 2 hidden. When a name is selected from the list, the neighbouring field [Number] is auto populated with the Number column in the combo box. This works beautifully in saving some data entry time. My issue, however, comes when I try to export data into and Excel format. The two fields, [Name] and [Number] display as numbers instead of their values. What do I need to do so that the names "stick" as words, not as numbers?

I know this must be related to the hidden ID column, and that forms don't store any actual data. I tried to modify the table design, but then it throws off the auto-pop function on the form. I thought it may have something to do with joining the table fields to each other, but I couldn't get that working either. Any suggestions would be welcome. Thanks.

 

[RuralGuy]

You aren't using LookUp fields are you? The Evils of Lookup Fields in Tables
Here's some other links that you should review as well:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[andysgirl8800]

Thank you RG, but I am not using LookUp fields. I read those articles, but they don't seem to apply to my problem. Any other suggestions?

 

[andysgirl8800]

Can I accomplish this better with a DLookUp function? I've been trying to avoid that, as many posts here suggest to. But I really need to be able to view the actual value NAME, not it's NUMBER when I run a report or export the data to Excel. Will DLookUp do that? It's only one field, and I already have a table built for just the name and ID information.

I have my text box, [POSCode] linked to a table, [tblPOSCodes]. The table has three columns, ID, POS Name, and Facility Tax ID When a selection is made from [POSCodes] I want a second text box, [TaxID] to fill in with the Facility Tax ID. Is there just a simple line of VBA code to generate a DLookUp for this?

 

[RuralGuy]

I'm having a little trouble understanding what you are doing. Is your form bound to the tblPOSCodes table? How do you "make a selection" in a textbox?

 

[andysgirl8800]

Sorry for the confusion. My form is bound to a different table, tblInpatientLog. The field [POSCodes] is currently a cascading combo box with record source linked to tblPOSCodes with a column count of 3, and column widths of 0;1;0; to hide the auto ID and TaxID. The field [TaxID] has a control source of =[POSCode].[Column](2) to display the hidden column from the combo box [POSCodes]. I know this is terribly confusing to explain, and I know there MUST be a better way to auto pop the [TaxID] field based on the [POSCodes] selection AND have both those fields display DATA, not a referential number when viewed on a report or exported to Excel, I just can't work it out. Thanks for taking the time to mull this over, I appreciate your help.

 

[andysgirl8800]

Made some progress, but not much. To export my data into Excel, I was able to retain field values by making the form data itself export to excel. However, when I do this, the data appears in Excel in the same order as it appears on the Access form. Makes sense. But I need this data in a specific order. So I made a query to sort the fields the way I need them, but now the data is exporting again with its field NUMBER instead of its value (will show 1 instead of "Memorial")

 

[RuralGuy]

In your export query, join the tblInpatientLog with the tblPOSCodes and don't include the ID field from either table but include the tblPOSCodes.[POS Name Facility] and tblPOSCodes.[Tax ID] fields.

 

[andysgirl8800]

thanks RG, I'll give that a go and post back

 

[andysgirl8800]

Alright, I gave it a try, and now the fields are appearing blank, no numbers or text data. When prompted in the Join properties, I selected option 2, as option 1 or 3 produced no results at all.

 

[RuralGuy]

Maybe if you could post your db it would clear up some of my questions. Remove anything sensitive of course.