Results 1 to 8 of 8

Thread: Aaron, please help me with the database

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 1999
    Posts
    15

    Post

    I am using DAO or the data control for working with the database. I have two text boxes – txtItemNumber and txtDescription and a command button – cmdFind on my form. And, I was trying to search for a part item number, but I got the run-time error – it says that ‘3077’: Syntax error (missing operator) in expression. Please help me...Thank you.

    The user enters a Item Number and clicks on the Fund button to get the Item Number’s record.

    Dim db As Database
    Dim rs As Recordset
    Dim num1 As Integer
    Dim target As String

    Private Sub cmdFind_Click()
    Data1.DatabaseName = App.Path & "\Ampem.mdb"
    Data1.RecordSource = "Select * FROM Inventory"

    num1 = txtItemNumber.Text
    if num1 = “” then
    exit sub
    else
    target = "Item Number = '" & num1
    Data1.Recordset.FindFirst target
    If Data1.Recordset.NoMatch Then
    MsgBox "no found"
    Exit Sub
    Else
    MsgBox "found"
    txtItemNumber.Text = num1
    Exit Sub
    End If
    End if
    End Sub

    [This message has been edited by nicky (edited 12-16-1999).]

  2. #2
    Guru Aaron Young's Avatar
    Join Date
    Jun 1999
    Location
    Red Wing, MN, USA
    Posts
    2,176

    Post

    You have a Table Name which Includes Spaces this is what's causing the secific error you're getting, you need to enclose the Table Name in Square Braces, eg.

    target = "[Item Number] = '" & num1

    You're also missing the Enclosing Apostrophe on the same line, it should be..

    target = "[Item Number] = '" & num1 & "'"

    ------------------
    Aaron Young
    Analyst Programmer
    aarony@redwingsoftware.com
    adyoung@win.bright.net


    [This message has been edited by Aaron Young (edited 12-16-1999).]

  3. #3
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Post

    It is a lot easier to use DAO:

    Code:
        Dim db As Database
        Dim rs As Recordset
        Dim num1 As Integer
        
        
        Set db = Workspaces(0).OpenDatabase(App.Path & "\Ampem.mdb")
        num1 = Val(txtItemNumber.Text)
        If num1 = 0 Then  '  "" Then
            Exit Sub
        End If
        Set rs = db.OpenRecordset("Select * FROM Inventory Where [Item Number] ='" & num1 & "'", dbOpenSnapshot)
        If rs.EOF Then
            MsgBox "Item NOT Found."
        Else
            MsgBox "Item Found."
        End If
    ------------------

    Serge

    Software Developer
    Serge_Dymkov@vertexinc.com
    Access8484@aol.com
    ICQ#: 51055819



    [This message has been edited by Serge (edited 12-16-1999).]

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 1999
    Posts
    15

    Post

    It runs perfectly! Thanks, Aaron!!! I really appreciate that! Again, thank you.

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 1999
    Posts
    15

    Post

    Aaron and Serge, thank you all very much for helping me on this problem. But, I have a several questions. I now am using DAO and Access MDB for working with the database. Before that, I used a flat file ( Random Access File) and the data control to work with the database. Which one is more easier to use? DAO, the Data Control, ADO, or a flat file?

    As I said earlier, I have two text boxes and a command button (cmdAdd for now) on my form. When I click on the Add button, I enter a item number for an existing item number. A message box should display informing me that the item number already exists. But, I got the run-time error “Data Type mistake in criteria expression” I just want to test if there is an existing item number or not. If found, it cancels everything what I type (not adding data to the database when I unload the form) Do you know what I mean? If you don’t know what I mean, please let me know and I will rephrase for you all. Please help… Thank you.

    Dim db As Database
    Dim rs As Recordset
    Dim bEdit As Boolean
    Dim bAddNew As Boolean
    Dim bFirstSearch As Boolean

    Private Sub Form_Activate()
    rs.MoveLast
    Label3.Caption = "There are " & rs.RecordCount & " records in the file."
    End Sub

    Private Sub Form_Load()
    Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\Ampem.mdb")
    Set rs = db.OpenRecordset("Inventory", dbOpenDynaset)
    bEdit = False
    bAddNew = False
    End Sub

    Private Sub cmdAdd_Click()
    bEdit = True
    bAddNew = True
    ' txtItemNumber.Text = ""
    ' txtDescription.Text = ""
    Call SearchData
    End Sub

    Private Sub cmdQuit_Click()
    bEdit = False
    bAddNew = False
    Unload Me
    End Sub

    Private Sub ShowData()
    txtItemNumber.Text = Val(rs!ItemNumber)
    txtDescription.Text = rs!Description '& ""
    txtItemNumber.DataChanged = False
    txtDescription.DataChanged = False
    End Sub

    Private Sub SaveData()

    'SearchData

    If bAddNew Then
    rs.AddNew
    Else
    rs.Edit
    End If

    With rs
    !ItemNumber = Val(txtItemNumber.Text)
    !Description = txtDescription.Text
    .Update
    End With

    bEdit = False
    bAddNew = False
    cmdAdd.Visible = False
    End Sub

    Private Sub SearchData()
    Dim inum1 As Integer
    Dim target As String

    inum1 = Val(txtItemNumber.Text)
    target = "[ItemNumber] = '" & inum1 & "'"

    With rs
    ' If bFirstSearch Then
    rs.FindFirst target
    'Else
    'rs.FindNext target
    'End If

    If rs.NoMatch Then
    MsgBox "Not found"
    Else
    MsgBox "found"
    ' bEdit = False
    ' bAddNew = False
    ' Exit Sub
    End If
    End With
    'bFirstSearch = False
    'Call SaveData
    End Sub

  6. #6
    Member
    Join Date
    Nov 1999
    Location
    Dover, NH
    Posts
    54

    Post

    Just jumping in here...

    Did you change your field "Item Number" to be "ItemNumber"? In your first example it has a space. In the next, it doesn't. This may be the source of your problem(?).

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 1999
    Posts
    15

    Post

    Yes, I changed my field "Item Number" to
    "ItemNumber" Please help with my current code. Thank you...

  8. #8
    Guru Aaron Young's Avatar
    Join Date
    Jun 1999
    Location
    Red Wing, MN, USA
    Posts
    2,176

    Post

    The Value you are searching on is Numeric, (Integer), yet you are enclosing it in Apostrophes, indicating it's a String, giving you the Error, change..

    inum1 = Val(txtItemNumber.Text)
    target = "[ItemNumber] = '" & inum1 & "'"


    To..

    inum1 = Val(txtItemNumber.Text)
    target = "ItemNumber = " & inum1



    ------------------
    Aaron Young
    Analyst Programmer
    aarony@redwingsoftware.com
    adyoung@win.bright.net

Posting Permissions

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



Click Here to Expand Forum to Full Width