Form Code Error

[xmeb]

I am getting compile error and syntax error using this code. Anybody know what is wrong with it? Thanks.

Private Sub Form_Current()
Dim strWhere As String
Dim lngCount As Long
If Not (IsNull(Me![1RegistrationState]) Or (IsNull(Me![1RegistrationNumber]) Or IsNull(Me!ID)) Then
    strWhere = "[1RegistrationNumber]='" & Me![1RegistrationNumber] & _
"' AND [1RegistrationState] = '" & Me.[1RegistrationState] & "' AND ID<>" & Me!ID
    lngCount = DCount("*", "ParkingTicketsIssuedTable", strWhere)
    If lngCount > 0 Then
        MsgBox lngCount & "  duplicate registration number(s) exist(s) in the table."
    End If
End If
End Sub

 

[Andrzejek]

It woud be very nice of you if you would point at which line of your code the error happens, and what is the error number - description.

Have fun.

---- Andy

 

[xmeb]

This following line of code is in red.

If Not (IsNull(Me![1RegistrationState]) Or (IsNull(Me![1RegistrationNumber]) Or IsNull(Me!ID)) Then

There is no error number - description, a popup just says compile error and syntax error.

Thanks.

 

[randy700]

Perhaps:

If Not [b][red]([/red][/b](IsNull(Me![1RegistrationState]) 
     Or (IsNull(Me![1RegistrationNumber]) 
     Or [b][red]([/red][/b]IsNull(Me!ID)) Then

Randy

 

[xmeb]

The same text is still red and the first line is highlighted in blue.

The following code works but I was told it needed the "if condition" added to it.

Private Sub Form_Current()
Dim strWhere As String
Dim lngCount As Long
If Not (IsNull(Me![1RegistrationNumber]) Or IsNull(Me!ID)) Then
    strWhere = "[1RegistrationNumber]='" & Me![1RegistrationNumber] & _
"' AND [1RegistrationState] = '" & Me.[1RegistrationState] & "' AND ID<>" & Me!ID
    lngCount = DCount("*", "ParkingTicketsIssuedTable", strWhere)
    If lngCount > 0 Then
        MsgBox lngCount & "Other registration number(s) exist."
    End If
End If
End Sub

 

[TheAceMan1]

How are ya xmeb . . .

It looks like your validating data before setting-up a [blue]where clause[/blue] for [blue]DCount[/blue]. For starters you should be using the [blue]AND[/blue] logical operator to insure none are null and then setup for your DCount. Also there's a problem with the prefix numeral [purple]1[/purple] attached to the names [blue][purple]1[/purple]RegistrationState[/blue] and [blue][purple]1[/purple]RegistrationNumber[/blue]. These are tagged as an syntax error and should be corrected. All in all try the following:

[blue]   If Not IsNull(Me!1RegistrationState) And _
      Not IsNull(Me![1RegistrationNumber]) And _
      Not IsNull(Me!ID) Then
      
      strWhere = [purple][b]"[1RegistrationNumber]= '" & Me![1RegistrationNumber] & "' AND " & _
                 "[1RegistrationState] = '" & Me.[1RegistrationState] & "' AND " & _
                 "ID = " & Me!ID[/b][/purple]
       lngCount = DCount("*", "ParkingTicketsIssuedTable", strWhere)
       
       If lngCount > [purple][b]1[/b][/purple] Then
           MsgBox lngCount & "  duplicate registration number(s) exist(s) in the table."
       End If
   End If[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[xmeb]

Thanks.

I tried it and the first three lines are in red and I get the same compile error and syntax error.

Strange because the following works fine but I am told it lacks an "if condition".

Private Sub Form_Current()
Dim strWhere As String
Dim lngCount As Long
If Not (IsNull(Me![1RegistrationNumber]) Or IsNull(Me!ID)) Then
    strWhere = "[1RegistrationNumber]='" & Me![1RegistrationNumber] & _
"' AND [1RegistrationState] = '" & Me.[1RegistrationState] & "' AND ID<>" & Me!ID
    lngCount = DCount("*", "ParkingTicketsIssuedTable", strWhere)
    If lngCount > 0 Then
        MsgBox lngCount & "Other registration number(s) exist."
    End If
End If
End Sub

 

[TheAceMan1]

xmeb . . .

Sorry to get back so late.

[blue]Change: If Not IsNull(Me!1RegistrationState)
To    : If Not IsNull(Me![purple][b][[/b][/purple]1RegistrationState[purple][b]][/b][/purple])[/blue]

The syntax error (in VBA) is due to the prefix numeral one of the control name [blue]1RegistrationState[/blue]. In VBA all control names should start with a letter. I was trying to point this out in my prior post of [blue]6 Nov 13 13:41[/blue] If I'm correct the same rule holds for fieldnames. I'll see what I can find in my library and post it.

Note if you remove the brackets it errors!

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

xmeb . . .

As the 1st rule of the following link shows Visual Basic Naming Rules... Names in VBA [blue]must start with a letter[/blue].

What isn't shown is that if you use a name with a perfix number you have to use braces [blue][][/blue] to get proper syntax. Now I think 1st character as a number works for field names ... but I consider this bad practice and never use a number first. You may want to consider correcting this in your tables and alleviate yourself of this problem.

Your Thoughts? . . .

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Andrzejek]

I would add to it – don’t use Spaces in field names.
Just because Access allows you to use “Last Name”, “First Name” as fields’ names, doesn’t mean you should go for it. I would use “Last_Name” and “First_Name” fields. Life is a lot easier if you don’t HAVE to use [ ] in your Select, Update, Delete, Insert statements, and in your code.


Have fun.

---- Andy

 

[TheAceMan1]

In total agreement with [blue]Andrzejek[/blue].

The end result your after is the readability of your code. If its readable you can [blue]easily parse[/blue] right thru it. If its not readable this is when you have to [blue]decipher[/blue] the code ... and it can get to be some mind boggling stuff!

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[xmeb]

Okay, thanks. I will put the "1" at the end and try it. Thanks again.