Saving 2 records instead of 1

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

Guest

I have a form that adds a new record then I close the form and it saves
another record. Is there a way to prevent this?
 
I have a form that adds a new record then I close the form and it saves
another record. Is there a way to prevent this?
Click to expand...

umm... forms don't do this on their own. A form will normally save a
record when you have it "dirtied" (data on the form has been changed)
and you close the form, or move to another record. Do you have code
which explicitly saves the record? If so, please post it.

John W. Vinson[MVP]
 
I have a button with this code behind it that filters:
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh

then I have another button that just had the comman
docmd.save

It will filter on the SSN and all of the questions associated with this SSN.
I needed to change on of them then saveto the current record. But it is
saving the questions right but it is adding a blank row such as this

SSN SetA SetB Set3
111111111 2 1 1
111111111
 
I have a button with this code behind it that filters:
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh

then I have another button that just had the comman
docmd.save
Click to expand...

This saves *DESIGN CHANGES* to the structure of the form; it may also
save data to the table, butit's not necessary to have any button to do
so. Just moving off the record or closing the form will save the
record. If you *do* want to save the current record use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False
It will filter on the SSN and all of the questions associated with this SSN.
I needed to change on of them then saveto the current record. But it is
saving the questions right but it is adding a blank row such as this

SSN SetA SetB Set3
111111111 2 1 1
111111111
Click to expand...

I'd be inclined to use a Form with a Subform for this purpose - base a
Form on the "people" table (with the SSN as its primary key) and a
subform on the questions table, using SSN as the master/child link
field. Then you don't need ANY code at all - just enter data and tab
to the next field!

John W. Vinson[MVP]
 
I am still getting the extra line saving. Here is my code for the button to
close the form and open a new one.

If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
ElseIf IsNull(Me.SetA) Then
MsgBox ("Please select Yes/No from Set A")
ElseIf IsNull(Me.SetB) Then
MsgBox ("Please select Yes/No from Set B")
ElseIf IsNull(Me.SetC) Then
MsgBox ("Please select Yes/No from Set C")
Else
DoCmd.Close
DoCmd.OpenForm ("PHQ")

End If
Else
MsgBox ("Please enter a full SSN")
End If

This is the code to filter the form. I put in a SSN then press this. Then
I change the answers to SetA,B, and C then i hit the button to close the form
and open a new on and it is still adding a blank record like the example.

varResult = DLookup("SSN", "Provider", "SSN = '" & Me.SSN & "'")

If IsNull(varResult) Then

If MsgBox("This is a new SSN. Would you like to add this SSN? " &
SSN, vbYesNo) = vbYes Then
If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
End If

Else
MsgBox ("Please enter a full SSN")
End If
End If
Else
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh
End If

John Vinson said:
I have a button with this code behind it that filters:
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh

then I have another button that just had the comman
docmd.save
Click to expand...

This saves *DESIGN CHANGES* to the structure of the form; it may also
save data to the table, butit's not necessary to have any button to do
so. Just moving off the record or closing the form will save the
record. If you *do* want to save the current record use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False
It will filter on the SSN and all of the questions associated with this SSN.
I needed to change on of them then saveto the current record. But it is
saving the questions right but it is adding a blank row such as this

SSN SetA SetB Set3
111111111 2 1 1
111111111
Click to expand...

I'd be inclined to use a Form with a Subform for this purpose - base a
Form on the "people" table (with the SSN as its primary key) and a
subform on the questions table, using SSN as the master/child link
field. Then you don't need ANY code at all - just enter data and tab
to the next field!

John W. Vinson[MVP]
Click to expand...
 
I am still getting the extra line saving. Here is my code for the button to
close the form and open a new one.

If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
Click to expand...

