Solved

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

Posted on 2007-12-01
14
237 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
  • 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
 
LVL 33

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 33

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 33

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now