Putting VB code into Access to validate

[Carin Cloutier]

Can anyone help me, I need a procedure to validate input into an Access table field that will not allow for a space or a hyphen. The database was already set up and there are already spaces and hyphens in the field.

[Johnny101]

if you are getting information from the user from a text box, for example, then you can not allow hyphens or spaces. there are several ways of doing this. i would recommend using the Replace function and take out those characters before yuo build your SQL statement to insert those values.

Sub ValidateData()
dim sName as string

sName = VBA.Replace(txtName.Text, "-")
sName = VBA.Replace(sName, " ")

sql = "INSERT INTO YourTable (Name) VALUES ('" & sName & "')"
End Sub





the variable sName now contains the text from the text box, but without any spaces or hyphens.

as for the data already in the table, you would have to write a little procedure to open the table and the go through the records one at a time and take out the spaces and hyphens. maybe something like this:


Sub FixData()
dim rs as adodb.recordset
dim cn as adodb.connection
dim x as long

set cn = new adodb.connection
cn.connecionstring = "Your connection string"

cn.open

sql = "SELECT * FROM theTable"

set rs = new adodb.recordset

rs.open sql, cn, adOpenDynamic

if not rs.eof then
while not rs.eof
for x = 0 to rs.fields.count
rs(x) = VBA.Replace(rs(x), " ")
rs(x) = VBA.Replace(rs(x), "-")
rs.update
next
rs.movenext
wend
end if

rs.close
set rs = nothing
cn.close
set cn = nothing




that should give you some ideas about how to clean up the data and keep it clean.

hope this helps,

John


John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org