Aaron, please help me with the database

[nicky]

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).]

[Aaron Young]

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).]

[Serge]

It is a lot easier to use DAO:


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 (http://www.icq.com/51055819)



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

[nicky]

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

[nicky]

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

[tpatten]

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(?).

[nicky]

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

[Aaron Young]

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