How to open forms

[raghuprabhu]

Hi All,

I have attached a small database (Access 2003). This has table to show
customer details and order details.

It also has 3 forms and 2 subforms. The forms are as follows

frmAddNewCustomer is to input new customer
frmAddNewOrderDetails is to input new orderlines by customers
frmShowAllByCustNo which show the customers and the orders

In the table "tblCustInfo" I have three customers at present
ID IDNumber Surname GivenNames
1 2012001 Prabhu Raghu
2 2012002 Jain Sanjay
3 2012003 Kamath Sunil

In the table "tblPurOrder" I have the following data.



ID tblCustInfoID PurOrderNo purDate
1 1 201206001 1/06/2012
2 1 201206002 1/06/2012
3 2 201206003 1/06/2012
4 2 201206004 1/06/2012

when I open form frmShowAllByCustNo it prompts the user for the customer number. If I input 2012001 or 2012002 order lines will show.

If I input 2012003 there are no records to show.

My first question is how do I make a message box popup and give the message "This customer does
not have and order lines" and then the form "frmAddNewOrderDetails " popup when customer number 2012003 is input?

My second question is how do I make a message box popup and give the message "This customer does
not exist in the tblCustInfo" and then the form "frmAddNewCust" popup when customer number 2012004 is input?

Thanks
Raghu Prabhu

[June7]

Can have code like this behind a form but not a subform.

Private Sub Form_Load()
If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "No records"
    DoCmd.Close acForm, Me.Name
End If
End Sub

Better would be menu/switchboard form (or a navigation form, which I have never used) with buttons that will open the forms with macro or VBA code. The current setup requires opening the form directly from the navigation pane. The menu form would be set to open by default when the project opens.

You are also using query input parameter prompts. I never use those because cannot validate user input. Better to use forms with controls for user input of search/filter criteria then query parameters refer to the form controls for input. Entries to controls can be validated by code and properties.

Suggest you review some working projects to get an understanding of this structure. Check out the templates available from MS and other sources. A well-known one is the Northwind database. It is used extensively in tutorials.

Check out some of the video tutorials at http://datapigtechnologies.com/AccessMain.htm

[raghuprabhu]

Thanks will try to do as you suggested.

Raghu