Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Unanswered: Basic Access Help

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    debit,

    If I understand your problem, you are trying to reference a value on a subform field to the main form. If this is correct, please see the following link:

    http://support.microsoft.com/?kbid=208998

    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.

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    So the On Change event for your ProductCode combo contains code resetting the row source for the description combo?

    Something like this:

    Combo0_Change()
    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).

  5. #5
    Join Date
    Jan 2005
    Posts
    23
    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.

    THANKS!

  6. #6
    Join Date
    Nov 2003
    Posts
    167
    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):

    Forms!Form1!SampleSubform.Form!Combo0.Value = Me!Combo3

    Note the SampleSubform.Form reference above. The subform's name is "form2", but the name of the subform is irrelevent, because I reference it through the name of the subform control on the main form.

    I just want to make sure that you are not changing the design of the subform independently of the main form by directly referencing the form's name instead of the subform control's name.
    Attached Thumbnails Attached Thumbnails Clipboard02.jpg  
    Kit Lemmonds

  7. #7
    Join Date
    Jan 2005
    Posts
    23
    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:

    =DLookup("[ItemName]","StoreItems","[StoreItemID]="&Forms!sbfCustOrders_
    !StoreItemID)

    Hope this is clear.

    THANKS!

  8. #8
    Join Date
    Nov 2003
    Posts
    167
    debit,

    Try:

    =DLookup("[ItemName]","StoreItems","[StoreItemID]='"&Forms!sbfCustOrders_
    !StoreItemID & "'")

    (That is - surround your variable with single ticks within the string.)

    Using a table with Name (string), Description (string) and Number (integer) as my dlookup source, I got the form/subform working with the following code:

    Private Sub Combo0_AfterUpdate()
    Dim testTxt As String, testNum As Integer
    testTxt = DLookup("[Description]", "sourceTable", "[Name] = '" & Me.Combo0 & "'")
    testNum = DLookup("[Number]", "sourceTable", "[Name] = '" & Me.Combo0 & "'")
    Forms!mainFrm.mainDescCombo = testTxt
    Forms!mainFrm.mainNumCombo = testNum
    End Sub

    In general, I try to avoid access's D functions (DLookup, DMax, etc) as they require a full table scan. If your key table is very large, I would suggest using SQL to change the source of the control.
    Attached Thumbnails Attached Thumbnails Clipboard01.jpg  
    Kit Lemmonds

  9. #9
    Join Date
    Jan 2005
    Posts
    23
    Okay,
    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)
    End Sub

    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.

    Debit

  10. #10
    Join Date
    Nov 2003
    Posts
    167
    Debit,

    Perhaps the easiest thing to do would be tell me the names and datatypes of all controls on both forms, where each control is getting their data from, and what events cause them to change.

    Alternatively, you can send a private message to me (click on my username in this forum) and I'll respond with an email address that you can send a zipped version of the db to.
    Kit Lemmonds

  11. #11
    Join Date
    Feb 2005
    Posts
    1

    open access mde file

    Hi,

    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.

    Please help if anyone could!

    Thanks in advance!!

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •