VB6 + access97, search function is needed.

[md0]

Hi all!

I can't say that i'm an advanced VB6 programmer, so I need some help.

I have a VB6 application that connects to a access 97 database, my company uses this for bugreporting on one of it's software applications. Let's call the software FRP.

I can add new entries to the database with my application, everything works just fine. But now i want to add a function where the you can search among the entries. So that the companys helpdesk can search it before calling me on technical advice regarding the FRP. How do I do this?
A really nice thing would be to be able to search the database and get the contents of the first match in a form, with every field in the database put in a textbox in the VB6 form. And if that not is what you are searching for be able to press a 'continue search' button to start searching from the entry you have passed to the VB form. Getting it? Okey, how do write some search code that will do that? I'm using VB6 Enteprise Edition with Access97 and i connect throug the Opendatabase and OpenRecordset thingy.

And again, _PLEASE_ be specific, I'm a novice.
And yes, I HAVE searched this fourm without any success!

Thanks in advance!
/A novice programmer from Sweden.

 

[pmanivas]

Are u using ADO for accessing data

 

[md0]

Really don't know, how do I find out?

 

[pmanivas]

How do u add new entries to the database? How do get the data from access?

If u are accessing the data thru recordset, Then you can use 'Find' method of the recordset object to search the database.

Let me know how u access the data.

 

[md0]

Ok, here's the code for adding a new entry.

Function AddNewPost()
Dim MyWorkspace As Workspace, MyDatabase As Database
Dim MyTable As Recordset
Dim MyFile As String
Dim ErrorCondition As Integer
Dim NewRecord As String
UpdateDb = True

'Check for "" - zero blocks
'and replace them with a " " (space)
'since it doesen't like updating the database
'with a empty string.
If company_name.Text = "" Then company_name.Text = " "
If company_phone.Text = "" Then company_phone.Text = " "
If company_fax.Text = "" Then company_fax.Text = " "
If company_email.Text = "" Then company_email.Text = " "
If company_custnr.Text = "" Then company_custnr.Text = " "
If contact_name.Text = "" Then contact_name.Text = " "
If contact_phone.Text = "" Then contact_phone.Text = " "
If contact_cellu.Text = "" Then contact_cellu.Text = " "
If contact_fax.Text = "" Then contact_fax.Text = " "
If contact_email.Text = "" Then contact_email.Text = " "
If IT_name.Text = "" Then IT_name.Text = " "
If IT_phone.Text = "" Then IT_phone.Text = " "
If IT_cellu.Text = "" Then IT_cellu.Text = " "
If IT_fax.Text = "" Then IT_fax.Text = " "
If IT_email.Text = "" Then IT_email.Text = " "
If system_os.Text = "" Then system_os.Text = " "
If system_sp.Text = "" Then system_sp.Text = " "
If network_type.Text = "" Then network_type.Text = " "
If problem_desc.Text = "" Then problem_desc.Text = " "
If problem_solution.Text = "" Then problem_solution.Text = " "
If date_start.Text = "" Then date_start.Text = " "
If date_stop.Text = "" Then date_stop.Text = " "
If uc_first.Text = "" Then uc_first.Text = " "
If uc_last.Text = "" Then uc_last.Text = " "


MydbString = initdb.Combo1.Text
MyFile = MydbString ' Define name of database.
On Error GoTo DBErrorHandler ' Enable error trapping.

