checking for unique field in group of records

H

hermanko

Hi,

I have a main form that has a subform embedded, both with the same
underlying table. The table contains information about documents (i.e.
file name, location, version, and a unique file code for each). Note
that the [File Code] is not a prim key as there can be multiple copies
of one document in the table, each with a different version (i.e.
different version, last modified date, etc).

I have a button that opens a form to allow the user to input info for a
new version of the same file (i.e file code). the form disables the
File Code text box and forces the user to input something into certain
fields (i.e. [Version] and [Last Modified]).

My problem comes when I want to check that the input text box called
[txt5] which represents [Version] in my table is unique. It does not
make sense to input a value that already exists if you are added a new
version.

I tried to use a Dcount to do the error check but i get a msg saying
there is a type mismatch. The field properties in my table is set as
TEXT so i am not sure why the type mismatch occurs in comparing with
the textbox inputs. The code is shown below. If anyone can look at this
and let me know what I can do to fix it, I would be very thankful!
~Herman

Option Compare Database

Private Sub cmd_update_Click()
On Error GoTo myerror_Click

Dim i As Long
Dim db As Database
Dim rs As Recordset

If MsgBox("Update version for document " & [txt1] & "?", vbOKCancel +
vbDefaultButton1) = vbOK Then
OK = True
i = Forms![frmDocumentBrowser].CurrentRecord

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDocList", dbOpenDynaset)
With rs
.AddNew
![File Code] = Me![txt1]
![File Name] = Me![txt2]
![File Location] = Me![txt3]
![Description] = Me![txt4]
![Version] = Me![txt5]
![Created By] = Me![txt6]
![Last Modified] = Me![txt7]
![Modified By] = Me![txt8]
![Comments] = Me![txt9]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing

If DCount("[Version]", "tblDocList", "[File Code] = " & Me![txt1] &
"AND [Version] = " & Me![txt1]) > 1 Then
MsgBox "The document version must be unique.", vbCritical
Exit Sub
End If

'close form
DoCmd.Close acForm, "frmNewVersion"
'requery
Forms![frmDocumentBrowser].Requery
DoCmd.GoToRecord acDataForm, "frmDocumentBrowser", acGoTo, i
Forms![frmDocumentBrowser].[cmd_cancelupdate].SetFocus
End If

Exit_myerror_Click:
Exit Sub

myerror_Click:
If Err.Number = 3314 Then
Response = acDataErrContinue ' Don't display the default
message
MsgBox "Document information must be entered for all fields
with an asterisk (*).", vbCritical, "Invalid Date"
Else
MsgBox "Error #: " & Err.Number & vbCr & Err.Description
End If
Resume Exit_myerror_Click

End Sub
 
K

Klatuu

If DCount("*", "tblDocList", "[File Code] = " & Me![txt1] &
"AND [Version] = " & Me![txt1]) > 1 Then


[email protected] said:
Hi,

I have a main form that has a subform embedded, both with the same
underlying table. The table contains information about documents (i.e.
file name, location, version, and a unique file code for each). Note
that the [File Code] is not a prim key as there can be multiple copies
of one document in the table, each with a different version (i.e.
different version, last modified date, etc).

I have a button that opens a form to allow the user to input info for a
new version of the same file (i.e file code). the form disables the
File Code text box and forces the user to input something into certain
fields (i.e. [Version] and [Last Modified]).

My problem comes when I want to check that the input text box called
[txt5] which represents [Version] in my table is unique. It does not
make sense to input a value that already exists if you are added a new
version.

I tried to use a Dcount to do the error check but i get a msg saying
there is a type mismatch. The field properties in my table is set as
TEXT so i am not sure why the type mismatch occurs in comparing with
the textbox inputs. The code is shown below. If anyone can look at this
and let me know what I can do to fix it, I would be very thankful!
~Herman

Option Compare Database

Private Sub cmd_update_Click()
On Error GoTo myerror_Click

Dim i As Long
Dim db As Database
Dim rs As Recordset

If MsgBox("Update version for document " & [txt1] & "?", vbOKCancel +
vbDefaultButton1) = vbOK Then
OK = True
i = Forms![frmDocumentBrowser].CurrentRecord

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDocList", dbOpenDynaset)
With rs
.AddNew
![File Code] = Me![txt1]
![File Name] = Me![txt2]
![File Location] = Me![txt3]
![Description] = Me![txt4]
![Version] = Me![txt5]
![Created By] = Me![txt6]
![Last Modified] = Me![txt7]
![Modified By] = Me![txt8]
![Comments] = Me![txt9]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing

If DCount("[Version]", "tblDocList", "[File Code] = " & Me![txt1] &
"AND [Version] = " & Me![txt1]) > 1 Then
MsgBox "The document version must be unique.", vbCritical
Exit Sub
End If

