?
Solved

An easy VB MS Access 2000 question

Posted on 2003-03-12
22
Medium Priority
?
270 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:alexel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 

Expert Comment

by:undaground
ID: 8122539
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
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8122554
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
0
 

Author Comment

by:alexel
ID: 8122654
Mark

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

which is User Defined type not defined.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
ID: 8122856
Do you have a reference to the DAO object in your project????

Brett
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8122908
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.
0
 

Author Comment

by:alexel
ID: 8122992
I've added the reference but now i'm getting a syntax error on the Select function
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8123377
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
0
 

Author Comment

by:alexel
ID: 8123555
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
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8123632
What's the exact error you're getting?
0
 

Author Comment

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

Author Comment

by:alexel
ID: 8123743
After I'm clicking the command control, i'm getting a "compile error:syntax error" when the Select function is highlighted.
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8123769
What's the exact error you're getting?
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8123857
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.
0
 

Author Comment

by:alexel
ID: 8123975
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.
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8124054
if you type "CurrentDb." in the VB window, does it pop up a context menu with all the methods/attributes of CurrentDb?
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8124088
Oh - one other thing you might need to do is put the SQL string in parentheses.

set rst = CurrentDB.OpenRecordset(strSQL)

0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8124092
Oh - one other thing you might need to do is put the SQL string in parentheses.

set rst = CurrentDB.OpenRecordset(strSQL)

0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8124098
Heh, it's pretty easy to submit the same comment multiple times by accident eh?  :)
0
 
LVL 10

Expert Comment

by:kiranghag
ID: 8124786
remove [] from the query
0
 
LVL 3

Accepted Solution

by:
wide_awake earned 200 total points
ID: 8131337
any luck?
0
 

Author Comment

by:alexel
ID: 8136101
Hey Mark

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

Thanks
Alex
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8137086
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.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
The business world is becoming increasingly integrated with tech. It’s not just for a select few anymore — but what about if you have a small business? It may be easier than you think to integrate technology into your small business, and it’s likely…
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question