Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: VBA Form/Subform Help

    I have a cow location subform within a cow entry form for a beef producer to enter his cows into. I am trying to make it where you can't exit out of the form without several fields being filled out. Here is what I have so far:

    Private Sub Form_Close()
    Dim stDocName As String
    Dim VarItem As Variant
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCowVID As String
    Dim strCowLocation As String
    Dim strDateMovedTo As String
    Dim db As DAO.Database
    Dim ctl As Control
    Dim tdf As DAO.TableDef
    Set db = CurrentDb()


    strDateMovedTo = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMoved To
    strCowLocation = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocati on
    strCowVID = " " & Me.CowVID

    If Trim(strCowVID) = "" And [Trim(strCowLocation) = "" Or Trim(strDateMovedTo) = ""] Then
    MsgBox "You Must Enter A Cow Location And A Cow Move Date To Continue!"
    Else
    DoCmd.Close

    End If

    End Sub
    ----------------
    The CowVID field is on the main form (frmCowEntry), while CowLocation and DateMovedTo are fields on the subform(frmsbCowLocationsEntry). What I want it to do, is if CowVID isn't null, to make sure that CowLocation and DateMovedTo are all filled out before the form will close. I don't know too much about coding and I know something is wrong, I just don't know what!
    When I enter a CowVID and hit close, I get run time error 2455 - you entered an expression that has an invalid reference to property Form/Report. When I hit debug, this line is highlighted:
    strDateMovedTo = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMoved To

    Also, when I try to close on a record that actually has everything filled out, I get runtime error 2465 - Microsoft Access can't find the field "|" referred to in your expression. When I hit debug on that, this line is highlighted:
    If Trim(strCowVID) = "" And [Trim(strCowLocation) = "" Or Trim(strDateMovedTo) = ""] Then

    Any help on this subject would be greatly appreciated.

    Thanks,

    David

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You got the general idea ... THis is what I do:

    Code:
    Function FieldValidations() As Boolean
        On Error GoTo Err_FV
        
        Dim ErrorMsg As String
        Dim AllIsOK As Boolean
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        AllIsOK = True
        If DtReqTxt.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = "The Date Required is missing." & vbCrLf
        ElseIf Not IsDate(DtReqTxt.Value) Then
            AllIsOK = False
            ErrorMsg = "The Date Required is invalid." & vbCrLf
        End If
        If DeliverToTxt.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "Where to deliver has not been selected." & vbCrLf
        End If
        If ItemCount < 1 Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "There must be at least (1)item for this Purchase Request." & vbCrLf
        End If
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        'Validate that each item has a W.O. and a Quantity.
        SQLString = "SELECT * FROM " & ItemsTbl & ";"
        TrgRecSet.Open SQLString, FormsConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            Do While TrgRecSet.EOF = False
                If (TrgRecSet.Fields(4).Value <= 0) Or (TrgRecSet.Fields(12).Value & "" = "") Then
                    AllIsOK = False
                    ErrorMsg = ErrorMsg & "There is at least one missing work order # or quantity not greater than (0) zero."
                    Exit Do
                End If
                TrgRecSet.MoveNext
            Loop
        End If
        TrgRecSet.Close
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        If Not AllIsOK Then MsgBox ErrorMsg, vbCritical, "System Monitor"
        FieldValidations = AllIsOK
        
    Exit_FV:
        Exit Function
        
    Err_FV:
        
        MsgBox Err.Number & ": " & Err.Description
        FieldValidations = False
        Resume Exit_FV
    End Function
    Then I just put a line in like:

    If not fieldvalidations then Exit sub

    Pretty simple huh?

  3. #3
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Thanks for the input!
    I entered the following code in place of the old and I get an error "Compile error: Expected End Sub." Clicking debug highlights the first line. "Private Sub Form_Close()"

    I don't know where to put the Exit Sub Line. Any suggestions?


    Private Sub Form_Close()
    Function FieldValidations() As Boolean
    On Error GoTo Err_FV

    Dim ErrorMsg As String
    Dim AllIsOK As Boolean
    Dim FormsConnect As ADODB.Connection
    Dim TrgRecSet As ADODB.Recordset

    AllIsOK = True
    If Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMoved To.Value & "" = "" Then
    AllIsOK = False
    ErrorMsg = "The Move Date Is Missing." & vbCrLf
    ElseIf Not IsDate(Forms!FrmCowEntry!frmsbCowLocationsEntry!Da teMovedTo.Value) Then
    AllIsOK = False
    ErrorMsg = "The Move Date Is Invalid." & vbCrLf
    End If
    If Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocati on.Value & "" = "" Then
    AllIsOK = False
    ErrorMsg = ErrorMsg & "The Cow Location Is Missing." & vbCrLf
    End If


    If Not AllIsOK Then MsgBox ErrorMsg, vbCritical, "System Monitor"
    FieldValidations = AllIsOK

    Exit_FV:
    Exit Function

    Err_FV:

    MsgBox Err.Number & ": " & Err.Description
    FieldValidations = False
    Resume Exit_FV
    End Function
    If Not FieldValidations Then
    DoCmd.Close

    Exit Sub

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Well...to start...you copied the provided function code directly into the OnClose event of your form and what you really want to do is "Call" the function from the even instead (so that it will work ). Really...you should call the function from the Unload event so that is field validation has failed, you can then cancel the close form process in order to allow the user to fill in the required data. This is how you would make the call:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
       If FieldValidations = False Then 
          MsgBox"Some necessary fields have not been filled in. " & _
          "Please supply all the required data"
          Cancel = True
       End  If
    End Sub
    The Function M Owen has provided should be copied and pasted to the declarations section of your form's code window.
    Last edited by CyberLynx; 02-26-04 at 03:05.

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Thanks for the help, but I haven't quite got it figured out yet.

    This is what I have in the form code:



    Private Sub Form_Unload(Cancel As Integer)
    If FieldValidations = False Then
    MsgBox "Some necessary fields have not been filled in. " & _
    "Please supply all the required data"
    Cancel = True
    End If
    Exit Sub
    End Sub
    -----------------

    When I close the form, I get the expected message,"Some necessary fields have not been filled in. Please supply all the required data" However, I get this no matter which record I am on, including the complete ones. Then, I put this code in the Declarations section at the top as mentioned:

    Option Compare Database

    Dim stDocName As String
    Dim VarItem As Variant
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCowVID As String
    Dim strCowLocation As String
    Dim strDateMovedTo As String
    Dim db As DAO.Database
    Dim ctl As Control
    Dim tdf As DAO.TableDef
    Set db = CurrentDb()

    strDateMovedTo = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMoved To
    strCowLocation = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocati on
    strCowVID = " " & Me.CowVID

    If Trim(strCowVID) = "" And [Trim(strCowLocation) = "" Or Trim(strDateMovedTo) = ""] Then
    MsgBox "You Must Enter A Cow Location And A Cow Move Date To Continue!"
    Else
    End If

    Dim stDocName As String
    Dim VarItem As Variant
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCowVID As String
    Dim strCowLocation As String
    Dim strDateMovedTo As String
    Dim db As DAO.Database
    Dim ctl As Control
    Dim tdf As DAO.TableDef
    Set db = CurrentDb()

    strDateMovedTo = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMoved To
    strCowLocation = " " & Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocati on
    strCowVID = " " & Me.CowVID

    If Trim(strCowVID) = "" And [Trim(strCowLocation) = "" Or Trim(strDateMovedTo) = ""] Then
    MsgBox "You Must Enter A Cow Location And A Cow Move Date To Continue!"
    Else
    End If
    --------------------
    I now get an error that says "The expression On Click you entered as the event property setting produced the following error: Invalid outside procedure."

    Any suggestions?

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    The code you provided within your last post must be contained within a SUB or FUNCTION procedure (I suggest you read up on this) not within the declaration section of your form. You can only declare functions, subs, variables, constants withing the declarations section. Variables dimensionerd in this area of the Form's code window is public (global) to that form only and variables dimensioned within a procedure will be private to the procedure only. You will definately need to do some reading on Visual Basic (VB) or Visual Basic for Applications (VBA) to get the full scope of this.

    Place the code you provided within the Form's Unload event as shown:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
        Dim Whoops as String
        If IsNull(Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMovedTo) then
            Whoops = "You must supply a 'Cow  Move Date' to continue."
        End If
       
        If IsNull(Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocation) Then
            Whoops = "You must supply a 'Cow  Location' to continue."
        End If
     
        If Whoops <> "" then
            MsgBox Whoops
            Cancel = 1
        End If
    End Sub
    You can also place this sort of thing into the Form's BeforeUpdate event as shown:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    
        Dim Whoops as String
        If IsNull(Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMovedTo) Then
            Msgbox = "You must supply a 'Cow  Move Date' to continue."
            Forms!FrmCowEntry!frmsbCowLocationsEntry!DateMovedTo.SetFocus
            Exit Sub
        End If
       
        If IsNull(Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocation) Then
            MsgBox = "You must supply a 'Cow  Location' to continue."
            Forms!FrmCowEntry!frmsbCowLocationsEntry!CowLocation.SetFocus
        End If
    End Sub

    Well....I Hope this helps a little.

  7. #7
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    I can see I need to learn more about the VB aspect of Access. What book(s) do you reccomend?

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    There are dozens of good books out there and (I'm sure) several good network sites as well to learn from. Go to your local book store and browse through some books....select the one(s) that suport your skill level.


Posting Permissions

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