Problem with inserting records from Textboxes

[AccessVBANewbie via AccessMonster.com]

Hi all, I have a problem when i tried to insert records through a form with
the source data from the multiple textboxes. A message appears saying that
Access can't append my records. It says that 0 records have been (0) fields
set to Null type conversion failure, it didn't add (0) records due to key
violations, (0) records due to lock violations, (1) record due to validation
rule violations. How should I solve this problem?

 

[Duane Hookom]

Check your table's validation rules and find out what you did or didn't
enter that caused the error message.

 

[AccessVBANewbie via AccessMonster.com]

i've checked all my fields in the tables and I have not set any validation
rules for any of the fields, all the fields are in text format, except for
the date field, which is in medium date format. The error message occured
just now perhaps because i was adding a record with the same ID as the
previous 1. Now when i tried to add records with empty textboxes, the
following similar error message occured. "Microsoft Access set 1 field(s) to
Null due to a type conversion failure. What does that mean?

Check your table's validation rules and find out what you did or didn't
enter that caused the error message.

Hi all, I have a problem when i tried to insert records through a form
with

[quoted text clipped - 5 lines]

validation
rule violations. How should I solve this problem?

 

[AccessVBANewbie via AccessMonster.com]

I found out that though that error message occured. However, if I closed my
form and opened it again, and re-open my table, the new data was inserted.
Why does this happen? And if i deleted a record using SQL statement, why does
all the textboxes contain the words "#Deleted"? The words only disappear when
i re-open the form. Why does this happen?

i've checked all my fields in the tables and I have not set any validation
rules for any of the fields, all the fields are in text format, except for
the date field, which is in medium date format. The error message occured
just now perhaps because i was adding a record with the same ID as the
previous 1. Now when i tried to add records with empty textboxes, the
following similar error message occured. "Microsoft Access set 1 field(s) to
Null due to a type conversion failure. What does that mean?

Check your table's validation rules and find out what you did or didn't
enter that caused the error message.

[quoted text clipped - 4 lines]

 

[Duane Hookom]

Deleting a record using code or an action query doesn't cause a form to
refresh. You can requery the form to display only current records.

--
Duane Hookom
MS Access MVP

I found out that though that error message occured. However, if I closed my
form and opened it again, and re-open my table, the new data was inserted.
Why does this happen? And if i deleted a record using SQL statement, why
does
all the textboxes contain the words "#Deleted"? The words only disappear
when
i re-open the form. Why does this happen?

i've checked all my fields in the tables and I have not set any validation
rules for any of the fields, all the fields are in text format, except for
the date field, which is in medium date format. The error message occured
just now perhaps because i was adding a record with the same ID as the
previous 1. Now when i tried to add records with empty textboxes, the
following similar error message occured. "Microsoft Access set 1 field(s)
to
Null due to a type conversion failure. What does that mean?

Check your table's validation rules and find out what you did or didn't
enter that caused the error message.

[quoted text clipped - 4 lines]

validation
rule violations. How should I solve this problem?

 

[AccessVBANewbie via AccessMonster.com]

how to I refresh my form to display the inserted new record and not show the
textboxes with the deleted record? will "Form.Requery" work?

Deleting a record using code or an action query doesn't cause a form to
refresh. You can requery the form to display only current records.

I found out that though that error message occured. However, if I closed my
form and opened it again, and re-open my table, the new data was inserted.

[quoted text clipped - 18 lines]

 

[AccessVBANewbie via AccessMonster.com]

i've tried using Form.Requery, Form.Repaint, and Form.Refresh, but when i
click on the delete button on my form, and clicked another button to view the
previous record which I had just added, and then deleted, the textboxes still
show "#DELETED" . Any idea how I could solve this?

how to I refresh my form to display the inserted new record and not show the
textboxes with the deleted record? will "Form.Requery" work?

Deleting a record using code or an action query doesn't cause a form to
refresh. You can requery the form to display only current records.

[quoted text clipped - 4 lines]

 

[Duane Hookom]

Please show your code.

--
Duane Hookom
MS Access MVP

i've tried using Form.Requery, Form.Repaint, and Form.Refresh, but when i
click on the delete button on my form, and clicked another button to view
the
previous record which I had just added, and then deleted, the textboxes
still
show "#DELETED" . Any idea how I could solve this?

how to I refresh my form to display the inserted new record and not show
the
textboxes with the deleted record? will "Form.Requery" work?

Deleting a record using code or an action query doesn't cause a form to
refresh. You can requery the form to display only current records.

[quoted text clipped - 4 lines]

validation
rule violations. How should I solve this problem?

 

[AccessVBANewbie via AccessMonster.com]

Hey, I tried again and the Form.Requery worked on the Delete!! Thanks Duane
for that! Now I had the problem with the insert, though I could insert a new
record, but the message "Microsoft Access has set 1 record(s) to Null due to
type conversion failure" keeps occuring everytime i click on my "Add Record"
button. Then it asks me whether i wish to continue with the action query, if
yes, it will ignore all the errors and run the query and insert the record.
But my questions is, why does this error message keep occuring?

i've tried using Form.Requery, Form.Repaint, and Form.Refresh, but when i
click on the delete button on my form, and clicked another button to view the
previous record which I had just added, and then deleted, the textboxes still
show "#DELETED" . Any idea how I could solve this?

