Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27

    Using a textbox control to check to see if a record exists in a table

    I have a navigation page with several forms attach. I am trying to develop a search/edit/update button where a user can put in a number to search if a record exists. If it already exists update form with record information so edits can be made. Once edits have been completed, update the record in the table and save entry. I have started by checking to see if the user has enter any information in the text box with the following code:

    'Search for Record to Edit and Update
    Private Sub cmdSearch_Click()
    Dim strAssessionNumber As String
    Dim strSearch As String
    'Check txtSearch for Null value or Nill Entry First
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then


    MsgBox "Please enter Assession Number!", vbOKOnly, "Invalid Search Criterion!"
    Me![txtSearch].SetFocus
    Exit Sub
    End If
    End Sub

    Here if the user failed to put in a number, the message box fires and reminds the user to enter data. This works, now I would like to search the table for a field AssessionNumber that the user places in the text box. If it exists populate the form with the record so changes can be made. If record does not exist allow user to input a new record. I found this code and have tried it in several locations in the cmdSearch_Click() with no luck.

    If DCount("*",tblDelay","[AssessionNumber] =#" & Me.[txtSeach])> 0 Then
    MsgBox "This record exists, please update record.
    Cancel = True
    End If

    Here is a view of my form and tblDelay includes field MRN, AssessionNumber, CaseNumber, and several other fields. I think if I can get this working I can add the remainder fields. Once record has been updated I need to save the update.

    Thanks again in advance,
    Kerry
    Attached Thumbnails Attached Thumbnails NavForm3.jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Why not use a combobox that lists the available assession numbers?

    Why do you have # character in the DCount?

    I don't use navigation form/control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    Ultimately can be 10s of thousands of records. Don't want a long list in combobox. Can you start a number and a combo box will move down the list towards the number? I found the dCount code on a nother forum. Thought I would try to make a few changes and see if I can get it wo work for my situation.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    Thanks June7, I'll review. I am no expert so if this is better method by all means I can use it. Just trying to make it simpler for users to edit/update a record or enter new. I am not sure how to get the record back into the form once I identify the record exists.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Apply criteria to form Filter property.

    However, I don't use navigation form/control so don't know how difficult that might be. Another item to review https://www.accessforums.net/forms/t...orm-32053.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also missing a quote before tblDelay and Search is misspelled:
    Code:
    If DCount("*","tblDelay","[AssessionNumber] = " & Me.[txtSeach])> 0 Then
    MsgBox "This record exists, please update record.
    Cancel = True
    End If

  8. #8
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    I made your recommended changes. I search for a AssessionNumber I know exists and I get a message that no such record exists and then I get an Run-time error '3075': Syntax error (missing operator) in query expression '[AssessionNumber]='

    Any Idea?
    Thanks,
    Kerry

  9. #9
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    Here is the code that fires the message no record exists:

    Private Sub txtSearch_AfterUpdate()
    If (txtSearch & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[AssessionNumber]=" & txtSearch
    If rs.NoMatch Then
    MsgBox "Sorry, no such record '" & txtSearch & "' was found.", _
    vbOKOnly + vbInformation
    Else
    Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtSearch = Null
    End Sub

  10. #10
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    I have found some addition code that might work as well but having an issues with recordset. This code is for a split form with the data entry at the top and a datasheet below. My form is not set up like this. I have a form and a table. Here is the coded:

    Private Sub cmdEdit_click()
    'check whether there is data in a list
    If Not (Me.frmStudentSub.Form.Recordset.EOF And Me.frmStudentSub.Form.Recordset.BOF Then
    'get data to text box control
    With Me.frmStudentSub.Form.Recordset
    Me.txtID = .Fields("stdid")
    Me.txtName = .Fields("stdname")
    Me.cboGender = .Fields("gender")
    Me.txtAddess = .Fields("address")
    Me.txtPhone = .Fields("phone")
    'change id of student in Tag of txtID in case id is modified
    Me.txtID.Tag = .Field("stdid")
    'change caption of button add to update
    Me.cmdAdd.Caption = "Update"
    'disable button edit
    Me.cmdEdit.Enabled. False
    End With
    End If
    End Sub

    Private Sub cmdAdd_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    If Me.txtID.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO student(stdid, stdname, gender, phone, address) " & _
    " VALUES(" & Me.txtID & ",'" & Me.txtName & "','" & _
    Me.cboGender & "','" & Me.txtPhone & "','" & Me.txtAddress & "')"
    ELSE
    'otherwise (Tag of txtID store the id of student to be modified)
    CurrentDb.Execute "UPDATE student " & _
    " SET stdid=" & Me.txtID & _
    ", stdname='" & Me.txtName & "'" & _
    ", gender='" & Me.cboGender & "'" & _
    ", phone='" & Me.txtPhone & "'" & _
    ", address='" & Me.txtAddress & "'" & _
    "WHERE stdid=" & Me.txtID.Tag
    End If
    'clear form
    cmdClear_Ckick()
    'refresh data in list on form
    frmStudentSub.Form.Requery
    End Sub


    In my situation, I would like for cmdEdit_Click() to check for the existence of a record in my TABLE tblDelay. My fields MRN Yes (Duplicates OK) and AssessionNumber Yes (No Duplicates) are Indexed. I would like to use a textbox or combobox to check if a particular AssessionNumber exists. If the AssessionNumber does exist populate the form with the record information. My problem is how to identify if the record exists in tblDelay, this code uses the following If Not (Me.frmStudentSub.Form.Recordset.EOF And Me.frmStudentSub.Form.Recordset.BOF Then. So I would like to see if AsseessionNumber exists in tblDelay and populate form if it does. How do I build this If statement is my dilemma?

    I will changes all these field to replicate my tables if I can get the find record to work for me.

    Thanks in advance,
    Kerry

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    One way is DLookup:

    Sub Combobox_BeforeUpdate(Cancel As Boolean)
    If Not IsNull(DLookup("AssessionNumber", "tblDelay", "AssessionNumber=" & Me.AssessionNumber)) Then
    'code to cancel user input and filter form for the existing record
    Me.Filter = "AssessionNumber=" & Me.AssessionNumber
    Cancel = True
    Me.FilterOn = True
    End If
    End Sub

    Why are you using INSERT and UPDATE actions? Is this form BOUND to table?
    Last edited by June7; 09-10-2014 at 01:21 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    It is bound to tblDelay and several cascading comboboxes are bound to additional tables. I am new to access vba and I have been struggling to find the best method to edit and update a record. My users on occasion will save and close the form before they have entered all the data on a particular record. I need to develop a way for them to search on the AssessionNumber which has no duplicate numbers to find the record and populate the form to complete it or make changes and update. Any suggestion would be greatly appreciated. I have been searching the net for examples that might work in my situation.

    Thanks for your help. Any further suggestions is greatly appreciated.
    Kerry

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Use an UNBOUND textbox or combobox for users to input an assession number to search for. If a combobox is used then only existing numbers will be available. If number is not in list then the user can be sent to a new record row to enter the data.

    I still don't understand why using INSERT and UPDATE.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    KerryAustin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    27
    I found this code on the internet. I thought I might be able to manipulate it to fit my needs. I am not using the INSERT and UPDATE. Was trying anything at this point to try and populate my form and update the record.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Did you try my suggested code? It assumed user was already on new record row and attempting to create a new record by selecting assession number in a BOUND combobox. Alternative would be to use an UNBOUND control as filter criteria.

    Sub Combobox_AfterUpdate()
    If Not IsNull(DLookup("AssessionNumber", "tblDelay", "AssessionNumber=" & Me.AssessionNumber)) Then
    'code to filter form for the existing record
    Me.Filter = "AssessionNumber=" & Me.AssessionNumber
    Me.FilterOn = True
    Else
    'code to move to new record row
    DoCmd.GoToRecord , , acNewRec
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Check to see if record already exists
    By zipaway in forum Programming
    Replies: 4
    Last Post: 06-05-2014, 09:16 AM
  2. Check if record exists, Check Number
    By burrina in forum Forms
    Replies: 9
    Last Post: 01-06-2013, 03:49 PM
  3. Replies: 3
    Last Post: 10-19-2012, 04:30 PM
  4. Replies: 1
    Last Post: 03-06-2012, 06:45 PM
  5. Check to see if record id exists in joined table
    By csoseman in forum Programming
    Replies: 1
    Last Post: 08-18-2011, 01:06 PM

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