Customise append sql message (1 Viewer)

[xaysana]

Hi,

I need to append multi records from append query call temp to table tblProductions. In fact, they seems to work fine but i like to happen if any record in both columns found then message "There is a duplicate." else run the query.
I have a button to run this query in a mainform. below is my current code.

Private Sub cbupdate_serialno_Click()

If Me.txtStartno = 0 Or IsNull(Me.txtStartno) Then
MsgBox "Startno needs values"
Me.txtStartno.SetFocus

ElseIf Me.txtEndno = 0 Or IsNull(Me.txtEndno) Then
MsgBox "Endno needs values"
Me.txtEndno.SetFocus

ElseIf Me.cbproducer = 0 Or IsNull(Me.cbproducer) Then
MsgBox "Producer needs value"
Me.cbproducer.SetFocus

ElseIf Me.cbVillage = 0 Or IsNull(Me.cbVillage) Then
MsgBox "Village is empty"
Me.cbVillage.SetFocus

ElseIf Me.cbWorker1 = 0 Or IsNull(Me.cbWorker1) Then
MsgBox "Please enter Worker name."
Me.cbWorker1.SetFocus

ElseIf Me.cbRecorder = 0 Or IsNull(Me.cbRecorder) Then
MsgBox "Recorder is empty"
Me.cbRecorder.SetFocus

ElseIf Me.txtLotserialno = 0 Or IsNull(Me.txtLotserialno) Then
MsgBox "Please enter Serial code."
Me.txtLotserialno.SetFocus

Else

If MsgBox("Sure! you want to submit data?", vbYesNo, "Becareful:") = vbYes Then
DoCmd.OpenQuery "update to tblProductions"
MsgBox "Finished."
End If

End If
Me.Refresh
End Sub

I would appreciate for any help. Thank you in advance.
Regards,

 

[xaysana]

Hi,

I need to append multi records from append query call temp to table tblProductions. In fact, they seems to work fine but i like to happen if any record in both columns found then message "There is a duplicate." else run the query.
I have a button to run this query in a mainform. below is my current code.

Private Sub cbupdate_serialno_Click()

If Me.txtStartno = 0 Or IsNull(Me.txtStartno) Then
MsgBox "Startno needs values"
Me.txtStartno.SetFocus

ElseIf Me.txtEndno = 0 Or IsNull(Me.txtEndno) Then
MsgBox "Endno needs values"
Me.txtEndno.SetFocus

ElseIf Me.cbproducer = 0 Or IsNull(Me.cbproducer) Then
MsgBox "Producer needs value"
Me.cbproducer.SetFocus

ElseIf Me.cbVillage = 0 Or IsNull(Me.cbVillage) Then
MsgBox "Village is empty"
Me.cbVillage.SetFocus

ElseIf Me.cbWorker1 = 0 Or IsNull(Me.cbWorker1) Then
MsgBox "Please enter Worker name."
Me.cbWorker1.SetFocus

ElseIf Me.cbRecorder = 0 Or IsNull(Me.cbRecorder) Then
MsgBox "Recorder is empty"
Me.cbRecorder.SetFocus

ElseIf Me.txtLotserialno = 0 Or IsNull(Me.txtLotserialno) Then
MsgBox "Please enter Serial code."
Me.txtLotserialno.SetFocus

Else

If MsgBox("Sure! you want to submit data?", vbYesNo, "Becareful:") = vbYes Then
DoCmd.OpenQuery "update to tblProductions"
MsgBox "Finished."
End If

End If
Me.Refresh
End Sub

I would appreciate for any help. Thank you in advance.
Regards,

I am sorry, but could anyone help please?
Regards,

 

[pbaldy]

You haven't really given enough info for a specific answer. Generally, you could test before running the query:

If DCount(...) > 0 Then
  'MsgBox "Duplicates exist"
End If

http://www.mvps.org/access/general/gen0018.htm

 

[xaysana]

You haven't really given enough info for a specific answer. Generally, you could test before running the query:

If DCount(...) > 0 Then
  'MsgBox "Duplicates exist"
End If

http://www.mvps.org/access/general/gen0018.htm

It works great. Here is the code:
ElseIf DCount("SerialID", "tblProductions", "SerialID=" & Me.txtStartno) > 0 Then
MsgBox "There is some duplicate records. Please check.", , "Warning:"
End if

I really appriciate. Thank you so much for your assistance.

 

[pbaldy]

Happy to help.