Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Exclamation Unanswered: Open form from a combo

    Hi,

    I am trying to open the same form (without Record Source) to edit record from a combo (txtSrch) as per ColourID (Unbound) but it replies an following error and debugs to following bold line. Please guide what I missed:

    Error:
    Run-time error '3070'
    The Microsoft Jet Engine does not recognize 'BLk' as a valid field name or expression.

    where 'BlK' = selected ColourId from combo

    =============
    Code:
    Private Sub txtSrch_AfterUpdate()

    Dim D As Database
    Dim rsCust As Recordset
    Dim Criteria As String
    Set D = CurrentDb

    Set rsCust = D.OpenRecordset("tblColour", DB_OPEN_DYNASET)
    Criteria = "[ColourID] = " & [txtSrch]
    rsCust.FindFirst Criteria

    Me!ColourID = rsCust("ColourID")
    Me!ColourName = rsCust("ColourName")
    rsCust.Close
    Me.ColourID.SetFocus
    End Sub

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    Criteria = "[ColourID] = '" & [txtSrch] & "'"
    Observe the added single and double quotes.

    Sam

  3. #3
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    thanks Sam,

    It worked fine.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're very welcome,

    Sam

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Just so that you understand, the next time something like this comes up,

    "[ColourID] = " & [txtSrch]

    would be the correct syntax, if [ColourID] were defined as a Number Field!

    Sam's suggestion of

    "[ColourID] = '" & [txtSrch] & "'"

    works because [ColourID], in this case, is defined as a Text Field!

    And since we're on the subject, might as well add that when dealing with Date/Time Fields the syntax would be

    "[DateInTable]= " & "#" & DateInForm & "#"

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    Great Missingling,

    All appreciated.

    thanks
    Anuj

Posting Permissions

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