search

HI ,

The above code is working properly but now i want empid text box should display only if that id is in database ie employee table.
how to do this?

if user enter id in inputbox,it should check in employee table whethere that id is there in employee table.
if it is there then it should display the id else should display messagebox saying there is no such id present so that user can't enter other fields........


please tell me

thank u.....

Dear sowmya s,

change the if part of our previous code as the one given below

Code:

---

If Not ((r1.EOF = True) And (r1.BOF = True)) Then
Text2.Text = r1(1)
Else
MsgBox id1 & " not found"
End If

---

Pramod S Nair

Sorry,to confuse u....................

actually i wanted to ask u about like.........
i have two table employee and dependent......
the dependent form should be allowed user to enter, when employee id is present in employee table.............

secondly user should not be allowed to enter fields of dependent form unless employee form is filled.how to do this??

thirdly if user enters employee form........and after adding employee form jumps to dependent form........at that time employee id should automatically displayed from employee form to dependent form.
here employee id is common to both forms and also table

please help

once again sorry.......

thank u very much

sowmya s

Dear sowmya s,

I think you can follow these steps to conquer the problem in hand.

Prob 1 : How to make the dependant form available only if Employee table has the employee id.

Solution

In the dependant form instead of user's entering the employee id, populate all the available id's to a combo box or list from which user can select the employee id. Doing this will prevent the user from entering nonexistant employeeids.

On the form load of depandant form you can populate the combobox.

A rough code will be

Code:

---

dim rs as new ADODB.Recordset
rs.open "select empid from Employee",con
If Not ((r1.EOF = True) And (r1.BOF = True)) Then
do while not rs.EOF
combo1.additem rs(0)
rs.MoveNext
loop
else
'Use this only if you want
msgbox "Not even a single employee found in employee table"
unload me
end if

---

Prob 2 & 3

If you use a logic like the one given above for selecting the employee code from a list then the 2nd and 3rd problem will not be there.

Pramod S Nair

HI prAMOD,

I CAN'T PLACE COMBO BOX BECAUSE IF THERE ARE 500 EMPLOYEES,IT WILL BE DIFFICULT TO SEARCH
PLEASE TELL ME IS THERE ANY OPTION?



NOW I WANT ANSWER FOR ONE QUESTION VERY BADLY IE WHETHER I HAVE TO WRITE CONNECTION CODE IN MODULE FOR DIFFERENT FORMS OR ONCE IF I WRITE CONNECTION IS THAT ENOUGH?

IAM GETTING ERROR AS...........OBJECT OR PROVIDER IS NOT CAPPABLE OF PERFORMING REQUIRED OPERATION...........WHEN I CLICK ADDNEW BUTTON


WHAT IS THIS ERROR PLEASE SOLVE THIS

THANK U

Dear sowmya s,

Make the connection at the module level. It will be a good way. So you only have to use one connection object.

Open the connection object when program starts and close when the app quits.

Regarding the error message, pls post the code, as it can be due to many factors.

Pramod S Nair

Private Sub Form_Load()
Me.MousePointer = 11
Call data_connect1
Set rs1 = New ADODB.Recordset
rs1.Open "dependent", con1, adOpenKeyset, adLockPessimistic, adCmdTable
rs1.MoveFirst
medical.Value = 0
lbldate.Caption = Format(Date, "dddd, dd mmmm yyyy")
dtiss = Date
dtexpire = Date
dtdpissu = Date
dpexpire = Date
depdob = Date

dtiss.MaxDate = Date
dtdpissu.MaxDate = Date
depdob.MaxDate = Date
Me.MousePointer = 0
End Sub


Private Sub addnew_Click()
'Dim result
'result = id()
'If result = False Then
'result = id()
'Else
With rs1
.addnew(here the error is occuring)
.Fields("Empid") = empidtxt.Text
.Fields("dependent_name") = deptxt.Text
.Fields("relation") = relatxt.Text
.Fields("depend_passpo_no") = depasstxt.Text
.Fields("dt_issue_passport") = dtiss.Value
.Fields("dt_exp_passport") = dtexpire.Value
.Fields("issu_place") = passplacissutxt.Text
.Fields("dp_issu_dt") = dtdpissu.Value
.Fields("dp_exp_dt") = dpexpire.Value
.Fields("dob") = depdob.Value
.Fields("FIN/NRIC") = defintxt.Text
.Fields("medi_insu") = medical.Value
.update
MsgBox "DEPENDENT DETAILS ADDED"
End With
'End If
End Sub


