form spontaneously creating empty records

M

martinmike2

Hello,

My main form is spontaneously creating empty records in my main table
when the form loads. There is nothing in the OnLoad event and this is
eluding me. Any help would be great.

I have a snippet of code in the Current event of the form that counts
records in a subform and if there arn't any then produce a msg box to
display a custom error.

CODE:

Private Sub Form_Current()
'-------------------------------------------------------------------
If Me.Actual_SSNs_subform.Form.Recordset.RecordCount = 0 Then
Me.Actual_SSNs_subform.Visible = False
Else
Me.Actual_SSNs_subform.Visible = True
End If
' -------------------------------------------------------------------
' Store the AimdNum for later use
Dim H_PHONE As Variant
On Error Resume Next
gSSN = Me.frcnum.Value
' Store Member data for later user
On Error Resume Next
gAddress = Me.txtAddy.Value
gCity = Me.txtCity.Value
gState = Me.txtState.Value
gZIP = Me.txtZip.Value
If (H_PHONE = "") Then
gPhone = Me.txtC_PHONE.Value
Else
gPhone = Me.txtH_Phone.Value
End If
'----------------------------------------------------------------------
Dim loc As String
Dim pic As Integer
Dim strPic As String
Me.lblNOPIC.Visible = False
loc = "S:\JAXS\AIMD\SHARES\Manpower db\Pictures\"
pic = gSSN
If Dir(loc & pic & ".jpg") = vbNullString Then
Me.imgEmp.Visible = False
Me.lblNOPIC.Visible = True
Else
Me.imgEmp.PICTURE = loc & pic & ".jpg"
Me.imgEmp.Visible = True
End If
'-----------------------------------------------------------------------
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
Me.Refresh
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
MsgBox "Member has no EDVR Data. Refer Member to MANPOWER for
check-in.", vbCritical + vbOKOnly, gstrAppTitle
End If
End If
End Sub

the frmPERSsub is the last addition before the problem started.
Behind frmPERSsub is:

SELECT tblBRCL.BRANCH, EDVR.UIC, EDVR.SSN, EDVR.A_RATE_ABR
FROM EDVR INNER JOIN tblBRCL ON EDVR.BR_CL = tblBRCL.BR_CL;


frmPERS is the main form, behind that form is a query:

SELECT PERS.*
FROM PERS
ORDER BY PERS.[NAME LAST]
WITH OWNERACCESS OPTION;


Any help is appreciated immensely.

-Michael Martin
 
W

Wayne-I-M

Quick test

Add the primary field to the from (if not already there)
Make visible =Yes

Open form
Is there anything in the primary control/field

Post back with answer
 
M

martinmike2

the primary field is on the form and visible. on the empty records it
shows a "0" so its not REALLY an empty record. But as you can see in
the Current code, theres nothing to tell it to make a new record, lol.
 
W

Wayne-I-M

Sounds like a number format with default set to zero. Use an autonumber for
the primary.

Can you post the code you are using to open the form
 
M

martinmike2

Sounds like a number format with default set to zero.  Use an autonumber for
the primary.

Can you post the code you are using to open the form

--
Wayne
Manchester, England.





- Show quoted text -
Click to expand...

I looked and found the default value WAS set to 0, I removed it, but
the code I am using to open the form is the wizard code from a command
button.

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PERS"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

I can't make the primary key an autonumber because the primary key is
related to several tables, one of them is the "Master Table" that has
an autonumber field generating the Key.
 
D

Dirk Goldgar

in message
Hello,

My main form is spontaneously creating empty records in my main table
when the form loads. There is nothing in the OnLoad event and this is
eluding me. Any help would be great.

I have a snippet of code in the Current event of the form that counts
records in a subform and if there arn't any then produce a msg box to
display a custom error.

CODE:

