Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: listbox to pop up a form based on selection

    Hello All,

    I'm yet another newbie (YAN)...I'm in need of help with my Order processing system..
    My Order form is based on (query) of Customer and Order tables (includes a subform for the product data)...has a listbox (values are check or credit card)...to store the payment type. What I'm looking for is a way to pop up a form based off either a credit card or a check selection. Either form would then store their data into the Payments table (used for report processing) i.e, paymentID, cardholder's name, exp date, last 4 digits of card, routing number, etc.........

    Help, does anyone understand what I'm trying to do and better yet, if someone has done this, could you point me to a sample form (similar)??? thanks so much.

    imrosie

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This might do the trick if I've understood correctly
    Code:
    If Not IsNull(Me.MyListBox.Value) Then
        DoCmd.OpenForm "MyOtherForm", , , "MyField = '" & Me.MyListBox.Value & "'"
    End If
    This is assuming that your second form (MyOtherForm) is bound
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    102

    pardon my ignorance

    Hello GeorgeV,

    Thanks so much for your quick reply....
    this looks simple enough that I should understand, but let me see if I understand what you're showing me.

    If the listbox, called paymnt
    Code:
    Me.paymnt.Value
    is not empty after selection, then based on selection it will open a frm
    Code:
    DoCmd.OpenForm "crdcarcheck"
    the name of my creditcard form, right?

    It's this
    Code:
    "MyField = '" & Me.MyListBox.Value & "'"
    that's not clear to me....

    is the "MyField" (referrring to control paymnt?),,,or to the selection check or creditcard? If its referring to the selection, do I need two 'DoCmd.OpenForm's so either form will open? thanks....again pardon the ignorance, newbie

    imrosie

  4. #4
    Join Date
    Aug 2007
    Posts
    102
    what I'm trying to ascertain is does the
    "MyField = '" & Me.MyListBox.Value & "'"
    pop up only one form?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You are completely right on the first 2 bits!
    Code:
    "MyField = '" & Me.MyListBox.Value & "'"
    This is the filter which can be applied when opening the new bound form.

    I've knocked up a quick example because I was feeling nice.

    Fire any questions back in this thread
    Attached Files Attached Files
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Posts
    102
    Wow GeorgeV,,,,
    You were feeling nice....I think I understand 99% of it....It was the 'command 2' part that I didn't see at first....

    The primary key in this case would have to be the 'paymentmethodID' and the foreign key, I believe would be 'paymentid?!?

    My Form (called Add an Order and Details) is the equivalent of your Form1 and currently it's based on a query(using Customers and Orders tables). For the listbox control (called paymnt) I bound it to the 'PaymentMethods' table, using control source like so:
    Code:
    =[Payment Methods]![PaymentMethodID]
    However, when I use your 'command2' button to pop up the form; the form (MyOtherForm="creditcarcheck" ) will be bound to the payments table...Do you see a problem with this structure as explained?
    Should I add the payments and paymentmethod tables to my query supporting my Form1 form (Add an Order and Details)?

    As for Primary Key and Foreign Keys as you laid out...I believe primary key (for paymnt control) is the PaymentMethodID and it would be the foreign key in the Payments table. Paymentid would be the Primary key in the payment table and all these keys would be tied to the customer by CustomerId and OrderID..

    I'm new so don't laugh so hard at my trying to understand your answer using the primary/foreign key terminology. thanks

    ImRosie

    thanks
    Last edited by imrosie; 08-02-07 at 14:45.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm afraid I don't quite follow!
    I'm having trouble picturing what you are wanting now...

    Fancy posting your table structures up here in the following format;
    TableName(PrimaryKey, ForeignKey, Field1, Field2 ... FieldN)
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2007
    Posts
    102
    Oops,,,I didn't mean to confuse you GeorgeV,

    Here are my table structures:
    Payment Methods table:
    Primary Key - Payment MethodID
    field - Payment Method

    Payments table:
    Primary Key - PaymentsID
    Foreign key - OrderID
    field - paymentamt
    field - paymentdate
    field - checkingacct
    field - routing number
    field - creditcardno
    field - bankname
    field - securitycode
    field - crdcardholdername
    field - crdcardexpire

    On my (Add an Order and Details = your Form1) supported by a query of Customer table and Order table fields. Only the listbox paymnts, is based on the Payment Methods table. I have that listbox where a user can select either to pay by Credit Card or Check, after an order is filled in. Now I have your command2 button which will allow me to open the second form 'creditcard'. the creditcard form (based on payments table) to store more transaction information.

    I wanted to open a "creditcard" form to store credit card data only, when 'Credit Card' was selected from the listbox...........and a separate form to store check no. and routing information when 'Check' was selected from the paymnts listbox........either form would be based on the Payments table however.

    Does this clear it up at all? I hope so...let me know.Another question is do I need a Payment Methods table at all, since I'm using a list box (which doesn't have to be based on a table or query)...Couldn't I just add a field to the Payments table called paymethod (and store the value)?

    thanks
    imrosie
    Last edited by imrosie; 08-02-07 at 16:58.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think I'm getting you!!
    Ok, so it sounds to me like one of the requirements is to open a different form based on the selection in the listbox, this can be achieved with simple If statements:
    Code:
    If Me.MyListBox.Value = 1 Then
     DoCmd.OpenForm "Form1"
    Else
     DoCmd.OpenForm "Form2"
    End If
    Now what you are wanting is to then save this second forms information after closing it. I can think of a few possible solutions;
    On your first form you have some hidden textboxes (Visible = False) that you can pass the values to from the second form when you close the second form. This would mean that all the data is in a single place when it comes to hitting the big "Submit" button on the main form.

    Alternatively you can use the value in the listbox to show/hide fields on the main form based on the selection - this would remove the need for a second form al together, but may not be as pretty.

    With regards to your last question - yes [I think] you should have a PaymentMethodID as a foreign key in your payments table.

    The good thing about having your payment method table is that it is a self supporting lookup. You don't want to be storing the word "credit car" hundreds of times, so the lookup is better normalization.

    Well done, I think you're nearly there!!
    George
    Home | Blog

  10. #10
    Join Date
    Aug 2007
    Posts
    102

    Regarding your first suggestion...

    Hello GeorgeV,

    You did get it! I think this first suggestion would be good.

    "On your first form you have some hidden textboxes (Visible = False) that you can pass the values to from the second form when you close the second form. This would mean that all the data is in a single place when it comes to hitting the big "Submit" button on the main form."

    To do this properly, I would have to add the payments table and payment method tables to the underlying forms query, which now only contains the Customer and Order tables, Right?

    Also, you're right on " You don't want to be storing the word "credit car" hundreds of times, so the lookup is better normalization."

    I don't.

    Also on your code:

    If Me.MyListBox.Value = 1 Then
    DoCmd.OpenForm "Form1"
    Else
    DoCmd.OpenForm "Form2"
    End If

    Should this go into a click event or behind a 'command' button, like your command button2 (your earlier suggestion)?

    thanks



    thanks so much for your help...Now on to the next issue...

    imRosie
    Last edited by imrosie; 08-02-07 at 21:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •