By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Syntax Question about Exporting Form-Based ComboBox Data to Word

P: 2

I am exporting data displayed in an access form into a word docm, with limited success.

The form is based on a query bringing together information from a number of tables.

Once the form is displayed some additional data can be input (and saved to a Table) and then the form printed.

I also want to export some of the displayed data into a word doc.

The word .docm is set up using bookmarks and is macro enabled.

This export runs smoothly unless the data has been called from a combo box based table.

for example, when I use:
Expand|Select|Wrap|Line Numbers
  1. .FormFields("ExpiryDate").Result = Me.ExpiryDate
Expand|Select|Wrap|Line Numbers
  1. .FormFields("StorageRequirements").Result = Me.StorageRequirements
I see the following:

the form it comes from looks like:

So i have typed the date into the form directly

The storage information has been called into the form based on an ingredient that has its storage requirements selected from a table:

I am hoping that I need to tweak the syntax of the form fields line above in some way as i am not a VBA adept.

I have tried inserting .column(1) but this results in an empty field rather than the row id.

All thoughts welcome,

Attached Images
File Type: png Storage Requirements Table.PNG (6.8 KB, 119 views)
File Type: png Clip From Form.PNG (4.0 KB, 120 views)
File Type: png Clip From Word Export.PNG (2.5 KB, 118 views)
Apr 26 '18 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 3,210
Hi bombora!

Welcome to Bytes.

First, when posting code to this forum, please use code tags around your code--a requirement on this site that helps us see your code better and troubleshoot.

So, when I look at these two statements:
Expand|Select|Wrap|Line Numbers
  1. .FormFields("ExpiryDate").Result = Me.ExpiryDate
  2. .FormFields("StorageRequirements").Result = Me.StorageRequirements
This is what I see. You are using the value of the underlying recordset and assigning that value to your FormFields.

Please note, that when you create a form in Access and drag and drop a field onto that form, the control name becomes the name of the field to which that control is bound. For example, you have a field named StorageRequirements. It is a lookup field that gets ists data from your Storage Requirements table. When you drag that field onto your form, you now have a Control which bears the Name of StorageRequirements. However, when you use the syntax of Me.StorageRequirements, it is ambiguous to Access whether you are referring to the Field named StorageRequirements or the control named StorageRequirements. It defaults to the underlying field.

When we look at that field, its Value is the index, not the type of storage--which I think it is clear from your post that you understand this difference. What you are trying to do is get the type of storage, not the index.

So, begin by renaming your controls (this is a good habit to get into with all your projects). Determine a good naming convention for your controls and stick with it (example: txt[Name] for text boxes, cbo[Name] for combo boxes, lbl[Name] for labels, etc.). So the control named StorageRequirements should now become cboStorageRequirements.

Now you can apply .column(1) to that control and you should get your desired result:

Expand|Select|Wrap|Line Numbers
  1. .FormFields("StorageRequirements").Result = Me.cboStorageRequirements.Column(1)
Hope this hepps!
Apr 26 '18 #2

P: 2
Thankyou so much for your time help.
Your solution works for this field.
Apr 27 '18 #3

Expert Mod 2.5K+
P: 3,210
I'm glad I could be of some hepp! Let us know ifyou run across any other issues.
Apr 30 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.