Results 1 to 9 of 9
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

    Using a combobox to populate a text box... or something better

    I have a form for purchase orders that I need help with.

    I have a Vendors table which is a list of all the vendors we use and have accounts with. I want to be able to assign a vendor to a PO. This sounds easy, however I would also like to be able to type in a vendor's name which is not in the vendors list. We do sometimes issue a PO for a vendor that we will only use once and don't want to add to our vendors table. I have a Form frmPurchaseOrder connected to a Table tblPurchaseOrder which has a VendorName field.



    My current idea is to have another form that pops up using the OnEnter event of the VendorName field on the frmPurchaseOrder. The popup form would just be a combobox to lookup a vendor. Then I'm hoping I can have that vendor's name populate the field on the first form. Or, if we're using someone not in the vendor table I can just type in a name and it would save into the VendorName field of the tblPurchaseOrder.

    I can't seem to figure out how to get the value from the combobox in the popup form to populate the frmPurchaseOrder.

    If anyone has any ideas, it would be greatly appreciated. Or if there's a better way to do this, I would appreciate that also.

    Thank you!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why not add that Vendor to the Vendor table? I'd like to hear the rationale.

  3. #3
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Quote Originally Posted by orange View Post
    Why not add that Vendor to the Vendor table? I'd like to hear the rationale.
    I don't know if this is good rationale or not, but... We don't want to add the vendor to the vendor table because we want to keep the vendors table clear of those we aren't wanting to use again. If we include everyone we use once and never again, the list would get really long for possible vendors.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How long is too long?

    Do you keep any other info for these "one time vendors"?

    You might consider a Union query as rowsource for your combo.

    This could use a tmp table to record those 1 time vendors

    SELECT VendorID, VendorName FROM [Vendor] ORDER BY [VendorName]
    UNION
    Select 9999,"FlyByNight" as VendorName from tbltempv;

  5. #5
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Quote Originally Posted by orange View Post
    How long is too long?

    Do you keep any other info for these "one time vendors"?

    You might consider a Union query as rowsource for your combo.

    This could use a tmp table to record those 1 time vendors

    SELECT VendorID, VendorName FROM [Vendor] ORDER BY [VendorName]
    UNION
    Select 9999,"FlyByNight" as VendorName from tbltempv;

    All we do for these one time vendors is fill in the name.

    I'm pretty new to Access, so I'll google and look into a Union query. Thank you.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you create a table for these 1 time vendors, you will have a permanent record.

    My table for testing was
    tblTempV
    vendorId number
    VendorName text

    Then this table was populated with the temp vendor

    The union query
    Code:
    SELECT VendorID, VendorName  FROM [Vendor] ORDER BY [VendorName] 
    UNION 
    Select 9999,"FlyByNight" as VendorName from tbltempv;
    was used as the Rowsource of the combo.

    The tempVendor now shows in the combo.

  7. #7
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Is there a way for the temp vendor not to show in the combobox and still exist?

  8. #8
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Never mind... Now that I've thought about that question, it's a non-issue. I do want to see all the vendors I've issued Purchase Orders for.

    Thank you.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just had another thought if you really don't want to save the vendor in a vendor table. I still think you'd need it on the PO.

    Your financial people would want it.

    Anyway, there is a technique in other database systems to get info from a "system table". There is no such table in Access.

    BUT this works

    Code:
    SELECT VendorId, VendorName FROM vendor ORDER BY VendorName 
    UNION 
    Select 9999,"FlyByNight"  from (select count(*) from MSysobjects where 5=9)
    This (select count(*) from MSysobjects where 5=9)
    just gives the syntax to make the SQL valid. Since 5 never equals 9, no records are involved in MSysObjects.

    The temp vendor will show up in the combo for selection.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-14-2015, 07:56 PM
  2. Text box to populate from combobox
    By Master Klick in forum Forms
    Replies: 2
    Last Post: 03-15-2015, 06:39 PM
  3. Replies: 1
    Last Post: 02-01-2015, 12:16 AM
  4. Replies: 1
    Last Post: 01-16-2013, 03:32 PM
  5. Populate a text box based on a combobox selection?
    By Richie27 in forum Programming
    Replies: 4
    Last Post: 04-25-2012, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums