Auto Fill based on Combo Box

[Stephanie T.]

I have a subform (called StoreProducts) that my data entry person enters the products that are carried by specific stores. She chooses the products from a Combo Box. Since all of the products we manufacture are already entered into a "Products" table, this is easy to do.

When these products were originally entered into the “Products” table, they were also assigned our out-the-door-price.

I would like it so that when the data entry person picks a product off the Combo Box in the subform (StoreProducts) the price that is already in the “Products” table automatically fills into the Price box on the subform (StoreProducts). How do I do this? I don’t need to create another combo box, I’d like it to auto fill .

Thanks,
Stephanie

 

[vangogh228]

I have the same issue with a table of Agents and their Phone number, in trying to enter an Agent from a form combo box and have the Phone number pop in automatically. I have read many of the previous threads and I am just not getting it... Help is appreciated!!!

Here are the pertinent fields:

Auction Table
Agent
Date
Address

Agent Phone Table
Agent
Phone

Auction Form
Agent
Phone

The Auction Form, with all its appropriate fields, fills the Auction Table, but the Phone field is stored only in the Agent Phone Table. I have written a Query that pulls [Auction Table]!Agent and [Agent Phone Table]!Phone but I don't know what to do next.

 

[Pat Hartman]

Stephanie T.,

Modify the combobox's rowsource query to include the Price field. Don't forget to change the column count and column widths properties to include the new field. Then in the combo's AfterUpdate event put code similar to:

Me.YourPriceField = Me.YourCombo.Column(2)

This assumes that the price field is the third field of the combo's rowsource. The Column property is a zero based array so the first column is .Column(0), the second is .Column(1), etc.

vangogh228,

Your problem is different because phone number should NOT be stored in the AuctionForm table. You should remove the column from the table and obtain it via a join to the AgentPhone table whenever you need it. In the above example, price is stored when a product is ordered because you want the price in effect at the time the product was sold. You do not want to call up old records and see the new price. In your case, you always want the current phone number so storing it in the AuctionForm table is wrong because if the number is changed in the AgentPhone table, you always want to see the new value.

To solve your problem, base your form on a query that joins AuctionForm to AgentPhone. Select all the fields that you need from both tables. Set the locked property of the Phone field on this form to Yes because you don't want it to be accidentially updated on this form. When you type a value in the Agent field (or select one from a combo), the phone number will populate automatically.

 

[vangogh228]

Pat:

Thanks for the help... but is the query as I described the one I need? On my form, the field is there for display only. I have the number in the agent phone table for the query to access and fill the form field. In the form's phone field, is there code I would write to requery the info and have the phone pop in based on my phone query?

 

[Pat Hartman]

The form needs to have as its recordsource a query that joins the AuctionForm and AgentPhone tables. That way columns from BOTH tables are available to the form. Look up AutoLookup queries in help for a description of how the auto fill works.

 

[Stephanie T.]

Hey Pat,

Thanks for coming to my rescue once again.

I will assume that I am modifying the Combo Box in the form and not the table. I usually set up a combo box in the table prior to creating the form. Let me know if that makes a difference, also I am on Access 97 if that helps.

Do I put the AfterUpdate code in the Product field properties or the Price field properties? I have tried both, and nothing happens in the Price field because I have no combo box linked to it, it is not controlled by any existing data at this point, since the price is currently reflected as a second column in the Product field's Combo Box. When I put it in the Product field properties, Access tells me it cannot find the me. macro and that I have to save my macro sets – huh?

Thanks,
Stephanie

 

[Pat Hartman]

The code goes in the AfterUpdate of the combo. The price field should be locked unless you want to allow updates to it on the form that will override any value from the product table.

I NEVER put combos in tables. I find that it messes up my queries since I end up with the text value rather than the numeric code value.

huh? is right. Try deleting the queryname from the form's recordsource. Save the form. Then put the queryname back. This forces Access to refresh the form's field collection. That might fix the problem.

My granddaughters (3 year old twins) liked the cookies Thanks again.

 

[vangogh228]

I can't believe I struggled with this after realizing that I already know how to do this!!

Write a query from your two tables. Include in the query the field on the "1" side of the relationship that you want to autofill. Then include ALL the fields from the "many" table. Base your input form on the query... and by having ALL the "many" fields available, you can do input into the table through the query, but you don;t have to input the information into the field from the "1" side.

 

[Stephanie T.]

Hey Pat,

Thanks for the suggestion about putting the combo info on the form and not on the table. That clears up that problem well! I also like the idea of having the tables be as simple as possible so that changes can happen in the forms or queries rather than the tables. It seems that whenever I have to make a change in a table I am re-doing the form and it’s just a mess. I am in the process of fixing all of my tables and then will get back to the auto fill question as that is still unresolved.

I have another question related to making these changes. I have some text boxes that were set up as “value lists” I would input the two options the data entry person has for example: domestic or international, then the data entry person simply picks the one for each distributor. How do I create a “value list” in a form?

Glad the grandkids enjoyed the cookies!


Vangogh228 – thanks for the suggestion, I will try that and get back to you. It sounds simple enough.

Thanks again,
Stephanie

 

[Pat Hartman]

When you add a combobox to a form, the wizard lets you choose whether to base the combo on a table/query or value list. If you choose value list, you can define the values right in the combo. This works well enough it you don't have too many different code fields and the code fields don't appear on multiple forms.

A number of years ago, I developed a table/form/report to manage ALL my simple code fields. ie those that simply had a code value (autonumber) and a descriptive text field. The table is recursive in that it includes a list of the code tables contained within the code table and has a relationship with itself.

 

[Stephanie T.]

Hey Pat,

Thanks for the suggestion. I think that creating a code table may be the way to go. I have three different tables/forms that need to know the pricing and if I can maintain pricing in one place that would make my life much more simple. That way when we change prices, I can simply change it in the code or "price" table and then it is automatically updated in any of the combo boxes that it relates to. But I don't understand how to set up a relationship with itself and what do you mean that yours has a code table within a code table?

Thanks,
Stephanie

 

[Pat Hartman]

Your price should come from a product table. The code table that I was referring to was for real code type data such as sex, hair color, department, etc.

If you're still having trouble with the autofill, take a look at northwind.mdb or orders.mdb. They pick up proce from a product table as well as description.

 

[Stephanie T.]

Pat,

I am still having trouble with the auto fill. I took the following code from the AfterUpdate event in the Northwind Order Subform. This Subform is doing exactly what I want to do. So, I changed some stuff in my table to be set up just like theirs and changed some stuff in my form to be just like theirs. I changed the names of the fields in the Expression to reflect my fields. Here is what I have:

Private Sub ProductName_AfterUpdate()
On Error GoTo Err_ProductName_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductName = " & Me!ProductName

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductName_AfterUpdate:
Exit Sub

Err_ProductName_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductName_AfterUpdate

End Sub

But now I get this error:

Syntax error (missing operator) in query expression ‘ProductName=Lemon Shortbread’.

That is when Lemon Shortbread is the chosen item. Any ideas to fix this? The goal is to choose the flavor in the combo box (easy enough), and then have the Unit Price auto fill.

Thanks,
Stephanie

 

[Pat Hartman]

I'm sorry for pointing you there since it is doing it the stupid way (ie with DLookup() rather than using an aditional field in the combo's recordsource). but in any event, your problem is most likely that your produce name field is text. Text strings need to be surrounded with single or double quotes.
strFilter = "ProductName = '" & Me!ProductName & "'"

 

[Stephanie T.]

Pat!

You are the man of the hour!! It worked!! Thank you, Thank you, Thank you!!!

Stephanie