Value is Null

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello,

I have this code which is for a button on a form with just one unbound text
box. The idea is that the user types in a name of a client and this name is
added to the table. Here is the code:

Private Sub Add_Click()
Dim DBS As Database, rs As Recordset

Set DBS = OpenDatabase("\\Database Name")
Set rs = DBS.OpenRecordset("tbl Customer Names", dbOpenTable)

rs.AddNew
rs![Name] = Me.CName.Value
rs.Update

DoCmd.Close acForm, "Customer Name Add"

End Sub

The problem is that the value of the unbound text box is null and so nothing
is added. My question is how do I capture the data the user has entered into
the text box?

Any help woudl be greatly appreciated.

Many thanks,

Martin
 
Sounds as though this only happens if they click the button before they fill
in the field, therefore you need to check for that case:

Private Sub Add_Click()
Dim DBS As Database, rs As Recordset

If IsNull(Me.CName.Value) = True Then
MsgBox "Enter a name first."
Else
Set DBS = OpenDatabase("\\Database Name")
Set rs = DBS.OpenRecordset("tbl Customer Names", dbOpenTable)

rs.AddNew
rs![Name] = Me.CName.Value
rs.Update

DoCmd.Close acForm, "Customer Name Add"
End If

End Sub


Personally, I'd be more inclined to use an INSERT INTO SQL statement rather
than bothering with a recordset:

Private Sub Add_Click()
Dim DBS As Database
Dim strSQL As String

If IsNull(Me.CName.Value) = True Then
MsgBox "Enter a name first."
Else
Set DBS = OpenDatabase("\\Database Name")
strSQL= "INSERT INTO tblCustomerNames ([Name]) " & _
"VALUES (""" & Me.CName.Value & """)"
DBS.Execute strSQL, dbFailOnError

DoCmd.Close acForm, "Customer Name Add"
End If

End Sub

I'd also change the field name from Name. Name is a reserved word, and you
should never use reserved words for your own purposes. For a good discussion
on what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
Back
Top