no duplicate record

W

Will G

I want to add a line of code to this code bellow that checks if the record
already exists. can anyone help me.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
'Dim prm_sched As Control
' Dim DBS As Database
'Dim rst As Recordset
Dim quantity As Double
Dim resp As String

Dim DBS As Database
'Dim rst As Recordset
Dim strSel As String
Dim db As DAO.Database
Dim rst As DAO.Recordset


quantity = Me.sched.Column(5)

'Set prm_sched = Me![sched]

If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
'Set DBS = CodeDb
' Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")
Set db = CurrentDb
Set rst = db.OpenRecordset("ProdHistory")

'run through all selected items
For Each itm In Me.sched.ItemsSelected
rst.AddNew
rst.Fields("OrderNo") = Me.sched.ItemData(itm)
'rst!RequiredDate = prm_sched.Column(1)
rst.Fields("RequiredDate") = Me.sched.Column(1, itm)
rst.Fields("CustomerID") = Me.sched.Column(2, itm)
'rst!CustomerID = prm_sched.Column(2)
rst.Fields("ModelNumber") = Me.sched.Column(3, itm)
'rst!ModelNumber = prm_sched.Column(3)
rst.Fields("Description") = Me.sched.Column(4, itm)
rst.Fields("OrderQty") = Me.sched.Column(5, itm)
rst.Fields("Comment") = Me.sched.Column(6, itm)
rst.Fields("Bfill") = Me.sched.Column(7, itm)
'rst!Bfill = prm_sched.Column(7)
rst.Fields("Qfill") = Me.sched.Column(8, itm)
rst.Fields("Uphfill") = Me.sched.Column(9, itm)
rst.Fields("Ptfill") = Me.sched.Column(10, itm)
rst.Fields("Spring") = Me.sched.Column(11, itm)
rst.Fields("Label") = Me.sched.Column(12, itm)
rst.Fields("Size") = Me.sched.Column(13, itm)
rst.Fields("Needle") = Me.sched.Column(14, itm)
rst.Fields("Pattern") = Me.sched.Column(15, itm)
rst.Fields("Config") = Me.sched.Column(16, itm)
rst.Fields("Border") = Me.sched.Column(17, itm)
rst.Fields("FoamCore") = Me.sched.Column(18, itm)
rst.Fields("FoamEnc") = Me.sched.Column(19, itm)
rst.Fields("ProtoTypeNum") = Me.sched.Column(22, itm)
rst.Fields("Color") = Me.txtColor
rst.Fields("Type") = Me.cmbType
rst.Fields("ProductionDate") = Me.Combo68
rst.update
strSel = "'" & Me.sched.ItemData(itm) & "',"
Next
' rst.AddNew
' rst.Fields("Color") = Me.Combo72
'rst.update
rst.close
Set rst = Nothing
Set db = Nothing
 
C

Carl Rapson

Will G said:
I want to add a line of code to this code bellow that checks if the record
already exists. can anyone help me.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
'Dim prm_sched As Control
' Dim DBS As Database
'Dim rst As Recordset
Dim quantity As Double
Dim resp As String

Dim DBS As Database
'Dim rst As Recordset
Dim strSel As String
Dim db As DAO.Database
Dim rst As DAO.Recordset


quantity = Me.sched.Column(5)

'Set prm_sched = Me![sched]

If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
'Set DBS = CodeDb
' Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")
Set db = CurrentDb
Set rst = db.OpenRecordset("ProdHistory")

'run through all selected items
For Each itm In Me.sched.ItemsSelected
rst.AddNew
rst.Fields("OrderNo") = Me.sched.ItemData(itm)
'rst!RequiredDate = prm_sched.Column(1)
rst.Fields("RequiredDate") = Me.sched.Column(1, itm)
rst.Fields("CustomerID") = Me.sched.Column(2, itm)
'rst!CustomerID = prm_sched.Column(2)
rst.Fields("ModelNumber") = Me.sched.Column(3, itm)
'rst!ModelNumber = prm_sched.Column(3)
rst.Fields("Description") = Me.sched.Column(4, itm)
rst.Fields("OrderQty") = Me.sched.Column(5, itm)
rst.Fields("Comment") = Me.sched.Column(6, itm)
rst.Fields("Bfill") = Me.sched.Column(7, itm)
'rst!Bfill = prm_sched.Column(7)
rst.Fields("Qfill") = Me.sched.Column(8, itm)
rst.Fields("Uphfill") = Me.sched.Column(9, itm)
rst.Fields("Ptfill") = Me.sched.Column(10, itm)
rst.Fields("Spring") = Me.sched.Column(11, itm)
rst.Fields("Label") = Me.sched.Column(12, itm)
rst.Fields("Size") = Me.sched.Column(13, itm)
rst.Fields("Needle") = Me.sched.Column(14, itm)
rst.Fields("Pattern") = Me.sched.Column(15, itm)
rst.Fields("Config") = Me.sched.Column(16, itm)
rst.Fields("Border") = Me.sched.Column(17, itm)
rst.Fields("FoamCore") = Me.sched.Column(18, itm)
rst.Fields("FoamEnc") = Me.sched.Column(19, itm)
rst.Fields("ProtoTypeNum") = Me.sched.Column(22, itm)
rst.Fields("Color") = Me.txtColor
rst.Fields("Type") = Me.cmbType
rst.Fields("ProductionDate") = Me.Combo68
rst.update
strSel = "'" & Me.sched.ItemData(itm) & "',"
Next
' rst.AddNew
' rst.Fields("Color") = Me.Combo72
'rst.update
rst.close
Set rst = Nothing
Set db = Nothing
Click to expand...

