SQL Add to Table, Used To Work, Now Doesnt ...

Hey Experts.

This chunk of code worked to add details to tblCustomer linking it to tblCustPhone.
I have made no changes to the tables, the DMax function on my CustomerID unbound control updates the addition, yet no data is diaplayed.

eg. i type in the details for the customer.
It passes the validation. And displayes a confirmation saying the user was added.

i go to the table. the record is not there.
i reopen my form, and the customer ID has incrimented the value (as if something WAS added but just isnt shown)

if that makes sence?



Private Sub cmdAddCust_Click()
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String

' ***********************************
    'Insert Customer Data
    sSQL1 = "INSERT INTO tblCustomer ([CustomerID], [Title], [Forename], [Surname], [PassPhrase], [Gender], [Street], [Town], [County], [Pcode], [DOB], [MFD], [DateJoined]) VALUES (txtCustomerID, txtTitle, txtForename, txtSurname, txtPassPhrase, cboGender, txtStreet, txtTown, txtCounty, txtPcode, txtDOB, txtMFD, txtDateJoined)"
    'Insert DAYTIME Phone Number
    sSQL2 = "INSERT INTO tblCustPhone ([CustomerID], [PhoneNumber], [Type], [EarliestCall], [LatestCall]) VALUES (txtCustomerID, txtTel1, cboTel1Type, txtTel1Early, txtTel1Late)"
    'Insert EVENING Phone Number
    sSQL3 = "INSERT INTO tblCustPhone ([CustomerID], [PhoneNumber], [Type], [EarliestCall], [LatestCall]) VALUES (txtCustomerID, txtTel2, cboTel2Type, txtTel2Early, txtTel2Late)"
' ***********************************


'Validate to see if there is data in the necessary field names
'And Me.txtCounty.Text And Me.txtTown.Text Or Me.txtPcode.Text Or Me.txtDOB.Text = ""

If IsNull(txtForename) = True Or IsNull(txtSurname) = True Or IsNull(txtStreet) = True Or IsNull(txtTown) = True Or IsNull(txtCounty) = True Or IsNull(txtPcode) = True Then
    MsgBox "We cannot add this customer because some data has not been typed into the necessary fields" & vbNewLine & "Please enter this data before you continue", vbOKOnly + vbExclamation, "Data Entry Error"
    Exit Sub  'Exit sub to stop any more code being run beyond this point!
Else
    'dont show any messages to the user saying they are about to UPDATE a row of data
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL1
    MsgBox "contact SQL Script Run"
    DoCmd.OpenTable "tblCustomer"
         
         
    'Add Corresponding Phone Numbers To Contact
        If IsNull(txtTel1) = False Then 'if there is data in TELEPHONE 1
            DoCmd.RunSQL sSQL2  'add phone 1
        ElseIf IsNull(txtTel2) = False Then 'if there is data in TELEPHONE 2
            DoCmd.RunSQL sSQL3  'add phone 2
        End If
    MsgBox [txtForename] & " " & [txtSurname] & " was sucessfully added to database!", vbOKOnly + vbInformation, "Record Addition"
End If

DoCmd.Close
DoCmd.OpenForm "frmCustSB"
End Sub
LVL 1
seraph_matrix_631Asked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
re:>  type conversion failure...

This happens when you are trying to append integer to string fields for example. You could do one of the followings:

1:   sSQL1 = "INSERT INTO tblCustomer ([CustomerID], [Title]) VALUES (txtCustomerID, txtTitle)"

And, add the next field after making sure it works.

2:  

Debug.Print "txtCustomerID: " & Vartype(txtCustomerID) & vbNewLine & _
                    "txtTitle: " & Vartype(txtTitle) & vbNewLine & _
                    "txtForename: " & Vartype(txtForename) & vbNewLine ...

  'Insert Customer Data
    sSQL1 = "INSERT INTO tblCustomer ([CustomerID], [Title], [Forename], [Surname], [PassPhrase], [Gender], [Street], [Town], [County], [Pcode], [DOB], [MFD], [DateJoined]) VALUES (txtCustomerID, txtTitle, txtForename, txtSurname, txtPassPhrase, cboGender, txtStreet, txtTown, txtCounty, txtPcode, txtDOB, txtMFD, txtDateJoined)"

To compare varibale type. and if [txtDateJoined] is date/time in table but txtDateJoined is text, use CDate(txtDateJoined) to match the field in the table.

Mike
, txtTitle, , txtSurname, txtPassPhrase, cboGender, txtStreet, txtTown, txtCounty, txtPcode, txtDOB, txtMFD, txtDateJoined
0
 
Rick_RickardsCommented:
One thing to check is whether the autonumber field has been reset to a number prior to the highest number currently in your application.  There is a bug in Jet 4.0 (Access 2K thru 2003) that can cause this to happen with tables that are linked to your front end.

