We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

mindserve
mindserve asked
on
Medium Priority
173 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
Comment
Watch Question

Commented:
Wouldn't you use something like:

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

Author

Commented:
I get a  method or data member not found error when I tried that..is that the correct syntax

Commented:
>>fldsimappointment.value = adodc4.fields("Phone")
fldsimappointment.text = adodc4.fields("Phone")

~marchent~

Author

Commented:
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

Commented:
what r the column names of ur table? give us the table structure
like
Table name : emp
Columns     : name , id, phone, dept

Author

Commented:
Table Name Customers as in the code I posted.
Field Name
CompanyName
ContactName
Phone

Commented:
>>.RecordSource = "SELECT *,CompanyName & ' ' & ContactName AS ContactInfo  FROM CUSTOMERS ORDER BY CompanyName"

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

Author

Commented:
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
Commented:
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~

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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...

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.