Is it possible...

[CynF]

I'm working on creating a simple database for my job, which uses only 1 table to track documents being worked on / created.

For the most part things are working smoothly, but during testing things while I'm adding in the relevant information I'm running into an annoying issue.

For the title field, it's set to require the title (ie Not Null), and no duplicates.

While entering data, unfortunately we have MULTIPLE copies of the request / information forms and having to deal with the error messages is annoying. Also, some of those copies have updated information (thus why there's so many ..) which needs to be updated in the database.

Is there a way for me to get the form so that after I enter the title, when I tab to the next control on the form IF there is already an entry in the table with that title, the form will pull up the entire record so that I can check that the information does match, and if there are any updates.. it's right there to be edited without me having to switch to the edit form from the data entry form?

Sadly I have very limited visual basic knowledge (when I was taking classes in college they were offering Java programming not VB so naturally.. I get asked to do this at work and I've been given several books to use for reference but I'm stumped..)

Is it possible and if so.. how?

 

[Isskint]

You should be able to check for existing record with DCount() in the BeforeUpdate event of the Title textbox. If the Title exists you can use the Filter Property to display that record.

Private Sub Title_BeforeUpdate(Cancel As Integer)
Dim strOldValue As String

If DCount("[Title]","[TableName]","[Title]='" & Me.Title.Text & "'") > 0 Then
    strOldValue = Me.Title.Text
    Cancel = True'Cancel update
    Me.Undo'reset form
    Me.Filter = "[Title] = '" & strOldValue & "'"'set filter
    Me.FilterOn = True'switch on filter
    Exit Sub
End If
Exit Sub

 

[khodr]

Hello IssKint,
I think we need to have a save or refresh command after the update to reset the form and enable us to move to other record because when we apply the filter, it will filter the form to show the only matching record and we cannot move to the other records unless we turn the filter off....
e.g:
Me.FilterOn = false
Me.Refresh
Correct me please if I am wrong.

 

[Isskint]

You would only need to turn off the filter. This can be done on the existing toolbar or you can add a command button to remove the filter using the code you suggested. Something you might consider is having the command button invizible until you set the filter, and then make it invizible again after the user presses it. It will stop people 'playing' with a button that does nothing unless a filter is active.