Subform then Write SQL

[Sash]

I'm building an SQL statement from 20 fields on a form that will update a
table in SQL Server. All fields that are NOT subforms work. Can someone be
so kind as to tell me what I'm missing on the subform format.


If Not IsNull(Forms!FrmS23.SURGDESC.Form![pimem_line]) Then
strSQL = strSQL & "dbo_Anesth.[ip_surgdesc] = '" &
Me.Form!FrmS23.SURGDESC.Form![pimem_line] & "' "
End If

 

[Dirk Goldgar]

I'm building an SQL statement from 20 fields on a form that will
update a table in SQL Server. All fields that are NOT subforms work.
Can someone be so kind as to tell me what I'm missing on the subform
format.


If Not IsNull(Forms!FrmS23.SURGDESC.Form![pimem_line]) Then
strSQL = strSQL & "dbo_Anesth.[ip_surgdesc] = '" &
Me.Form!FrmS23.SURGDESC.Form![pimem_line] & "' "
End If

In your example, what are:

The name of the main form?
The name of the subform *control* on the main form?
The name of the control on the subform?

And where is the code executing? On the main form, on the subform,
elsewhere?

If the code is executing on the main form, then if the subform control
on that form is named "SURGDESC" and the control on the subform is named
"pimem_line", then this ought to work:

If Not IsNull(Me!SURGDESC.Form![pimem_line]) Then
strSQL = strSQL & _
"dbo_Anesth.[ip_surgdesc] = '" & _
Me!SURGDESC.Form![pimem_line] & "' "
End If

 

[Sash]

I'm building an SQL statement from 20 fields on a form that will
update a table in SQL Server. All fields that are NOT subforms work.
Can someone be so kind as to tell me what I'm missing on the subform
format.


If Not IsNull(Forms!FrmS23.SURGDESC.Form![pimem_line]) Then
strSQL = strSQL & "dbo_Anesth.[ip_surgdesc] = '" &
Me.Form!FrmS23.SURGDESC.Form![pimem_line] & "' "
End If

In your example, what are:

The name of the main form?
The name of the subform *control* on the main form?
The name of the control on the subform?

And where is the code executing? On the main form, on the subform,
elsewhere?

If the code is executing on the main form, then if the subform control
on that form is named "SURGDESC" and the control on the subform is named
"pimem_line", then this ought to work:

If Not IsNull(Me!SURGDESC.Form![pimem_line]) Then
strSQL = strSQL & _
"dbo_Anesth.[ip_surgdesc] = '" & _
Me!SURGDESC.Form![pimem_line] & "' "
End If

The name of the main form?
FrmS23

The name of the subform *control* on the main form?
Name of Subform is SURGDESC (Should I be using something else?)

The name of the control on the subform?
Name of field on subform is pimem_line

And where is the code executing?
Code is executing from the main form via on button click

I tried making the change you suggested and I'm getting and error. What's
frustrating for me is that my reference to the subform worked prior to
creating the SQL server back-end. Any other ideas would be greatly
appreciated. My project is a week behind because I can't get the code
tweaked correclty.

 

[Dirk Goldgar]

I tried making the change you suggested and I'm getting and error.

What is the error? Please quote the message and tell me the error
number, if that is available to you.

 

[Sash]

Dirk,
Thank you. I was driving myself crazy. The message was something like "No
value specified". I figured out that if subform had no data, SQL server
didn't like it. Whereas, Access just knew to skip it or make that value
Null. I had to put some code in to check for this, in addition need to
change the join on one of my queries.
Thanks again!
Sash

 

[Dirk Goldgar]

Dirk,
Thank you. I was driving myself crazy. The message was something
like "No value specified". I figured out that if subform had no
data, SQL server didn't like it. Whereas, Access just knew to skip
it or make that value Null. I had to put some code in to check for
this, in addition need to change the join on one of my queries.

Hmm, probably had to do with the subform not supporting additions, so
being left completely blank. You're okay now? Great. You're welcome.