I am new to access and am having a little problem with a sub-form. I would like to be able to enter a product code and have access fill in the balance of the fields with information from a table. There is a product code, product desc, unit price. I can get the unit price to fill in but the prod desc always comes up with "#Name?". The other unit price fills in because I am using an "afterupdate" event. Hope this is enough information. Please help?
If your problem is simpler than that, and you just wish for Access to populate another field based on the value of a field you've entered (whether on a form or subform) - you can either use dlookup (see Access help for this method) or use a query to pull this value (but you can only return one value, so make sure the query only pulls the first matching value). Once again, if the field you're populating is on the main form and the field this value is being based on is on the subform, you'll still need to reference the subform's fields correctly using the article above.
Actually - the link above comes from a general Access help page that is very useful for beginning Access developers because it deals with many of the common issues we all hit when first starting out (such as referencing fields on a subform). The link to this page is http://www.kayodeok.btinternet.co.uk...ccesshowto.htm.
Thanks for your help. I am trying to populate other fields here is an example:
I select thru a combo box a product code. I want it to populate the description which is in a table called Store Items (StoreItemID, Product Code, Product Description, Unit Price). When I select in the combo box the product code, the unit price comes up with the proper information because of afterupdate event(VB) telling it where to find the info for the unit price. But I tried to do this also for the description, but it won't do it. Maybe has to do that the unit price is a number and the other is a text? I will look on the link you gave me. I hope I didn't confuse you more. It is a simple thing but I am confused at this point.
So the On Change event for your ProductCode combo contains code resetting the row source for the description combo?
Something like this:
strSQL = "SELECT DISTINCT [Product Description] from [Store Items] where [Product Code] = " & Forms!Form2.Combo0
Combo2.RowSource = strSQL
Remember, this is a combobox - so it will remain blank, but when you click on it the selector the possible choices will spill down.
If you actually want to populate the description field with the corresponding description (ie - no choice necessary from the user), simply set the value of the field. If the field is named comboDescription, the Change event for the product code field will contain the following line:
comboDescription = "SELECT FIRST([Product Description]) FROM [Store Items] WHERE [Product Code] = " & Forms!Form2.Combo0
You can also use a DLookup here (whichever you're more comfortable with).
Okay I finally got this to work using the DLookUp function...THANKS. But now I have another dilemma. This took place on a subform. The subform works fine, but when I go to my main form which has the subform as part of it, the field that I updated in the DLookUp function doesn't work and leaves the Item Desc as #Name?. Is there something I need to change on the main form. I don't understand why the subform works but when it is on the main it doesn't.
Your help is appreciated as this will be the basis to many of the things I will do for my database that I am trying to set up for my business.
Odd. If the control that's changing the subform's combobox is coded wrong it shouldn't even work in your subform. Just to make sure we're not getting stuck on the subform reference issue - I've thrown together a quick sample form & subform (form1 containing combo3 and a subform control named SampleSubform referencing form2 as a subform containing combo0).
In this example, I want the subform's combobox to be populated with the selection I choose in the main form's combobox. To accomplish this, I add the following code to the Change event of the main forms combobox (form1.combo0):
Okay, I see your illustration or example. My situation is that I am selecting an item on the subform and want it to fill the description based on this selected item on the subform. It works when I just open the subform. But when I open the main form with the subform on it, the place where the description is has "#Name?". Here is the code I have in the description box on the subform:
This I know is getting crazy, but what is weird I changed what you told me and now I have an error not only in the main form which has the subform on it, but when you open the subform by itself it also has an error. I looked at the code for the Subroutine for afterupdate, but that confuses me. On this subform there is another field (currency) called Unit price and I use the following to update that when the StoreItemID is selected.
Private Sub Store_ItemID_AfterUpdate()
Me.UnitPrice = Me.Store_ItemID.Column(3)
This works to update the unit price. The field before is called ItemName, but thats where I get the error.???
Thanks for your help and effort I am sure we will get this over with soon.
I had problem with access database (mde format) at server, workstation is windows xp, we can open this file simultaneouly from diff pc, but since we upgrade windows xp to sp2, we can not open it simultaneouly, only one pc can open it.