Referencing a controls on a subform from a module using a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to use a for...next loop to collect info from 4 controls on a
subform (txtUpdC2_D1;txtUpdC2_D2;txtUpdC2_D3, and;txtUpdC2_D4) but can't seem
to get the syntax right. I get an error message: "Run-time error 438 Object
doesn't support this property or method."

Here's my code:

For j = 1 To 4
If (Not (Forms!frmCustomersViewEdit.frmCustomerDetailSubForm.txtUpdC2_D
& j) Like "") And (Not
IsNull((Forms!frmCustomersViewEdit.frmCustomerDetailSubForm.txtUpdC2_D & j)))
Then
consql = "INSERT INTO tblCustomerContractDocs (CustomerID,
StartDate, DocumentID) VALUES(" &
Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtCustomerID & ", #" &
CDate(Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtUpdC2_StartDate)
& "#, " & Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtUpdC2_D & j
& ")"
MsgBox consql
'DoCmd.RunSQL (consql)
End If
next j


Any suggestions on how to get something like this to work

Thanks in advance.

Cheers,
PWR
 
PWR said:
I'm trying to use a for...next loop to collect info from 4 controls on a
subform (txtUpdC2_D1;txtUpdC2_D2;txtUpdC2_D3, and;txtUpdC2_D4) but can't seem
to get the syntax right. I get an error message: "Run-time error 438 Object
doesn't support this property or method."

Here's my code:

For j = 1 To 4
If (Not (Forms!frmCustomersViewEdit.frmCustomerDetailSubForm.txtUpdC2_D
& j) Like "") And (Not
IsNull((Forms!frmCustomersViewEdit.frmCustomerDetailSubForm.txtUpdC2_D & j)))
Then
consql = "INSERT INTO tblCustomerContractDocs (CustomerID,
StartDate, DocumentID) VALUES(" &
Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtCustomerID & ", #" &
CDate(Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtUpdC2_StartDate)
& "#, " & Forms!frmCustomersViewEdit!frmCustomerDetailSubForm!txtUpdC2_D & j
& ")"
MsgBox consql
'DoCmd.RunSQL (consql)
End If
next j
Click to expand...


I think this kind of syntax is what you're looking for:

With Me.frmCustomerDetailSubForm.FORM
For j = 1 To 4
If Not .Controls("txtUpdC2_D" & j) = "" _
And Not IsNull(.Controls(".txtUpdC2_D" & j)) Then
consql = "INSERT INTO tblCustomerContractDocs " _
& "(CustomerID, StartDate, DocumentID) " _
& "VALUES(" & !txtCustomerID _
& ", #" & !txtUpdC2_StartDate & "#, " _
& .Controls("txtUpdC2_D" & j) & ")"
MsgBox consql
'DoCmd.RunSQL (consql)
End If
next j
End With
 
Hi Marshall,

I've got the looping code working with the Me in a form module but can't get
it to work when I'm trying to reference a control on a subform from a
standard module (a module under the modules tab).

Thanks,
PWR
 
When you reference controls on a subform, you need to go through the subform
container on the form:

Me.MySubformContainer.Form!MyControl
 
PWR said:
I've got the looping code working with the Me in a form module but can't get
it to work when I'm trying to reference a control on a subform from a
standard module (a module under the modules tab).
Click to expand...


What Doug said.

Why would you want to put this in a standard module when it
is specific to the form and its controls? The only logical
reason for doing that is if you will be calling it from two
or more different forms that have the exact same control
names, subform, etc. If that's what you're doing, I then
have to ask the question - Why not use the same form instead
of different forms??

Regardless of all that, where ever you call the procedure,
you need to pass the form object, the control objects or,
probably best, the field values as parameters of the
procedure.
 
Back
Top