how to I refresh my form to display the inserted new record and not show the
textboxes with the deleted record? will "Form.Requery" work?

[quoted text clipped - 4 lines]

 

[AccessVBANewbie via AccessMonster.com]

My codes for the insert function is as follows:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Me!txtCustomer.SetFocus

Dim stDocName As String

If Me!txtCustomer.Text = "" Then
MsgBox "Please input a value!"
Else
Dim intResponse As Integer
Dim strPrompt As String

strPrompt = "Do you really want to add this record?"

intResponse = MsgBox(strPrompt, vbYesNo)

If intResponse = vbYes Then
Cancel = True

Dim SQl As String

SQl = "INSERT INTO GeneralSummary (Customer,Contact, Part_Number,
Customer_Part_IDNumber, Date_Of_Open, PRT_Number, Part_LNumber, Date_Code,
Lot_Number, Package_Stamp, Visual_Inspection, Test_Stage, Symptom, FA_Results)
VALUES ('" & Me!txtTesting & "', '" & Me!txtContact & "', '" & Me!txtPartNo &
"', '" & txtCPIDNo & "', '" & Me!txtDateOfOpen & "', '" & Me!txtPRTNo & "',
'" & Me!txtPartLNo & "', '" & Me!txtDateCode & "', '" & Me!txtLNo & "', '" &
Me!txtPackageStamp & "', '" & Me!txtVisualInspection & "', '" & Me!
txtTestStage & "', '" & Me!txtSymptom & "', '" & Me!txtFAResults & "')"
DoCmd.RunSQL SQl
MsgBox " Record Added"
Form.Requery
Me!txtCustomer = ""
Else
Cancel = False
End If
End If

'DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

Please show your code.

i've tried using Form.Requery, Form.Repaint, and Form.Refresh, but when i
click on the delete button on my form, and clicked another button to view

[quoted text clipped - 12 lines]

 

[Douglas J. Steele]

What are the data types of the various fields to which you're attempting to
insert?

Your code assumes they're all text: is that correct? For numeric fields,
remove the ' around the values. For date fields, replace the ' with # (and
make sure the dates are in mm/dd/yyyy format, or some unambigous format like
yyyy-mm-dd or dd mmm yyyy, regardless what your regional settings have set
the Short Date format)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My codes for the insert function is as follows:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Me!txtCustomer.SetFocus

Dim stDocName As String

If Me!txtCustomer.Text = "" Then
MsgBox "Please input a value!"
Else
Dim intResponse As Integer
Dim strPrompt As String

strPrompt = "Do you really want to add this record?"

intResponse = MsgBox(strPrompt, vbYesNo)

If intResponse = vbYes Then
Cancel = True

Dim SQl As String

SQl = "INSERT INTO GeneralSummary (Customer,Contact,
Part_Number,
Customer_Part_IDNumber, Date_Of_Open, PRT_Number, Part_LNumber, Date_Code,
Lot_Number, Package_Stamp, Visual_Inspection, Test_Stage, Symptom,
FA_Results)
VALUES ('" & Me!txtTesting & "', '" & Me!txtContact & "', '" &
Me!txtPartNo &
"', '" & txtCPIDNo & "', '" & Me!txtDateOfOpen & "', '" & Me!txtPRTNo &
"',
'" & Me!txtPartLNo & "', '" & Me!txtDateCode & "', '" & Me!txtLNo & "', '"
&
Me!txtPackageStamp & "', '" & Me!txtVisualInspection & "', '" & Me!
txtTestStage & "', '" & Me!txtSymptom & "', '" & Me!txtFAResults & "')"
DoCmd.RunSQL SQl
MsgBox " Record Added"
Form.Requery
Me!txtCustomer = ""
Else
Cancel = False
End If
End If

'DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

Please show your code.

i've tried using Form.Requery, Form.Repaint, and Form.Refresh, but when
i
click on the delete button on my form, and clicked another button to
view

[quoted text clipped - 12 lines]

validation
rule violations. How should I solve this problem?

 

[AccessVBANewbie via AccessMonster.com]

douglas, yeah my fields are all in text format, except for the date, which is
in medium date format in the form of dd-mm-yyyy, so i just have to enclose it
with something like #" & txtDateOfOpen & "# ?

What are the data types of the various fields to which you're attempting to
insert?

Your code assumes they're all text: is that correct? For numeric fields,
remove the ' around the values. For date fields, replace the ' with # (and
make sure the dates are in mm/dd/yyyy format, or some unambigous format like
yyyy-mm-dd or dd mmm yyyy, regardless what your regional settings have set
the Short Date format)

My codes for the insert function is as follows:

[quoted text clipped - 62 lines]

 

[Douglas J. Steele]

Yes, replace the ' with # for dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

douglas, yeah my fields are all in text format, except for the date, which
is
in medium date format in the form of dd-mm-yyyy, so i just have to enclose
it
with something like #" & txtDateOfOpen & "# ?

What are the data types of the various fields to which you're attempting
to
insert?

Your code assumes they're all text: is that correct? For numeric fields,
remove the ' around the values. For date fields, replace the ' with # (and
make sure the dates are in mm/dd/yyyy format, or some unambigous format
like
yyyy-mm-dd or dd mmm yyyy, regardless what your regional settings have set
the Short Date format)

My codes for the insert function is as follows:

[quoted text clipped - 62 lines]

validation
rule violations. How should I solve this problem?