vb, simple "IF" statement

[scimatic]

I'm new to vb so bear with me... I have a form that browses table1, table1 has an id field. I want to create an IF statement that will take id the current record displayed in the form and check to see if that id exists in table2. Here is what i've been trying to go with but for some reason Statement2 is always being executed, weather "Me.ID.Value" is in table2 or not.

Private Sub Form_Current()

If IsNull("SELECT table2.ID FROM table2 WHERE table2.ID = '" & Me.ID.Value & "'") Then

Statement1

Else

Statement2

End If

End Sub

 

[bitmite]

dim rst as recordset
set rst = currentdb.openrecordset("SELECT table2.ID FROM table2 WHERE table2.ID = '" & Me.ID.Value & "'",dbopendynaset)

if isnull(rst.fields![ID] then
Statement1
else
statement2
end if

in the references under tools -> references select DOA 3.6

Brought to you By Nedaineum
The Portal to Geek-tum

 

[scimatic]

I'm getting a Run-time error '3464'
Data type mismatch in criteria expression and it points to the line with the SELECT statement. The ID fields in all the tables are the same data type which is number. Any ideas?

Private Sub Form_Current()

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT table2.ID FROM table2 WHERE table2.ID = '" & Me.ID.Value & "'", dbOpenDynaset)

If IsNull(rst.Fields![ID]) Then
statement1
Else
statement1
End If

End Sub

 

[RoyVidar]

Remove the single quotes (they are text delimiters):

[tt]...WHERE table2.ID = " & Me.ID.Value, dbOpenDynaset)[/tt]

Roy-Vidar

 

[scimatic]

That gets rid of the error i was getting but the statement still does not work. The ELSE statement is always executed. Even if I substitute a value that should trigger the IF statement into the SELECT statement for Me.ID.Value:

...WHERE table2.ID = value", dbOpenDynaset)

the if statement is not executed, it's always the ELSE statement that gets executed.

I know the Me.ID.Value gets the correct value I want for the SELECT statement, and I know the IF and ELSE statement execute correctly, because I have tested them independanly. My problem is getting the ID of the current record displayed in the form and checking to see if it exists or not in a different table, (Table2).

Anyone have any suggestions?

 

[willir]

...Another approach

SELECT table2.ID FROM table2 WHERE table2.ID = '" & Me.ID.Value & "'

Dim strQ as String
strQ = Chr$(34)

'If ID is numeric
If Nz(DLookup("ID", "table2", "ID = " & Me.ID), 0) Then
   'Found a value
Else
   'Value not found, or ID is 0
End If

'If ID is a text string
If Len(Nz(DLookup("ID", "table2", "ID = " & strQ & Me.ID & strQ), "")) Then
   'Found a value
Else
   'Value not found, or ID is an empty string
End If

Richard

 

[AccessGuruCarl]

How about one more.

Dim strSQL as String
Dim strReturn as String

strSQL = "SELECT table2.ID FROM table2 WHERE table2.ID = " & Me.ID.Value

DoCmd.RunSQL strSQL

strReturn = strSQL

If (strReturn <> 0 OR strReturn <> "") Then
statement1
Else
statement1
End If


One of these scenerio's should work for you.

Hope it helps...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[TheAceMan1]

How are ya scimatic . . . . .

Try This (you substitute name in [purple]purple[/purple]):

[blue]   Dim Criteria As String, idName As String, tblName As String
   
   idName = "[[purple][b]YourID_FieldName[/b][/purple]]"
   tblName = "[purple][b]Table2Name[/b][/purple]"
   Criteria = idName & " = " & Me(idName)
   
   If IsNull(DLookup(idName, tblName, Criteria)) Then
      [green]'Code if ID doesn't exist[/green]
   Else
      [green]'Code if ID exist[/green]
   End If[/blue]

See Ya! . . . . . .

 

[ALEKSJAY]

You can also use DCOUNT instead of DLOOKUP, and test if it returns a value of 0, but it is the same idea as AceMan posted.
If DCount(idName, tblName, Criteria)=0 Then...