[RESOLVED] Working Searchable DB Now!

[Quizton]

VBCode----------------------------
Private Sub txtSearch_Change()
If txtSearch.Text = vbNullString Then
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
Else
Set rs = db.OpenRecordset("SELECT * FROM EMPDATA WHERE ID LIKE'" & txtSearch.Text & "'" & "& '*'")
End If

End Sub

everything else seems to work fine If it will help I can paste the rest of code ?

[Hack]

What is "EMPDATA"?

[Quizton]

it is my table name

[si_the_geek]

You have got the last part of the Like query wrong I'm afraid (I don't think you can join like that in SQL), you should really have one ' at each end of the string, eg:

Set rs = db.OpenRecordset("SELECT * FROM EMPDATA WHERE ID LIKE '" & txtSearch.Text & "*'")

If that doesn't work, just use % as a wildcard character at the end instead of *

[Hack]

it is my table name

Quizton, I believe I already explained that you cant create a recordset using a table. You have to use a query to the table and collect a set, or subset of records within that table.

Try si_the_geek's code...

[Quizton]

vbcode------
Private Sub txtSearch_Change()
If txtSearch.Text = vbNullString Then
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
Else
Set rs = db.OpenRecordset("SELECT * FROM EMPDATA WHERE ID LIKE '" & txtSearch.Text & "*'")
End If

End Sub


Hmm...still no go ?
Do I have to somehow update what i just did at the end?

[Hack]

What happens when you run the code?

[Quizton]

Nothing happens, Should i change it to a search button instead of just entering it in text?

[Hack]

The Change event will not fire unless something "changes" in the control. So, in order for the code to even execute, you would either have to type something, or paste something, into the textbox.

Click events fire code on mouse clicks, so yes, I would suggest you move your code to an event of a control that could be executed by a user.

Also, you still have (unless you are pasting old code) a table name in an openrecordset which will never work.

[Quizton]

I will change the command to the cmdSearch and see what happens.

I am curious to know why the EMPDATA name for a table will not work though?
should I change it , I created the table from scratch

[Hack]

I will change the command to the cmdSearch and see what happens.

I am curious to know why the EMPDATA name for a table will not work though?
should I change it , I created the table from scratch

You can't create a recordset using a table name!!!!

I'm not sure how much more clear on this I can be.

If you want data from your EMPDATA table, you have to write a query to get it.

Dim sSQL As String
sSQL = "SELECT * from EMPDATA "
Set rs = db.OpenRecordset(sSQL)

Now, you have a recordset that can be used/manipulated.

[Quizton]

Great Ill throw that is there and see how it works out, and thanks for all your info

[Quizton]

Still no go here it what ive changed up now
vb code---------
Private Sub cmdSearch_Click()
If txtSearch.Text = vbNullString Then
sSQL = "SELECT * from EMPDATA "
Set rs = db.OpenRecordset(sSQL)
Else
Set rs = db.OpenRecordset("SELECT * FROM EMPDATA WHERE ID LIKE '" & txtSearch.Text & "*'")
End If

End Sub

[Quizton]

WoooHOOOO
Figured it all out thanks again for all the help. Below is a complete searchable addable , editable , ect.... Amazing thing that I figured it out after coming home from a night at the bar!!
thanks to you all you guys/gals are really a helpfull bunch!!

[Quizton]

Fully Functional APP all debugged and ready to go
Note: Employee Project but can do a whole lot more with this and a immagination!!
I wish I seen this code a week ago lol VB 6.0

Option Explicit
Private db As Database
Private rs As DAO.Recordset
Dim sSQL As String
Private ws As Workspace
Private max As Long
Private i As Long
Private errormsg
Private dbadd As Boolean
Private dbedit As Boolean
 
 
Private Sub cmdend_Click()
db.Close
End
End Sub
 
Private Sub Form_Load()
sSQL = "SELECT * from EMPDATA "
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(App.Path & "\EmployeeData.mdb")
Set rs = db.OpenRecordset(sSQL)
list
End Sub
 
Private Function list()
    If rs.RecordCount = 0 Then
    errormsg = MsgBox("No Records Found", , "Error")
    If Len(txtSearch.Text) > 0 Then
        txtSearch.Text = Mid(txtSearch.Text, 1, Len(txtSearch.Text) - 1)
    Else
        Exit Function
    End If
End If
rs.MoveLast
rs.MoveFirst
max = rs.RecordCount
rs.MoveFirst
listData.Clear
For i = 1 To max
    listData.AddItem rs("FirstName") & rs("LastName")
    rs.MoveNext
    Next i
