Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form Code Error

Status
Not open for further replies.

xmeb

Technical User
Jun 5, 2013
89
I am getting compile error and syntax error using this code. Anybody know what is wrong with it? Thanks.

Code:
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
 
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
 
This following line of code is in red.

Code:
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.
 

Perhaps:

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


Randy
 
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.

Code:
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
 
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:
Code:
[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! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
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".

Code:
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
 
xmeb . . .

Sorry to get back so late.

Code:
[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! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
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! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
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
 
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! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Okay, thanks. I will put the "1" at the end and try it. Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top