Export Query to Excel - But with combobox values not text (1 Viewer)

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
Hello All,


I have done some VBA to export a query into excel which was easy. Issue is that this is then to be imported into another DB.

There are several combo boxes that take data from other tbls, instead of exporting the number it exports the actual text, this owuld be fine except when you import this into the other DB none of it works as it is a number not a text value for them fields.

Anyone know how to export the number of the combo fields and not the text into a spreadsheet? Hope that makes sense!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
It may depend on how you have set up those comboboxes. Did you set them up in the table's design view?
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
I did but they're linked to another tbl rather than me entering the lookup value
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
I did but they're linked to another tbl rather than me entering the lookup value
As I said, it depends on how you set it up. Can you tell us how? Normally, if you set it up correctly, it shouldn't be a problem exporting the data with the numerical values.
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
The tbls with the lookup info in are

ID Code
1 1.01 dlkjcnsldc
2 1.02 dkcldc

Etc. the ID is autonumber

There are about 5 fields in the main tbl that all use different tbls for the lookup value
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
The tbls with the lookup info in are

ID Code
1 1.01 dlkjcnsldc
2 1.02 dkcldc

Etc. the ID is autonumber

There are about 5 fields in the main tbl that all use different tbls for the lookup value
What we really need to see are the properties of the comboboxes. For example, these:

Row Source
Column Count
Column Widths
Bound Column
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
1633979229511.png
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
Should have said also that the DB is split, don't think it should make too much difference though?
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
Row Source SELECT [tblDept].[ID], [tblDept].[Department/Charter] FROM tblDept ORDER BY [Department/Charter];
Column Count 2
Column Widths 0cm;2.54cm
Bound Column 1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
Row Source SELECT [tblDept].[ID], [tblDept].[Department/Charter] FROM tblDept ORDER BY [Department/Charter];
Column Count 2
Column Widths 0cm;2.54cm
Bound Column 1
Okay, the setup looks correct. So, if that's not working out, we'll need to see the VBA code for exporting it next. Can you please post that?
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
DoCmd.SendObject acQuery, "Export", acFormatXLSX, vRecipient, , , vSubject, vMsg, True

I export it to an email attachment using the above
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
DoCmd.SendObject acQuery, "Export", acFormatXLSX, vRecipient, , , vSubject, vMsg, True

I export it to an email attachment using the above
And it shows the text columns rather than the IDs? Can you post a sample db with test data?
 

mounty76

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2017
Messages
341
Have emailed it through to you, might want to check junk folder
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
DoCmd.SendObject acQuery, "Export", acFormatXLSX, vRecipient, , , vSubject, vMsg, True

I export it to an email attachment using the above
Okay, I just did a quick test and see what you're saying. In that case, I think what you can do is update the query to display the ID values by joining the lookup tables. I don't remember this was happening before. Perhaps MS made an update to the way Access handles this scenario now. It used to export the number values as far as I can remember.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
42,970
This may be a result of using a lookup at the table level. If you remove the lookup from the table, you will always see the ID which is probably numeric. Removing the table level lookup won't affect your forms since they probably have combos but it may affect your reports. You will either replace the TextBox with a combo or modify the RecordSource query to join to the lookup table and select the text field. Then you can use the text field in the report.
 

Users who are viewing this thread

Top Bottom