End Function
 
Private Sub listData_Click()
Set rs = db.OpenRecordset("Select * from EMPDATA where FirstName &  LastName = '" & Trim(listData.list(listData.ListIndex)) & "'")
rs.MoveFirst
txtFirstName.Text = rs("FirstName")
txtLastName.Text = rs("LastName")
txtSocialSecurity.Text = rs("SocialSecurity")
txtPhone.Text = rs("Phone")
txtStreet.Text = rs("Street")
txtCity.Text = rs("City")
txtState.Text = rs("State")
txtZip.Text = rs("Zip")
txtID.Text = rs("ID")
txtRate.Text = rs("Rate")
txtGender.Text = rs("Gender")
txtDateofBirth.Text = rs("DateofBirth")
txtEmployed.Text = rs("Employed")
cmdEdit.Enabled = True
cmdDelete.Enabled = True
End Sub
 
Private Sub cmdDelete_Click()
errormsg = MsgBox("Are You Sure You Want To Delete This Record", vbYesNo, "Delete Record")
If errormsg = vbYes Then
rs.Delete
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
list
txtFirstName.Text = vbNullString
txtFirstName.Enabled = False
txtLastName.Text = vbNullString
txtLastName.Enabled = False
txtSocialSecurity.Text = vbNullString
txtSocialSecurity.Text = False
txtPhone.Text = vbNullString
txtPhone.Enabled = False
txtStreet.Text = vbNullString
txtStreet.Text = False
txtCity.Text = vbNullString
txtCity.Text = False
txtState.Text = vbNullString
txtState.Text = False
txtZip.Text = vbNullString
txtZip.Text = False
txtID.Text = vbNullString
txtID.Text = False
txtRate.Text = vbNullString
txtRate.Text = False
txtGender.Text = vbNullString
txtGender.Text = False
txtDateofBirth.Text = vbNullString
txtDateofBirth.Text = False
txtEmployed.Text = vbNullString
txtEmployed.Text = False
txtSearch.Text = vbNullString
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdadd.Enabled = True
Else
Exit Sub
End If
End Sub
 
 
Private Sub cmdAdd_Click()
txtFirstName.Text = vbNullString
txtLastName.Text = vbNullString
txtSocialSecurity.Text = vbNullString
txtPhone.Text = vbNullString
txtStreet.Text = vbNullString
txtCity.Text = vbNullString
txtState.Text = vbNullString
txtZip.Text = vbNullString
txtID.Text = vbNullString
txtRate.Text = vbNullString
txtGender.Text = vbNullString
txtDateofBirth.Text = vbNullString
txtEmployed.Text = vbNullString
txtFirstName.Enabled = True
txtLastName.Enabled = True
txtSocialSecurity.Enabled = True
txtPhone.Enabled = True
txtStreet.Enabled = True
txtCity.Enabled = True
txtState.Enabled = True
txtZip.Enabled = True
txtID.Enabled = True
txtRate.Enabled = True
txtGender.Enabled = True
txtDateofBirth.Enabled = True
txtEmployed.Enabled = True
cmdadd.Enabled = False
cmdDelete.Enabled = False
cmdEdit.Enabled = False
cmdSave.Enabled = True
cmdCancel.Enabled = True
dbadd = True
 
End Sub
 
Private Sub cmdsave_Click()
If dbadd = True Then
    Call add
ElseIf dbedit = True Then
    Call edit
End If
End Sub
 
Public Function add()
If txtFirstName.Text = vbNullString Or _
txtLastName.Text = vbNullString Or _
txtSocialSecurity.Text = vbNullString Or _
txtPhone.Text = vbNullString Or _
txtStreet.Text = vbNullString Or _
txtCity.Text = vbNullString Or _
txtState.Text = vbNullString Or _
txtZip.Text = vbNullString Or _
txtID.Text = vbNullString Or _
txtRate.Text = vbNullString Or _
txtGender.Text = vbNullString Or _
txtDateofBirth.Text = vbNullString Or _
txtEmployed.Text = vbNullString Then
    errormsg = MsgBox("All Fields Must Contain Data", vbCritical, "Error")
    Exit Function
