i have an error in my numric fields.
just like Phone no., fax no., zip code.
these field in data base are assigned numeric data type.
now when i m assigning these fields to textboxes, there is error cuming, type mis match.
phTxt.Text = rs!Phone
in this line.
Type mismatch refers to the data collected. When you created your table with "Phone" as a field,, what type did you gave it, integer or text?
If a user types text, the data will mismatch if the table requires a number. Also, make sure there is no other characters added than only numbers, i.e. (097) 999-22345 etc. This will also raise an error. Spaces as well.
Post all the code in the command button sub, let me have a look.
Also, if you can post a picture of what data was added in the text box.
From there I can add some error trapping code for you.:)
this is what code, i have used for adding new record, and i have assingned numeric data type to phone, fax, n etc in sql server....
Private Sub update1_Click()
Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM ORGANIZATION", con, adOpenStatic, adLockOptimistic
rs.AddNew
rs!Orgcode = Text1.Text
rs!Organizationname = Text2.Text
rs!Shortname = Text3.Text
rs!Contactperson = Text4.Text
rs!Address = Text5.Text
rs!City = Text6.Text
rs!Phone = Text7.Text
rs!Fax = Text8.Text
rs!NTN = Text9.Text
rs!Email = Text10.Text
rs!URL = Text11.Text
rs!Lontitude = Text12.Text
rs!Latitude = Text13.Text
rs!Address1 = Text14.Text
rs!Address2 = Text15.Text
rs!State = Text16.Text
rs!Zip = Text17.Text
rs!Country = Text18.Text
rs!Joiningdate = DTPicker1
rs!Leavingdate = DTPicker2
xDate = vbNullString
rs!Category = Combo1.Text
rs!Status = Combo2.Text
rs.Update
rs.close
con.closethis is a picture...
and in this record shown is already stored by me in sql server, now when i clicking on add, it shows an error before typing anything....
phTxt.Text = rs!Phone
This is your problem, you do not have a textbox called phTxt. You must learn to use conventional naming structures. What that means, is naming your controls so you know which refers to what. If you get an error, it is very difficult to determine where if you use as you did above text1, text2, text3 etc.
Rather start using -
rs!Orgcode = Text1.Text - txtOrgCode.Text
rs!Organizationname = Text2.Text - txtOrganizationname.Text etc....
Now to solve your problem, -
Text7.Text = rs!PhoneSorted, please mark as solved, thanks.:)
hahahah....i knew this....i just edit it to send it before you...
it is written in text7.text=rs!phone...
i myself edit it in this editor, so u can easily understand...
tht is not a problem...
still error is another...
i think i should to change data type from sql server, then it will accept..
and i just tried, when i changed data type from numeric to text, it didnt pass any error..but i have to keep same data type numeric
You will be surprised how small things can fool us around.:)
To do some error trapping, do the following -
BEFORE you call the show method, this
If Not IsNumeric(Text7.Text) Then
msgbox "Numbers Only"
Else
rs!Phone = Text7.Text
End If
'Another Easy way is under your Text7 Change event....
Private Sub Text7_Change()
Dim WshShell As Object
Set WshShell = CreateObject("WScript.Shell")
If Not IsNumeric(Text7.Text) Then
WshShell.SendKeys "{BackSpace}"
End If
End SubThis way, your database table will accept the data, because it is only in numerical values.:)
it is not solved....still same error...
Have you try the code above?
Also, did you change the type to either integer/numerical or text?
I'll run all code in a test project to see where the error occurred. I will however test in access and mysql, do not have mssql.
you must to change data type on database..
also i want to know which line of your code that error came up...
about text box input, you can set text box to not allowed an input except a numeric input. so text box just accept a numeric input.
Private Sub Text7_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 48 To 57, 8
'Let these key codes pass through
Case Else
'All others get trapped
KeyAscii = 0
End Select
End Sub:) Which is similar to what I posted above:)
It seems his data types is throwing the error. After a few posts, I have decided to test it and see. If you have mssql, and you can test it, cool.;)
i've tried the similiar code. it running nicely.
as we said to you to change data type in database field in mssql server.
and maybe u need to convert an input to numeric type before add it to database.
Tested on MySql AND Access, working perfect. Change your data types to overcome the error.
how can i change my data type in MSSQL...?
it is primary key, Id, That will b in much need...its data type is numeric length 5, which data type should i keep..
i already told u that when i using text data type it is not erroring...bt as i told u i want the numeric data type, as it is ID..
and as i already told u, it is not erroring when i m adding the record, but when navigating the record, it only points out the fields which have numeric data type.
and as i already told u
No you have not...
Show me the code when navigating the records AND the line where the error occurs.
>>and as i already told u, it is not erroring when i m adding the record, but when navigating the record...
so,when you telling this?? you concern us with your adding codes from the start..
just post your navigate code and provides a full info about error..what an error came up, which line, etc.. so we can fully understand the problem..
Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * From ORGANIZATION", con, adOpenStatic, adLockOptimistic
If rs.BOF Then
MsgBox "First Record Was Reached,no more to display"
Exit Sub
Else
rs.MovePrevious
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
Text4.Text = rs!Contactperson
Text5.Text = rs!Address
Text6.Text = rs!City
Text7.Text = rs!Phone
Text8.Text = rs!Fax
Text9.Text = rs!NTN
Text10.Text = rs!Email
Text11.Text = rs!URL
Text12.Text = rs!Lontitude
Text13.Text = rs!Latitude
Text14.Text = rs!Address1
Text15.Text = rs!Address2
Text16.Text = rs!State
Text17.Text = rs!Zip
Text18.Text = rs!Country
DTPicker1 = rs!Joiningdate
DTPicker2 = rs!Leavingdate
Combo1.Text = rs!Category
Combo2.Text = rs!Status
rs.Update
rs.close
con.close
End Ifwhen i m pressing move previous button, it is showing type mismatch error and when pressing debug, it goes to
Text1.Text = rs!Orgcodeand when blocking this line, then
Text2.Text = rs!OrganizationnameError is Either BOF or EOf is true, or the current record has been deleted.requested opertion requires a current record
Have a look at your last part of the code -
Combo1.Text = rs!Category
Combo2.Text = rs!Status
rs.Update
rs.close
con.close
The update statement IS ONLY USED WHEN ADDING OR EDITING records.:) Remove "rs.Update" from the movePrevious code and in the other move subs as well if it does appear there.. If it came from my code, I do apologize, remember that I wrote the code sample for you in 15 minutes.;)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.