connecting databases and comparing fields

[sunilvedula]

HI

i have been doing a project for my company. i am trying to develop a tool in access. My problem is that i am unable to pull records associated with an id. every time i give the id in the combo box in form it gives me back the result of the first record irrespective of the id i enter in combo box. Pls give me the code if possible. i am pasting the code which i have written. there are two table table 1. Employees table 2. Details.

Dim rs As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
Set rs = New ADODB.Recordset

Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"

Cnxn.Open

'rs.Open (["SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'""], Cnxn)
or
set rs = cnxn.Execute ("SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'"")

rs.Close
Cnxn.Close

 

[llkhoutx]

set rs = cnxn.Execute ("SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'"")

with one less ending quote mark for a text EmpID. For a number EmpID, use

set rs = cnxn.Execute ("SELECT LineManager FROM TblEmployees where EmpID=" & CboxHbiid)

 

[Moniker]

If your ID is a numeric value, lose the single quotes in the WHERE statement.

 

[sunilvedula]

thanq for ur reply but problem is not resolved

First it is not a numeric value but a text.

when i removed the quotes it give the following error "syntax error (missing operator) in query expression 'EmpID HBI0A8"

Also what was i trying is that both the table have empid as commom field and when i input the empid it should return the manager name associated with it. and the details of whatever is there with that id in the first table.
hope i am clear if not please advise i will write back. I appreciate ur fast response and i am just hopoing for ur response to help me resolve this project and my submission date is nearing. please help. Also it now says the following error "No value given for one or more required Parameters"

 

[modest]

You have two posts. I'm surprised anyone has replied with a title like "Please Respond To This Asap"

 

[sunilvedula]

Hi modest,

Sorry if i am not using the right language as i am very new to this kind of environment. What do u think would give me more posts. Since iwas desperate i did not know what else to do.

 

[llkhoutx]

Try

set rs = open("SELECT LineManager FROM TblEmployees where EmpID=" & CboxHbiid)

I think the ".Value is not needed in VBA syntax. Try the SQL in the QBE (Query-By-Example) window.

My earlier post was incorrect. Sorry.

Good luck, you have lots of time. Some times, the simplest errors are hidden the best.

 

[sunilvedula]

Thanq for the reply llkhoutx. But when i try the one which u provided it give me "syntax error" .
whe i use set rs =cnxn.execute("SELECT LineManager FROM TblEmployees where EmpID=" & CboxHbiid)
it give me "No value given for one or more reqd parameters".

 

[sunilvedula]

require help regarding connecting databases and comparing fields.

i have been doing a project for my company. i am trying to develop a tool in access. My problem is that i am unable to pull records associated with an id. every time i give the id in the combo box in form it gives me back the result of the first record irrespective of the id i enter in combo box. Pls give me the code if possible. i am pasting the code which i have written. there are two table table 1. Employees table 2. Details.

Dim rs As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
Set rs = New ADODB.Recordset

Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"

Cnxn.Open

'rs.Open (["SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'""], Cnxn)
or
set rs = cnxn.Execute ("SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'"")
and
set rs = cnxn.Execute ("SELECT LineManager FROM TblEmployees where EmpID=" & CboxHbiid)
It gives a error " No value given for one or more reqd parameters"
rs.Close
Cnxn.Close

 

[llkhoutx]

As I said, try:

set rs = open("SELECT LineManager FROM TblEmployees where EmpID=" & CboxHbiid)

Don't you have any reference material?

 

[Treason]

I dont know why you are using ADODB connection. I always use DAO. But if you insist on ADODB, its the way you are opening your recordset. You need to set the right parameters to open..

rs.Open "SELECT LineManager FROM TblEmployees where EmpID='" & CboxHbiid.Value & "'", Cnxn, adOpenKeyset, 2, adCmdText

I didnt test it but something like that should work

 

[sunilvedula]

thank you hope it should work and once it works i will get back to you.

 

[Mile-O]

Sorry if i am not using the right language as i am very new to this kind of environment. What do u think would give me more posts. Since iwas desperate i did not know what else to do.

A summary of your problem is what's best. And posting it only once.

 

[sunilvedula]

thank you for ur reply Treason

i did try the option which u gave me earlier and it gives me an error. Can u suggest me if i am using ado how do i go about it. It should help me. i will post the error message and all other details in detail once i reach office but since right now i am at home i cant do that.

 

[sunilvedula]

thank you. That was nice of you. i like the site and the people here who respond to the questions fast and help people like us. Once again thank you.

 

[sunilvedula]

i am using the following code and it says that variable not defined. If i have to define how should i do that.

Dim rs As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
Set rs = New ADODB.Recordset

Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"
Cnxn.Open
rs.Open "SELECT TblEmployees.LineManager, TblDetails.Resolves, TblDetails.APR,TblDetails.Oploss"
FROM TblDetails, TblEmployees
WHERE TblDetails.Empid = TblEmployees.Empid

Me.TxtPoints = DLookup("[Points]", "TblDetails")
Me.TxtPMI = DLookup("[PMI]", "TblDetails")
Me.TxtResolves = DLookup("[Resolves]", "TblDetails")
Me.TxtRegz = DLookup("[REGZ]", "TblDetails")
Me.TxtSickLeaves = DLookup("[SickLeaves]", "TblDetails")
Me.TxtAPR = DLookup("[APR]", "TblDetails")
Me.TxtOploss = DLookup("[Oploss]", "TblDetails")
Me.TxtPPH = DLookup("[PPH]", "TblDetails")
Me.TxtAtt = DLookup("[Attendance]", "TblDetails")
Me.TxtCategory = DLookup("[CAT]", "TblDetails")
Me.TxtANP = DLookup("[ANPHrs]", "TblDetails")
Me.TxtBiz = DLookup("[BIZhrs]", "TblDetails")
Me.TxtMan = DLookup("[Man%]", "TblDetails")
Me.TxtViolation = DLookup("[Viol%]", "TblDetails")

 

[llkhoutx]

What line is causing the problem?

It's probably a spelling error, but could be a field not defined in TblDetails.

Step through the cope using the debugger.
Select the first line in the routine and press F9; the run the program, it will stop of the preceeding line. Then press F8 to execute the next line; hover over each field in the line to check its value. You;ll spot the bad variable.

 

[sunilvedula]

Finally resolved. Thanks to everyone

Finally it got resolved. Thank u very much si the geek. u are indeed the super moderator. I used the below code to get the result.

Private Sub CboxHbiid_AfterUpdate()
Dim rs As New ADODB.Recordset
Dim Cnxn As New ADODB.Connection
Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"
Cnxn.Open
rs.Open "SELECT TblEmployees.LineManager, TblDetails.Resolves, TblDetails.APR,TblDetails.Oploss" _
& " FROM TblDetails, TblEmployees WHERE TblDetails.Empid = '" & CboxHbiid.Value & "'", Cnxn, adOpenDynamic
'& " WHERE TblDetails.Empid = TblEmployees.Empid"

rs.MoveFirst

Me.TxtAPR = rs.Fields("APR").Value
Me.TxtResolves = rs.Fields("Resolves").Value
Me.TxtOploss = rs.Fields("Oploss").Value
Me.CboxManager = rs.Fields("LineManager").Value
rs.Close
Cnxn.Close
End Sub

 

[sunilvedula]

resolved. thanks to everyone

Finally it got resolved. Thank u very much si the geek. u are indeed the super moderator. I used the below code to get the result.

Private Sub CboxHbiid_AfterUpdate()
Dim rs As New ADODB.Recordset
Dim Cnxn As New ADODB.Connection
Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"
Cnxn.Open
rs.Open "SELECT TblEmployees.LineManager, TblDetails.Resolves, TblDetails.APR,TblDetails.Oploss" _
& " FROM TblDetails, TblEmployees WHERE TblDetails.Empid = '" & CboxHbiid.Value & "'", Cnxn, adOpenDynamic
'& " WHERE TblDetails.Empid = TblEmployees.Empid"

rs.MoveFirst

Me.TxtAPR = rs.Fields("APR").Value
Me.TxtResolves = rs.Fields("Resolves").Value
Me.TxtOploss = rs.Fields("Oploss").Value
Me.CboxManager = rs.Fields("LineManager").Value
rs.Close
Cnxn.Close
End Sub

 

[sunilvedula]

The project is complete. All issues have been resolved and i once again thank everyone.