Solved

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

Posted on 2007-12-01
14
241 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:seraph_matrix_631
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20389883
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20390185
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20390815
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20390848
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20390885
"Upon putting those SQL statments into VBA they turned red immediately.:

What compiler error did you get?

AW
0
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391314
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391350
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391373
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391645
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
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 250 total points
ID: 20391848
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391944
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20391976
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 20392322
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20392571
I just checked in. It seems you are on the right track to make it all work.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compress Newid value ms sql Mssql 4 48
Add Underline to custom Caption on Label 4 36
2 Global Vars, 1 List Box 4 34
ISeries Remote Location Route entry 2 28
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question