Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2015
    Posts
    5

    Unanswered: Ensure TB is used if CB is not null

    Hello all.

    I'm at a bit of a stumbling block trying to get some VBA to work for me. I would like a MsgBox to pop up if tboEquipmentTime is not used when cboEquipment has been used. I'm using my current line of code under my cmdSave sub. What I have now pops up a MsgBox asking for equipment time regardless of the use of cboEquipment. I would like it to pop up only if cboEquipment has been used. Both have been set to default value = Null.

    This is what I'm currently working with.

    Code:
    If Not IsNull(Me.cboEquipment) Then
         If IsNull(Me.tboEquipmentTime) Then
              MsgBox Prompt:="Please Enter Equipment Time", Buttons:=vbOKOnly Or vb, Title:="Enter Equipment Time"
              Me.tboEquipmentTime.SetFocus
        Exit Sub
         End If
    Looking forward to any suggestions,

    Stephenson.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    This says : tboEquipmentTime
    is it supposed to be 'tbo' and not 'cbo'?
    What is a 'tbo'?

  3. #3
    Join Date
    Sep 2015
    Posts
    5
    Thanks for having a look ranman256.

    tboEquipmentTime is a text box showing the time the equipment form the combo box cboEquipment was used.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you explicitly required vriables to be declared.. use Option Explicit as the first line in each and every form, report and code module. depending on your version of Access there is a setting that cand be defined. but Option Explicit always gets round such settigns and forces it.

    have you put a watch / breakpoint on the code and stepped through it to make certain that the code you think is running is doing what yiu think it is.
    is getting the right values.

    rather than rely on default properties you would be far better always explicitly referring to the properties you want

    Ive not seen := used in VBA before, that may reflect I'm not using the most recent versions of Access, and haven't done much 'serious' work in Access for a while
    the style of code for your msgbox isn't ione I'm familiar with, as above that may be down to me being old school and out of date
    I'm more used to the style referred to here

    so refer to the value property such as tboEquipmentTime.value, not tboEquipmentTime
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2015
    Posts
    5
    Thank you Healdem.

    I'm going to attempt to make the changes you've suggested and see if I can get it to work. I'm very new to using VBA. I'm sure it shows.

  6. #6
    Join Date
    Sep 2015
    Posts
    5
    I've got it working.

    Here is the code that I used.

    Code:
    If (IsNull(Me.EquipmentID)) Or (Me.tboEquipmentTime = "") Then
             Else
            If (IsNull(Me.tboEquipmentTime)) Or (Me.tboEquipmentTime = "") Then
              MsgBox "Please Enter Time For " & (DLookup("[Model]", "Equipment", "ID=" & [cboEquipment])) & " ", vbOKOnly Or vbExclamation, "Enter Equipment Time"
              Me.tboEquipmentTime.SetFocus
        Exit Sub
         End If

  7. #7
    Join Date
    Sep 2015
    Posts
    5
    I'd like to use variables to streamline my code as I feel it is quite clunky at this point.

    Is there a good way to streamline this section of code? Or perhaps a better way to handle it? This is for a form that I'm trying to ensure the required fields have been used and add a nice message for the user.

    Code:
    Private Sub cmdSave_Click()
       If IsNull(Me.JobID) Then
            MsgBox "Please Enter Job Name", vbOKOnly Or vbExclamation, "JOB NAME"
            Me.cboJob.SetFocus
       Exit Sub
        End If
       If IsNull(Me.EmployeeID) Then
            MsgBox "Please Enter Employee Name", vbOKOnly Or vbExclamation, "EMPLOYEE NAME"
            Me.cboEmployee.SetFocus
       Exit Sub
        End If
       If IsNull(Me.tboDate) Then
            MsgBox "Please Enter Date", vbOKOnly Or vbExclamation, "Date"
            Me.tboDate.SetFocus
       Exit Sub
        End If
       If IsNull(Me.ServiceID) Then
            MsgBox "Please Enter Service Type", vbOKOnly Or vbExclamation, "SERVICE"
            Me.cboEmployee.SetFocus
       Exit Sub
        End If
         If (IsNull(Me.EquipmentID)) Or (Me.tboEquipmentTime = "") Then
             Else
            If (IsNull(Me.tboEquipmentTime)) Or (Me.tboEquipmentTime = "") Then
              MsgBox "Please Enter Time For " & (DLookup("[Model]", "Equipment", "ID=" & [cboEquipment])) & " ", vbOKOnly Or vbExclamation, "Enter Equipment Time"
              Me.tboEquipmentTime.SetFocus
        Exit Sub
         End If
            
        Exit Sub
        End If
            MsgBox "Record for " & Me.tboFormTitle.Value & " has been added", vbOKOnly Or vbInformation, "Saved"
        Call Save
            Me.cboJob.SetFocus
    End Sub

    Thanks for having a look.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •