Searching Access Database
Hello, I have an access database with 100,000+ records that I need to search through. I only need to work with the records that have a value in the account number field. Right now I have it going down the table and whenever it finds a row with an account number in it, it does the computations. This process only allows a few hundred records to be done every minute. I need to speed this up, but I have no clue how. Any help would really help me out
Code:
For i = 1000 To x 'x is number of rows in table Adodc1.RecordSource = "SELECT * FROM tSaleTransactions Where cdeTrans='A" & i & "'" Adodc1.Refresh If Adodc1.Recordset.Fields("cdeCustCode") <> Empty Then'does computations
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Searching Access Database
Good morning! i'm connecting to an access97 database with ADO. i've got some searching options available, and they're working ok....they do find the first record that matches the search criteria. i'm wondering if there's a way to order the results so that if a user does a search for NY, it will display the first record for NY followed by the rest of the NY records.
anyone know how to do this? here's a sample of the search code
Code:
Private Sub mnuFindState_Click()
Dim pstrState As String
pstrState = InputBox("Enter the State *Format: NY", "Find")
If pstrState <> "" Then
rsMerchants.Find "[State] = '" & pstrState & "'"
If rsMerchants.EOF = True Then
rsMerchants.MoveFirst
rsMerchants.Find "[State] = '" & pstrState & "'"
End If
End If
End Sub
Thanks!
Ryan
Searching An Access Database
ok i have an access database called modems, and in that database i have 2 fields (manufacturers, and names)
i created a program that i would like to be able to search for either on, in other words have 2 text boxes one to search names and the other to search manufacturers, then i would have a search button and when you hit search it would look at one of the boxes, not both, and then display the results in a list box below.
basically it is all set accept, i have no idea what i need to use for the search_click code????
does anyone have any ideas????
"who says you need wings to Phly"
Searching An Access Database From Excel
Hello all,
I have yet another problem. I would like to automate Access from within an Excel workbook. I want to use an Excel userform to search an Access table, and fill in values within that Access database. I found this generic code on http://www.ExcelTip.com/:
Code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:FolderNameDataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("FirstName") = Range("B" & r).Value
.Fields("MI") = Range("C" & r).Value
.Fields("LastName") = Range("D" & r).Value
.Fields("Prefix") = Range("E" & r).Value
.Fields("Suffix") = Range("F" & r).Value
.Fields("MaintCo") = Range("G" & r).Value
.Fields("Installer") = Range("H" & r).Value
.Fields("Distributor") = Range("I" & r).Value
.Fields("Manufacturer") = Range("J" & r).Value
.Fields("Agency") = Range("K" & r).Value
.Fields("ContractDue") = Range("L" & r).Value
.Fields("StartDate") = Range("M" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
What that code does is copy information from an Excel worksheet to an Access table, but I am not sure how to search an Access database, or how to fill in the information in the Access database from an Excel userform.
I am perfectly willing to read over the basics of automating Access within Excel's VB capabilities, but I have not really been able to find any helpful web sites on the subject.
The reason I am asking about this is:
We are generating contracts through an Excel workbook. The first worksheet of the workbook is an input sheet, which fills in all of the pertinent information throughout the rest of the workbook (the body of the contract). We also use an Access database to track our contracts, so that we can schedule inspections, etc. I would like to give users the option of adding a record to our Access database when they finish generating the contract. We assign a job number to each client, so the user would enter the job number in an Excel userform. Then, I want Excel to search our Access database (all of the information is stored in an Access table) for that job number. If the job number exists, then Excel would initialize one userform to update the Access database record. If the job number does not exist, then Excel would initialize a different userform to add a record to our Access database.
I know that this is a very long post, and that I am asking an awful lot, but really all I am asking (at the moment) is that someone point me in a direction toward a free web site that will give me some tutorials on automating Access from within Excel VBA.
Searching An Access Database With VB Front
Hi,
I have written a VB script that handles a Database,
The database comprises of member details for a club,
I have a form that will add, edit, remove etc and will also cycle through details (the database does not appear as a list)
However I want to be able to "search" for members, and have their details appear on the form.
How can I do this? I am using VB6 and Access 2000
Searching An Access Database Using VB Form
Hi Out there,
I have created a VB App. for a museum - there is the imput form to update the database but i would like to have on that same form a search button where someone could enter at least one field into the form and by clicking the search button well retrieve the other data from that record.
Help anyone please
Searching An Access 2000 Database
Im making a lunch detention database program for my school and i need to search the database by ID number. I left you a copy of my program so you can fill in the search code.
Searching And Updating Database Access
Hey there,
I'm not too sure on how to go about this i would like to
1) Search the database
2) if a matching computername is found in the database
3) overwrite the computername with a new info
Thus far my codes are as such
Rs.Find "PC"
Do While Rs.EOF <> True
Debug.Print "PC Name: "; Rs!PC
Count = Count + 1
Mark = Rs.Bookmark
Rs.Find "PC", 1, adSearchForward, Mark
Loop
How do i continue from here
thank you very much
cheers!
Searching An Access Database--from A 6 Month Newbie
I've been struggling with a problem for weeks now. I have a search box which I downloaded and am adapting to work with my Access 2000 database. It has well over 3000 records and I need to search using two different criteria. The search box does that by creating a SQL statement which queries my tables as I input the criteria. I have a "Create Query" button which saves the SQL statement created by the search under the name "Deposit Edit". The code is as follows:
Private Sub cmdCreateQDF_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim qdf As QueryDef
Dim strName As String
'first get a unique name for the querydef object
strName = Application.Run("acwzmain.wlib_stUniquedocname", "Deposit Edit", acQuery)
strName = InputBox("Please specify a query name", "Save As", strName)
If Not strName = vbNullString Then
'only create the querydef if user really wants to.
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
qdf.Close
Else
'ok, so they don't want to
MsgBox "The save operation was cancelled." & vbCrLf & _
"Please try again.", vbExclamation + vbOKOnly, "Cancelled"
End If
ExitHere:
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.QueryDefs.Refresh
Set db = Nothing
Exit Sub
ErrHandler:
Resume ExitHere
End Sub
I then have a buttlon called "Edit Deposit" which is tied to the following code:
Private Sub Edit_Deposit_Click()
On Error GoTo Err_Edit_Deposit_Click
DoCmd.Close acForm, Me.Name
Dim stDocName As String
stDocName = "Deposit Edit"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Edit_Deposit_Click:
Exit Sub
Err_Edit_Deposit_Click:
MsgBox Err.Description
Resume Exit_Edit_Deposit_Click
End Sub
These codes work great the first time. But I can't get the Create Query section of the code to just replace the previous save so that it updates the information I'm searching for each time I make a search. Is what I'm trying to do possible? Or am I the victim of a pipe-dream?
Any advice is appreciated.
Maureen
Regarding Searching By Date Range In Access W/oracle Database
Larry , thank you for your hlep, however I am still not getting it to respond.... I did correct the error you point out regarding the "bang" I missed.... so this is my issue again... if you guys could look it over for me once more....
( Oh and I did not understand the piece about time/date issue, Larry spoke of ... it's only a date I need it to respond too)
First of all...I am new at this site.... and new at VB.... so plz be gentle with me...
this is the code...
I am using access 2000, and this is a front end to an Oracle on a I think a rs6000 unit... if that makes sense...
This is to collect data for a well information in a date range... all my other searches work so for...
thank you in advance
barry
Private Sub Command34_Click()
On Error GoTo Err_Command34_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim MyDate, MyCheck
stDocName = "PV_WELL_DATA4"
stLinkCriteria = "([PV_WELL_DATA.JOB_DATE]>=" & Me![From] & ") AND (" & "[PV_WELL_DATA.JOB_DATE] <= " & Me![To] & ")"
MyDate = IsDate(Me![From])
MyCheck = IsDate(Me![To])
'MsgBox ("result of isdate = " & MyCheck)
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command34_Click:
Exit Sub
Err_Command34_Click:
MsgBox Err.Description
Resume Exit_Command34_Click
End Sub
Access 2002 Database Search Problem: Searching Words With '
I'm using VB6 and ADO 2.7 to open an access 2002 database wich I created. I can normaly read and write values. I can browse it so the problem might not be in the database.
The problem is that when I search in a recordset for a word with a ' in it, it will generate an error. The error is generated here:
PHP Code:
rs2.Find "Nome_Ingles like '[" & FindName & "]' "
Thanks for your atention...
Searching A Database Without Loading The Whole Database
I have a SQL database I am trying to search for just a few records at a time from. But when i pull over the datatable it loads the whole database. The database is so large it takes forever. What can i do just to pull the records I am searching for?
Thanks
Database Searching
Okay i wrote a program that takes text file information and uses a split function to save it it a text file, the fields are seperated by a comma.
Anyone know of any well written turts. to teach me how to make something to search them pending on something in the text field, the date or anything else.
Thanks
Brent
Searching A Database...
hi all,
i have a couple of questions for you.
im using vb6 and i have implemented a database using the adodc1 and the dataGrid components. i have made a text object and have used it to search for a record. my problem is, i have to search for a exact record eg. "Apples are red". I cant just search for "red" - is there anyway to search for anything within a feild's record?
also how can i toggle on and off to search every feild and not just the main one?
thanks heaps!
Searching A Database
I've got a variable called strSurname declared as a string. I'm trying to find the userid where the surname is found. When the SQL statement gets executed I get a [syntax error (missing operator) in query expression].
The SQL Statement reads "SELECT Userid FROM Users WHERE Surname = '" & strSurname & "'"
This statement works ok for all records except for one name where the surname is I'Anson which produces the error message. How can I adjust the SQL statement to take into account the apostrophe in that surname.
Thanks
Searching VSS Database
Hi all,
Does anyone know of a smooth way of searching a VSS database, other than Tools | Search inside of VSS? I need to search for 143 stored procedures in 8 different project folders. Ideally I'd love to be able to join with the sybase system tables, but I'm sure I'm really wishing there.
All suggestions welcome..
Frances
Help Searching Database
I have a text box that the user can type a name and then a search button that returns values in a flexgrid. Now I can search on a person full name, ex: John Q. Smith. But if I just type in John, or Smith, I get an error that says "either BOF or EOF is true,or the current record is deleted, etc...
How can I just search on any part of the name?
Heres the code I am using.
Code:
Private Sub cmdSearchBios_Click()
Screen.MousePointer = vbHourglass
Call CreateConnection(objConn)
MSFlexGrid1.Clear
MSFlexGrid1.Rows = 1
Set objRs = New ADODB.Recordset
objRs.ActiveConnection = objConn
objRs.CursorLocation = adUseClient
objRs.CursorType = adOpenDynamic
objRs.LockType = adLockOptimistic
objRs.Source = "select * from Bios where tm_name ='" & txtFindBio.Text & "'"
'MsgBox objRs.Source
objRs.Open
While Not objRs.EOF()
'MsgBox objRs!tm_name
'ADDING RECORDS TO FLEXGRID
MSFlexGrid1.AddItem objRs!tm_name & Chr(9) _
& objRs!tm_title & Chr(9) _
& objRs!tm_id & Chr(9)
objRs.MoveNext
Wend
Set objRs = Nothing
Call CloseConnection(objConn)
Screen.MousePointer = vbDefault
End Sub
Searching Database With ADO
Hello,
I want to be able to search for a specific record in a datagrid. And I want to be able to search, using a textbox and submit button, on a remote form. I have a Book database that I am using, I want to search for the title of the book and highlight that record in the datagrid. So this is the process... I start at the Search Title Form (frmTitle) and contains a textbox (txtTitle) and a Submit button (cmdTitleSubmit). Then I type in the name of the book I want to search for and hit the Submit Button. (The Submit Button should be coded to search for the title of the book in the datgrid in the next form.) The Submit button then brings up another form (frmDatabase) which contains my datagrid (dgddatabase) and my adodc (adodatabase), the record that corresponds with my search terms is highlighted. And I want it setup so that it clears the records and displays only the records that relate to my search terms.
My code I have so far is below...
frmDatabase.adoDatabase.RecordSource = "SELECT * FROM tblDatabase WHERE (title) like '" & txtTitle.Text & "%'"
frmDatabase.adoDatabase.Refresh
frmDatabase.Show
But it comes up with these error messages...
"No value given for one or more required parameters."
AND
Run-time error '-2147217904 (80040e10)': Method 'Refresh' of object 'IAdodc' failed.
I have been trying to figure what the error message is saying but I dont know at the moment what is wrong.
Help!! Thanks for your time!!!
Searching Database
I want to use a list box to display the contents of a particular column (Project Name) in a table situated in the database. I thought of having the list box's data field as the same field i want to display but it just takes the first record in the column. How do i fix this?
I also would like to have the information on the selected Project (from the list box) displayed in a series of labels how would i do this.
If it helps i can put the form and database up in another post.
Searching The Database...
Just wondering, is it possible to search a database for a field on form 1, and then to display the result on form 2?
Also is there a way of setting out the row nicely on the form, for example, if the row in the database looked like :
4 2 10 Broke
would it be possible to put each of the items into seperate labels on the form 2?
thanx in advance!
Searching A Database !!!! Please Help
I have been working on this problem since last Friday and Iam as frustrated as ever!
Here's the problem:
My database contains a field called "entry". I want to be able to...
1) click a command button that I have named "Search"
2) enter search criteria
3) have VB search through the "entry" field of all my records and display records that contain my search criteria.
For ex: I enter "PA"
One of my records contains "abandoned mine of PA" in the field "entry"
My goal is for the computer to find and display any records that contain “PA”.
I have the following line in declarations:
Private db As ADODB.Connection
My load form code is as follows:
[code]
Private Sub Form_Load()
Set db = New Connection
Dim rs As ADODB.Recordset
Set rs = New Recordset
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:gentestGen_Cards.mdb;"
rs.Open "SELECT * FROM Cards", db, dbOpenKeyset
End Sub
___________________________
My search command code is as follows:
[code]
Private Sub Command1_Click()
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim INQUIRE As String
INQUIRE = InputBox("Enter search criteria:")
sSQL = "SELECT entry FROM cards WHERE entry LIKE '%" & INQUIRE & "%'"
Set rs = New ADODB.Recordset
rs.Open sSQL, db
End Sub
Here is what is supposed to happen:
1) An input box appears when I click the "search" button
2) I enter whatever phrase or word that I want to search for
3) The program searches through the "entry" field of all records
4) displays the first record it finds that matches the search
Everything seems fine. No error messages. However, after I enter the search criteria and click ok... nothing happens.
It is my understanding that the problem is because my fields are bound to the datacontrol and my search is contained in a coded recordset. I have tried using a MSHFlexgrid but can’t seem to figure out how to get the new recordset bound to it. This has been a lot more confusing than I ever anticipated. But maybe it’s just me!
Do I need to open a new form to display the new recordset?
Cinnamon
Searching A Database
Hi,
I was wondering whether anyone could help. i need to search i database i created using Visual Basic. i'm pretty handy with SQL, and i can get the desired results when searching in access but i want to make a VB form so that people without SQL knowledge can search. there are multiple fields that i want to search, or give the user the option of searching just one. i have very little VB knowledge so i need it explained step by step. thanks for any help.
Searching Database
anyone knows where i can find reference/tutorial on "searching from database" using combox or datacombo, textbox that works exactly like MSDN search.
Searching Through Database
hey all...
I have the following code that is designed to search through database and display corresponding information on the form.
If (txtCurrentSearch.Text > 1) And (txtCurrentSearch.Text < 99999) Then
For x = 1 To Countdata
If txtCurrentSearch.Text = CurrentSubNum(x) Then
lblCurrentNewSubNum.Caption = "Subscription Number - " & CurrentSubNum(x)
lblCurrentName.Caption = "Name - " & CurrentName(x)
lblCurrentAddress.Caption = "Address - " & CurrentAddress(x)
lblCurrentSex.Caption = "Sex - " & CurrentSex(x)
lblCurrentSubDate.Caption = "Last Subscription Date - " & CurrentSubDate(x)
cmbCurrentSubType.Text = CurrentSubType(x)
cmbCurrentSubAge.Text = CurrentSubAge(x)
End If
Next x
End If
I have only 7 subscription numbers in the database, which range from 10001 - 10007. i want a message box to appear if the number entered does not match the number in the database.
Does anyone know how i can achieve this?
thanks for ya help...
DK
VB6 Database Searching
I would like to write back to a database, but only edit a specific line.
how can i do this without having to writeover the whole database:
PLEASE I NEED YOUR HELP
dkeegh
Searching A Database
I have a text box in which a user would be in the keyboard they are looking for in the database. Then I have a button the press to perform the search.
Question is what would the code look like to search the entire database for records that contain the keyword then display the matches only.
Need A Little Help With Searching A Database
I have been able to figure out how to get what i want loaded into a datagrid viewer on a form the only thing i'm having trouble with is how to i use what is typed in a textbox as the search agent (i.e. a last name) below is the code i have so far...What i have learned is mostly from reading books and trial and error so if anyone has a better way of doing this i'm eager to learn.
Code:
Private Sub FindCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindCustomerButton.Click
Dim cn As New SqlClient.SqlConnection()
Dim cmd As New SqlClient.SqlCommand()
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Try
cn.ConnectionString = "Data Source=STORMSQLEXPRESS;Initial Catalog=customers.mdf;Persist Security Info=True"
cn.Open()
cmd = cn.CreateCommand
cmd.CommandText = "SELECT LastName FROM customers"
da.SelectCommand = cmd
da.Fill(ds, "Customers")
cn.Close()
CustomersDataGridView.DataSource = ds
CustomersDataGridView.DataMember = "Customers"
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, _
MsgBoxStyle.OkOnly, "FindCustomerButton")
End Try
End Sub
Searching Database
I have created student database in MS Access name "Student.mdb" table name is "student list" table having four fiedls name 1.Registration No, 2.Student Name, 3.standard, 4. city . In vb i have designed one form "frmsearch" in that i have placed one text box name "txtsearch, two option button, name "optsearchmode Regno" & "optsearch mode alphabetical " and on datagrid contol & one command button name "cmdsearch"
My question is
1. user optsearch mode Regno and enter Regno in txt search and click seach command button the resulat will display MessageBox, Found Or not.
2. user optsearch mode alphabetical and enter *A* in txt search and click search command button the resulat will display in Datagrid control only the name begin with A.
how to wite the code for above my question
Note: I am connecting the database using ADODC
awaiting your valuable reply
thanking you
Database Searching
I have been stuck on a certain search for a month. I am searching my Access database by customer name, but sometimes you don't have the customers full name or don't know exactly how to spell it. So my problem is that my search, even though it's not case sensitive, can't search right unless you know exactly how the customers name is spelled. Here is my code:
Code:
Dim strQueryString As String, i As Integer
i = 1
strRenter = UCase(txtFind.Text)
strDate = txtDate.Text
strDate2 = txtDate2.Text
varBookMark = datTickets.Recordset.Bookmark
strQueryString = "Renter = '" & strRenter & "'"
ListView1.ListItems.Clear 'Clear list
If strRenter = "" Then
MsgBox "Enter the renter's name.", vbCritical, "Enter Renter's Name"
Exit Sub
End If
datTickets.Recordset.FindFirst strQueryString 'Find first one
It also searches the date when the customer was helped. The strRenter, strDate, and strDate2 are declared at the form level. This search works, but I have to type in john smith, I can't just type john, or smi, or whatever needed. This then displays to a listview. Any help you can give me I would appriciate very much.
Thank you
Searching Through A Database
Hi,
Let say I have a list box and connected and pulled a database for Client Names. I would like to type in the name and it would appear automatically. How do I do that? Can someone help?
Thanks
Database Searching
hi,
i am newish the VB i have connected to a database and have been able to display all the data from the database. can you to explain how i can search a sql database for a specific result from a user textbox. and display the result. thanks.
Searching A Database
I have a vb form with an excel database. The fields are shown in individual text boxes. I want to be able to have the use input a search term in a text box and be able to pull up the corresponding record in the database. Any suggestions?
Searching A Database
I want to write an informational program that people can search something and if a form has any keywords that they search, it displays a link to that form in a listbox or something. Any suggestions?
Searching In SQL Database
I am not sure if this topic has been posted somewhere. But I am working in a project for collage. I am having some problems. If anyone could help..
Here's my program code:
Code:
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Command1_Click()
Dim objform2 As Form2
Set objform2 = New Form2
objform2.Show
End Sub
Private Sub Form_Load()
con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=InstManagement;Data Source=MYHOMEPC"
rs.Open "Select * from Student", con, adOpenDynamic, adLockBatchOptimistic
On Error Resume Next
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End Sub
Private Sub add_Click()
On Error Resume Next
If add.Caption = "ADD" Then
StudID.Text = rs(0)
FName.Text = " "
LName.Text = " "
GardianName.Text = " "
DOB.Text = " "
Gender.Text = " "
Edu_Quali.Text = " "
Proff_Quali.Text = " "
Remarks.Text = " "
Hno.Text = " "
Street.Text = " "
City.Text = " "
State.Text = " "
Pincode.Text = " "
Contactno.Text = " "
RegDate.Text = " "
CourseName.Text = " "
CourseFee.Text = " "
RegFee.Text = " "
Batch.Text = " "
ModePayment.Text = " "
rs.AddNew
StudID.SetFocus
End If
End Sub
Private Sub delete_Click()
On Error Resume Next
s = Val(InputBox("Enter the Student_Id"))
i = "delete from Student where Student_Id=" & s
Set rs = con.Execute(i)
Set rs = Nothing
rs.Open ("Student"), con, adOpenDynamic, adLockBatchOptimistic
rs.Update
rs.MoveFirst
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
MsgBox "DATA DELETED"
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Public Sub find_Click()
On Error Resume Next
s = Val(InputBox("enter the Stdent_Id"))
i = "select * from Student where student_id=" & s
Set rs = con.Execute(i)
If rs.RecordCount = 0 Then
MsgBox "sorry"
Else
If i <> rs.BOF And rs.EOF <> True Then
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
Else
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End If
End If
End Sub
Private Sub first_Click()
On Error Resume Next
rs.MoveFirst
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End Sub
Private Sub last_Click()
On Error Resume Next
rs.MoveLast
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End Sub
Private Sub next_Click()
On Error Resume Next
rs.MoveNext
If rs.EOF <> True Then
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
Else
rs.MoveLast
MsgBox "THIS IS LAST RECORD"
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End If
End Sub
Private Sub previous_Click()
On Error Resume Next
rs.MovePrevious
If rs.BOF <> True Then
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
Else
rs.MoveFirst
MsgBox "THIS IS FIRST RECORD"
StudID.Text = rs(0)
FName.Text = rs(1)
LName.Text = rs(2)
GardianName.Text = rs(3)
DOB.Text = rs(5)
Gender.Text = rs(4)
Edu_Quali.Text = rs(6)
Proff_Quali.Text = rs(7)
Remarks.Text = rs(8)
Hno.Text = rs(9)
Street.Text = rs(10)
City.Text = rs(11)
State.Text = rs(12)
Pincode.Text = rs(13)
Contactno.Text = rs(14)
RegDate.Text = rs(15)
CourseName.Text = rs(17)
CourseFee.Text = rs(19)
RegFee.Text = rs(16)
Batch.Text = rs(18)
ModePayment.Text = rs(20)
End If
End Sub
Private Sub save_Click()
If save.Caption = "SAVE" Then
rs(0) = StudID.Text
rs(1) = FName.Text
rs(2) = LName.Text
rs(3) = GardianName.Text
rs(4) = Gender.Text
rs(5) = DOB.Text
rs(6) = Edu_Quali.Text
rs(7) = Proff_Quali.Text
rs(8) = Remarks.Text
rs(9) = Hno.Text
rs(10) = Street.Text
rs(11) = City.Text
rs(12) = State.Text
rs(13) = Pincode.Text
rs(14) = Contactno.Text
rs(15) = RegDate.Text
rs(17) = CourseName.Text
rs(19) = CourseFee.Text
rs(16) = RegFee.Text
rs(18) = Batch.Text
rs(20) = ModePayment.Text
rs.UpdateBatch
End If
MsgBox "DATA SAVED"
End Sub
This code is running well but at some points I want to improve this program:
* in Save_Click() function : I want a function to search in SQL whether studentID already exist, if it does then program show a dialog box saying "StudentID already exist" and user can input different studentID. (Can I also make it that user don't have to re-enter the entire data again?)
* In Find_Click() : Here if function doesn't work.
Code:
If rs.RecordCount = 0 Then
MsgBox "sorry"
Else
code....
instead of checking rs.recordcount it directly go to else part. If record doesn't found it shows error instead of showing "Sorry" in msgbox.
* I added a sub function later with save_click() the program but it doesn't work too.
Code:
public sub checkid()
if val(studID.text)=rs(0) then
msgbox "StudentID already exist. Enter another StudentID"
else
rs.updatebatch
end sub
and added it with save_click() function in the end.
it shows the msgbox and run rs.updatebatch both at the same time. I know i am doing some mistake here....
Can anyone give me there suggestion...
Thank You in Advance
Searching A Database Help
this is my search engine in my program it works but I want it to be a little more specific when I search the ratings part of my database. for instance if I were to search for a PG movie it will also list the G and PG-13 movie as well I just want it to list the specific one i'm searching. this is my code.
VB Code:
Private Sub cmdSearch_Click() lstMovies.ListItems.Clear strSearch = "[" & cboSearchType.Text & "] Like '*" & txtSearchText.Text & "*'" With rsMovies .FindFirst strSearch If .NoMatch Then MsgBox "Your search is complete. There are no movies with """ & txtSearchText.Text & """ in the """ & cboSearchType & """ section.", 16, "Search Complete" Exit Sub Else Set a = lstMovies.ListItems.Add(, , .Fields("Title"), , 2) a.SubItems(1) = "(" & .Fields("MovieDate") & ")" frmMain.Again strSearch End If End With End Sub
Database Searching
I have changed my program and can't figure out how to change my search in
my program to make it work with the way I changed my program around.
Ok, This is a movie program and I used to have the credits(actors names)
in with the movie itself and my search program worked then. I have recently
changed my program around a little to make the actors names associate with
the character they play in that movie, in witch I took the credits part out of
the movies section and made a table called tblactors, and it associates with
the character table call tblactorlink.
tblactor would look something like this
actorID----actor_name----movieID
tblactorlink
Lactor_ID----char_name----movieID
This is my code that worked before I changed my program
VB Code:
frmMain.StatusBar.Panels(1) = "You are currently Searching for " & Chr(34) & txtSearchText.Text & Chr(34) & " in the " & cboSearchType.Text & " Section" & Chr(34) frmMain.lstMovies.ListItems.Clear strSearch = "[" & cboSearchType.Text & "] Like '*" & txtSearchText.Text & "*'" With rsMovies .FindFirst strSearch If .NoMatch Then MsgBox "Your search is complete. There are no movies with """ & txtSearchText.Text & """ in the """ & cboSearchType & """ section.", 16, "Search Complete" Else Set a = frmMain.lstMovies.ListItems.Add(, , .Fields("Title"), , 2) a.SubItems(1) = Replace(rsMovies.Fields("Genre"), "§", ", ") If Right$(a.SubItems(1), 2) = ", " Then a.SubItems(1) = Left$(a.SubItems(1), Len(a.SubItems(1)) - 2) a.SubItems(2) = .Fields("Director") a.SubItems(3) = .Fields("Credits") a.SubItems(4) = .Fields("Rating") a.SubItems(5) = .Fields("MovieDate") a.SubItems(6) = .Fields("Type") frmMain.Again strSearch End If End With txtSearchText = "" Me.Hide
If you require anymore info just ask because I'd really like to get this working
again, Thank you for your time.
Searching Database
im making a projet wherein i will search for an available room in a hotel. i have to search my database first with the type of room, and/or room no., then if its available for the given date. i dont have any code because i dont know how to do this.
Searching Database
i have a new question, i need to search my database and output the search result in the datagrid or table. and i should be able to edit or update that table. im only a beginner in visual basic and i only know the basic of VB, pls help
Searching A Database
Dear all,
I want to use a form with a text box called txtCitySearch.text to look for city names in a databse. The user should be able to enter a value then click command button 'cmdCitySearch' to locate results.
My db is called details.mdb and table where field 'City' is located can be found in a table called 'Details'
Can anyone please help me with the code, how would I then display these results????
Regards,
Colin
Searching To Database
How about searching thru database? Can anyone help me on this. At present I have created some forms where the user will key in information in the textbox and the information will then save into a sql server. What I want is to retrieve back the data by searching thru a indentical number for example. Can you help me on this...thanks in advance
Searching In Database
Hi there,
I have a simple problem, I have a combo box where I have names of certain cities & two text box which is for latitude & longitude. I have already entered all these in a access file (DAO) now what I want is when I click on any place the long & latitude should get displayed. Please Help
Thanking u & waiting for some kind replies.
arv
Searching A Database
Hey there.
I want to search a database.. and if there are no exact matches
... show a list of the closest ones. I want to do this all programmaticly (with out a control)...
Anyone able to help me?
Searching A Database
My form currently has a combo box, a text box, a command button and a flexi grid. The combo box currently displays all the fields in my database.
What I would like to do is search for a record(s) with specified data in a specfic field and display the results in the flexi grid. I would like to be able to specify the field by selecting it from the combo box unless theres an easier way.
Thanks for your time
Searching Database
Guys,
I have an Access database and I want to put search option on my form (like KEYWORD search), it should be able to get from any field of the table.
Can some one help me???
Thanks in advance
Searching A Database
In my small football management game, I am using an Access database to hold all information about the players. They are held in tables, by "regions" such as Europe, Africa, Asia etc, all of which are identical, except for the info held within them... I am currently using the following code to search for players:
select * from [Europe] Order by [Name]
What I want to be able to do is search more than one region at a time, for example the user wants to find players from Europe and Asia...
How would I do this?
Searching A Database
OK.. Here goes. I made a datbase using Acess 97 all goes fine and good and the fields work. I can add subtract and do logs at the same time. he he . Humor. Anyway I need to search the datbase I made the code to search as follows however it fails to work. If you can please help. Post either how to fix this or ever start from the beggining I am willing to read.
prompt$ = "Enter the full (complete) criteria."
SearchStr$ = InputBox(prompt$, "Search")
Data1.Recordset.Index = ""
Data1.Recordset.Seek "=", SearchStr$
If Data1.Recordset.NoMatch Then
Data1.Recordset.MoveFirst
End If
|
TRACKER
