Required text box with Save Button on form

[inkbird1]

Hi, I have a data entry form with a save button - I am wanting to check that the text box has a value before submitting to table.

Private Sub btn_save_Click()

Dim strSQL        As String
  Dim DB            As DAO.Database
  Dim RS            As DAO.Recordset
  Dim ctl           As Control
 
  Set DB = CurrentDb()
  Set RS = DB.OpenRecordset("Tbl_users", dbOpenDynaset, dbAppendOnly)

  'Add user to table
    RS.AddNew
    RS!UserName = txt_username
    RS!FirstName = txt_firstname
    RS!FirstName = txt_lastname
    RS!Password = txt_genpassword
    RS!AccessLvl = cbo_AccessLvl
    RS!Facility = cbo_facility
    RS!Email = txt_email
    RS!IsActive = "Yes"
    RS!CreatedBy = Environ("USERNAME")
    RS!CreateDate = Now()
    RS.Update
   
        MsgBox "Record Successfully Added"
   

End Sub

 

[Micron]

Not sure why you'd open a recordset for this when you can just run a query or a sql statement or even just make it a required field in table design (assuming the control is bound to a field). Anyway, something like

If Not Nz(Me.txtMyTextbox,0) = 0 Then
 Set DB = CurrentDb()
 Set RS = DB.OpenRecordset("Tbl_users", dbOpenDynaset, dbAppendOnly)
'rest of code

End If

If you SET a variable to something, at the end you should set it to Nothing.

 

[inkbird1]

Not sure why you'd open a recordset for this when you can just run a query or a sql statement or even just make it a required field in table design (assuming the control is bound to a field). Anyway, something like

If Not Nz(Me.txtMyTextbox,0) = 0 Then
 Set DB = CurrentDb()
 Set RS = DB.OpenRecordset("Tbl_users", dbOpenDynaset, dbAppendOnly)
'rest of code

End If

If you SET a variable to something, at the end you should set it to Nothing.

Thank you for your reply.
What would the correct way be to insert this record whilst also having the text field validation?

 

[Micron]

The simple validation is what I posted. If you want something more robust, such as only allowing numbers, or text or whatever else, then that would be added to the IF block. As I mentioned, you'd either build a sql statement and run or execute it or run a query. Having said that, if there is something about that query that would make it not updatable, then yes you would use recordset(s). When executing a sql statement you don't have to turn off warnings, so it's my preferred method. Look up ms access CurrentDb.Execute if interested.

Now that I've re-read your first post I have to wonder why the form isn't just bound to a table or query and you do validation using form or control BeforeUpdate event? No executing, no running query, no code to build sql statements?