[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Check for existing value in table

I have a subform that displays a table with 1 to many relationship.  One field in subform is combo box listing a number of item names.  I am using the AfterUpdate event to check if the item is already in the list.  Problem is by selecting the item the event now sees the item and returns invalid response.  If I try using the BeforeUpdate event, then get error BeforeUpdate preventing Access from saving......

qrypSizings querries items form table that match relationship to main form.  Any suggestions?
Private Sub cboSizingName_AfterUpdate(Cancel As Integer)
Dim bName As String
    bName = DLookup(Me.cboSizingName, "qrypSizings")
    If IsNull(bName) = False Then
        MsgBox ("Sizing " & bName & " already exists!")
        If IsNull(Me.cboSizingName.OldValue) Then
            Me.cboSizingName.Value = Empty
        Else
            Me.cboSizingName.Value = Me.cboSizingName.OldValue
        End If
    End If
    Cancel = True
End Sub

Open in new window

0
chobe
Asked:
chobe
  • 8
  • 3
2 Solutions
 
peter57rCommented:
"bName = DLookup(Me.cboSizingName, "qrypSizings")"


This command is trying to look up (in the first record of qrypSizings) the value of the field whose NAME is the value selected in the combo.
Is that what you intend?
0
 
peter57rCommented:
Or are you really trying to see if the value selected in the combo is already present in the query?
If so, then the syntax is..

DLookup("Name of any field in query which is never null", "qrypSizings", " matchfieldname = '" & me.cbosizingname & "'")
0
 
chobeAuthor Commented:
Peter;
Your code DLookup("[Project Number]", "qrypSizings", " matchfieldname = '" & me.cbosizingname & "'")produces the following error; The expression On Error you entered as the event property setting produced the following error: Precedure declaration does not match description of event or procedure having the same name.  If I rem out error procedure, it simply errors out on another sub or function generating the same error.  Also tried     bName = DLookup("Project Number]" , "qrypSizings" ,  "Me.cboSizingName").  Am I doing something wrong?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
chobeAuthor Commented:
Actually, I went back and tested my original code, and for some reason - it produces the same error....don't know what's going on.
0
 
chobeAuthor Commented:
I recreated the routine and is working again (invalid response but without error) with original code, but when I change to your code, now getting "You cancelled the previous operation"
0
 
peter57rCommented:
DLookup("Name of any field in query which is never null", "qrypSizings", " matchfieldname = '" & me.cbosizingname & "'")

You appear to have got..
"Name of any field in query which is never null"

but not
matchfieldname

which must be the name of the field in the query that has to match the value in the combobox

0
 
chobeAuthor Commented:

I tried dim MatchFileName, loaded variable with combobox list item and then executed code and still getting error "...cancelled previous operation".  Am I still doing something wrong?  Attached are AC97 and 2000 copies of subform
Subform.zip
0
 
chobeAuthor Commented:
Got it!  I may figure this thing out yet.  Changed code to following....
Private Sub cboSizingName_AfterUpdate()
Dim bname As Variant
    bname = DLookup("[szi_ID]", "qrypSizings", "szi_ID =" & Me.cboSizingName & "")
        If IsNull(bname) = False Then
        MsgBox ("Sizing " & Me.cboSizingName.Column(1) & " already exists!")
        If IsNull(Me.cboSizingName.OldValue) Then
            Me.cboSizingName.Value = Empty
        Else
            Me.cboSizingName.Value = Me.cboSizingName.OldValue
        End If
    End If
    Cancel = True
End Sub

Open in new window

0
 
chobeAuthor Commented:
Actually, I do not wish to close the question at this time....I just discovered another error in the same code.  I don't think I'll ever get the hang of this.....

Code does prompt if existing item is selected but if I leave the field after getting the warning, I now get error "The MS Jet DB engine cannot find a record in the table SizingNames with key matching fields 'szi_ID'.  Assume I need to somehow prevent access from generating error?  correct?
0
 
chobeAuthor Commented:
Modified code again and thing its working now
    bname = DLookup("[szi_ID]", "qrypSizings", "szi_ID =" & Me.cboSizingName & "")
        If IsNull(bname) = False Then
            If IsNull(Me.cboSizingName.OldValue) Then
                MsgBox ("Sizing '" & Me.cboSizingName.Column(1) & "' already exists!")
                Me.Undo
            Else
                Me.cboSizingName.Value = Me.cboSizingName.OldValue
        End If
    End If
    Cancel = True

Open in new window

0
 
chobeAuthor Commented:
Please close now and assign points to Peter57 for his assistance.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now