Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013

    Unanswered: Beginner needs help with SQL expression

    Hi all,
    I could not figure out why this code not working? The query works perfectly when i ran it in Access. I don't know whey it is not working here?
    The query result is get the record in NameTbl where NameId eq selected name list id on form.
    Any helps will be appreciated. thanks.

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set dbs = CurrentDb()

    strSQL = "SELECT NameTbl.* from NameTbl where NameTbl.[NameID]=CInt(Me.ChooseListBox.Value)"

    Set rst = dbs.OpenRecordset(strSQL, Type:=dbOpenDynaset)

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you need to understand when to drop in and out of SQL/VBA
    strSQL = "SELECT NameTbl.* from NameTbl where NameTbl.[NameID]=CInt(Me.ChooseListBox.Value)"
    strSQL = "SELECT * from NameTbl where NameID = " & CInt(Me.ChooseListBox.Value)
    there is no need to repeat the table name if its the same as the FROM Table or is uniquely identified within the scope of the query (ie there is only one column of that name int he current query)
    nor is there any need to use square brackets to delimit column or table names, if there are no spaces in your column/table names

    had you actually examined the SQL you would have seen that right away.
    you could have set a breakpoint/watch in the code and stepped through it examining varaiables as required

    or just dumped the SQL out using a msgbox
    msgbox "My SQL is:-" & strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Thank you so much ! it woks fantastic!

Posting Permissions

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