ummmm... if the Len of SSN is equal to 9, then you can be absolutely
CERTAIN that this condition will never occur.
ElseIf IsNull(Me.SetA) Then
MsgBox ("Please select Yes/No from Set A")
ElseIf IsNull(Me.SetB) Then
MsgBox ("Please select Yes/No from Set B")
ElseIf IsNull(Me.SetC) Then
MsgBox ("Please select Yes/No from Set C")
Else
Click to expand...

And these conditions will be tested one at a time; if both SetA and
SetB are NULL, you'll see only the message from SetA. I'd use separate
If/End If blocks.
DoCmd.Close
DoCmd.OpenForm ("PHQ")

End If
Else
MsgBox ("Please enter a full SSN")
End If
Click to expand...

What Event is this code part of? A Click event? I wonder if the test
for null values and invalid SSN's shouldn't be in the Form's
BeforeUpdate event, which can be cancelled (and which will be called
at the line DoCmd.Close).

This is the code to filter the form. I put in a SSN then press this. Then
I change the answers to SetA,B, and C then i hit the button to close the form
and open a new on and it is still adding a blank record like the example.

varResult = DLookup("SSN", "Provider", "SSN = '" & Me.SSN & "'")
Click to expand...

If the only point to this form is to select a SSN and open a new form
- then simply make the Form UNBOUND. Am I misunderstanding the purpose
of this form? What's done with the second form?
If IsNull(varResult) Then

If MsgBox("This is a new SSN. Would you like to add this SSN? " &
SSN, vbYesNo) = vbYes Then
If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
End If

Else
MsgBox ("Please enter a full SSN")
End If
End If
Else
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh
End If
Click to expand...

John W. Vinson[MVP]
 
These are both on the same form the one with the filter is used to bring up a
record. Then I used the 1st set to check if SSN=9 and the question answers
aren't blank. Then I need to save the form if any question answers changed.

So I hit one button to bring up a record(using filter) then I hit a second
button to save the information and move to a new form. But this is where it
is saving the extra record.

John Vinson said:
I am still getting the extra line saving. Here is my code for the button to
close the form and open a new one.

If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
Click to expand...

ummmm... if the Len of SSN is equal to 9, then you can be absolutely
CERTAIN that this condition will never occur.
ElseIf IsNull(Me.SetA) Then
MsgBox ("Please select Yes/No from Set A")
ElseIf IsNull(Me.SetB) Then
MsgBox ("Please select Yes/No from Set B")
ElseIf IsNull(Me.SetC) Then
MsgBox ("Please select Yes/No from Set C")
Else
Click to expand...

And these conditions will be tested one at a time; if both SetA and
SetB are NULL, you'll see only the message from SetA. I'd use separate
If/End If blocks.
DoCmd.Close
DoCmd.OpenForm ("PHQ")

End If
Else
MsgBox ("Please enter a full SSN")
End If
Click to expand...

What Event is this code part of? A Click event? I wonder if the test
for null values and invalid SSN's shouldn't be in the Form's
BeforeUpdate event, which can be cancelled (and which will be called
at the line DoCmd.Close).

This is the code to filter the form. I put in a SSN then press this. Then
I change the answers to SetA,B, and C then i hit the button to close the form
and open a new on and it is still adding a blank record like the example.

varResult = DLookup("SSN", "Provider", "SSN = '" & Me.SSN & "'")
Click to expand...

If the only point to this form is to select a SSN and open a new form
- then simply make the Form UNBOUND. Am I misunderstanding the purpose
of this form? What's done with the second form?
If IsNull(varResult) Then

If MsgBox("This is a new SSN. Would you like to add this SSN? " &
SSN, vbYesNo) = vbYes Then
If Len(Me.SSN) = 9 Then

If IsNull(Me.SSN) Then
MsgBox ("Please enter a SSN")
End If

Else
MsgBox ("Please enter a full SSN")
End If
End If
Else
Me.Filter = "[SSN] = '" & SSN & "'"
Me.FilterOn = True
Me.Refresh
End If
Click to expand...

John W. Vinson[MVP]
Click to expand...
 
Back
Top