Private Sub Form_Current()
'-------------------------------------------------------------------
If Me.Actual_SSNs_subform.Form.Recordset.RecordCount = 0 Then
Me.Actual_SSNs_subform.Visible = False
Else
Me.Actual_SSNs_subform.Visible = True
End If
' -------------------------------------------------------------------
' Store the AimdNum for later use
Dim H_PHONE As Variant
On Error Resume Next
gSSN = Me.frcnum.Value
' Store Member data for later user
On Error Resume Next
gAddress = Me.txtAddy.Value
gCity = Me.txtCity.Value
gState = Me.txtState.Value
gZIP = Me.txtZip.Value
If (H_PHONE = "") Then
gPhone = Me.txtC_PHONE.Value
Else
gPhone = Me.txtH_Phone.Value
End If
'----------------------------------------------------------------------
Dim loc As String
Dim pic As Integer
Dim strPic As String
Me.lblNOPIC.Visible = False
loc = "S:\JAXS\AIMD\SHARES\Manpower db\Pictures\"
pic = gSSN
If Dir(loc & pic & ".jpg") = vbNullString Then
Me.imgEmp.Visible = False
Me.lblNOPIC.Visible = True
Else
Me.imgEmp.PICTURE = loc & pic & ".jpg"
Me.imgEmp.Visible = True
End If
'-----------------------------------------------------------------------
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
Me.Refresh
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
MsgBox "Member has no EDVR Data. Refer Member to MANPOWER for
check-in.", vbCritical + vbOKOnly, gstrAppTitle
End If
End If
End Sub

the frmPERSsub is the last addition before the problem started.
Behind frmPERSsub is:

SELECT tblBRCL.BRANCH, EDVR.UIC, EDVR.SSN, EDVR.A_RATE_ABR
FROM EDVR INNER JOIN tblBRCL ON EDVR.BR_CL = tblBRCL.BR_CL;


frmPERS is the main form, behind that form is a query:

SELECT PERS.*
FROM PERS
ORDER BY PERS.[NAME LAST]
WITH OWNERACCESS OPTION;


Any help is appreciated immensely.

-Michael Martin
Click to expand...


Do you have code in any other events? This is a textbook symptom of having
code that dirties every record, but I don't see it in your Current event as
posted above. The only control on your form that I see as being modified at
all is imgEmp, and I assume that's an image control and hence unbound.
 
M

martinmike2

I removed the default value and that fixed the issue. Thanks for all
the help!!!!
 
D

Dave

Hey Martinmike2

Are you using a query as the recordsource for the form? I had the same
problem so (be it the best solution or not) I made a module level variable
(m_bFormLoading) that I set to True when the form loads. The form then
executes the Form_Current event (whether you have code or not) and that
starts a new record. I put code in the Form_Current event and check for
m_bFormLoading = True. If true then I set m_bFormLoading = False.

Example below:

Private Sub Form_Current()

If m_bFormLoading Then
m_bFormLoading = False
Else
If Me.NewRecord Then
cboPlantID = m_iPlantSelected
NewDebitMemo
Else
FormCaption
End If
End If

End Sub

In the Form_Load event
Private Sub Form_Load()

....Form_Load code

m_bFormLoading = True

End Sub

Form_Load executes before Form_Current therefore, setting the variable in
the load will set it before the current event occurs (creating a new record).
Give is a try.

Dave




martinmike2 said:
Hello,

My main form is spontaneously creating empty records in my main table
when the form loads. There is nothing in the OnLoad event and this is
eluding me. Any help would be great.

I have a snippet of code in the Current event of the form that counts
records in a subform and if there arn't any then produce a msg box to
display a custom error.

CODE:

Private Sub Form_Current()
'-------------------------------------------------------------------
If Me.Actual_SSNs_subform.Form.Recordset.RecordCount = 0 Then
Me.Actual_SSNs_subform.Visible = False
Else
Me.Actual_SSNs_subform.Visible = True
End If
' -------------------------------------------------------------------
' Store the AimdNum for later use
Dim H_PHONE As Variant
On Error Resume Next
gSSN = Me.frcnum.Value
' Store Member data for later user
On Error Resume Next
gAddress = Me.txtAddy.Value
gCity = Me.txtCity.Value
gState = Me.txtState.Value
gZIP = Me.txtZip.Value
If (H_PHONE = "") Then
gPhone = Me.txtC_PHONE.Value
Else
gPhone = Me.txtH_Phone.Value
End If
'----------------------------------------------------------------------
Dim loc As String
Dim pic As Integer
Dim strPic As String
Me.lblNOPIC.Visible = False
loc = "S:\JAXS\AIMD\SHARES\Manpower db\Pictures\"
pic = gSSN
If Dir(loc & pic & ".jpg") = vbNullString Then
Me.imgEmp.Visible = False
Me.lblNOPIC.Visible = True
Else
Me.imgEmp.PICTURE = loc & pic & ".jpg"
Me.imgEmp.Visible = True
End If
'-----------------------------------------------------------------------
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
Me.Refresh
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
MsgBox "Member has no EDVR Data. Refer Member to MANPOWER for
check-in.", vbCritical + vbOKOnly, gstrAppTitle
End If
End If
End Sub

