Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please advice.

    I am a novice at working with VBA and complicated Access implementation. I am working in Access 2003.

    I have created a form that after the user enters the customer number in a text box it runs a macro that updates the ownership code in the table. Every customer number has an associated ownership code, but the user does not know the ownership code, only the customer number. I have a table that has all the customer numbers and the associated ownership codes. Unfortunately, the ownership code does not appear on the form in the text field after the macro has run. The table has the ownership code after the macro runs, but the ownership code does not appear on the form. Is there a way to have the ownership code appear in the text box without closing the form?

    Thanks for your help!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are you working with VBA or are you using a macro? If you are using VBA, you can do either a Refresh or a Requery on the form and it should then display the value. If you are using a Macro, then you can use the Requery Action and specify the control name for the ownership code. If you need further assistance, post your VBA Code or Macro and we'll try to give you a hand.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I attempted to use the Requery Action, but I think I maybe missing something.

    Attached is a sample database.

    I added the Requery Action to the macro. I was unsure how to assign the control name. When I did not assign a control name it worked for the first entry in the form, but every subsequent entry in the form brought up the first entry.

    Thank you for your help.
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a revised version. This does not use your macro nor the query.

    I have changed the textbox where you enter the MBR into a combo box that lists the MBRs, and their corresponding OwnerIDs, then uses the After Update Event of the combo to set the value of the OWNERID to the value in the second column of the combo.
    (Column numbers are zero based, so the second column is Column (1))
    [attachment=89075:SampleJH.zip]
    Attached Files Attached Files
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John - Thanks for your help!

    Unfortunately, the sample I provided only listed 2 Customers, but the actual database I am working with have over 17,000 customer records and owner IDs, so I think it would be too difficult to have the user work with a combo box.

    Please let me know if there are any other suggestions.

    Thanks!

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The alternative is to do a lookup:

    Code:
    Me.OwnerID = DLookup("OwnerID", "tblOwnershipCode_ShopStatus", "[MBR]= " & Chr(34) & Me.MBR & Chr(34))
    but the MBR entered might be a mistake, so better would be

    Code:
    Me.OwnerID = NZ(DLookup("OwnerID", "tblOwnershipCode_ShopStatus", "[MBR]= " & Chr(34) & Me.MBR & Chr(34)),"Not Found")
    But I suppose the bigger question is why are you duplicating two fields between two tables. In general that is something you should avoid.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    kgudgel

    It does seem that John has set this up the easiest way.

    Have you tried it? You can still input the customer number into the combo box. The combo box will find it the listing, and the rest happens from there.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  8. #8
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes. This worked. Thanks for your help, John!

    The reason this is needed is because each customer (MBR) has a specific set of zip codes tied to the MBR and each OwnerID can have multiple MBR tied to the OwnerID.

    For example:

    OwnerID = 1234
    MBR = 12-3456AA
    Zips = 12345, 12346, 12347
    MBR = 12-4578AA
    Zips = 12348, 12349, 12350

    When the user enter the database they only know the MBR and they want to know all the zip codes under the OwnerID umbrella. I have set up a subform on the form that displays all zips codes (12345, 12346, 12347, 12348, 12349, 12350) based on the OwnerID that is located based on the MBR entered in the form.

    But maybe I have went about getting this data the totally wrong way? Not sure.

Posting Permissions

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