Vinesh

I have acouple of questions for u Pro Access 2000 users.

1 How would i make the search box disaapear on a form automaticaly, once it has found the record?

2 My d/b is based on a video shop. What validation rule do I need to use so that when I enter a member ID no. it checks that it is valid or not? I have tried =[Members]![MemberID] but I get a error message saying there is a mismatch, but it does find the record, then the data disappears.

3 how would I limit a number of videos allowed for rental? ie if I only want 2 rentals a day for each member.

Cheers

Vinesh

Crispin

1. If you are using the default Data Access for Access 2000 then you'll be using ADO.
What you need to do is Declare a connection or a recordset "Withevents", then you can trap the ExecuteComplete event of the connection object, or the Fetchcomplete event of the Recordset object, these fire when either an execute or a recordset retrieval complete.
if you want some sample code for the above lemme know....

Once the event fires you can set the visible property of the textbox.

2. Not sure what you mean here - do you want to check if the ID exists, or is in a valid format?

3. To limit the number of videos rented you may want to use a rentals table which might store the MemberID, the VideoID, DateRented, DateDueReturn, DateReturned. Then you can check when a person's member ID is entered to rent a video how many entries are in that table with a blank DateReturned (i.e. still out) for that particular memberID, that'll tell you how many they have out, and if the number is greater than or equal to 2 then don't let em rent any more.....

hth

Vinesh

Crispin-

2) Yes I want to check if the ID exists.

Cheers

Vinesh

Vinesh

Crispin-

Can u mail me some sample coding for question 1 please.

Cheers

Vinesh

Martingb

Vinesh,

For number 2, I would use on the after update event the dlookup function.

e.g. sub text1_afterupdate()
dim vntTemp as variant

vntTemp=dlookup("[MemberID]", "Members", "[MemberID]=" & me.text1.value)

If vnttemp <> me.text1.value then
msgbox "Member does not exist"
end if

end sub

Hope that helps!

Martin.

Vinesh

Martin-

Where would I type the code in? Do I type in at the back end off access? ie the vb coding side?

Vinesh

Crispin

You have mail Vinesh.

Jza

You dont need the "["'s around fieldnames unless theres a space in them. So you need brackets for [Member ID] but not MemberID

Assuming ID is a string:

SQL = "SELECT * FROM MEMBERS WHERE ID = '" & strID & "'"

then open a recordset - if not eof then theres no record.

Avoid dlookups if you can!!

Jza