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

[KerryAustin]

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

[June7]

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.

[KerryAustin]

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.

[June7]

Review http://allenbrowne.com/ser-32.html

[KerryAustin]

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.

[June7]

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

[ssanfu]

Also missing a quote before tblDelay and Search is misspelled:

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

[KerryAustin]

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

[KerryAustin]

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

[KerryAustin]

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

[June7]

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?

[KerryAustin]

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

[June7]

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.

[KerryAustin]

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.

[June7]

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