By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

How to open forms....

P: 83
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 {Removed - Please check the rules}


Raghu Prabhu
Attached Files
File Type: zip (31.2 KB, 63 views)
Jun 2 '12 #1
Share this Question
Share on Google+
3 Replies

P: 33
Someone who probably knows a little more than me might also be able to give you some advice, but this is what I would do in you situation. For both questions, I'd use the DCount function to determine if there are records that contain the value you entered.

For your first question (assuming you're typing the ID number in a textbox control called TxtBox), I'd use something like:

Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "tblPurOrder", "[tblCustInfoID] = " & _
  2. DLookup("ID", "tblCustInfo", "[IDNumber] = " & Me.TxtBox)) = 0 Then
  4. If MsgBox("The selected customer does not have any order lines. Would you like to add an order?", vbYesNo + vbQuestion) = vbYes Then
  6. DoCmd.OpenForm "frmAddNewOrderLines", acNormal
  8. Else   'No is selected
  9. Exit Sub
  11. End If
  13. Else   'If there are order lines
  14. DoCmd.OpenForm "frmShowAllByCustNo", acNormal
  16. End If
For your second question, I'd pretty much use the same method (assuming that you're typing into TxtBox again):

Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "tblCustInfo", "[IDNumber] = " & Me.TxtBox) = 0 Then
  3. If MsgBox("This customer does not exist. Would you like to add this new customer now?", vbYesNo + vbQuestion) = vbYes Then
  5. DoCmd.OpenForm "frmAddNewCust", acNormal
  7. Else   'No is selected
  8. Exit Sub
  10. End If
  12. Else
  13. DoCmd.OpenForm ""   'You'll need to set this to what you want to open if the customer ID DOES exist.
  15. End If
I think I pretty much get the gist of what you're asking, but I'm a little confused as to how your forms are set up. You can obviously tweak what I wrote to make it work for you, but it gives you a basic idea of one process you could potentially use.

Good luck!
Jun 2 '12 #2

P: 83
Thanks JenniferM will try. I was working along those lines too.
Jun 2 '12 #3

P: 83
Hi Jennifer,

I have attached a slightly different version. I put a form named frmMainMenu

When I input 2012001 or 2012002 the form "frmShowAllByCustNo" is showing which is good.

When I 2012003 it should open form "frmAddNewOrderDetails" and when I input 2012004 it should open form "frmAddNewCust"

It is not happening. Please look at my coding. I used what you supplied earlier.


Attached Files
File Type: zip (26.0 KB, 57 views)
Jun 5 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.