How to compare Data on a form to data in a record

[ScorpioX]

Hi All,
Having a little trouble here. What I am trying to do is take the value entered by a user and compare it to data in a table, which will then pop up a message box that tells them the data is already there and to please use a different Asset number.
For example:
Form Template opens up. The users enters in a Asset number, the number is then compared to a seperate table. If the Asset number does not exsist, it enters it as a new record. If it does already exsists, it prompts them to enter a different number.
I have the insertion part working but not the if already exsists part. Anyone have a idea what is the best way to do this?
Info:
Form "frmTemplate" with a field named "Asset" then checks my table "tblMainU" field "Asset_tag_new_Current" to see if it is already there. If it does exsist, it will tell them that and go back to the Asset Field. If it does not it enters it as a new record onto "tblMainU".

Thank you for any help and or ideas,
ScorpioX

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScorpioX]

PH,
Thank you. I figured it was probably the best way to go also, just wanted to make sure I was not missing something.

Thanks again,
ScorpioX

 

[ScorpioX]

Ok. I must be missing something. I have to click the button twice sometimes three times, in order for it to show up in the "tblMainU" table. Any ideas what I may be missing? The code I am using is below. The second box of code is the Append query I'm using.

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
    
    If (DLookup("[tblMainu].[Asset]", "tblMainU", "[Asset]='" & Me.Asset & "'")) Then
    MsgBox "Sorry Asset number " & Me.Asset & " already exists, please use a different Asset number", vbOK
    Me.Asset = "0"
    DoCmd.GoToControl "Asset"
    
End If
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendTemplate", acViewNormal, acAdd
    DoCmd.SetWarnings True
    
Exit_Add_Record_Click:
    Exit Sub

Err_Add_Record_Click:
    MsgBox Err.Description

    Resume Exit_Add_Record_Click
    
End Sub

INSERT INTO tblMainU ( Asset, Status, Ownership, Site, Region, Category, Type, Model, CPU_Class, CPU_Speed, RAM, Serial, Loaner, Barcode, PO, Lease_D, Purchase_D, Life_Cycle, Lease_E, Purchase_E )
SELECT qryTemplate.Asset, qryTemplate.Status, qryTemplate.Ownership, qryTemplate.Site, qryTemplate.Region, qryTemplate.Category, qryTemplate.Type, qryTemplate.Model, qryTemplate.CPU_Class, qryTemplate.CPU_Speed, qryTemplate.RAM, qryTemplate.Serial, qryTemplate.Loaner, qryTemplate.Barcode, qryTemplate.PO, qryTemplate.Lease_D, qryTemplate.Purchase_D, qryTemplate.Life_Cycle, qryTemplate.Lease_E, qryTemplate.Purchase_E
FROM qryTemplate;

Thanks again,
ScorpioX

 

[ScorpioX]

Cancel. I got this working by adding code to save the record prior to running the query.

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
    
    If (DLookup("[tblMainu].[Asset]", "tblMainU", "[Asset]='" & Me.Asset & "'")) Then
    MsgBox "Sorry Asset number " & Me.Asset & " already exists, please use a different Asset number", vbOK
    Me.Asset = "0"
    DoCmd.GoToControl "Asset"
    
End If
  'Start new code
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  'End new code
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendTemplate", acViewNormal, acAdd
    DoCmd.SetWarnings True
    
Exit_Add_Record_Click:
    Exit Sub

Err_Add_Record_Click:
    MsgBox Err.Description

    Resume Exit_Add_Record_Click
    
End Sub

Thanks again for the help,
ScorpioX