Specific Values for Textbox Input

[accessNOOB1234]

I have VB Code running (which I pulled from another site), and I was wondering if there was anyway to make it say that ONLY these entries are valid.

DoCmd.SetWarnings False
If IsNull(DLookup("ProgramName", "tbl123", "ProgramName ='" & Me.ProgramName & "'")) Then
DoCmd.RunSQL "INSERT INTO tbl123(ProgramName, Status) VALUES('" & Me.ProgramName & "', '" & Me.Status & "')"
Else
DoCmd.RunSQL "UPDATE tbl123 SET Status ='" & Me.Status & "' WHERE ProgramName='" & Me.ProgramName & "'"
End If
DoCmd.SetWarnings True

I was wanting to only allow the status to be set to Active or Inactive. Is there a way to do this?

 

[pbaldy]

Personally I'd probably use a combo box with its Limit to List property set to Yes. It would get the two values from a table, which would allow you to add a status later. You could probably also use a validation rule or code like:

If Me.Status = "Active" Or Me.Status = "Inactive" Then
  'do your thing
Else
  Msgbox "Invalid status"
End If

 

[missinglinq]

I agree with Paul, when limiting an entered Value is necessary, a Combobox is always a good approach. But if you insist on a 'freehand' data entry approach, this will work:

Private Sub Status_BeforeUpdate(Cancel As Integer)
 If Me.Status = "Active" Or Me.Status = "Inactive" Then
  'do whatever or simply do nothing
Else
  MsgBox "Invalid Status"
  Cancel = True
  Status.SelStart = 0
  Status.SelLength = Len(Me.Status.Text)
End If
End Sub

Linq ;0)>

 

[accessNOOB1234]

missinglinq- When I run that code, I get an error of "Compile Error: Variable Not Defined" on this portion of code:

Cancel = True

 

[pbaldy]

What event are you running it in? It is intended for the before update event.

 

[accessNOOB1234]

I was trying to run it in a onclick() event. I was trying to add it to some simple VB code that I had found...

'DoCmd.SetWarnings False
'If Me.Status = "Active" Or Me.Status = "Inactive" Then
'Else
'MsgBox "Invalid Status"
'Cancel = True
'Status.SelStart = 0
'Status.SelLength = Len(Me.Status.Text)
'End If
'If IsNull(DLookup("ProgramName", "tbl123", "ProgramName ='" & Me.ProgramName & "'")) Then
'DoCmd.RunSQL "INSERT INTO tbl123(ProgramName, Status) VALUES('" & Me.ProgramName & "', '" & Me.Status & "')"
'Else
'DoCmd.RunSQL "UPDATE tbl123 SET Status ='" & Me.Status & "' WHERE ProgramName='" & Me.ProgramName & "'"
'End If
'DoCmd.SetWarnings True

 

[pbaldy]

Again, Linq's code is only appropriate for the before update event. Did you try incorporating yours into what I posted?