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.

 

[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?

 

[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

 

[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

 

[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

 

[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

 

[Stephanie T.]

Pat!

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

Stephanie