Select coding

  • Thread starter Thread starter spieters via AccessMonster.com
  • Start date Start date
S

spieters via AccessMonster.com

L..S.

I'm creating a coding using the tables of an ODBC SQL Server connection.

I'm searching in a table on the SQL server using the following code.
I'm receiving however the data of the first record in the table as output.

Dim bdd As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As Variant
Dim datHireDate As Date
Dim strTitle As String

Set bdd = CurrentDb()

Me.EmplName = " "
Me.voucher_subcont = " "
datHireDate = Me.voucher_date

' Read Kronos to display Info if present

Set rs = Nothing

SQL = "Select personnum, applydate, personfullname, hours, subcontractor"
& _
"FROM dbo_foodvoucher_right Where personnum = '" & Me!
voucher_personnum & "' And applydate = #" & datHireDate & "# "

Set rs = bdd.OpenRecordset("dbo_foodvoucher_right")

Me.EmplName = rs.Fields("personfullname")
Me.voucher_subcont = rs.Fields("subcontractor

Can you please give me some info on how to correct this coding to allocate
the record I'm searching for?
Thanks in advance for your effort and cooperation.

Stella Pieters
 
You directly open the table dbo_foodvoucher_right and make no use whatsoever
of the sql string that you have just build in the OpenRecordset command. I
suppose you should write something like the following instead:

Set rs = bdd.OpenRecordset (sql)

However, I cannot tell you for sure. You should ask this question in a more
appropriate newsgroup like m.p.access.odbcclientsvr or m.p.a.externaldata as
this one is about ADP and SQL-Server and has nothing to do with MDB file and
ODBC linked tables.
 
Back
Top