how to get the data on a text box?

[jini.fernandez]

hi,
i am trying to get a specific record to a number of textboxes from ms access database. but iam not able to do that. please help me here is the coding which i am trying to do. many thanks

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = Nothing
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = (" SELECT * FROM FAITH WHERE EMPLOYEE_ID = ' " & Form2.Text1.Text & " '")
rs.Open
Text1.Text = rs.Fields("S NO").Value
Text2.Text = rs.Fields("FIRST NAME").Value

[Doogle]

"but iam not able to do that" - what results are you getting ?

The code you have posted should select the set of rows from Table 'FAITH' where column 'EMPLOYEE_ID' exactly matches (including the Case) whatever is in Form2.Text1 and then put the values of columns "S NO" and "FIRST NAME" of the first row returned into Text1 and Text2, on the Form in which this code resides, respectively.

If there were no matches found you will get an error, telling you something like 'No Current Record' or 'Either BOF or EOF is true'

[jini.fernandez]

"but iam not able to do that" - what results are you getting ?

The code you have posted should select the set of rows from Table 'FAITH' where column 'EMPLOYEE_ID' exactly matches (including the Case) whatever is in Form2.Text1 and then put the values of columns "S NO" and "FIRST NAME" of the first row returned into Text1 and Text2, on the Form in which this code resides, respectively.

If there were no matches found you will get an error, telling you something like 'No Current Record' or 'Either BOF or EOF is true'

hi, thanks for the reply. actually am not gettiing any error message, the text fields are empty....not retrieving the data from the table.......

[DataMiser]

Do you by chance have an On Error Resume Next somewhere above that in the procedure?

If data was returned you should see it in the textbox unless of course those fields are blank in the selected record.
If no data is returned then you should be getting an error.

[jini.fernandez]

Do you by chance have an On Error Resume Next somewhere above that in the procedure?

If data was returned you should see it in the textbox unless of course those fields are blank in the selected record.
If no data is returned then you should be getting an error.

no there is no on error resume in my procedure, but i do not know why this is not working, is there any other way to get data on a text field other than this.

[Doogle]

I think we need to see more of your code. What you've shown us so far doesn't make sense regarding the results you're seing.

[DataMiser]

Unless of course those 2 fields are blank in the selected record.

Another possibility would be that there is code somewhere that is blanking the textboxes after they are set or that code is not being executed.

The code shown should work

[jini.fernandez]

I think we need to see more of your code. What you've shown us so far doesn't make sense regarding the results you're seing.

ok here are the coding for the entire form
Private Sub Command1_Click()
Form2.Show
Unload Me
End Sub

Private Sub Command2_Click()
Frame1.Visible = True
UPDATE.Visible = True
End Sub

[COLOR="#FF0000"]Private Sub Command3_Click()
Frame1.Visible = True
SAVE.Visible = True
End Sub

Private Sub Command4_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = Nothing
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = (" SELECT * FROM FAITH WHERE EMPLOYEE_ID = ' " & Form2.Text1.Text & " '")
rs.Open
Text1.Text = rs.Fields("S NO").Value
Text2.Text = rs.Fields("FIRST NAME").Value
End Sub

Private Sub Command6_Click()
Unload Me
End Sub

Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
cn.Open
Set rs = New ADODB.Recordset

With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open (" select * from faith where employee_id= ' " & Form2.Text1.Text & " '")
End With
DataGrid1.Refresh
DataGrid1.DataSource = rs
End Sub

iam getting another error too in the red colored codings its says method or datamember not found in the line of datagrid1.datasource = rs.
please help

[jini.fernandez]

thanks datamiser,
my record in not empty, its my company database, there are more fields in it but i just wanna try the first two fields but still its not working.

[jini.fernandez]

thanks datamiser,
my record in not empty, its my company database, there are more fields in it but i just wanna try the first two fields but still its not working.

[ladoo]

on data control
databasename type it in etc.mdb ' dont put in path name then save project and reopen put mdb file in application folder
make sure all controls such as textbox an datagrid etc are in all forms to avoid errors

