Syntax Question about Exporting Form-Based ComboBox Data to Word

[bombora]

Hi,

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:

.FormFields("ExpiryDate").Result = Me.ExpiryDate

.FormFields("StorageRequirements").Result = Me.StorageRequirements

I see the following:
[IMGNOTHUMB]https://bytes.com/attachments/attachment/9430d1524711864/clip-word-export.png[/IMGNOTHUMB]

the form it comes from looks like:
[IMGNOTHUMB]https://bytes.com/attachments/attachment/9429d1524711864/clip-form.png[/IMGNOTHUMB]

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:
[IMGNOTHUMB]https://bytes.com/attachments/attachment/9428d1524711864/storage-requirements-table.png[/IMGNOTHUMB]

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,

Rob.

[twinnyfo]

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:

.FormFields("ExpiryDate").Result = Me.ExpiryDate
.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 StorageRequirem ents. 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 StorageRequirem ents. However, when you use the syntax of Me.StorageRequi rements, it is ambiguous to Access whether you are referring to the Field named StorageRequirem ents or the control named StorageRequirem ents. 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 StorageRequirem ents should now become cboStorageRequi rements.

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

.FormFields("StorageRequirements").Result = Me.cboStorageRequirements.Column(1)

Hope this hepps!

[bombora]

Thankyou so much for your time help.
Your solution works for this field.

[twinnyfo]

I'm glad I could be of some hepp! Let us know ifyou run across any other issues.