Forum - On Open query

smjohns

Registered User.
Local time
Today, 13:00
Joined
Mar 15, 2004
Messages
20
Hi,

I am very new to MS Access, so please excuse the potentially simple question.

I have created a two forms off the back of a DB which I have built. I am trying to add functionality which will enable me to auto-close the form on entry if data does/does not exist on a table. I have managed to work out how to auto close a form if no data exists by adding the following to the On-Open script.

If IsNull(Me!Transport_ID) Then
MsgBox "No invoices have been set up for this Transport Company. Use Create Invoice button to add a new invoice.", vbExclamation + vbOKOnly
DoCmd.Close
End If

This may not be the best way to do this, but it seemed to work for me.

The second form is proving to be more difficult. When entering the form I am passing accross a transport_id piece of data. What I want the screen to do on open, is to see if this ID appears on a table. If it does, I want the form to issue a message similar to the above one and then auto close. The only way I can think of, is to get the open event to run an sql query against the db, using the transport_id. Only problem is I am not sure how to return the result of that query, to enable me to use it in code.

If this is not possible on opening the form, can I use it on the previous screen, to protect a button, when a certain record (displayed in a list box), is highlighted. e.g. as the user selects records on the list box, it will check the db for each and if records exist, then the button is protected, if no records exist, then the button is enabled.

Thanks for any help you can provide

Kind Regards

Simon
 
The easiest way to check to see if records exist in a table/query is to run a Dcount against it. If you're clever, you don't even have to write any code to use it, though it's simple to use if you do. The basic structure of a DCount function is:
DCount("field_name","table_name", "criteria)

You can use it like this in code:
Code:
If DCount("myfield","mytable","myfield=" & txtIDNumber) > 0 Then
    'do this
Else
    'do this
End If
 
Cheers for the quick reply. I will have a play and see what I can come up with.

Kind Regards

Simon
 

Users who are viewing this thread

Back
Top Bottom