'close form
DoCmd.Close acForm, "frmNewVersion"
'requery
Forms![frmDocumentBrowser].Requery
DoCmd.GoToRecord acDataForm, "frmDocumentBrowser", acGoTo, i
Forms![frmDocumentBrowser].[cmd_cancelupdate].SetFocus
End If

Exit_myerror_Click:
Exit Sub

myerror_Click:
If Err.Number = 3314 Then
Response = acDataErrContinue ' Don't display the default
message
MsgBox "Document information must be entered for all fields
with an asterisk (*).", vbCritical, "Invalid Date"
Else
MsgBox "Error #: " & Err.Number & vbCr & Err.Description
End If
Resume Exit_myerror_Click

End Sub
Click to expand...
 
H

hermanko

There is still a "type mismatch" error. I am not really sure why this
error is occurring :(
 
K

Klatuu

Your code is written so that both [File Code] and [Version] are numeric
fields in your table. If this is not correct, you need to change you syntax
to compare against the correct data type.
Here is an example show them both to be text fields:
If DCount("*", "tblDocList", "[File Code] = '" & Me![txt1] &
"' AND [Version] = '" & Me![txt1] & "'") > 1 Then
 
H

hermanko

Klatuu,

thanks, I didn't realize I had to insert the double quotations until
you mentioned it. Now i do not get errors, however my DCount statment
isn't working, in that I dont think i've placed it in the right area of
the code (shown in my first post). Am I allowed to put the DCount
statments within the "With rs.......End With" ? Cuz now when i run it,
it's as if it skips over the dcount even tho i purposely insert a
duplicate version value.

My experience dealing with recordsets is limited. Do I have to run the
dcount statement somehow else in the code?

Thanks!
Herman
 
K

Klatuu

Actually, I would do it differently. What I get from you code is that you
want to update a version number, but it must be unique. I would check for
the existance of the version number before I add it to the table. I would
also use the DLookup. It is usually faster than a DCount. The DCount has to
scan the entire table, but the DLookup stops when it finds the first match.

Private Sub cmd_update_Click()
On Error GoTo myerror_Click

Dim strFileCode as String
Dim db As Database
Dim rs As Recordset

If Not IsNull(DLookup(("[Version]", "tblDocList", "[File Code] = " & _
Me![txt1] & "AND [Version] = " & Me![txt5]) Then
MsgBox "Version " & Me![txt1] & " Already In Use", vbExclamation
Else
If MsgBox("Update version for document " & Me![txt1] & "?", _
vbQuestion + vbOKCancel + vbDefaultButton1) = vbOK Then
OK = True
strFileCode = Me![txt1]
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDocList", dbOpenDynaset)
With rs
.AddNew
![File Code] = Me![txt1]
![File Name] = Me![txt2]
![File Location] = Me![txt3]
![Description] = Me![txt4]
![Version] = Me![txt5]
![Created By] = Me![txt6]
![Last Modified] = Me![txt7]
![Modified By] = Me![txt8]
![Comments] = Me![txt9]
.Update
.Close
End With
Set rs = Nothing
Set db = Nothing

'close form
DoCmd.Close acForm, "frmNewVersion"
'requery
Forms![frmDocumentBrowser].Requery
Set rs = Forms![frmDocumentBrowser].RecordsetClone
rs.FindFirst [FileCode] = strFileCode
Forms![frmDocumentBrowser].Bookmark = rst.Bookmark
Set rs = Nothing
Forms![frmDocumentBrowser].[cmd_cancelupdate].SetFocus
End If

Exit_myerror_Click:
Exit Sub
 
H

hermanko

Thanks, that seems to resolve that issue.

I am trying to add an addition constraint where the field [Last
Modified] must also be unique. I included the following code as well in
the nested IF statments. When I test it, it does not identify a
duplicate value. this [Last Modified] field is a Date/Time type. is
there anything different I need to do? I already removed the double
quotations in the Dlookup criteria.

If Not IsNull(DLookup([Last Modified], "tblDocList", "[File Code] = '"
& Me![txt1] & "' AND [Last Modified] = " & Me![txt7])) Then
MsgBox "The date " & Me![txt7] & " is already in use. Enter a
unique date.", vbExclamation

btw, thanks for your help with this. I reeeally appreciate it.
 
K

Klatuu

It would require it's own DLookup. The DLookup only searchs the field
identified in the first argument. You will need to make modifications to
your code to include a second DLookup for the [Last Modified] field.
 
H

hermanko

Yes, I think I figured this part out for myself (woohoo!). Thanks for
ALL of your great help :)

Herman
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Prompt For Replace in Selection And More... 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Undo all changes in session 1
Word macro that can call cells out of Excel for a find and replace 0
Dynamic Naming of Form Button 3
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Delete all data after a symbol in a field 0

Top