Solved

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

Posted on 2006-11-06
11
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…

717 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