how to get the data on a text box?

Printable View

May 19th, 2013, 02:46 AM
jini.fernandez

how to get the data on a text box?

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
May 19th, 2013, 03:25 AM
Doogle

Re: how to get the data on a text box?

"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'
May 19th, 2013, 05:42 AM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by 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'

hi, thanks for the reply. actually am not gettiing any error message, the text fields are empty....not retrieving the data from the table.......
May 19th, 2013, 08:07 AM
DataMiser

Re: how to get the data on a text box?

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.
May 19th, 2013, 08:17 AM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by 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.

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.
May 19th, 2013, 10:03 AM
Doogle

Re: how to get the data on a text box?

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.
May 19th, 2013, 11:32 AM
DataMiser

Re: how to get the data on a text box?

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
May 20th, 2013, 01:43 AM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by 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.

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
May 20th, 2013, 01:45 AM
jini.fernandez

Re: how to get the data on a text box?

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.
May 20th, 2013, 01:46 AM
jini.fernandez

Re: how to get the data on a text box?

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.
May 20th, 2013, 01:51 AM
ladoo

Re: how to get the data on a text box?

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

:p:p:psoemthing i made in past
http://oi41.tinypic.com/30cps3q.jpg
:p:p:p:p:p
May 20th, 2013, 02:20 AM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by 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

:p:p:psoemthing i made in past
http://www.vbforums.com/images/ieimages/2013/05/1.jpg
:p:p:p:p:p

thanx ladoo
i will try your way
May 20th, 2013, 02:30 AM
Doogle

Re: how to get the data on a text box?

Try testing for no records returned

Code:

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:

Code:

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

I suspect you mean:

Code:

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

or even

Code:

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

which will remove any leading or trailing spaces from the TextBox contents.
May 20th, 2013, 07:01 AM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by Doogle

Try testing for no records returned

Code:

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:

Code:

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

I suspect you mean:

Code:

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

or even

Code:

..... 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
May 20th, 2013, 09:17 AM
DataMiser

Re: how to get the data on a text box?

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
May 20th, 2013, 09:29 AM
techgnome

Re: how to get the data on a text box?

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:

Code:

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
May 20th, 2013, 10:20 AM
DataMiser

Re: how to get the data on a text box?

I think the text box being referenced is on a different form so there may be data there when the code executes
May 20th, 2013, 11:29 AM
techgnome

Re: how to get the data on a text box?

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
May 20th, 2013, 01:21 PM
Doogle

Re: how to get the data on a text box?

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.

Code:

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.
May 20th, 2013, 11:55 PM
jini.fernandez

Re: how to get the data on a text box?

Quote:

Originally Posted by 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.

Code:

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
May 21st, 2013, 12:38 AM
Vincent Hadison

Re: how to get the data on a text box?

Maybe there is something wrong with your database location
May 21st, 2013, 07:36 AM
techgnome

Re: how to get the data on a text box?

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