What field determines whether a record is duplicated? Use that field in a
DCount call before you add the new record. For example, if it's the OrderNo
field:

If DCount("*", "ProdHistory", "OrderNo=" & Me.sched.ItemData(itm)) = 0
Then
' add the new record like now
End If

Carl Rapson
 
W

Will G

i am not sure where to put the IF statement.....this is what i tried.....i
like to have the orderNo and these three other items rst.Fields("Color") =
Me.txtColor
rst.Fields("Type") = Me.cmbType
rst.Fields("ProductionDate") = Me.Combo68
this is what i've tried.


For Each itm In Me.sched.ItemsSelected

rst.AddNew
If DCount("*", "ProdHistory", "OrderNo=" & Me.sched.ItemData(itm)) = 0
Then
' add the new record like now
rst.Fields("OrderNo") = Me.sched.ItemData(itm)
'rst!RequiredDate = prm_sched.Column(1)
rst.Fields("RequiredDate") = Me.sched.Column(1, itm)
rst.Fields("CustomerID") = Me.sched.Column(2, itm)
'rst!CustomerID = prm_sched.Column(2)
rst.Fields("ModelNumber") = Me.sched.Column(3, itm)
'rst!ModelNumber = prm_sched.Column(3)
rst.Fields("Description") = Me.sched.Column(4, itm)
rst.Fields("OrderQty") = Me.sched.Column(5, itm)
rst.Fields("Comment") = Me.sched.Column(6, itm)
rst.Fields("Bfill") = Me.sched.Column(7, itm)
'rst!Bfill = prm_sched.Column(7)
rst.Fields("Qfill") = Me.sched.Column(8, itm)
rst.Fields("Uphfill") = Me.sched.Column(9, itm)
rst.Fields("Ptfill") = Me.sched.Column(10, itm)
rst.Fields("Spring") = Me.sched.Column(11, itm)
rst.Fields("Label") = Me.sched.Column(12, itm)
rst.Fields("Size") = Me.sched.Column(13, itm)
rst.Fields("Needle") = Me.sched.Column(14, itm)
rst.Fields("Pattern") = Me.sched.Column(15, itm)
rst.Fields("Config") = Me.sched.Column(16, itm)
rst.Fields("Border") = Me.sched.Column(17, itm)
rst.Fields("FoamCore") = Me.sched.Column(18, itm)
rst.Fields("FoamEnc") = Me.sched.Column(19, itm)
rst.Fields("ProtoTypeNum") = Me.sched.Column(22, itm)
rst.Fields("Color") = Me.txtColor
rst.Fields("Type") = Me.cmbType
rst.Fields("ProductionDate") = Me.Combo68
End If
rst.update
strSel = "'" & Me.sched.ItemData(itm) & "',"
Next

thanks for your help
 
C

Carl Rapson

Your If statement should come before the rst.AddNew statement, and the End
If should be after the rst.Update statement.

If DCount ...
rst.AddNew
'...
rst.Update
End If

To add more conditions to the DCount call (I've split the Where condition
out into as separate string for clarity):

Dim strSQL As String
strSQL = "(OrderNo=" & Me.sched.ItemData(itm) & ")"
strSQL = strSQL & " AND (Color='" & Me.txtColor & "')"
strSQL = strSQL & " AND (Type='" & Me.cmbType & "')"
strSQL = strSQL & " AND (ProductionDate=#" & Me.Combo68 & "#)"
If DCount("*", "ProdHistory", strSQL) = 0 Then
' Insert new record here
rst.AddNew
'...
rst.Update
End If

Carl Rapson
 
W

Will

This works perfectly Carl, thanks. Now, i want it to do nothing after
checking if the data exists. i added an ELSE to the if statement, but after
displaying the MSGBOX, i guess it runs the code that follows. how can i make
it do nothing if the record already exist. and one other thing, it seems like
it's checking for each items by itself. what i want it to do is check for a
combination of record. e.g. it should check for a row of information.
combination of, OrderNo, Color, Type, Production Date. i used used the code
that u provided me with and it seems to check each of this items
individually. thanks again Carl, i hope you could help me.
 
C

Carl Rapson

Your code should be laid out something like this:

If DCount(...) = 0 Then
rst.AddNew
'...
rst.Update
Else
MsgBox "The record already exists!"
End If

A new record should only be added if the DCount returns zero, which means no
matching record exists.

I'm not exactly sure what you mean by checking each item by itself, but if
you used ANDs when you built the string, it should be checking for the
combination of the 4 fields in a single record.

Carl Rapson
 
W

Will

got it thanks....

Carl Rapson said:
Your code should be laid out something like this:

If DCount(...) = 0 Then
rst.AddNew
'...
rst.Update
Else
MsgBox "The record already exists!"
End If

A new record should only be added if the DCount returns zero, which means no
matching record exists.

I'm not exactly sure what you mean by checking each item by itself, but if
you used ANDs when you built the string, it should be checking for the
combination of the 4 fields in a single record.

Carl Rapson
Click to expand...
 

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

list box send to history table 4
Coding to make AddItem work for 2000 6
needing help with loop issues 1
Attachments 4
FindFirst not finding first login/password attempt 1
Can't understand this error - File not found 3
Creating Record in Form Click Event 5
Type mismatch error 2

Top