Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-06
11
Medium Priority
?
165 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 250 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

916 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