If this happens to apply to your situation let me know and provide the name of the table, autonumber field affected and any other field (with it's data type) and I'll post a query to reset the autonumber field for you.
0
 
Arthur_WoodCommented:
your SQL string needs to have the actual DATA from the fields, not the names of the fields:
sSQL1 = "INSERT INTO tblCustomer ([CustomerID], [Title], [Forename], [Surname], [PassPhrase], [Gender], [Street], [Town], [County], [Pcode], [DOB], [MFD], [DateJoined]) VALUES ('" & txtCustomerID & '","' & txtTitle & '","' & txtForename & '","' & txtSurname & '","' & txtPassPhrase & '","' & cboGender & '","' & txtStreet & '","' & txtTown & '","' & txtCounty & '","' &  txtPcode & '","# & txtDOB & #","' & txtMFD & '","# & txtDateJoined & "#)"
 
 sSQL2 = "INSERT INTO tblCustPhone ([CustomerID], [PhoneNumber], [Type], [EarliestCall], [LatestCall]) VALUES ('" & txtCustomerID & '","' & txtTel1 & '","' &  cboTel1Type & '","' &  txtTel1Early & '","' & txtTel1Late & "')"
 
   sSQL3 = "INSERT INTO tblCustPhone ([CustomerID], [PhoneNumber], [Type], [EarliestCall], [LatestCall]) VALUES ('" & txtCustomerID & '","' & txtTel2 & '","' &  cboTel2Type & '","' & txtTel2Early & '","' & txtTel2Late & "')"
 
AW

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
seraph_matrix_631Author Commented:
Arthur Wood.
Upon putting those SQL statments into VBA they turned red immediately.


Rick_Rickard:

tblCustomer
    CustomerID         AutoNumber
    Title                     Text
     Forename           Text
     surname            Text
     PassPhrase       Text
     Gender              Text
     Street                 Text
     Town                  Text
      County               Text
      PCode               Text
     DOB                  Date/Time
     MFD                   Text
     DateJoined          date/time


with the VBA i posted above i would also like to make sure that the SQL Insert into's are not run unless there is data in ALL of the text boxes, and that they meet validation (so no number's in text fields etc how wud this be done?


many thanks
[if a solution is provided for the form validation i will increase the point allocation by at least 100 points
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
re:> Used To Work, Now Doesnt ...

The code hasn't changed but the data being processed apparently has. The solution from Arthur_Wood is good one but doesn't cover all cases. His solution covers data which includes single qoute like O'Brian but doesn't cover data like 132 Main Street, Ste. "A". In case where data include some double qoutes, CHR(34), they have to be replace with another version of double qoute, Chr(48), which doesn't cause error. A typical application of it would be like:

VALUES (" & Chr(34) & Replace(txtCustomerID, Chr(34), Chr(48)) & Chr(34) & ", ...


BTW, if you remove DoCmd.SetWarnings False, you will see the error message.

Mike
0
 
Arthur_WoodCommented:
"Upon putting those SQL statments into VBA they turned red immediately.:

What compiler error did you get?

AW
0
 
seraph_matrix_631Author Commented:
POSH Cars Database can't append all the records in the append query.

POSH Cars database set 1 field(s) to Null due to a type conversion failure, and set it
0
 
seraph_matrix_631Author Commented:
POSH Cars Database can't append all the records in the append query.

POSH Cars database set 1 field(s) to Null due to a type conversion failure, and it didnt add 0 records(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

do you want to run the query anyway?
To ignore the error(s) and run the query, click yes


thats the error i get if i run the Sql statment on the button with Warnings shown
---
I really dont understand this. It worked 2 days ago!!   (Angry Face)
if it worked 2 days ago and weeks before, and i have not changed relationships regarding tblCustomer and tblCustPhone, and have not changed data types or form controls then LOGICALLY it would work!!!
0
 
seraph_matrix_631Author Commented:
ok thats weird - it just added ONE ROW to the table...
Then it came up with that same error again

this is starting to really tick me off now
0
 
seraph_matrix_631Author Commented:
eghtebas, tried your solution, and it came up as the same result im currently getting with the SQL statment in my original post. thank you for the thought though.
0
 
seraph_matrix_631Author Commented:
Created a table called tblTEST
It added the record for the customer. tested 3 times and the customer details are being added to tblTEST no problems.


I am using some input masks for the date fields. could this be causing the issue?

0
 
seraph_matrix_631Author Commented:
the problem seems to be originating from the DOB field.
As there was a problem adding it to the tblTEST.

tblTEST
     DOB       Date/Time      No Deviation from Ms Access Default Field Properties
0
 
seraph_matrix_631Author Commented:
SIMPLE SOLUTION

I changed all the DATE fields to TEXT DATA TYPE
I then added an input mask to the table for the date fields:  00/00/0000

On the form i saved the input mask 00/00/0000 to my unbound date controls.
This then added the customer and all contact telephone numbers using the sql script provided in my original post.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I just checked in. It seems you are on the right track to make it all work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.