getting value from text box in sub form

[gavin31]

Small problem, and I know the solution should be simple.

I have form called frmExpenses

This has a sub form called frmExpenseItems.

In frmExpenseItems is a textbox called totalAmount.

In frmExpenses when user clicks command button, I want to send totalAmount value to an Excel spreadsheet.

The relevant code I have at the moment (which does not work) is as follows

With xlApp
:
:
:

'Add total amount of expense claim to Excel
.Cells(36, 6) = Forms!frmExpenses!frmExpenseItems.totalAmount

:
:
:
End With

The error I get is "Microsoft Access can't find the form frmExpenseItems referred to in a macro expression or Visual Basic Code"

I checked the spelling of the form name and that is correct.

I have tried various combinations of ! and . and [....], but can't for the life of me figure out how to correctly refernce the totalAmount textbox.

Any help greatly appreciated

Gavin

 

[kjv1611]

Is the value stored in an underlying table? If so, you could reference the table/field as apposed to the form/control...

 

[kjv1611]

Also, have you tried taking out the main form reference?

I believe there is a slight variation of format when you refer to a subform within a form... I'll see what I can find...

 

[kjv1611]

Ahh, ok, try this:

.Cells(36, 6) = Forms!frmExpenses![highlight][[/highlight]frmExpenseItems [highlight]SubForm][/highlight].totalAmount

 

[kjv1611]

Also, here is a link to Office online help about this:
[URL unfurl="true"]http://office.microsoft.com/en-us/assistance/HP030716381033.aspx[/url]

 

[gavin31]

kjv1611

Thank for you pointers. Read the Microsoft Office page, finally sorted out what code should be:

.Cells(36, 6) = Forms!frmExpenses![ExpenseItems]![totalAmount]

where [ExpenseItems] is the name of the control in the main form that holds the subform, and not the name of the actual subform itself.

Anyway got it sorted

Thanks again

Gavin