Queries in VBA

[Nina]

Hey Guys,

I hope you can help me with my problem.....

I am developing a database system which deals with the allocation of equipment to engineers. Because each item of equipment can only be allocated to one engineer, a previous allocation for the item is deleted when it is allocated to a new engineer.

A request for a piece of equipment can also be made, so when the holding engineer has returned an item, it can be assigned to the engineer that requires it next.

Only one person can have a request on an individual item. Because of this, i need a constraint to only allow a request to be created for an item that has not already been requested.

So far, i have a query that counts the number of requests for the item in question. The query returns either a count of 1, or nothing (if there are no existing requests). What i'd like to do now, in vba, is something along the lines of:

docmd.openquery (qrycountrequests)
if qrycountrequests.(name of quantity field) is null then
save request
else
msgbox ("Sorry, this item has already been requested")
endif

I am having problems with the bit that pulls the result of the query into the code....... i've read some stuff about DAO recordsets, but i'm a bit of a numbskull when it comes to VB......

I am using Access 2003 in 2000 format.

Any help would be much appreciated....... or even any suggestions as to other ways i can have this constraint work.

Thanks in advance

 

[Nina]

Thanks, but having played with Access for a while now....... i'm yet to ever
get anything productive out of the Expression Builder........ any other
suggestions?

I don't use vb but you can have a macro run the query and then use the

build expression to test your value and then use the msgbox to display the
info need.