Solved

Split a string containing numeric and non numeric data to two separate textboxes

Posted on 2006-11-06
11
163 Views
Last Modified: 2010-04-30
I need to split a string that contains numeric and non numeric data.
For example:
I have this block of code which loads the data to a datacombo box.
With Adodc4
 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SS2X.MDB;Persist Security Info=False"
.RecordSource = "SELECT *,CompanyName & ' ' & ContactName & ' ' & Phone AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"
 End With
*************************************
I need to send the Phone number to a textfield named:
FldSimAppointment
and to leave the CompanyName and ContactName in another textfield named:txtAppointmentFor

I am not sure of how to split this so that the numeric data ie the phone number goes to fldsimappointment textbox
0
Comment
Question by:mindserve
  • 6
  • 4
11 Comments
 

Expert Comment

by:kalebb
ID: 17885897
Wouldn't you use something like:

fldsimappointment.value = adodc4.fields("Phone")

0
 
LVL 1

Author Comment

by:mindserve
ID: 17886195
I get a  method or data member not found error when I tried that..is that the correct syntax
0
 
LVL 13

Expert Comment

by:marchent
ID: 17887138
>>fldsimappointment.value = adodc4.fields("Phone")
fldsimappointment.text = adodc4.fields("Phone")

~marchent~
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 1

Author Comment

by:mindserve
ID: 17888618
I still get the same error message, but the error shows for "fields".
I am putting the code block this way:

 With Adodc4

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SS2X.MDB;Persist Security Info=False"
.RecordSource = "SELECT *,CompanyName & ' ' & ContactName AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"
FldSimAppointment.Text = Adodc4.Fields("Phone")
             
 End With

0
 
LVL 13

Expert Comment

by:marchent
ID: 17888849
what r the column names of ur table? give us the table structure
like
Table name : emp
Columns     : name , id, phone, dept
0
 
LVL 1

Author Comment

by:mindserve
ID: 17889457
Table Name Customers as in the code I posted.
Field Name
CompanyName
ContactName
Phone
0
 
LVL 13

Expert Comment

by:marchent
ID: 17889777
>>.RecordSource = "SELECT *,CompanyName & ' ' & ContactName AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"

.RecordSource = "SELECT Phone,CompanyName & ' ' & ContactName AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"
0
 
LVL 1

Author Comment

by:mindserve
ID: 17889845
No, I did that..then all of the data is on the drop down list but none is sent to the textbox as I posted originally... Notice Phone is in the query....
The txtappointmentfor textbox gets the datacombo information which is CompanyName and ContactName,, when you add Phone it's all going to the same textbox..I need to split it up...
--------------------------------------------------
With Adodc4
 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SS2X.MDB;Persist Security Info=False"
.RecordSource = "SELECT *,CompanyName & ' ' & ContactName & ' ' & Phone AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"
 End With
*************************************
I need to send the Phone number to a textfield named:
FldSimAppointment
and to leave the CompanyName and ContactName in another textfield named:txtAppointmentFor

I am not sure of how to split this so that the numeric data ie the phone number goes to fldsimappointment textbox
0
 
LVL 13

Accepted Solution

by:
marchent earned 125 total points
ID: 17895997
it is tough to use adodc at runtime, i thing its really a tough issue. another approach is using ADODB for creat connection to database at runtime

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As Variant

Private Sub Form_Load()
    'set new connection, and open
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SS2X.MDB;Persist Security Info=False"
    'set new recordset and open
    Set rs = New ADODB.Recordset
    rs.Open "SELECT *,CompanyName & ' ' & ContactName & ' ' & Phone AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName", con, adOpenStatic, adLockOptimistic
   
    str = Split(rs.Fields(0))
    txtAppointmentFor.Text = str(2) 'phone number
    FldSimAppointment.Text = str(0) & " " & str(1) 'contact info
   
    'set the variables
    Set rs = Nothing
    Set con = Nothing

End Sub

From ur VB editor, click, Project->Refrences.., then select the reference "Mircosoft ActiveX Data Object Linbrary" version 2.X
~marchent~
0
 
LVL 1

Author Comment

by:mindserve
ID: 17896759
I can't change the data connection since there are more than one adodc connections for this screen. That wouldn't work. There must be a way to split the string or to get that field into the fldsimappointment.text texbox...
0
 
LVL 1

Author Comment

by:mindserve
ID: 17898257
Well, since it's a datacombo drop down, I was able to make the change from the adodc4 to a different recordset adodb and placed it there. Now when the user looks in the drop down list, the phone is sent to the correct field. Not elegant or what I was hoping for, but it works.
*******************************
Set adoPrimaryRS = New ADODB.Recordset
 adoPrimaryRS.Open "select Phone From Customers", dataconnection, adOpenStatic, adLockOptimistic
adoPrimaryRS.Bookmark = DataCombo2.SelectedItem
FldSimAppointment.Text = adoPrimaryRS.Fields!Phone
 txtAppointmentFor.Text = DataCombo2
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

777 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