Set MyWorkspace = Workspaces(0)
' Open database.
Set MyDatabase = MyWorkspace.OpenDatabase(MyFile)
If Not ErrorCondition Then
On Error GoTo TableErrorHandler ' Enable error trapping.
' Open table.
Set MyTable = MyDatabase.OpenRecordset("bugreport&quot
If Not ErrorCondition Then
On Error GoTo EditErrorHandler ' Enable error trapping.
MyTable.AddNew ' Enables adding new records.
MyTable![company_name] = company_name.Text ' Add the new record.
MyTable![company_phone] = company_phone.Text ' Add the new record.
MyTable![company_fax] = company_fax.Text ' Add the new record.
MyTable![company_email] = company_email.Text ' Add the new record.
MyTable![company_custnr] = company_custnr.Text ' Add the new record.
MyTable![contact_name] = contact_name.Text ' Add the new record.
MyTable![contact_phone] = contact_phone.Text ' Add the new record.
MyTable![contact_cellu] = contact_cellu.Text ' Add the new record.
MyTable![contact_fax] = contact_fax.Text ' Add the new record.
MyTable![contact_email] = contact_email.Text ' Add the new record.
MyTable![IT_name] = IT_name.Text ' Add the new record.
MyTable![IT_phone] = IT_phone.Text ' Add the new record.
MyTable![IT_cellu] = IT_cellu.Text ' Add the new record.
MyTable![IT_fax] = IT_fax.Text ' Add the new record.
MyTable![IT_email] = IT_email.Text ' Add the new record.
MyTable![system_os] = system_os.Text ' Add the new record.
MyTable![system_sp] = system_sp.Text ' Add the new record.
MyTable![network_type] = network_type.Text ' Add the new record.
MyTable![network_dom] = network_dom.Value ' Add the new record.
MyTable![network_workg] = network_workg.Value ' Add the new record.
MyTable![problem_desc] = problem_desc.Text ' Add the new record.
MyTable![problem_solution] = problem_solution.Text ' Add the new record.
MyTable![date_start] = date_start.Text ' Add the new record.
MyTable![date_stop] = date_stop.Text ' Add the new record.
MyTable![problem_solved_yes] = problem_solved_yes.Value ' Add the new record.
MyTable![problem_solved_no] = problem_solved_no.Value ' Add the new record.
MyTable![uc_first] = uc_first.Text ' Add the new record.
MyTable![uc_last] = uc_last.Text ' Add the new record.
MyTable.Update ' Save changes.
NewRecord = "true"
MyTable.Close ' Close table.

End If

MyDatabase.Close ' Close database.
End If

On Error GoTo 0 ' Disable error trapping.

If NewRecord = "true" Then
MsgBox "Databasen har uppdaterats, felrapporten inlagd.", vbExclamation
End If

Exit Function
DBErrorHandler:
ErrorCondition = True
UpdateDb = False
NewRecord = "False"
MyString = "Kan inte öppna databasen, " & MyFile & " v.g. försök igen. Vid bestående fel ring 9136."
MsgBox MyString, vbExclamation
Resume Next
TableErrorHandler:
ErrorCondition = True
UpdateDb = False
NewRecord = "False"
MsgBox "Kan inte öppna den aktuella tabellen, ring Christian på Helpdesk.", vbExclamation
Resume Next
EditErrorHandler:
ErrorCondition = True
UpdateDb = False
NewRecord = "False"
MsgBox "Kan inte uppdatera ett av fälten, v.g. kontrollera uppgifterna.", vbExclamation
Resume Next
End Function

 

[pmanivas]

Ok I got it
U are using DAO

Ok Here is the code for search
The strSearchCriteria is condition string to be searched.
For example,
if u are searching the 'state' field for value = 'NY' then your
strSearchCriteria = "State='NY'"

if u are searching for list of employees whose age is greater than 50 then

strSearchCriteria = "AGE > 50"

let me know if it is helpful


Function SearchPost(strSearchCriteria as string)
Dim MyWorkspace As Workspace, MyDatabase As Database
Dim MyTable As Recordset
Dim MyFile As String
Dim ErrorCondition As Integer
Dim NewRecord As String


MydbString = initdb.Combo1.Text
MyFile = MydbString ' Define name of database.
On Error GoTo DBErrorHandler ' Enable error trapping.

Set MyWorkspace = Workspaces(0)
' Open database.
Set MyDatabase = MyWorkspace.OpenDatabase(MyFile)
If Not ErrorCondition Then
On Error GoTo TableErrorHandler ' Enable error trapping.
' Open table.
Set MyTable = MyDatabase.OpenRecordset("bugreport&quot
MyTable.FindFirst (strSearchCriteria) ' Searches for the record.
IF NOT (myTable.NOMATCH) THEN
Company_name.Text = MyTable![company_name]
Company_phone.Text = MyTable![Company_phone]
company_fax.Text = MyTable![company_fax]
company_email.Text = MyTable![company_email]
company_custnr.Text = MyTable![company_custnr]
contact_name.Text = MyTable![contact_name]
contact_phone.Text =MyTable![contact_phone]
contact_cellu.Text =MyTable![contact_cellu]
contact_fax.Text =MyTable![contact_fax]
contact_email.Text =MyTable![contact_email]
IT_name.Text =MyTable![IT_name]
IT_phone.Text =MyTable![IT_phone]
IT_cellu.Text =MyTable![IT_cellu]
IT_fax.Text = MyTable![IT_fax]
IT_email.Text = MyTable![IT_email]
system_os.Text = MyTable![system_os]
system_sp.Text = MyTable![system_sp]
network_type.Text = MyTable![network_type]
network_dom.Value = MyTable![network_dom]
network_workg.Value = MyTable![network_workg]
problem_desc.Text =MyTable![problem_desc]
problem_solution.Text = MyTable![problem_solution]
date_start.Text = MyTable![date_start]
date_stop.Text = MyTable![date_stop]
problem_solved_yes.Value = MyTable![problem_solved_yes]
problem_solved_no.Value =MyTable![problem_solved_no]
uc_first.Text = MyTable![uc_first]
uc_last.Text = MyTable![uc_last]
ELSE
MSGBOX "There is no record found for that criteria"
ENDIF
MyDatabase.Close ' Close database.
End If

On Error GoTo 0 ' Disable error trapping.


Exit Function
DBErrorHandler:
ErrorCondition = True
MyString = "Kan inte öppna databasen, " & MyFile & " v.g. försök igen. Vid bestående fel ring 9136."
MsgBox MyString, vbExclamation
Resume Next
TableErrorHandler:
ErrorCondition = True
MsgBox "Kan inte öppna den aktuella tabellen, ring Christian på Helpdesk.", vbExclamation
Resume Next
End Function

 

[pmanivas]

Also if u want to loop thru all records that satisfies the criteria use 'FINDNEXT' method of the recordset. Hope this helps u to satisfy your search

 

[md0]

Thank you very, very much, I'm gonna try it when I get home today!

/c

 

[md0]

It doesen't work, all it says is "Runtime error 3251 - Operation is not supported for this type of object" And the strSerachCriteria = company_name="uc ab". So I think that I got that part right...

 

[pmanivas]

No
The search criteria is wrong

It should be as follows

strSerachCriteria = "company_name='uc ab'"

Hope this helps

 

[md0]

Tried that to, doesen't work...