the frmPERSsub is the last addition before the problem started.
Behind frmPERSsub is:

SELECT tblBRCL.BRANCH, EDVR.UIC, EDVR.SSN, EDVR.A_RATE_ABR
FROM EDVR INNER JOIN tblBRCL ON EDVR.BR_CL = tblBRCL.BR_CL;


frmPERS is the main form, behind that form is a query:

SELECT PERS.*
FROM PERS
ORDER BY PERS.[NAME LAST]
WITH OWNERACCESS OPTION;


Any help is appreciated immensely.

-Michael Martin
Click to expand...
 
M

martinmike2

Hey Martinmike2

Are you using a query as the recordsource for the form?  I had the same
problem so (be it the best solution or not) I made a module level variable
(m_bFormLoading) that I set to True when the form loads.  The form then
executes the Form_Current event (whether you have code or not) and that
starts a new record.  I put code in the Form_Current event and check for
m_bFormLoading = True.  If true then I set m_bFormLoading = False.

Example below:

Private Sub Form_Current()

    If m_bFormLoading Then
        m_bFormLoading = False
    Else
        If Me.NewRecord Then
            cboPlantID = m_iPlantSelected
            NewDebitMemo
        Else
            FormCaption
        End If
    End If

End Sub

In the Form_Load event
Private Sub Form_Load()  

...Form_Load code

    m_bFormLoading = True

End Sub

Form_Load executes before Form_Current therefore, setting the variable in
the load will set it before the current event occurs (creating a new record).
 Give is a try.

Dave



martinmike2 said:
Hello,
Click to expand...
My main form is spontaneously creating empty records in my main table
when the form loads.  There is nothing in the OnLoad event and this is
eluding me.  Any help would be great.
Click to expand...
I have a snippet of code in the Current event of the form that counts
records in a subform and if there arn't any then produce a msg box to
display a custom error.

Private Sub Form_Current()
'-------------------------------------------------------------------
If Me.Actual_SSNs_subform.Form.Recordset.RecordCount = 0 Then
    Me.Actual_SSNs_subform.Visible = False
Else
    Me.Actual_SSNs_subform.Visible = True
End If
' -------------------------------------------------------------------
' Store the AimdNum for later use
Dim H_PHONE As Variant
On Error Resume Next
gSSN = Me.frcnum.Value
' Store Member data for later user
On Error Resume Next
gAddress = Me.txtAddy.Value
gCity = Me.txtCity.Value
gState = Me.txtState.Value
gZIP = Me.txtZip.Value
If (H_PHONE = "") Then
gPhone = Me.txtC_PHONE.Value
Else
gPhone = Me.txtH_Phone.Value
End If
'----------------------------------------------------------------------
Dim loc As String
Dim pic As Integer
Dim strPic As String
 Me.lblNOPIC.Visible = False
loc = "S:\JAXS\AIMD\SHARES\Manpower db\Pictures\"
pic = gSSN
If Dir(loc & pic & ".jpg") = vbNullString Then
        Me.imgEmp.Visible = False
        Me.lblNOPIC.Visible = True
Else
        Me.imgEmp.PICTURE = loc & pic & ".jpg"
        Me.imgEmp.Visible = True
End If
'-----------------------------------------------------------------------
If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
    Me.Refresh
    If Me.frmPERSsub.Form.Recordset.RecordCount = 0 Then
    MsgBox "Member has no EDVR Data.  Refer Member to MANPOWER for
check-in.", vbCritical + vbOKOnly, gstrAppTitle
    End If
End If
End Sub
Click to expand...
the frmPERSsub is the last addition before the problem started.
Behind frmPERSsub is:
Click to expand...
SELECT tblBRCL.BRANCH, EDVR.UIC, EDVR.SSN, EDVR.A_RATE_ABR
FROM EDVR INNER JOIN tblBRCL ON EDVR.BR_CL = tblBRCL.BR_CL;
Click to expand...
frmPERS is the main form, behind that form is a query:
Click to expand...
SELECT PERS.*
FROM PERS
ORDER BY PERS.[NAME LAST]
WITH OWNERACCESS OPTION;
Click to expand...
Any help is appreciated immensely.
Click to expand...
-Michael Martin- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

the form WAS running off a query, i have since moved since things
around and now the form runs off the table and all the subforms run
off queries. But I fixed the problem I was having, it was just a
default value I kept missing.
 
Top