Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    6

    Cool Unanswered: Vba Code Help Pls....

    Hi!
    I'm kinda new here! and just a beginner... wanna learn more and more...
    Can u help me with these Access VBA Codes pls.
    i've got a sample table specs here... thanks...
    TABLE1 COMMODITY TYPE
    Field Type Width Description
    ComID Text 3 3 Character Commodity Type Code
    Description Text 50 Commodity Type Description

    Sample Entry:

    COMID Description
    AGR Agricultural Supplies
    ITS IT Supplies
    OFS Office Supplies
    MED Medical Supplies



    TABLE2 ITEM CODE
    Field Type Width Description
    ComID Text 3 3 Character Comdty Type Code ItemCode Text 8 Item Code = COMID+Counter
    Nomenclature Text 50 Item Name
    Description Text 50 Item Description
    Brand Text 30 Item Brand

    ***** Everytime a new item is entered, it is assigned an item code formatted as the Commodity Code + AutoNumber(Counter).
    1.How do I concatenate a numeric format with a string?
    2. Next thing is, the counter is applicable per COMID. Therefore, each commidity has its own counter.....
    Sample Entry:

    ComID ItemCode Nomenclature Description Brand
    AGR AGR-0001 Dog Food For Canine Dogs Alpo
    AGR AGR-0002 Hog Feeds Pre-Starter Purina
    AGR AGR-0003 Molasses For Horse Generic
    ITS ITS-0001 Hard Disk 40 GB Seagate
    ITS ITS-0002 Memory Module 512 MB Generic
    AGR AGR-0004 Dewormer For Piglets B-Meg
    MED MED-0001 Paracetamol Tablet, 250 mg Decolgen


    TABLE3 PURCHASEORDER
    Field Type Width Description
    PONo Number 6 6 Digit PO Number
    PODate Date Date
    Supplier Text 50 Item Name/Description
    Purchaser Text Purchaser

    TABLE4 PURCHASEORDERItemDetails
    Field Type Width Description
    PONo Number 6 6 Digit PO Number
    ItemCode Text 8 Item Code = COMID+Counter
    Qty Number Quantity of Item Ordered
    UI Text 2 Unit of Issue
    UnitPrice Currency Unit Price
    Amount Currency Qty * UnitPrice

    THE MAIN DATA ENTRY INTERFACE SHOULD LOOK LIKE THIS?

    PO Number:________ PO Date:___________
    Supplier: __________ Purchaser:__________

    PO Item Details Subform:
    Record1. Nomenclature __________ Description ____________
    Item Code : ________ Qty: ________ Unit of Issue: _______
    Unit Price : ________ Amount: __________
    Record2. Nomenclature __________ Description ____________
    Item Code : ________ Qty: ________ Unit of Issue: _______
    Unit Price : ________ Amount: __________

    ...

    RecordN...Nomenclature __________ Description ____________
    Item Code : ________ Qty: ________ Unit of Issue: _______
    Unit Price : ________ Amount: __________

    In this Subform ItemCode should automatically be displayed and/or generated by the database once the user types in the nomenclature and description matching a certain item code. However, if the combined nomenclature and description is not on the itemcode table, a certain ItemCode Data Entry form should be opened for the user to input the item not on the list.
    Perhaps, it would be more interesting if the options will be on combo boxes so that I could just click on the nomeclature on the first one and then the description on the second one.....


    'hope ud help me out with this... thanks...
    God bless everyone!
    nicklaus
    Philippines

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi nicklaus and "Welcome to the Forum"

    Just a quickie as it's late and about to head to sleep. For the part on Concatenating two fields try this:
    You have a Products table with ProductID (AutoNumber) and ProductName (Text) and want to combine (concatenate) those into one, do this...

    [ProductsID]&"-"&[ProductName]

    The dash (-) in between is optional and can simply be done without any character in between. Hope this helps you out. Oh, if the two are in different tables you might have to identify that like this...
    [ProductsID] is in the OrderDetails table as(FK) (just for the heck of it), and the [ProductName] is in the Products table as (PK)you might have to do this....

    [tblOrdersDetails].[ProductsID]&" "&[Products].[ProductName]

    if you have a One-To-Many link with a ForeignKey (FK) and PrimaryKey (PK) you have to add the table is if to come from with what I think is called a Qualifier. Anyhow, that's how that works and hope it helps you in part of your situation.

    hope you enjoy the forum,
    have a nice one,
    BUD
    niters all and see ya laterz......zzzzzzz....

  3. #3
    Join Date
    Feb 2005
    Posts
    6

    Thumbs up thanks

    hi ya!
    thanks for the code!
    it works!

Posting Permissions

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