[Poojaaa]

Private Sub Command1_Click()

Dim conn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim p As Integer
Dim accessconnect, n, a, num, ty As String
accessconnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=POOJA-D4FD585E2\SQL2000"
conn1.ConnectionString = accessconnect
conn1.Open
n = Text1.Text
a = Text2.Text
p = Val(Text3.Text)
num = Text4.Text
ty = Text5.Text

cmd1.ActiveConnection = conn1
cmd1.CommandText = " insert into customer(customer_name,customer_address,customer_phn,cutomer_dob,account_number,account_type) values(' " & n & " ' , ' " & a & " ',' " & p & " ' , ' " & DTPicker1.Value & " ',' " & num & " ' , ' " & ty & " ')"
Set rs1 = cmd1.Execute
conn1.Close
MsgBox ("DATABASE UPDATED SUCCESSFULLY")
End Sub

Private Sub Text1_Validate(Cancel As Boolean)
n = Text1.Text
If n = (" ") And (Len(n) < 3) Then
    MsgBox ("Enter valid customer name")
    End If
End Sub

Private Sub Text2_Validate(Cancel As Boolean)
a = Text2.Text
If a = (" ") And (Len(a) < 3) Then
    MsgBox ("Enter valid address")
    End If
End Sub

Private Sub Text3_Validate(Cancel As Boolean)
p = Val(Text3.Text)
If Not IsNumeric(p) And p = (" ") And (Len(p) < 10) Then
        MsgBox "Please enter numbers only."
    End If
End Sub


Private Sub Text4_Validate(Cancel As Boolean)
num = Text4.Text
If num = (" ") Then
    MsgBox ("Account number cannot be null ")
End If
End Sub

Private Sub Text5_Validate(Cancel As Boolean)
ty = Text5.Text
If ty = (" ") Then
    MsgBox ("Enter a type of account ")
End If
End Sub

I am a beginner so I am not able to figure it out what is the error this code is not able to validate

[maj3091]

Can you tell us what error you get, it's not clear from your post?

Also a couple of points.

1. Have a look at using a naming convention for your variables. Something along the lines of strMyName would be a string type with the name MyName, intPointer would be an integer type, etc., etc. This makes your code easier to read for yourself and others. At the moment you have variables n, a, p and ty which mean nothing.

2. When declaring variables, either do it one on each line, or specify the type for each one. In your line "Dim accessconnect, n, a, num, ty As String", only ty is declared as string, the rest are declared as Variant type.

These are just some suggestions to help you in future.

This post has been edited by maj3091: 30 September 2012 - 01:54 AM

[Poojaaa]

maj3091, on 30 September 2012 - 01:53 AM, said:

Can you tell us what error you get, it's not clear from your post?

Also a couple of points.

1. Have a look at using a naming convention for your variables. Something along the lines of strMyName would be a string type with the name MyName, intPointer would be an integer type, etc., etc. This makes your code easier to read for yourself and others. At the moment you have variables n, a, p and ty which mean nothing.

2. When declaring variables, either do it one on each line, or specify the type for each one. In your line "Dim accessconnect, n, a, num, ty As String", only ty is declared as string, the rest are declared as Variant type.

These are just some suggestions to help you in future.

Private Sub Command1_Click()
02	 
03	Dim conn1 As New ADODB.Connection
04	Dim cmd1 As New ADODB.Command
05	Dim rs1 As New ADODB.Recordset
06	Dim p As Integer
07	Dim accessconnect, n, a, num, ty As String
08	accessconnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=POOJA-D4FD585E2\SQL2000"
09	conn1.ConnectionString = accessconnect
10	conn1.Open
11	cust_name = Text1.Text
12	cust_address = Text2.Text
13	contact_no = Val(Text3.Text)
14	accnt_numbr = Text4.Text
15	account_type = Text5.Text
16	 
17	cmd1.ActiveConnection = conn1
18	cmd1.CommandText = " insert into customer(customer_name,customer_address,customer_phn,cutomer_dob,account_number,account_type) values(' " & cust_name & " ' , ' " & cust_address & " ',' " & contact_no & " ' , ' " & DTPicker1.Value & " ',' " & accnt_numbr & " ' , ' " & account_type & " ')"
19	Set rs1 = cmd1.Execute
20	conn1.Close
21	MsgBox ("DATABASE UPDATED SUCCESSFULLY")
22	End Sub
23	 
24	Private Sub Text1_Validate(Cancel As Boolean)
25	cust_name = Text1.Text
26	If cust_name = (" ") And (Len(cust_name) < 3) Then
27	    MsgBox ("Enter valid customer name")
28	    End If
29	End Sub
30	 
31	Private Sub Text2_Validate(Cancel As Boolean)
32	cust_address = Text2.Text
33	If cust_address = (" ") And (Len(cust_address) < 3) Then
34	    MsgBox ("Enter valid address")
35	    End If
36	End Sub
37	 
38	Private Sub Text3_Validate(Cancel As Boolean)
39	contact_no = Val(Text3.Text)
40	If Not IsNumeric(contact_no) And contact_no = (" ") And (Len(contact_no) < 10) Then
41	        MsgBox "Please enter numbers only."
42	    End If
43	End Sub
44	 
45	 
46	Private Sub Text4_Validate(Cancel As Boolean)
47	accnt_numbr = Text4.Text
48	If accnt_numbr = (" ") Then
49	    MsgBox ("Account number cannot be null ")
50	End If
51	End Sub
52	 
53	Private Sub Text5_Validate(Cancel As Boolean)
54	account_type = Text5.Text
55	If account_type = (" ") Then
56	    MsgBox ("Enter a type of account ")
57	End If
58	End Sub

The problem is when i get input the code does not validate i.e if i leave all text fields empty then also the database is updated

[maj3091]

In your command click event, try validating the length of the textboxes, for example

' Do this for all your TextBoxes.
If Len(Text.Text)>0 and Len(Text2.Text)>0 then

  'Write data here

else
  'Not all fields filled

endif

Also in your Validate Events, your checking the textboxes for having a space (not being empty).

Check your logic in your Validate events.

[Poojaaa]

THANK YOU