asked on

An easy VB MS Access 2000 question

I have a single table (Employees) with 3 rows: Name, Age and Salary

I have one form with a command control

What code do I have to write in order to perform a simple search (salary>1000)
and print all the data relevant.
I've tried to write a code, but I'm having a problem with the CurrentDB.OpenRecordset and with the search function.

Please try to help me.
Thanks

undaground

Try this code

Dim salary(1 to 3) as integer, x as integer

Private Sub Command1_Click()
For x=1 to 3
If salary(x)>1000 then
Picture1.print salary(x)
End if
Next x
End Sub

Hope it helps!
-Mike

wide_awake

dim rst as DAO.Recordset
set rst = CurrentDB.OpenRecorset "Select * from Employees where [Salary] > 1000"

do while !rst.EOF
' print the data from the current record
Debug.print(rst("Name"))
Debug.print(rst("Age"))
Debug.print(rst("Salary"))
rst.MoveNext
loop

That should do it...

-Mark

alexel

ASKER

Mark

Thanks, but I'm getting a compile error on the:
Dim rst as DAO.Recordset

which is User Defined type not defined.

arbert

Do you have a reference to the DAO object in your project????

Brett

wide_awake

You have to add a reference for DAO - To do that in Access:

Go to the VB screen, then go to Tools -> References

In there, make sure "Microsoft DAO 3.6 Object Library" is selected. Then it should recognize DAO.Recordset as a valid data type.

-Mark.

alexel

ASKER

I've added the reference but now i'm getting a syntax error on the Select function

wide_awake

If you just copy & pasted, I made a typo above... it should be

set rst = CurrentDB.OpenRecordset "Select * from Employees where [Salary] > 1000"

but I entered
> set rst = CurrentDB.OpenRecorset "Select * from Employees where [Salary] > 1000"

without a "d" in OpenRecordset

Ooops.

-Mark

alexel

ASKER

I've noticed the typo and I've entered the code correctly, but I'm still getting a syntax error.

Do I have to perform additional references or to define something else?

Thanks
Alex

wide_awake

What's the exact error you're getting?

alexel

ASKER

After I'm clicking the command control, i'm getting a "compile error:syntax error" when the Select function is highlighted.

alexel

ASKER

After I'm clicking the command control, i'm getting a "compile error:syntax error" when the Select function is highlighted.

wide_awake

What's the exact error you're getting?

wide_awake

Try this:

set rst = CurrentDB.OpenRecordset "Select * from [Employees]"

If that doesn't work, try doing this:

Dim strSQL as String
strSQL = "Select * from [Employees]"
set rst = CurrentDB.OpenRecordset strSQL

If that doesn't work, then something weird is going on.

-Mark.

alexel

ASKER

Something weird is going on for sure.
The first option didn't work, as for the second it gave the syntax error on the:
set rst = CurrentDB.OpenRecordset strSQL

It seems like it doesn't recognize the CurrentDb parameter.

I'm increasing the points as this problem as not simple as I thought.

wide_awake

if you type "CurrentDb." in the VB window, does it pop up a context menu with all the methods/attributes of CurrentDb?

wide_awake

Oh - one other thing you might need to do is put the SQL string in parentheses.

set rst = CurrentDB.OpenRecordset(strSQL)

wide_awake

Oh - one other thing you might need to do is put the SQL string in parentheses.

set rst = CurrentDB.OpenRecordset(strSQL)

wide_awake

Heh, it's pretty easy to submit the same comment multiple times by accident eh? :)

kiranghag

remove [] from the query

ASKER CERTIFIED SOLUTION

wide_awake

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexel

ASKER

Hey Mark

Sorry for the delay.
Anyway, it still doesn't work, but I'll give you the points for youre help

Thanks
Alex

wide_awake

If you type "CurrentDb." does it come up with a context menu?

if not, it's probably a missing reference.

If you go to the VB Window, and go to Tools -> References, which libs have checks beside them?

-Mark.