Check if data already exists in database.

[Guest]

Hi,

Wonder if someone can help me please.

I’ve a form (F_OE_Ideas_FillIn) with a couple fields where users can put
their data in. After they put their data in, I’ve a submit button witch
select all the data from the fields on this specific form and put it in a
database table (T_OE_Ideas).

What I want…
Is a check if the data is already in the database,
1) if so: ask the user conform this operation and then continue or stop the
action.
2) if not:then put the data into the table (T_OE_Ideas) without confirmation.

Is this possible?

Kind regards, Stefan van der Hooft.

 

[Guest]

On the OnClick event of the button try something like

Dim DB as Dao.DataBase, MyRec As Dao.RecordSet
Set DB = CurrentDb
Set MyRec = DB.OpenRecordSet("Select * From T_OE_Ideas Where
TextFieldNameInTable = '" & Me.TextFieldNameInForm & "' And NumberFieldName
=" & Me.NumberFieldNameInFrom )
' Check if the record doesnt exist
If Not MyRec.Eof Then
MsgBox "Record Already Exist"
Else
MyRec.AddNew
MyRec!TextFieldNameInTable = Me.TextFieldNameInForm
MyRec!NumberFieldName = Me.NumberFieldNameInFrom
MyRec.Update
End If

Note: you need to change the names of the fields and the type in the
recordset.
If you need more help, then post the fields type and name

 

[Guest]

Hi! First of all, thanks for your reply!

The primary key field name in the database is Fld_Date and the name of the
textbox on my form is Field_Date. I don’t know what you mean with
NumberFieldName and NumberFieldNameInFrom?

When I put this code into my VB editor, Set MyRec till …… InFrom ) is given
red.

Do you’ve any suggestions?

Thanks,

Stefan.

 

[Guest]

There are rules to how to check if a value exist in the table, each field
type has a different way of checking

Dim SQLStr as String
' For Date field
SQLStr = "Select * From TableName Where FieldName = #" & Variable & "#"

' For Text field
SQLStr = "Select * From TableName Where FieldName = '" & Variable & "'"

' For numeric field
SQLStr = "Select * From TableName Where FieldName = " & Variable

Set MyRec=MyDb.OpenRecordSet(SQLStr )

About the red line, check that the line of the Open recordSet continue as
one line and it doesn't break.

 

[Guest]

Hi!
I’ve fixed the line break; it was the broken line solution. But, I still get
an Run-time error ‘424’: Object required.

This is the code behind the butto
-----------------------------------------------------------------------------------------
Private Sub Button_Submit_Click()
Dim DB As DAO.Database, SQLStr As String
Set DB = CurrentDb
SQLStr = "SELECT * FROM T_OE_Ideas WHERE Fld_Date = #" &
F_OE_Ideas_FillIn.Field_Date & "# "
' Check if the record doesnt exist
If Not MyRec.EOF Then
MsgBox "Record Already Exist"
Else
' Put insert code here
End If

End Su
-----------------------------------------------------------------------------------------

(F_OE_Ideas_FillIn is the name of my form)

Hope you can help me,

Regards, Stefan.

 

[John W. Vinson]

Private Sub Button_Submit_Click()
Dim DB As DAO.Database, SQLStr As String
Set DB = CurrentDb
SQLStr = "SELECT * FROM T_OE_Ideas WHERE Fld_Date = #" &
F_OE_Ideas_FillIn.Field_Date & "# "
' Check if the record doesnt exist
If Not MyRec.EOF Then
MsgBox "Record Already Exist"
Else
' Put insert code here
End If

End Sub
-----------------------------------------------------------------------------------------

This code defines a string variable... and then checks to see if the
recordset variable MyRec is at its end-of-file.

Nowhere in the code do you create or define MyRec.

Reread Ofer's first reply; it might help if you check the online help
for Recordset and understand what's going on rather than just blindly
(mis)copying the code.

John W. Vinson [MVP]