post source if possible attach


putting path to mdb will give error later such as file not found this is why just type in the .mdb then put the file in application folder

soemthing i made in past

[jini.fernandez]

on data control
databasename type it in etc.mdb ' dont put in path name then save project and reopen put mdb file in application folder
make sure all controls such as textbox an datagrid etc are in all forms to avoid errors

post source if possible attach


putting path to mdb will give error later such as file not found this is why just type in the .mdb then put the file in application folder

soemthing i made in past

thanx ladoo
i will try your way

[Doogle]

Try testing for no records returned

Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
cn.Open
Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open (" select * from faith where employee_id= ' " & Form2.Text1.Text & " '")
End With
If Not (rs.BOF And rs.EOF) Then
    DataGrid1.Refresh
    DataGrid1.DataSource = rs
Else
    MsgBox "Query Returned No Records searching for Employee-ID:" & vbNewLine & Form2.Text1.Text
End If
End Sub

EDIT: I see you have leading and trailing spaces in the Query,(and also in the Command4_Click Event code) vis:

..... where employee_id= ' " & Form2.Text1.Text & " '"

I suspect you mean:

..... where employee_id= '" & Form2.Text1.Text & "'"

or even

..... where employee_id= '" & Trim$(Form2.Text1.Text) & "'"

which will remove any leading or trailing spaces from the TextBox contents.

[jini.fernandez]

Try testing for no records returned

Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
cn.Open
Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open (" select * from faith where employee_id= ' " & Form2.Text1.Text & " '")
End With
If Not (rs.BOF And rs.EOF) Then
    DataGrid1.Refresh
    DataGrid1.DataSource = rs
Else
    MsgBox "Query Returned No Records searching for Employee-ID:" & vbNewLine & Form2.Text1.Text
End If
End Sub

EDIT: I see you have leading and trailing spaces in the Query,(and also in the Command4_Click Event code) vis:

..... where employee_id= ' " & Form2.Text1.Text & " '"

I suspect you mean:

..... where employee_id= '" & Form2.Text1.Text & "'"

or even

..... where employee_id= '" & Trim$(Form2.Text1.Text) & "'"

which will remove any leading or trailing spaces from the TextBox contents.

hey thanx alot doogle.......i really appreciate the time you took do the coding. but unfortunately i found some other way to get the data on the textbox using adobc connection. but now iam finding difficulty for inserting the data back to the database from the textbox. can you please help me with that please. am attaching the
coding below .
Private Sub SAVE_Click()
Dim RS As Recordset
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
Adodc1.RecordSource = "INSERT INTO FAITH VALUES Adodc1.Recordset(S_NO).Value = ' " & Text1.Text & " ',Adodc1.Recordset(F_NAME).Value = '" & Text2.Text & "',Adodc1.Recordset(LAST NAME).Value = '" & Text3.Text & "',Adodc1.Recordset(EMPLOEE_ID).Value ='" & Text4.Text & " ',Adodc1.Recordset(PASPORT NUM).Value ='" & Text5.Text & " ',Adodc1.Recordset(PP EXPIRY DATE).Value = '" & Text6.Text & "',Adodc1.Recordset(VISA EXPIRY DATE).Value = '" & Text7.Text & "',Adodc1.Recordset(LABOUR CARD NUMBER).Value = '" & Text8.Text & "',Adodc1.Recordset(LABOUR CARDEXPIRY).Value =' " & Text9.Text & "',Adodc1.Recordset(EMIRATES ID EXPIRY).Value ='" & Text10.Text & " ',Adodc1.Recordset(LAST ENTRY IN UAE).Value ='" & Text11.Text & "',Adodc1.Recordset(CONTACT).Value ='" & Text12.Text & "',Adodc1.Recordset(ROOM NO).Value ='" & Text13.Text & "' "
MsgBox "NEW RECORD ENTERD SUCCESSFULY"
Frame1.Visible = True
SAVE.Visible = True
End Sub

[DataMiser]

I would try to stay away from using the ADODC.
You can not use an insert string as a recordsource.

Use the connection object and the .Execute method to execute the insert
Use a Recordset to hold the results of a select query like you were doing before being lead down the adodc path.

Also you should not place the mdb in the application folder that can lead to problems

[techgnome]

sweet jesus.... this is the problem:
Private Sub Form_Load()

you're loading data based on a textbox in the load event of the form... only the text box IS EMPTY... because the form only just loaded...
so this
.Open (" select * from faith where employee_id= ' " & Form2.Text1.Text & " '")
becomes this
.Open (" select * from faith where employee_id= '' ")
which means select rows where the employee_id is empty.... so OF COURSE you're going to get nothing in the text boxes... because you DIDN'T load anything...
This is why we need the greater context sometimes ... there was nothing wrong with the original code itself... the problem was in where it was being called from.

as for this:

Private Sub SAVE_Click()
Dim RS As Recordset
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\user\Desktop\NEW DB\FAITH.mdb;Persist Security Info=False"
Adodc1.RecordSource = "INSERT INTO FAITH VALUES Adodc1.Recordset(S_NO).Value = ' " & Text1.Text & " ',Adodc1.Recordset(F_NAME).Value = '" & Text2.Text & "',Adodc1.Recordset(LAST NAME).Value = '" & Text3.Text & "',Adodc1.Recordset(EMPLOEE_ID).Value ='" & Text4.Text & " ',Adodc1.Recordset(PASPORT NUM).Value ='" & Text5.Text & " ',Adodc1.Recordset(PP EXPIRY DATE).Value = '" & Text6.Text & "',Adodc1.Recordset(VISA EXPIRY DATE).Value = '" & Text7.Text & "',Adodc1.Recordset(LABOUR CARD NUMBER).Value = '" & Text8.Text & "',Adodc1.Recordset(LABOUR CARDEXPIRY).Value =' " & Text9.Text & "',Adodc1.Recordset(EMIRATES ID EXPIRY).Value ='" & Text10.Text & " ',Adodc1.Recordset(LAST ENTRY IN UAE).Value ='" & Text11.Text & "',Adodc1.Recordset(CONTACT).Value ='" & Text12.Text & "',Adodc1.Recordset(ROOM NO).Value ='" & Text13.Text & "' "
MsgBox "NEW RECORD ENTERD SUCCESSFULY"
Frame1.Visible = True
SAVE.Visible = True
End Sub

1) you really should use parameters if you're going to build the SQL like that -ie, don't use concatenation
2) holy smokes... I just relooked at that... and that "SQL" is just all kinds of wrong... you have Adodc1.Recordset insidet the SQL.... which the database is going to know nothing about...
3) all you did was set the recordsource of the ADODC... you never executed it
4) Don't use ADODC... use ADO and Command/Connection objects instead
5) if you insist on using ADODC, add to it by adding to the RecordSet

-tg

[DataMiser]

I think the text box being referenced is on a different form so there may be data there when the code executes

[techgnome]

good point.... I missed that... but... makes me wonder if those spaces in the inside of the string are supposed to be there or not.... (I'll go with "or not") ... also is Emplyee_ID a number or a string? If it's a number IN THE DATABASE, then it should be treated as a number in the SQL...

-tg

[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.

[jini.fernandez]

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.

hi
thanks a lot everyone, this code seems to be working but at the end again i ended up with an another error which is data type mismatched in criteria expression and
it is showing in this " con.Execute strSQL" line, i have some fields for the dates where am using the like this 5-may-13, so i think probably date fields are showing the error, so i changed the date field to text field but the result is same. so please help me. thanks a lot again

[Vincent Hadison]

Maybe there is something wrong with your database location

[techgnome]

Not sure why I left it off my list earlier, but there should have been a #6 - Use parameters...
Odds dates are an issue... they should be wrapped in # not ' .... #5-may-13#

-tg