Results 1 to 7 of 7
  1. #1
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28

    Call function on textbox entry and then save to table

    Hi All,
    I have a public function called UPCE2UPCA that converts short UPC codes to full expanded ones. It works fine, because I use it elsewhere in this DB. I have a form where the user will enter a new product and I want to:
    1) convert the UPC entered into a full, expanded UPCA
    2) Compare the expanded version to my database to see if it's already in there
    3) If it's not in there, save the expanded version to the table

    The table name is ItemList and the field is call ItemUPCCode. The textbox name is ItemUPCCode, as is the control source. Here is my VBA so far, which is being called on the before update event of the text box:


    Private Sub ItemUPCCode_BeforeUpdate(Cancel As Integer)
    Me.ItemUPCCode = UPCE2UPCA(Me.ItemUPCCode)

    If DCount("*", "ItemList", "[ItemUPCCode]='" & Me.ItemUPCCode & "'") <> 0 Then
    rslt = MsgBox("This UPC is already on file", vbOKOnly)
    Me.Undo
    Else
    Me.ItemUPCCode.Value = UPCE2UPCA(Me.ItemUPCCode)



    End If
    End Sub

    This code returns a runtime error of '-2147352567 (80020009)'

    If I make the textbox a calculated field that displays =UPCE2UPCA([ItemUPCCode]) then is displays the expanded UPC code fine, but doesn't save it to the table.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    A. The Dcount will never be less than 0.
    B. you should use 2 textboxes,
    1 for the small UPC ccode,
    And 1 for the Expanded UPC that uses the small UPC.
    this box should be bound to a table field.

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can put in AfterUpdate of ItemUPCCode field:

    If not isnull(DLookUp([ItemPCCode], "ItemList", "[ItemUPCCode]='" & Me.ItemUPCCode & "'") Then
    Me.ItemUPCCode = null 'or can do the undo
    Else
    Me.ItemUPCCode = UPCE2UPCA(Me.ItemUPCCode)
    End If

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIRC, this error number has more than one possible message, which is why you should include at least part of it in your post.
    Depending on how you interact with the form and the specific message, you might still encounter problems. You might want to consider not assigning the result of the function to the control before you DCount it:
    Code:
    Private Sub ItemUPCCode_BeforeUpdate(Cancel As Integer)
    Dim strUPC As String
    
    strUPC = UPCE2UPCA(Me.ItemUPCCode)
    
    If DCount("*", "ItemList", "[ItemUPCCode]='" & strUPC & "'") > 0 Then 'note operator changed
     rslt = MsgBox("This UPC is already on file", vbOKOnly)
     Cancel = True
    Else
    'running an append query might be required instead
     Me.ItemUPCCode.Value = UPCE2UPCA(Me.ItemUPCCode)
    End If
    
    End Sub
    BTW, code tags make code easier to read. Often, the issue is that you can't assign a value to a calculated textbox. Sometimes you need 2 controls as suggested. One unbound one to do the calculation, and a bound one that refers to the calculated control.
    Last edited by Micron; 03-27-2017 at 02:34 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28
    Ok, as ranman suggested I added a new textbox and called it UPCEntry. I will have the user enter the UPC there, convert it to a full UPC code, compare it to the table to see if it's a Duplicate, and if not, then save it to the ItemUPCCode textbox before saving. I adjusted Micron's suggested code, which now looks like this:

    Private Sub ItemUPCCode_BeforeUpdate(Cancel As Integer)
    Dim strUPC As String


    strUPC = UPCE2UPCA(Me.UPCEntry)


    If DCount("*", "ItemList", "[ItemUPCCode]='" & strUPC & "'") > 0 Then 'note operator changed
    rslt = MsgBox("This UPC is already on file", vbOKOnly)
    Cancel = True
    Else
    'running an append query might be required instead
    Me.ItemUPCCode.Value = strUPC
    End If
    End Sub

    This doesn't return errors, but also doesn't return the converted UPC to the ItemUPCCode textbox. The record just saves with that field blank.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    Private Sub ItemUPCCode_BeforeUpdate(Cancel As Integer) 
    Dim strUPC As String
    
    strUPC = UPCE2UPCA(Me.ItemUPCCode)
    Whoops -the control doesn't hold the data yet in the BeforeUpdate event. If you're using the 2 control approach where one is not bound, the function call should be on the AfterUpdate event of the unbound control, not the bound one.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28
    That did it! Thanks a million everyone.

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

Similar Threads

  1. Replies: 22
    Last Post: 10-04-2016, 12:03 AM
  2. Replies: 8
    Last Post: 12-03-2015, 10:18 AM
  3. Replies: 3
    Last Post: 04-04-2015, 01:17 PM
  4. Save data from textbox to table
    By GrayWolf in forum Access
    Replies: 7
    Last Post: 03-27-2012, 09:21 AM
  5. Replies: 13
    Last Post: 09-14-2011, 07:19 AM

Tags for this Thread

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