Populating multiple table fields based on one form

[davenumber40]

I’m creating a small database in Access 2003(XP) to track issues during software testing. As far as databases go, it’s going to be a relatively small, short term project (No more than 20,000 records in the main table over about 2 months).

I have a main table (Issues) that is populated by a form (IssueAdds). On my form there is a combobox to select Opened by (who created the record). That list is populated by a table called Contacts.

Here is how I am populating the combo for that field:

SELECT Contacts.ID, Contacts.[Last Name] & ", " & Contacts.[First Name] AS Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

I’m using Contacts.ID (a numerical value that is the primary key in the Contacts table) here because I have a smart tag that allows you to email them from the table or form.

Here’s my problem:
I have to export the main table to excel daily (A management thing that I’m not going to be able to change). When I export, the Contact.ID field is displayed as a numerical value. I need it to show the name.

Basically, I need to populate another field in the Issues table with the actual name from the combobox selection in the IssueAdd form.

I had a query that pulled the information however, the query caused problems with a search button I have on another form.

Can I do a dlookup that will export properly? I feel like there is a simple solution that I'm missing. I opened Access for the first time a month ago so I could be missing a very obvious solution. Thanks all!

[emsik1001]

Hi

I'm not sure if I understand this correctly but Is there a reason why you cannot set up a separate query (which won't be used anywhere else) to export the data? if you use form to export the data then I would use the new query to do the actual export.

I hope it makes some sense.

Regards
Emil

[davenumber40]

The idea is this:

I have a search form (with an attached subform) that does the export. Management wants end users to be able to search, then export the results.

[nico5038]

I would use a datasheet subform based on the fields you want to show and select on.
Instruct the users to use the right-click pop-up form.
(See: http://www.geocities.com/nico5038/xR...nstruction.zip)

Now in the expotr button OnClick code you can use the Me.Filter to add as WHERE clause to the query for the export.

Getting the idea ?

Nic;o)