please look this code and tell me why error is coming near addnew

please tell me

thank u

Dear sowmya s,

Please change the rs1.open statement which opens dependent table to the one given below

rs1.Open "select * from dependent", con1, adOpenKeyset, adLockPessimistic, adCmdTable

try it and reply

Pramod S Nair

Hello ms sowmya,

I see a lot of posting from your side. Are you into freelancing or what? ;)

Anyway jokes apart, let me tell you the hint.

On button click handler, try to use the recordset to query based on employee id.

'Select' should contain only the required fields to display and filtering should be done through where or Like . You can use DAO or ADO for the same.

Let me know if you need more details

Satish

Quote:

---

Originally Posted by sowmya s

HI,

please tell me how to adopt search??

Here iam inserting one input box saying please enter employee id,now user enters employee id..................
After this based on employee id i have to retrieve other fields and show the record.......
how to do this please give some hint .........
thank u

sowmya s

---

Quote:

---

Originally Posted by PramodsNair

Dear sowmya s,

Please change the rs1.open statement which opens dependent table to the one given below

rs1.Open "select * from dependent", con1, adOpenKeyset, adLockPessimistic, adCmdTable

try it and reply

Pramod S Nair

---

HI pramod it is giving syntax error..........
really iam confused lot....
actually it was working fine before i could insert connection in module but now ............giving error

help me thank u...
sowmya s

Dear sowmya s,

Please change the rs1.open statement which opens dependent table to the one given below. Sorry there was a typo in the previous post

Code:

---

rs1.Open "select * from dependent", con1, adOpenKeyset, adLockPessimistic

---

try it and reply

Pramod S Nair

Quote:

---

Originally Posted by PramodsNair

Dear sowmya s,

Try this modified version of your code

Code:

---

Private Sub Command2_Click()
id1 = InputBox("ENTER EMPLOYEE ID")
If id1 = "" Then Exit Sub
Dim cc As New ADODB.Connection
Dim r1 As New ADODB.Recordset
Dim sq As String
sq = ""

cc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\emp.mdb"
cc.Open
'''''''''''''''''''''''''''''''
'NOTE
'IF you have declared the Empid as string or text then comment the sq line given under
'the comment line as use the one which is commented jsut below this line
'sq = "select empid from employee where Empid ='" & id1 & "'"
'''''''''''''''''''''''''''''''
sq = " select empid from employee where Empid =" & id1
'''''''''''''''''''''''''''''''
'NOTE
'''''''''''''''''''''''''''''''

r1.Open sq, cc
If Not ((r1.EOF = True) And (r1.BOF = True)) Then
Text2.Text = r1(0)
End If


End Sub

---

it works perfectly here

Pramod S Nair

---

Hi Promod,

I always prefer to use fieldname instead of nos.Because in a big project you can't figure out the fields by seeing the nos.I think you should give a thought of not to use nos in recordset.
Rudraksh

hey somya s,
Pls put your code in a zip file and let us know the exact prob. we'll try to help you out. also if possible let us know where exactly the problem is occuring.
waiting for your codes.
Take Care.

hey somya s,
as far as your search prob is concerned let me give you one more idea,
to make your search good and robust you can use one more combination of tools in your project:

you add one text box inspite of an input box.
place a list box below your text box and on text1_change event you can fire a query like:
select * from tblA where personName like '%pranay%'
basically this query will give you all the records where you have 'pranay' anywhere in the entire name now you get it displayed in ur listbox.
the code for displaying that in list box will be approximately same as combobox. (in case you dont know that let me know i will provide that.)
now on list1_dblclick event you can populate the other fields from that table to the form with the query like :
select col001,col002,col003 from tblA where personid= list1.listindex

like this you need not worry if there r 500 records or 5000 records as list box is capable enough to take care of that.

Hope it helps.
Take Care.

HI pranay,

Thank u pranay..........i will work it out and let u know when i get good results

once again thank u very much.............

i hope i can ask u if i get any doubt..............

take care
bye
sowmya s