Duplicate data linking

Jdreyfus

Registered User.
Local time
Today, 12:08
Joined
Jun 19, 2008
Messages
27
I'm pretty new to Access, I've been working with it for about three weeks now. My issue is that I have a form for entering assets and have set the primary key to a unique id tag we use for inventory, that's all well and good for preventing duplicate data, however I'd like to know if there is a way to quickly pull up the record of an entry. For example: I'll go to enter an asset and it will have already been entered, is there a way in that form to quickly single out that entry for editing? We move equipment from room to room, and in the inventorying process it's not always efficient to search the records before entering catalogued assets. Any suggestions would be really great, I'm having a lot of trouble finding a specific solution.
 
you can do a number of things to search for a certain record, but it all depends on HOW you are attempting to search for it.

say for example, you are searching the form for a record that has a matching asset id number to one that you've entered in a textbox. For something like this, you can use VB to search it, filter it, and then edit it.

It sounds like you need some code similar to this:
Code:
On Click Event of the "Add Asset ID" button

  if dcount("AssetIDfieldHere", "tableNameHere", _
               "AssetIDfield = " & me.AssetIDTextBoxName) = 0 then
    docmd.gotorecord acdataform, me.name, acnewrec
  else
    if msgbox("This asset already exists in the database.  " & _
    "Would you like to edit that record?", vbexclamation + vbyesno) = vbyes then
      me.filter = "AssetID = " me.AssetIDTextBoxName
        me.filteron = true
    end if
  end if
Just a suggestion.... :)
 
Thank you very much for pointing me in the right direction. I've been trying to implement the code but haven't had much luck. I attempted to change the event to before update of the Asset ID textbox field, the name of it within my database is YC_Tag, so that instantly when someone enters an identical tag the message would appear.... Atleast I think that's how it's supposed to work. I'm getting compile errors though, syntax error. I don't know the proper syntax so I wouldn't begin to know how to correct it. Here's a copy of the code I'm trying to run, I don't know hardly anything about VB, so I'm sure I've done something obviously and horribly wrong.

Code:
Private Sub YC_TAG_BeforeUpdate(Cancel As Integer)
If DCount("YC_TAG", "Asset Details", _
"YC_TAG = " & Me.YC_TAG) = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
If MsgBox("This asset already exists in the database. " & _
"Would you like to edit that record?", vbExclamation + vbYesNo) = vbYes Then
me.filter = "YC_TAG = " me.YC_TAG
Me.FilterOn = True
End If
End If
End Sub
End Sub
 
Last edited:
Code:
Private Sub YC_TAG_BeforeUpdate(Cancel As Integer)
If DCount("YC_TAG", "Asset Details", _
"YC_TAG = " & Me.YC_TAG [COLOR="Red"]<--- this syntax will only work if the [B][U]YC_TAG[/U][/B] is [U]not[/U] a text data type.[/COLOR]) = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
If MsgBox("This asset already exists in the database. " & _
"Would you like to edit that record?", vbExclamation + vbYesNo) = vbYes Then
me.filter = "YC_TAG = " me.YC_TAG [COLOR="Red"]<---filters are kind of annoying sometimes.
                                  You can use [B][U]docmd.searchforrecord[/U][/B] and 
                                  jump to it with that method instead of filtering if you want.[/COLOR]
Me.FilterOn = True [COLOR="Red"]<--- if you do as above, you don't have to mess with this line either.  :)[/COLOR]
End If
End If
End Sub
End Sub [COLOR="Red"]<--- one too many subs here![/COLOR]


Where exactly is the syntax error? Any highlighting going on in the modules when the code runs?
 
Thank you so much for your help, I have two questions, the yc_tag field consists of records formatted as ##-#### much of the time the id tag starts with 00-#### though, so I've left the field as text because I didn't know which numerical data type to choose, and seemingly any time I choose one it threatens me with loss of data. I had selected long integer but that deleted the zeros from the beginning of my records.

Could you explain how to properly use the docmd.searchforrecord command? How is it used?



Code:
Private Sub YC_TAG_BeforeUpdate(Cancel As Integer)
If DCount("YC_TAG", "Asset Details", _
"YC_TAG = " & Me.YC_TAG) = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
If MsgBox("This asset already exists in the database. " & _
"Would you like to edit that record?", vbExclamation + vbYesNo) = vbYes Then
[COLOR=darkgreen]me.filter = "YC_TAG = " me.YC_TAG <---This line is highlighted [/COLOR]
Me.FilterOn = True
End If
End If
End Sub
End Sub
 
the yc_tag field consists of records formatted as ##-#### much of the time the id tag starts with 00-#### though, so I've left the field as text because I didn't know which numerical data type to choose
This is why the line below is highlighted.
Code:
[COLOR=darkgreen]me.filter = "YC_TAG = " me.YC_TAG <---This line is highlighted [/COLOR]
Replace the line with this:
Code:
me.filter = "YC_TAG = '" & me.YC_TAG & "'"
you can figure out the SearchForRecord method very easily by typing it into a line in the VB editor. Test it out. It works in an automatic sense, exactly the way GoToRecord does...
 
Thank you very much, I've tried replacing the filter with a SearchForRecord command, however I'm now getting the following error: Run-Time Error '2465': Microsoft Office Access can't fing the field '|' referred to in your expression. I really know virtually nothing about VB so I'm finding interpreting the help given in access rather confusing. My code now is :

Code:
Private Sub YC_TAG_AfterUpdate()
  If DCount("YC_TAG", "Assets", _
  "YC_TAG = " & Me.YC_TAG) = 0 Then
  DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
  Else
  If MsgBox("This asset already exists in the database. " & _
  "Would you like to edit that record?", vbExclamation + vbYesNo) =vbYes 
  Then
 [COLOR=red]DoCmd.SearchForRecord ([acDataForm = Assets, "YC_TAG", Me.Name = acFirst])  <--- This line is my issue, I have no clue whether it's even remotely correct.[/COLOR]
End If
End If
End Sub
 
why not just do a drop-down box of the items with a "find record that matches the control" code?

Code:
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemID] = " & Str(Nz(Me![cmbSelectItem], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
Here is an example of the SearchForRecord method:
Code:
docmd.searchForRecord acdataform, me.name, , "fieldNameOnTheForm = Your Criteria Here"
Attached is a sample. I just found out too, that this function is only available in 2007, so I guess you're using that version, right?
 

Attachments

Users who are viewing this thread

Back
Top Bottom