Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    13

    Unanswered: Access 2007: Trouble w/ VBA code for Message Boxes

    Hi,
    I am having trouble figuring out how to do the following:
    This is for a study. A patient is suppossed to have each visit in a specific order. So if the database user is entering information on form3 and there is no record of form2 or form1 in the database we want a message to pop up that warns them there is no record of the visit. So basically The code should be like:
    If form3 entered and form1 and form2 NOT entered then message: Form1 and Form2 not entered
    Else
    If form3 entered and Form1 NOT entered but form2 IS entered message: Form1 not entered
    Else
    If form3 entered and Form1 IS entered but form2 IS NOT entered message: Form2 not entered

    There is already code for a message popping up if Form1 isn't filled out, but I can't figure out how to add code for a message to pop up if data from a second form is missing as well...Any suggestions? I have tried a number of different ways ..and it's it's a little difficult to recount every way I have tried, but i understand that it is helpful to know what i have already done, so if needed i can try to give more specifics! Your help is very much appreciated in advance!!

    The following is code that goes in the Form3 "Before update" event: (FormCompleted is the code for the pop up message)

    Code:
    'pop up message if db user fills out form3 without having entered
    'records of form1
    FormCompleted    
        
    'error handling
    errhandler:
    If Err.Number > 0 Then
         If Err.Number = 3021 Or Err.Number = 2101 Or Err.Number = 2110 Or Err.Number = 2169 Then
           ' do nothing
          Else
            MsgBox Err.Num & " " & Err.Description
          End If
    End If
    End Sub
    The FormCompleted code is this:
    Code:
    Private Function FormCompleted() As Boolean
    'pop up message if db user fills out Form3 without having entered
    'records of Form1
    
        Dim sql As String
        Dim rs As DAO.Recordset
        Dim f As Boolean
        Dim msgWarning As Variant
        
        sql = "SELECT tblForm1.ClientUID FROM tblForm1 AND tblForm1.ClientUID = " & Me.ClientUID & ""
        
        Set rs = CurrentDb().OpenRecordset(sql)
        
        f = (Not (rs.BOF And rs.EOF))
        
        rs.Close
        
        Set rs = Nothing
        
        FormCompleted = f
        
        If f = False Then
            msgWarning = MsgBox("Warning: You have completed Form 3 before Form 1." & vbCrLf & vbCrLf & "Make sure to complete Form 1 and enter records of these into the database.", vbCritical, "Form 1 not Completed")
        End If
        
    End Function
    (p.s. this was code already made before..I am pretty new to VBA, which is why i am having trouble!)

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Wholy moley, each form has a different table? I would be surprised if this code works well and is really running around the bush. Would I be correct if I said that *any* record in the tblForm1 table with the ClientUID field set to the Me.ClientUID control on the current form would indicate completion of Form1?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Jul 2009
    Posts
    13
    Hi, thanks for your response.

    Would I be correct if I said that *any* record in the tblForm1 table with the ClientUID field set to the Me.ClientUID control on the current form would indicate completion of Form1?
    Yes, that is the case. So if there is client ID in table Form1 that matches the current client ID for the Form being entered (Form2 or Form3) this indicates that Form1 has been completed.

    Thanks!

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    So all it would take is a DCount() in the right place to answer your question, right?
    General: DLookup Usage Samples
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Jul 2009
    Posts
    13
    Thanks for your reply, the Dcount/Dlook may help to figure out better type of code for this:
    Code:
    sql = "SELECT tblForm1.ClientUID FROM tblForm1 AND tblForm1.ClientUID = " & Me.ClientUID & ""
    ?
    But I am not sure if that is my main problem. I was able to come up with different combinations of the above code in order select if form2 had been filled out, etc. I think my problem is that I am missing some "If not this then check this" or "Go to next steps"
    For example, I tried putting this code underneath the original code starting with a different SQL combo, but with all of the other same code, like so:

    Code:
    Private Function FormCompleted() As Boolean
    'pop up message if db user fills out Form3 without having entered
    'records of Form1

    Dim sql As String
    Dim rs As DAO.Recordset
    Dim f As Boolean
    Dim msgWarning As Variant

    sql = "SELECT tblForm1.ClientUID FROM tblForm1 AND tblForm1.ClientUID = " & Me.ClientUID & ""

    Set rs = CurrentDb().OpenRecordset(sql)

    f = (Not (rs.BOF And rs.EOF))

    rs.Close

    Set rs = Nothing

    FormCompleted = f

    If f = False Then
    msgWarning = MsgBox("Warning: You have completed Form 3 before Form 1." & vbCrLf & vbCrLf & "Make sure to complete Form 1 and enter records of these into the database.", vbCritical, "Form 1 not Completed")
    End If

    Else
    'check if form 2 is completed
    sql = "SELECT tblForm2.ClientUID FROM tblForm2 AND tblForm2.ClientUID = " & Me.ClientUID & ""

    Set rs = CurrentDb().OpenRecordset(sql)

    f = (Not (rs.BOF And rs.EOF))

    rs.Close

    Set rs = Nothing

    FormCompleted = f

    If f = False Then
    msgWarning = MsgBox("Warning: You have completed Form 3 before Form 2." & vbCrLf & vbCrLf & "Make sure to complete Form 2 and enter records of these into the database.", vbCritical, "Form 2 not Completed")
    End If


    When i set it up like this I get a run time error 438, in which the error is at: MsgBox Err.Num & " " & Err.Description. (In the first part of the code in the first

    I also tried coming up with 3 different functions and codes for each like "CompleteALl" "CompleteForm2NotForm1" "CompleteForm1NotForm2" and than added those to the before update event code...but still get the same 438 runtime error.

    Sorry for not explaining this earlier, and thankyou for your patience!

  6. #6
    Join Date
    Dec 2010
    Posts
    31
    First of all, your query is not correct. You do not have a where clause. Change it to this:

    sql = "SELECT tblForm1.ClientUID FROM tblForm1 WHERE tblForm1.ClientUID = " & Me.ClientUID & ""

    Do the same for table 2 and you should have your answer.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your database does not seem to be normalized, you should have a look at: The Relational Data Model, Normalisation and effective Database Design
    Have a nice day!

  8. #8
    Join Date
    Jul 2009
    Posts
    13
    Thank you both for your help.

    @ RickyTicky: Yeah, I figured that out eventually, and as you said it solved the problem!

    @ Sinndho: Thanks for the info, I am not sure. I didn't build the database but I will check out the below link. I like to learn about these things anyway.

    Thanks again.

Posting Permissions

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