SQL string fault. Something small i think :s

[vinzz]

hi,

why won't this sql string work in VBA, i tried everything and it seems to be something small, but i don't see the fault
What i try to do is a check if a record already exists with the RefNumber and the companyname.

as you can see is RUit a string and Mednam a number.
this code is placed in the afterupdate on the fields Me.Ref_Uitgever and Me.medium_naam. (this code runs after checking if the fields are filled in)

Anyone knows what i should do? the code stops running at set Lrs so there is something wrong in the sql string i think.

    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim LSQL As String
    Dim RUit As String
    Dim Mednam As Long
    
    RUit = Me.Ref_Uitgever.Value
    Mednam = Me.medium_naam.Column(0)
    Set db = CurrentDb()
    LSQL = "SELECT Hoofdform.medium_naam, Hoofdform.Ref_Uitgever FROM Hoofdform WHERE ((Hoofdform.medium_naam='" & Mednam & "') AND (Hoofdform.Ref_Uitgever='" & RUit & "'));"
    Set Lrs = db.OpenRecordset(LSQL)
...

i also made an other function that can check dubble records but i cant get the id number from it to go to that record :s

Dim zoekdos As Long
If Me.medium_naam.Column(0) <> "" And Ref_Uitgever <> "" Then
zoekdos = DCount("id", "hoofdform", "Ref_Uitgever= forms!hoofdform.Ref_uitgever AND Medium_naam= forms!hoofdform.medium_naam")
Debug.Print zoekdos
If zoekdos > 0 Then
MsgBox "blabla"
End If
End If

i think the first method is the best, anyone knows how to get this alive?
A big thanks in advance for the smart guys here off course

greetz
vinzz

 

[WayneRyan]

vinzz,

... as you can see is RUit a string and Mednam a number.

You were treating them both as strings.

I'd vote for the DCount method as the best.

zoekdos = DCount("id", _
                 "hoofdform", _
                 "[Ref_Uitgever] = " & Me.Ref_uitgever & " AND " & _
                 "[Medium_naam] = '" & Me.medium_naam & "'")

I'm assuming that Ref_uitgever is the number.

Wayne

 

[vinzz]

vinzz,

... as you can see is RUit a string and Mednam a number.

You were treating them both as strings.

I'd vote for the DCount method as the best.

zoekdos = DCount("id", _
                 "hoofdform", _
                 "[Ref_Uitgever] = " & Me.Ref_uitgever & " AND " & _
                 "[Medium_naam] = '" & Me.medium_naam & "'")

I'm assuming that Ref_uitgever is the number.

Wayne

No, Ref number can be a string or a number. this field is setted as text
only medium_naam (combobox) is a number in the first column.

can you get the id with the Dcount function is a record is found?

(my dcount function works, but i cant see where the dubble record is (which id)

 

[vinzz]

nvm. found the fault, i had a ; at the end. deleted that and changed the "" where a number is and it works fine now.

LSQL = "SELECT Hoofdform.medium_naam, Hoofdform.Ref_Uitgever FROM Hoofdform WHERE hoofdform.medium_naam= " & Mednam & " AND Hoofdform.Ref_Uitgever='" & RUit & "'"

now getting the id and i'm done, thanks for the help mate!