Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: problems with checking if records exist

    Evening All

    Having a probelm with the following code

    I am trying to check if a form exists in a table and if not provide a message box otherwise if it does exist then it opens the correct form based on the criteria heres the code

    Private Sub Submit_Click()
    If IsNull(Me.Serial_Number) = True Or IsNull(Me.Log_Number) Or IsNull(Me.Type_of_Incident) = True Then
    MsgBox "Feilds cannot be Left Blank"

    Exit Sub
    End If

    If DCount("*", Me![Type of Incident], "[Serial Number] = " & Me![Serial Number] & " AND [Log Number] = " & Me![Log Number]) > 0 Then

    MsgBox "Incident does not exist Please check you inputted Data and try again", , "Report Does Not Exist"

    DoCmd.OpenForm Me.Type_of_Incident, , , "[Serial Number]='" & Me![Serial Number] & "' and [Log Number]='" & Me![Log Number] & "'"

    DoCmd.Close acForm, "continue report", acSaveNo
    DoCmd.Close acForm, "Selection", acSaveNo
    'End If
    End Sub

    Opening of the form works correctly but cannot get it to work with checking the record it just opens a blank form

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    If DCount("*", Me![Type of Incident], "[Serial Number] = " & Me![Serial Number] & " AND [Log Number] = " & Me![Log Number]) > 0 Then


    First off, your syntax is incorrect for DCount. The second argument should be the name of the Domain, i.e. the Table or Query, that holds the data in question.

    Me![Type of Incident]

    would appear to be the name of a Control on the current Form. You later use it as the name of a Form! What, exactly, is Type of Incident?

    Your logic also looks suspicious. If DCount returns a Value that is greater than 0 , it means that it found a Record that has a matching [Serial Number] and a matching [Log Number], which to me would mean that the incident does exist, not that it 'does not exist,' as your messagebox

    MsgBox "Incident does not exist...

    would seem to indicate.

    Linq ;0)>
    Last edited by Missinglinq; 06-13-13 at 20:20.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2013
    Posts
    99

    Thanks for the reply but still confused

    Me![Type of Incident] is with in a drop down menu in a form with three option Incident , Intruder Alarm and Fire Alarm.

    There are then 3 tables with the same naming convention, So I thought that it would perform the dcount using the information in the dropdown box the same way it loads the form.

    With respect to the dcount Value I persume it would be < 1 to indecate that if there is less than 1 record then msgbox

    Thanks for your time

  4. #4
    Join Date
    Feb 2013
    Posts
    99

    Not getting any where

    Looking at another posts online made the following alterations now I get a message box but even if the record does exist ????

    Any help would be much appricated

    Thank you

    Private Sub Submit_Click()
    If IsNull(Me.Serial_Number) = True Or IsNull(Me.Log_Number) Or IsNull(Me.Type_of_Incident) = True Then
    MsgBox "Feilds cannot be Left Blank"

    Exit Sub
    End If

    If DCount("*", Me![Type of Incident], "[Serial Number] = """ & Me![Serial Number] & """") = 0 Then GoTo Error1

    Error1:
    MsgBox "Incident does not exist Please check you inputted Data and try again", , "Report Does Not Exist"
    Exit Sub


    DoCmd.OpenForm Me.Type_of_Incident, , , "[Serial Number]='" & Me![Serial Number] & "' and [Log Number]='" & Me![Log Number] & "'"

    DoCmd.Close acForm, "continue report", acSaveNo
    DoCmd.Close acForm, "Selection", acSaveNo

    End Sub

  5. #5
    Join Date
    Feb 2013
    Posts
    99

    Sorted

    Got it working

    Private Sub Submit_Click()
    If IsNull(Me.Serial_Number) = True Or IsNull(Me.Log_Number) Or IsNull(Me.Type_of_Incident) = True Then
    MsgBox "Feilds cannot be Left Blank"

    Exit Sub
    End If


    If DCount("*", Me![Type of Incident], "[Serial Number] = """ & Me![Serial Number] & """") = 1 Then GoTo openform
    If DCount("*", Me![Type of Incident], "[Serial Number] = """ & Me![Serial Number] & """") = 0 Then GoTo Error1


    Error1:
    MsgBox "Incident does not exist Please check you inputted Data and try again", , "Report Does Not Exist"
    Exit Sub

    openform:
    DoCmd.openform Me.Type_of_Incident, , , "[Serial Number]='" & Me![Serial Number] & "' and [Log Number]='" & Me![Log Number] & "'"

    DoCmd.Close acForm, "continue report", acSaveNo
    DoCmd.Close acForm, "Selection", acSaveNo

    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the column is text/string column then literal values MUST be quoted
    eg:-
    Code:
    strWhereClause = " where MyStringColumn = 'abcdefgh'"
    or
    Code:
    strWhereClause = " where MyStringColumn = '" & avariable & "'"
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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