End If
rs.AddNew
rs("FirstName") = txtFirstName.Text
rs("LastName") = txtLastName.Text
rs("SocialSecurity") = txtSocialSecurity.Text
rs("Phone") = txtPhone.Text
rs("Street") = txtStreet.Text
rs("City") = txtCity.Text
rs("State") = txtState.Text
rs("Zip") = txtZip.Text
rs("ID") = txtID.Text
rs("Rate") = txtRate.Text
rs("Gender") = txtGender.Text
rs("DateofBirth") = txtGender.Text
rs("Employed") = txtEmployed.Text
rs.Update
txtFirstName.Text = vbNullString
txtFirstName.Enabled = False
txtLastName.Text = vbNullString
txtLastName.Enabled = False
txtSocialSecurity.Text = vbNullString
txtSocialSecurity.Enabled = False
txtPhone.Text = vbNullString
txtPhone.Enabled = False
txtStreet.Text = vbNullString
txtStreet.Enabled = False
txtCity.Text = vbNullString
txtCity.Enabled = False
txtState.Text = vbNullString
txtState.Enabled = False
txtZip.Text = vbNullString
txtZip.Enabled = False
txtID.Text = vbNullString
txtID.Enabled = False
txtRate.Text = vbNullString
txtRate.Enabled = False
txtGender.Text = vbNullString
txtGender.Enabled = False
txtDateofBirth.Text = vbNullString
txtDateofBirth.Enabled = False
txtEmployed.Text = vbNullString
txtEmployed.Enabled = False
txtSearch.Text = vbNullString
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdadd.Enabled = True
list
 
End Function
 
Private Sub cmdEdit_Click()
txtFirstName.Enabled = True
txtLastName.Enabled = True
txtSocialSecurity.Enabled = True
txtPhone.Enabled = True
txtStreet.Enabled = True
txtCity.Enabled = True
txtState.Enabled = True
txtZip.Enabled = True
txtID.Enabled = True
txtRate.Enabled = True
txtGender.Enabled = True
txtDateofBirth.Enabled = True
txtEmployed.Enabled = True
cmdadd.Enabled = False
cmdDelete.Enabled = False
cmdEdit.Enabled = False
cmdSave.Enabled = True
cmdCancel.Enabled = True
dbedit = True
 
End Sub
 
Public Function edit()
If txtFirstName.Text = vbNullString Or _
txtLastName.Text = vbNullString Or _
txtSocialSecurity.Text = vbNullString Or _
txtPhone.Text = vbNullString Or _
txtStreet.Text = vbNullString Or _
txtCity.Text = vbNullString Or _
txtState.Text = vbNullString Or _
txtZip.Text = vbNullString Or _
txtID.Text = vbNullString Or _
txtRate.Text = vbNullString Or _
txtGender.Text = vbNullString Or _
txtDateofBirth.Text = vbNullString Or _
txtEmployed.Text = vbNullString Then
    errormsg = MsgBox("All Fields Must Contain Data", vbCritical, "Error")
    Exit Function
End If
rs.edit
rs("FirstName") = txtFirstName.Text
rs("LastName") = txtLastName.Text
rs("SocialSecurity") = txtSocialSecurity.Text
rs("Phone") = txtPhone.Text
rs("Street") = txtStreet.Text
rs("City") = txtCity.Text
rs("State") = txtState.Text
rs("Zip") = txtZip.Text
rs("ID") = txtID.Text
rs("Rate") = txtRate.Text
rs("Gender") = txtGender.Text
rs("DateofBirth") = txtDateofBirth.Text
rs("Employed") = txtEmployed.Text
rs.Update
txtFirstName.Text = vbNullString
txtFirstName.Enabled = False
txtLastName.Text = vbNullString
txtLastName.Enabled = False
txtSocialSecurity.Text = vbNullString
txtSocialSecurity.Enabled = False
txtPhone.Text = vbNullString
txtPhone.Enabled = False
txtStreet.Enabled = False
txtCity.Text = vbNullString
txtCity.Enabled = False
txtState.Text = vbNullString
txtState.Enabled = False
txtZip.Text = vbNullString
txtZip.Enabled = False
txtID.Text = vbNullString
txtID.Enabled = False
txtRate.Text = vbNullString
txtRate.Enabled = False
txtGender.Text = vbNullString
txtGender.Enabled = False
txtDateofBirth.Text = vbNullString
txtDateofBirth.Enabled = False
txtEmployed.Text = vbNullString
txtEmployed.Enabled = False
txtSearch.Text = vbNullString
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdadd.Enabled = True
cmdEnd.Enabled = True
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
list
End Function
 
