how to get the data on a text box?

[Doogle]

I'd go with the earlier suggestions and use a Connection Object to update the Table. Also, as perhaps a matter of style, I'd create a variable containing the SQL to be executed.

Private Sub SAVE_Click()
 Dim con As ADODB.Connection
 Dim strSQL As String
 Set con = New ADODB.Connection
 con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
 con.Open
 strSQL = "INSERT INTO FAITH ("
 strSQL = strSQL & "[S_NO],"                    'Text1
 strSQL = strSQL & "[F_NAME],"                  'Text2
 strSQL = strSQL & "[LAST NAME],"               'Text3
 strSQL = strSQL & "[EMPLOYEE_ID],"             'Text4
 strSQL = strSQL & "[PASSPORT NUM],"            'Text5
 strSQL = strSQL & "[PP EXPIRY DATE],"          'Text6
 strSQL = strSQL & "[VISA EXPIRY DATE],"        'Text7
 strSQL = strSQL & "[LABOUR CARD NUMBER],"      'Text8
 strSQL = strSQL & "[LABOUR CARDEXPIRY],"       'Text9
 strSQL = strSQL & "[EMIRATES ID EXPIRY],"      'Text10
 strSQL = strSQL & "[LAST ENTRY IN UAE],"       'Text11
 strSQL = strSQL & "[CONTACT],"                 'Text12
 strSQL = strSQL & "[ROOM NO]"                  'Text13
 strSQL = strSQL & ")"
 strSQL = strSQL & " VALUES("
 strSQL = strSQL & "'" & Text1.Text & "',"      'S_NO
 strSQL = strSQL & "'" & Text2.Text & "',"      'F_NAME
 strSQL = strSQL & "'" & Text3.Text & "',"      'LAST NAME
 strSQL = strSQL & "'" & Text4.Text & "',"      'EMPLOYEE_ID
 strSQL = strSQL & "'" & Text5.Text & "',"      'PASSPORT NUM
 strSQL = strSQL & "'" & Text6.Text & "',"      'PP EXPIRY DATE
 strSQL = strSQL & "'" & Text7.Text & "',"      'VISA EXPIRY DATE
 strSQL = strSQL & "'" & Text8.Text & "',"      'LABOUR CARD NUMBER
 strSQL = strSQL & "'" & Text9.Text & "',"      'LABOUR CARDEXPIRY
 strSQL = strSQL & "'" & Text10.Text & "',"     'EMIRATES ID EXPIRY
 strSQL = strSQL & "'" & Text11.Text & "',"     'LAST ENTRY IN UAE
 strSQL = strSQL & "'" & Text12.Text & "',"     'CONTACT
 strSQL = strSQL & "'" & Text13.Text & "'"      'ROOM NO
 strSQL = strSQL & ")"
 con.Execute strSQL
 MsgBox "NEW RECORD ENTERD SUCCESSFULY"
 Frame1.Visible = True
 SAVE.Visible = True
 con.Close
 Set con = Nothing
End Sub

Doing 'a line at a time' makes it easier to visually check that all the necessary quotes and commas are present as well as checking you're submitting as many values as there are columns. Also, if something goes wrong you can 'Debug.Print strSQL' prior to the 'con.Execute' and see what is actually being presented to the SQL Parser. It may take a few more minutes to construct the source code but in the long term (IMHO) it's worth it.