Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005

    Unanswered: Entry form needs popup box to obtain more info..


    I need to add something to a data entry form to obtain additional information. My current form populates a products table. The form will populate multiple product records from this form with one add button.

    Example, I sell products logoed for many teams, so I have a vendor who makes a Birdhouse for several collegiate teams. So instead of re-entering the same info multiple times, it loops through based on the teams selected in a multi list. While most information is static for every product type, one piece of information isn't like the manufacturer's product code. Let's say for the birdhouse it is BH-ALA for Alabama, BH-AUB for Auburn and so on. Each different type of product from different manufacturers will have a different product code or SKU #. What I thought I would do would pop up a message type box letting me know which team we are dealing with with a window for me to enter the product ID and then hit enter and update the row with appropriate information from the form entry as well as the product code pop-up. Below is the code from my form currently..

    Dim dbsProducts As DAO.Database
    Dim a As Long
    Dim nbr As Long
    Dim fname As String
    Dim rstProduct As DAO.Recordset
    Dim rstTeams As DAO.Recordset

    Set dbsProducts = CurrentDb
    Set rstProduct = dbsProducts.OpenRecordset("tblProducts", dbOpenTable)

    For a = 0 To Me.List36.ListCount
    If Me.List36.Selected(a) = True Then
    If IsNull(DMax("[ProductID]", "tblProducts")) Then
    nbr = 0
    nbr = DMax("[ProductID]", "tblProducts")
    End If
    rstProduct!ProductID = nbr + 1
    rstProduct!TeamID = Me.List36.ItemData(a)
    rstProduct!ProductName = Forms!frmproductsEntry!ProductName.Value
    rstProduct!SupplierID = Forms!frmproductsEntry!Combo32.Value
    rstProduct!DropShipFee = Forms!frmproductsEntry!DropShipFee.Value
    rstProduct!ShipCharge = Forms!frmproductsEntry!ShipCharge.Value
    rstProduct!UnitWholesaleCost = Forms!frmproductsEntry!UnitWholesaleCost.Value
    rstProduct!UnitMSRP = Forms!frmproductsEntry!UnitMSRP.Value
    rstProduct!ystore_ID_KeywordNames = Forms!frmproductsEntry!ystore_ID_KeywordNames.Valu e
    rstProduct!ProductCategory = Me.List68.Value
    rstProduct!SupplierProductCode = Forms!frmproductsEntry!SupplierProductCode.Value
    Set rstTeams = dbsProducts.OpenRecordset("SELECT TeamName, TeamLeague, TeamShortName, TeamProductName, TeamKeywordName FROM tblTeams WHERE TeamID = " & rstProduct!TeamID)
    rstProduct!TeamName = rstTeams!TeamProductName
    rstProduct!ProductLeague = rstTeams!TeamLeague
    rstProduct!TeamShortName = rstTeams!TeamShortName
    rstProduct!CFGProductCode = "CFG-" & rstProduct!TeamShortName & "-" & Forms!frmproductsEntry!SupplierProductCode.Value & "-" & Forms!frmproductsEntry!Combo32.Value
    rstProduct!Yahoo = Forms!frmproductsEntry!Yahoo.Value
    rstProduct!Froogle = Forms!frmproductsEntry!Froogle.Value
    rstProduct!Ebay = Forms!frmproductsEntry!Ebay.Value
    rstProduct!Discontinued = Forms!frmproductsEntry!Discontinued.Value
    rstProduct!ystore_id = rstTeams!TeamKeywordName & "-" & rstProduct!ystore_ID_KeywordNames & "-" & rstProduct!ProductID
    rstProduct!ystore_code = LCase(rstProduct!ystore_id)
    rstProduct!ystore_path = rstProduct!ProductLeague & ":" & rstTeams!TeamName & ":" & rstProduct!ProductCategory
    rstProduct!ystore_path2 = rstProduct!ProductCategory & ":" & rstProduct!TeamName
    rstProduct!ystore_name = rstTeams!TeamProductName & " " & rstProduct!ProductName
    rstProduct!ystore_price = rstProduct!UnitMSRP
    rstProduct!ystore_sale_price = Forms!frmproductsEntry!ystore_sale_price.Value
    rstProduct!ystore_headline = rstProduct!ystore_name
    rstProduct!ystore_caption = Forms!frmproductsEntry!Text85.Value
    rstProduct!ystore_options = Forms!frmproductsEntry!Text91.Value
    rstProduct!ystore_ship_weight = Forms!frmproductsEntry!ystore_ship_weight.Value
    rstProduct!ystore_ypath = Forms!frmproductsEntry!ystore_ypath.Value
    rstProduct!ystore_my_keywords = rstProduct!ystore_name & "," & Forms!frmproductsEntry!Text89.Value
    fname = rstProduct!ystore_code
    fname = Replace(rstProduct!ystore_code, "#", "-")
    fname = Replace(fname, "&", "-")
    rstProduct!froogle_product_url = "" & fname & ".html"
    rstProduct!froogle_image_url = "" & fname & ".gif"
    rstProduct!froogle_name = rstProduct!ystore_name
    rstProduct!froogle_description = rstProduct!ystore_caption
    If IsNull(rstProduct!ystore_sale_price) Then
    rstProduct!froogle_price = rstProduct!ystore_price
    rstProduct!froogle_price = rstProduct!ystore_sale_price
    End If
    rstProduct!froogle_category = rstProduct!ystore_ypath

    'strtext = rstProduct!froogle_price
    'MsgBox strtext
    Name "C:\CFG\Vendor\" & rstProduct!SupplierID & "\" & rstProduct!SupplierProductCode & "\" & rstProduct!TeamID & ".jpg" As "C:\CFG\Export\" & fname & ".gif"

    End If
    Next a


    Thanks for your assistance..

    Troy Gregory

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    So what actually is the problem that you hitting, and are looking for assistance on?

  3. #3
    Join Date
    Jun 2005
    I need to have some kind of message box that gets my user input of the manufacturers item number and use this value to populate a field as well as be concatenated to another field. Currently all fields right now on the form are the same for each loop through. Example, Product Name is Birdhouse, that field is birdhouse for every team I choose in the team list. Unit Price, is the same for each birdhouse as it loops through for each team. The MFG # is the only field that is unique or could have a different value for each team.

    I'm not sure how to get this unique value inserted properly on each row. I know how to place a MSG box, but I don't know what to use to get user entry to obtain this value. Is there something like a MSG box that acquires user entry?

  4. #4
    Join Date
    Jun 2005
    I'm assuming I need to use an InputBox, I was just wondering if this is what I would use, or is there a better way of getting this individual information?

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    An alternative approach would be to populate a combobox with the list of teams in a list box, slect the required team(s) from the list box for a specified product type
    then press a button to writee your records to the table.

    you can iterate through the list box selected collection to find which teams have been selected.

    You can evolve ythe design to have 2 list boxes one teams available and one selected. (similar to the forms & report wizard column field selectors)

    this depends on whether you can make a commone coding policy
    Last edited by healdem; 11-27-05 at 05:36.

Posting Permissions

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