How can I check existing columns in a table before adding a record?

Question

I have a table called Visitor. It can accept duplicate values. I mean the same visitor can come to the shop many times but only one time per day.

Visitor_Name	Purchase Price	Date
John	200.00 USD	15/12/2020
Bob	150.00 USD	15/12/2020
Marshall	170.00 USD	16/12/2020

I need to add a check.
If I try to add John with existing date (15/12/2020) then prevent the record insertion with a popup message "Visitor already visited the shop in that particular day".

This is the code I'm currently using.

Private Sub add()
    DoCmd.RunSQL "INSERT INTO Visitor([Visitor_Name], [Purchase Price], [Date] VALUES ([Text1].Value, [Text2].Value, [Text3].Value)"
End Sub

Before adding a record I need to check the Visitor_Name value and Date value with all existing values related to that particular visitor.

https://stackoverflow.com/questions/2127698/can-we-create-multicolumn-unique-indexes-on-ms-access-databases — Tim Williams, Dec 27 '20 at 07:40
I think above link is not related to my situation. My table can accept duplicate values. — Zajjith Vedha, Dec 27 '20 at 07:50
A unique index on Visitor name and Date would prevent the types of entries you're asking about. — Tim Williams, Dec 27 '20 at 08:27

score 0 · Accepted Answer · answered Dec 27 '20 at 07:55

0

This can be achieved by using DCount to count how many entries in the table contain that Visitor_Name and Date, exiting early if it is not 0:

Private Sub add()
   If(DCount("Visitor_Name", "Visitor", "Visitor_Name=""" & [Text1].Value & """ AND [Date]=#" & [Text3].Value & "#") <> 0) Then
      MsgBox "Visitor already visited the shop in that particular day"
      Exit Sub
   End If
    
   DoCmd.RunSQL "INSERT INTO Visitor([Visitor_Name], [Purchase Price], [Date] VALUES ([Text1].Value, [Text2].Value, [Text3].Value)"
End Sub

answered Dec 27 '20 at 07:55

sbgib

5,580
3
19
26

Thank you very much sbgib. I have one more question. If I want to check purchase_price also in the condition then how should I need to change the code? – Zajjith Vedha Dec 27 '20 at 08:28
You're welcome! In the `DCount` function, you would change the 3rd parameter to include it: `If(DCount("Visitor_Name", "Visitor", "Visitor_Name=""" & [Text1].Value & """ AND [Purchase Price]=" & CStr([Text2].Value) & " AND [Date]=#" & [Text3].Value & "#") <> 0) Then`. Think of it as an Access SQL WHERE condition. – sbgib Dec 27 '20 at 08:32
It converts the number to a string. Here's the [reference documentation for CStr](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions). – sbgib Dec 27 '20 at 08:38
2

@ZajjithVedha - The person was kind enough to answer your question and you were kind enough to accept it. Please don't pester them with follow-up questions. If you have a new question [ask it](https://stackoverflow.com/questions/ask) separately, adding a link to this question for context, if needed. – Gord Thompson Dec 27 '20 at 19:35

How can I check existing columns in a table before adding a record?

1 Answers1