Help with Sub Forms

[Guest]

My Access 2003 project consists of a switchboard with several sub forms all
positioned on top of one another. On the switchboard is a number of command
buttons which makes each sub form visible or not. All works great.

On one of my subforms i have a print envelope command button which looks at
values on the same subform and copies them to a table. The code is as follows:

Sub AddAddressToEnvelope()
On Error GoTo Err_AddAddressToEnvelope

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.Title = Forms!sbfrmSwitchboardCustomers.combo101"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.FirstName =
Forms!sbfrmSwitchboardCustomers.FirstName"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.LastName = Forms!sbfrmSwitchboardCustomers.LastName"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.Company = Forms!sbfrmSwitchboardCustomers.Company"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.Address1 = Forms!sbfrmSwitchboardCustomers.Address1"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.Address2 = Forms!sbfrmSwitchboardCustomers.Address2"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.City = Forms!sbfrmSwitchboardCustomers.City"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.County = Forms!sbfrmSwitchboardCustomers.County"
DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET
tblPrintingOnToEnvelopes.PostCode = Forms!sbfrmSwitchboardCustomers.PostCode"
DoCmd.SetWarnings True

Exit_AddAddressToEnvelope:
Exit Sub

Err_AddAddressToEnvelope:
MsgBox Err.Number & Err.Description, , CurrentDb.Properties("AppTitle")
Resume Exit_AddAddressToEnvelope

End Sub

This vba code works exactly as it should when the sub form is opened on its
own, without the switchboard but when the project is opened up without using
the shift key and the switchboard opens, the vba code cant find all the
variables eg Forms!sbfrmSwitchboardCustomers.PostCode"

Any ideas anyone, please.

 

[Svetlana]

You may need to refer to mainform then to subform as
Forms!NameOfMainForm.NameOfSubForm!NameOfControl

 

[Steve Schapel]

StuJol,

This is because when sbfrmSwitchboardCustomers is a subform, it is not
open, and so the code cannot reference values from controls on a form
that is not open. Since the values being referenced are on the subform,
and this is where the code is beong run from, you can use the Me keyword
to refer to the subform itself.

You are running a separate Update for each value, to the same table.
This is not necessary. Why not do it in one hit?

DoCmd.RunSQL "UPDATE tblPrintingOnToEnvelopes SET" & _
" Title = '" & Me.combo101 & "'," & _
" FirstName = '" & Me.FirstName & "'," & _
" LastName = '" & Me.LastName & "'," & _
" Company = '" & Me.Company & "'," & _
" Address1 = '" & Me.Address1 & "'," & _
" Address2 = '" & Me.Address2 & "'," & _
" City = '" & Me.City & "'," & _
" County = '" & Me.County & "'," & _
" PostCode = '" & Me.PostCode & "'"

 

[Guest]

Steve,

Seems to be working fine now. Many Thanks