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

    Unanswered: Converting a Dcount VBA to a Macro

    I need to convert one of my forms from VBA on update event to a macro so that it is compatible with the web, but I am having problems with the dcount.

    I need to on update check if the text and numbers entered into text box alaready exisits in a table works for the vba but not within the macro

    Please find enclosed a snap shot of what I am trying to achieve

    Thank you
    Attached Thumbnails Attached Thumbnails Untitled.png  

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    I was not able to view your image. Can you post the vba?

  3. #3
    Join Date
    Feb 2013
    Posts
    99

    VBA Code

    If DCount("*", "ParkingViolationstbl", "[VIN]= '" & Me![VIN] & "'") <= -1 Then
    MsgBox "Vehcile Does Not exist In database", vbOKOnly

    ElseIf DCount("*", "ParkingViolationstbl", "[VIN]= '" & Me![VIN] & "'") > 0 Then
    DoCmd.Openform "Vehcile Warning and Notice Records", acFormDS, , "[VIN] = '" & Me.VIN & "'", acFormReadOnly
    End If

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you ever seen the first message box? I can't imagine it's possible for a DCount() to return a negative number. In any case, in a macro you'd have to use the full form reference rather than Me. You also don't need the second DCount(). Test for > 0 and open the form, else the message box.
    Paul

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    From what form does this code run and or what event or command button? More details Please.

  6. #6
    Join Date
    Feb 2013
    Posts
    99

    Changes

    Sorry copied the wrong verion


    If DCount("*", "ParkingViolationstbl", "[VIN]= '" & Me![VIN] & "'") = 0 Then
    MsgBox "Vehcile Does Not exist In database", vbOKOnly

    ElseIf DCount("*", "ParkingViolationstbl", "[VIN]= '" & Me![VIN] & "'") >= 1 Then
    DoCmd.Openform "Vehcile Warning and Notice Records", acFormDS, , "[VIN] = '" & Me.VIN & "'", acFormReadOnly
    End If

  7. #7
    Join Date
    Feb 2013
    Posts
    99
    Would this be the correct Syntax

    If DCount("*", "ParkingViolationstbl", "[VIN]= '" & FORMS![WarningsWeb]![VIN]& "'") =0 Then
    MsgBox "Vehcile Does Not exist In database", vbOKOnly

    Thank you

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It would be in VBA, not positive about macros as I don't use them. If that doesn't work there, try

    DCount("*", "ParkingViolationstbl", "[VIN]= FORMS![WarningsWeb]![VIN])
    Paul

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Like Paul, and most of us here, never use Embedded Macros, but took the time to experiment. See Macro1 in the attached file.

    Note that you didn't say what you wanted to do if the VIN wasn't found, other than popping a Messagebox, so I simply returned Focus to the VIN Control. You may want to change that, but what I posted should give you most of what you need.

    As far as the DCount() syntax goes, in the Macro, it would be

    DCount("*","ParkingViolationstbl","[VIN]= '" & [VIN] & "'")=0

    This assumes that the [VIN] Field is defined as Text, as it apparently was in your original VBA code.

    Linq ;0)>
    Attached Files Attached Files
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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