New Record

  • Thread starter Thread starter jimphilly
  • Start date Start date
J

jimphilly

I have a form which I use to update records. However, sometimes a record
doesn't exist for the person in the table I would like to update, so I need
to create one and update the appropriate fields. The problem I'm having is I
can't figure out a clean way to do this. I can create the record with the
following code. (Excluding the first line)

If [Forms]![ONE]![sfrm ONE]![SSN] <> [Forms]![lONE]![SSN] Then
'Create and Update Participant record in FIRST table.
Dim cmd As ADODB.Recordset
Set cmd = New ADODB.Recordset
Dim EMP As String
EMP = "Select FIRST.*,"
With cmd
.Open EMP, CurrentProject.Connection, adOpenStatic, adLockOptimistic
.AddNew
.Fields("SSN") = [Forms]![loan/emp form history]![SSN]
.Fields("G_SNT") = "Y"
.Fields("G_SNT_DATE") = Date
.Fields("YEAR") = "06/07"
.Update
End With
cmd.CLOSE
Set cmd = Nothing

End If

I can update the record with this code.

If [G_ELIGIBLE] = "Y" Then
If [Forms]![ONE]![sfrm ONE Subform]![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform]![REMAIL_G] = "Y"

Else
[G_SNT] = "") Then
[G225_SNT] = "Y"

End If

However, I can't put the two together right. Sometimes a record will exist
with nothing but an SSN. I tried using NULL or NOT NULL but it doesn't work.
Intellisense tells me the field G_SNT = NULL but the code doesn't pick it up
therefore it doens't engage the IF THEN statement.

Also I get the error "You entered an expression that has no value" when it
compares the two SSN fields in forms [ONE] and sfrm [ONE].

Any help would be appreciated.
 
jimphilly said:
I have a form which I use to update records. However, sometimes a record
doesn't exist for the person in the table I would like to update, so I
need
to create one and update the appropriate fields. The problem I'm having
is I
can't figure out a clean way to do this. I can create the record with the
following code. (Excluding the first line)

If [Forms]![ONE]![sfrm ONE]![SSN] <> [Forms]![lONE]![SSN] Then
'Create and Update Participant record in FIRST table.
Dim cmd As ADODB.Recordset
Set cmd = New ADODB.Recordset
Dim EMP As String
EMP = "Select FIRST.*,"
With cmd
.Open EMP, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
.AddNew
.Fields("SSN") = [Forms]![loan/emp form history]![SSN]
.Fields("G_SNT") = "Y"
.Fields("G_SNT_DATE") = Date
.Fields("YEAR") = "06/07"
.Update
End With
cmd.CLOSE
Set cmd = Nothing

End If

I can update the record with this code.

If [G_ELIGIBLE] = "Y" Then
If [Forms]![ONE]![sfrm ONE Subform]![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform]![REMAIL_G] = "Y"

Else
[G_SNT] = "") Then
[G225_SNT] = "Y"

End If

However, I can't put the two together right. Sometimes a record will
exist
with nothing but an SSN. I tried using NULL or NOT NULL but it doesn't
work.
Intellisense tells me the field G_SNT = NULL but the code doesn't pick it
up
therefore it doens't engage the IF THEN statement.

Also I get the error "You entered an expression that has no value" when it
compares the two SSN fields in forms [ONE] and sfrm [ONE].

Any help would be appreciated.
Click to expand...

You're not addressing the subform properly. Your code:

If [Forms]![ONE]![sfrm ONE Subform]![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform]![REMAIL_G] = "Y"

ought to read:

If [Forms]![ONE]![sfrm ONE Subform].Form![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform].Form![REMAIL_G] = "Y"

Notice I've inserted .Form after the name of the subform control that is
hosting your subform. That's what you need to do in order to refer to the
subform itself, not it's host container.
 
Stuart

Thanks for the reply. Unfortunately, it still not working for me. [G_SNT]
represents a field in the subform [sfrm ONE subform]. The code seemed to
work independently. My problem I can't seem to invoke the code correctly
when I nest them into one IF THEN statement. If a record does not exist, I
would like to invoke the IF THEN statement starting with the line If
[Forms]![ONE]![sfrm ONE]![SSN] <> [Forms]![lONE]![SSN] Then" If a record does
exist I would like to evoke the code starting with "If [G_ELIGIBLE] = "Y"
Then"

Stuart McCall said:
jimphilly said:
I have a form which I use to update records. However, sometimes a record
doesn't exist for the person in the table I would like to update, so I
need
to create one and update the appropriate fields. The problem I'm having
is I
can't figure out a clean way to do this. I can create the record with the
following code. (Excluding the first line)

If [Forms]![ONE]![sfrm ONE]![SSN] <> [Forms]![lONE]![SSN] Then
'Create and Update Participant record in FIRST table.
Dim cmd As ADODB.Recordset
Set cmd = New ADODB.Recordset
Dim EMP As String
EMP = "Select FIRST.*,"
With cmd
.Open EMP, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
.AddNew
.Fields("SSN") = [Forms]![loan/emp form history]![SSN]
.Fields("G_SNT") = "Y"
.Fields("G_SNT_DATE") = Date
.Fields("YEAR") = "06/07"
.Update
End With
cmd.CLOSE
Set cmd = Nothing

End If

I can update the record with this code.

If [G_ELIGIBLE] = "Y" Then
If [Forms]![ONE]![sfrm ONE Subform]![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform]![REMAIL_G] = "Y"

Else
[G_SNT] = "") Then
[G225_SNT] = "Y"

End If

However, I can't put the two together right. Sometimes a record will
exist
with nothing but an SSN. I tried using NULL or NOT NULL but it doesn't
work.
Intellisense tells me the field G_SNT = NULL but the code doesn't pick it
up
therefore it doens't engage the IF THEN statement.

Also I get the error "You entered an expression that has no value" when it
compares the two SSN fields in forms [ONE] and sfrm [ONE].

Any help would be appreciated.
Click to expand...

You're not addressing the subform properly. Your code:

If [Forms]![ONE]![sfrm ONE Subform]![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform]![REMAIL_G] = "Y"

ought to read:

If [Forms]![ONE]![sfrm ONE Subform].Form![G_SNT] = "Y" Then
[Forms]![ONE]![sfrm ONE Subform].Form![REMAIL_G] = "Y"

Notice I've inserted .Form after the name of the subform control that is
hosting your subform. That's what you need to do in order to refer to the
subform itself, not it's host container.
Click to expand...
 
jimphilly said:
Stuart McCall said:
[sfrm ONE subform]
[sfrm ONE]
Click to expand...

Which is the correct name?


The correct name is [sfrm ONE subform]. Sorry for the confusion.
Click to expand...
Click to expand...

I was attempting to draw your attention to:

If [Forms]![ONE]![sfrm ONE]![SSN] <> [Forms]![lONE]![SSN] Then

Maybe that ought to be:

[Forms]![ONE]![sfrm ONE subform]![SSN]

or, more accurately:

[Forms]![ONE]![sfrm ONE subform].Form![SSN]
 
Back
Top