Preserving answer from previous record?

[floridaboy2004]

Hello all,

I have a quick question:

I have a form for 'BRANDS OF PREFERENCE." On this form, I have several fields, including an OPEN BOX with a drop-down list of about 200 brand names (e.g. LEVI's, NIKE, REEBOK, etc.). This form is linked to a parent form with more biographical information about the customer.

Now my question: Is there a way to set up a MACRO, etc. for the open box/form so that if I select "REEBOK" on Record #1 on the subform to become pre-populated with "REEBOK" again if I create a 2nd new record for the same customer?

e.g. Record # 1: Open Box--> REEBOK selected
Record #2: Open Box--> REEBOK (pre-populated)
Record #3: Open Box--> REEBOK (pre-populated)

etc.

FYI: The parent form is named 'CUSTOMER' and the 2nd form is named 'CUSTOMER BRANDS'. I am already pre-populating the 'CUSTOMER BRANDS' form using the customer # from the 'CUSTOMER' form but am unsure how to achieve this ONLY if there is a brand selected in the previous record on the 'CUSTOMER BRANDS' form.

I have found great levels of brand-loyalty and would like to save this added step since I have 15< items/customer.

Thanks again to everyone for helping.

Happy (early) Thanksgiving!

 

[floridaboy2004]

Can anyone PLEASE help me with this? I'm begging....PLEASE?

-Thanks!

 

[Corticus]

The easiest way I can think of would be to create a button that starts a new reocrd. You'll have to put the button on the parent form, since it sounds like your subform is in datasheet view. This procedure will store the brand selected for the current record before beginning a new one.

I have used the name cmbBrand to represent whatever control you are using to hold the brand name, adjust accordingly. selBrand is a variable, you can dimension and set it's type according to your needs, though it's not necessary.

The button will execute code something like:

selBrand = [customer brands].Form![cmbBrand]
[customer brands].form.setfocus
docmd.gotorecord,,acNewRecord
[customer brands].Form![cmbBrand] = selBrand

If you don't want to use a button, you can use a global variable to hold the value of the brand selected. Update this value when the control holding this value is updated, so that the last brand entered is always the current value of this global variable. Then, you can use the subforms current even to test for the value of your ID field. If it is null, this should indicate a new record, at which point you can update the brand control with the global variable's value.

Hope this helps,

 

[floridaboy2004]

Thanks for the reply, Corticus...

Do I need to do this as an 'Expression Builder," 'Macro Builder,' or 'Code Builder?' Also, when I click on 'Properties' for the new button, what field should I enter this new code under (e.g. ON CLICK, ON GOT FOCUS, ON EXIT, etc.)? I currently have the following button on the 1st form titled 'Customer'; on that form is the following button:

"Add Customer Brands'

Under 'CustmrNumbr"-->Properties, I have

"On Enter" + "On Got Focus":

[Forms]![CUSTOMER]![CustmrNum]=0 (Condition)
SetValue (Action)
[Forms]![CUSTOMER]![CustmrNum] (ITEM)
[Forms]![CUSTOMER]![CustmrNum] (EXPRESSION)

This has allowed me to 'pre-populate' the Customer # from the previous form that has a button that opens the 'CUSTOMER BRANDS' form.

However, I would like to do as you suggest below and ADD A BUTTON on the 2nd form ('CUSTOMER BRANDS' form) that preserves/prepopulates the next new record created by clicking on a created button on the 'CUSTOMER BRANDS' form.

Could you PLEASE walk me through in as much detail as possible (what menus, etc) I need to create such a button? I want this to work as follows:

I click on the 'ADD CUSTOMER BRANDS' button on the 1st form, which then opens up the 2nd form titles 'CUSTOMER BRANDS.'

The Customer # is already pre-populated using the above code on the 1st record for the 2nd form, but the 'Customer Brands' field is blank (for the 1st record).

However, after I select 'NIKE' on the 1st record on the 2nd form, I would like to 'preserve 'NIKE' & the Customer # from the 1st record when I click on the 'ADD MORE CUSTOMER BRANDS', and if I enter 'REEBOK' on record # 3 on form 2, to preserve 'REEBOK' and Customer # from record #3 on record # 4, etc.

In general, I need to pre-populate ALL form #2 records with the same Customer # from the referring 1st form, and to preserve the selection for 'Customer Brand' from only the form #2 Immediate Predecessor/Previous Record for ALL subsequent records.

Thank-you again for your time & help...I REALLY appreciate any and all advice that you (and any other experts) can offer.

Many thanks!

 

[Corticus]

Use the code builder for the button, and use the on_click event.

I mentioned above how to move values around from one control to another. As far as opening a form, use the

docmd.openform "form name"

method.

To create a button, use the control toolbox, select the button icon, and place the icon on your form.

There are many ways you can get where you need to go, if you ask me specific questions it may be of more use to you.