Unanswered: listbox to pop up a form based on selection
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.
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
is not empty after selection, then based on selection it will open a frm
the name of my creditcard form, right?
"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
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:
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
Here are my table structures:
Payment Methods table:
Primary Key - Payment MethodID
field - Payment Method
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)?
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:
If Me.MyListBox.Value = 1 Then
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.
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."
Also on your code:
If Me.MyListBox.Value = 1 Then
Should this go into a click event or behind a 'command' button, like your command button2 (your earlier suggestion)?
thanks so much for your help...Now on to the next issue...