Search records?

  • Thread starter Thread starter Shelley
  • Start date Start date
S

Shelley

I want to run some code to insert a new record. But, if the PK is
already in the table, I want to stop the code, print an error message,
and exit. Here's what I have so far:

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
Dim mySQL As String

DoCmd.GoToRecord , , acNewRec
Me.ReceiptDate = Date
Me.ReleaseDate = DateAdd("m", 6, Format(Now, "mm/dd/yyyy"))
Me.TypeOfCase = "PRR"
mySQL = "INSERT INTO DNLOAD (dnloaded, FIELD001, FIELD008,
FIELD006, FIELD002, FIELD011, FIELD015, FIELD016, FIELD024, FIELD044,
FIELD068) VALUES ('0','VA','" &
[Forms]![Mega-SiteClaimFileReceipt].[SSN] & "','1','" &
[Forms]![Mega-SiteClaimFileReceipt].[FolderNumber] & "','1','1','" &
[Forms]![Mega-SiteClaimFileReceipt].[Shelf Location] & "','" & Date &
"', 'ABC', '1');"
Debug.Print mySQL
DoCmd.RunSQL mySQL, -1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
If Err.Number = 3022 Then
MsgBox "This Folder is already in the MegaSite!"
Resume Exit_Add_Record_Click
Else
MsgBox Err.Description
Resume Exit_Add_Record_Click
End If


End Sub

Thanks!
Shelley
 
Hi

The following lets you check for exising records. It uses DCOUNT which
everyone hates because they are inefficient, but quick to code. If you need
it faster then repost and ask for recordset instructions.

dim criteriaStr as string
dim tableName as string
dim fieldName as string
dim recordCount as integer

tableName = "your_table_here"
fieldName = "[any_field_from_tableName]" ' does not matter what field
criteriaStr = "([PK1] = " & me.<value1> & ") AND ([PK2] = " & me.<value2> &
")" '... repeat for each primary key
'where PK1 = primary key 1, PK2 = primary key 2 etc
'and value1 = primary key 1 value from the current form, value 2 = primary
key 2 value from the current form etc
'if some of these are dates or strings then you may need to add in
formatting info, repost for details if you are unsure

recordCount = DCOUNT(fieldName, tableName, criteriaStr)
if (recordCount > 0 ) then
MsgBox("This already exists")
'do something maybe
goto routine_exit_name
else
'record does not exist
'do whatever here
endif
 
Back
Top