Private Sub cmdcancel_Click()
txtFirstName.Text = vbNullString
txtFirstName.Enabled = False
txtLastName.Text = vbNullString
txtLastName.Enabled = False
txtSocialSecurity.Text = vbNullString
txtSocialSecurity.Enabled = False
txtPhone.Text = vbNullString
txtPhone.Enabled = False
txtStreet.Enabled = False
txtCity.Text = vbNullString
txtCity.Enabled = False
txtState.Text = vbNullString
txtState.Enabled = False
txtZip.Text = vbNullString
txtZip.Enabled = False
txtID.Text = vbNullString
txtID.Enabled = False
txtRate.Text = vbNullString
txtRate.Enabled = False
txtGender.Text = vbNullString
txtGender.Enabled = False
txtDateofBirth.Text = vbNullString
txtDateofBirth.Enabled = False
txtEmployed.Text = vbNullString
txtEmployed.Enabled = False
txtSearch.Text = vbNullString
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdadd.Enabled = True
cmdEnd.Enabled = True
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
list
End Sub
 
Private Sub cmdSearch_Click()
If txtSearch.Text = vbNullString Then
sSQL = "SELECT * from EMPDATA "
Set rs = db.OpenRecordset(sSQL)
Else
    Set rs = db.OpenRecordset("SELECT * FROM EMPDATA WHERE ID LIKE '" & txtSearch.Text & "*'")
End If
list
End Sub

[si_the_geek]

I'm glad you worked it out It's surprising how a night drinking can help occasionally (as it usually does the opposite!!).

One thing I notice is that you have a few bits of repeated code, which could be put into Subs instead. That way you if things change you don't need to change code in several places, and the other code is much easier to read (as there is far less of it!).

For example, in cmdAdd/cmdDelete/etc you have code like this:

txtFirstName.Text = vbNullString
...
...
txtEmployed.Text = vbNullString

and like this:

txtFirstName.Enabled = True  '(or false, but all the same)
...
...
txtEmployed.Enabled = True

These can both be replaced (everywhere in your code that uses them) by a call to a subs like these:

Private Sub ClearTextBoxes
'Clear the text in all data text boxes
 
  txtFirstName.Text = vbNullString
  txtLastName.Text = vbNullString
  txtSocialSecurity.Text = vbNullString
  txtPhone.Text = vbNullString
  txtStreet.Text = vbNullString
  txtCity.Text = vbNullString
  txtState.Text = vbNullString
  txtZip.Text = vbNullString
  txtID.Text = vbNullString
  txtRate.Text = vbNullString
  txtGender.Text = vbNullString
  txtDateofBirth.Text = vbNullString
  txtEmployed.Text = vbNullString
End Sub
 
Private Sub EnableTextBoxes (booEnable as Boolean)
'Enable or disable all data text boxes (pass True to enable all, or False to disable all)
 
  txtFirstName.Enabled = booEnable
  txtLastName.Enabled = booEnable
  txtSocialSecurity.Enabled = booEnable
  txtPhone.Enabled = booEnable
  txtStreet.Enabled = booEnable
  txtCity.Enabled = booEnable
  txtState.Enabled = booEnable
  txtZip.Enabled = booEnable
  txtID.Enabled = booEnable
  txtRate.Enabled = booEnable
  txtGender.Enabled = booEnable
  txtDateofBirth.Enabled = booEnable
  txtEmployed.Enabled = booEnable
End Sub

(just paste this before cmdend_Click)

These can be used in cmdAdd and cmdDelete like this:

Private Sub cmdDelete_Click()
errormsg = MsgBox("Are You Sure You Want To Delete This Record", vbYesNo, "Delete Record")
If errormsg = vbYes Then
  rs.Delete
  Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
  list
 
[B]  Call ClearTextBoxes
  Call EnableTextBoxes(False)[/B]
 
  txtSearch.Text = vbNullString
  cmdSave.Enabled = False
  cmdCancel.Enabled = False
  cmdadd.Enabled = True
Else
  Exit Sub
End If
End Sub
 
 
Private Sub cmdAdd_Click()
 
[B]  Call ClearTextBoxes
  Call EnableTextBoxes(True)[/B]
 
  cmdadd.Enabled = False
  cmdDelete.Enabled = False
  cmdEdit.Enabled = False
  cmdSave.Enabled = True
  cmdCancel.Enabled = True
  dbadd = True
 
End Sub

..you can also call them from other places in your code that do the same work.

[Quizton]

Nice, thanks si , I like that way better =o)