avoiding repetitive records in CurrentDb.Execute InsertQuery

[hamidreza.sajjadi]

I have a data entry form in which there are two text boxes named "NameTxtBoxAt1" and "NamesTxtBoxAt2" . I have wrote a VBA code which gets value stored in them and then insert them into table "UnpivotedName" in column named "UserName" along side with constant value of "1403" in column Year. the problem arises when users decide to edit each single text box. consequently again two repetitive record (one for each text box) will be added to the UnpivotedName Table. here is my code in ms access 2021

Private Sub Command3_Click()

 Dim RequiredQyrAt1 As String
 Dim RequiredQyrAt2 As String
 Dim NameValueAt1 As String
 Dim NameValueAt2 As String

 NameValueAt1 = Me![NameTxtBoxAt1].Value
 NameValueAt2 = Me![NameTxtBoxAt2].Value
 RequiredQyrAt1 = "INSERT INTO UnpivotedName (UserName,Year) VALUES ('" & NameValueAt1 & "','1403')"
 RequiredQyrAt2 = "INSERT INTO UnpivotedName (UserName,Year) VALUES ('" & NameValueAt2 & "','1404')"

 CurrentDb.Execute RequiredQyrAt1, dbFailOnError
 CurrentDb.Execute RequiredQyrAt2, dbFailOnError

End Sub

how can i handle this problem?
+the aim of this procedure is to a kind of unpivoting data without UNION Query

 

[Gasman]

Check to see if they exist in the first place?, Use Dcount()

 

[plog]

What do you want to occur? You just told us what not to do.

To catch/prevent a repeated button click you could do a variety of things:

1. Set a global variable that you flag when you have already done the INSERT once.
2. Hide the button after the insert.
3. 2 buttons--one that starts visible and says 'Insert'. One that starts invisible and says 'Edit'. After Insert is clicked it hides itself and makes other button visibe.
4. After Insert close the form and take user to a bound form showing those 2 records that they can edit.

Again, what do you want to occur?

 

[MajP]

Make a unique composite index
UserName and Year
No way to get duplicates

 

[ebs17]

sSQL = "INSERT INTO UnpivotedName (UserName, Year)" & _
    " SELECT '" & Me.NameTxtBoxAt1 & "', 1403 FROM T1Only" & _
    " WHERE NOT EXISTS (SELECT NULL FROM UnpivotedName" & _
    " WHERE UserName = '" & Me.NameTxtBoxAt1 & "' AND Year = 1403)"
CurrentDb.Execute sSQL, dbFailOnError

T1Only is a table that contains exactly one record.
With such a query, each pair of values is only entered once; if there is a unique index (which should always be the case), no index error is generated.
In good practice, this would be designed and executed as a parameter query.

Using a recordset is easier and just as quick:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Set rs = db.Openrecordset("SELECT UserName, Year FROM UnpivotedName" & _
    " WHERE UserName = '" & Me.NameTxtBoxAt1 & "' AND Year = 1403", dbOpenDynaset)
With rs
   If .EOF Then
      .AddNew
      !UserName = Me.NameTxtBoxAt1
      !Year = 1403
      .Update
      .Close
End With
' and so on

 

[hamidreza.sajjadi]

sSQL = "INSERT INTO UnpivotedName (UserName, Year)" & _
    " SELECT '" & Me.NameTxtBoxAt1 & "', 1403 FROM T1Only" & _
    " WHERE NOT EXISTS (SELECT NULL FROM UnpivotedName" & _
    " WHERE UserName = '" & Me.NameTxtBoxAt1 & "' AND Year = 1403)"
CurrentDb.Execute sSQL, dbFailOnError

T1Only is a table that contains exactly one record.
With such a query, each pair of values is only entered once; if there is a unique index (which should always be the case), no index error is generated.
In good practice, this would be designed and executed as a parameter query.
Using a recordset is easier and just as quick:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.Openrecordset("SELECT UserName, Year FROM UnpivotedName" & _
    " WHERE UserName = '" & Me.NameTxtBoxAt1 & "' AND Year = 1403", dbOpenDynaset)
With rs
   If .EOF Then
      .AddNew
      !UserName = Me.NameTxtBoxAt1
      !Year = 1403
      .Update
      .Close
End With
' and so on

thank you very much for your respons. sorry but i am a kind of naive in vba. in your first code, should i create T1Only Table with subquery? because i don't have such a table with uniqe rows.

 

[hamidreza.sajjadi]

Check to see if they exist in the first place?, Use Dcount()

good idea. i also have considered using a complementary query by which I can group records or use some Dcount ac criteria. but i am looking if a VBA Based solution could be exist.

 

[ebs17]

because i don't have such a tabl

A table with a field and a record - you can create this by hand, faster than it takes to think about it.
If you have a problem with that, it's better to find another hobby.

 

[Gasman]

good idea. i also have considered using a complementary query by which I can group records or use some Dcount ac criteria. but i am looking if a VBA Based solution could be exist.

DCount() is VBA ?

 

[hamidreza.sajjadi]

DCount() is VBA ?

not necessarily. for example i can create a select query by which i can filter out the results which have Dcount>1. raw idea

 

[hamidreza.sajjadi]

A table with a field and a record - you can create this by hand, faster than it takes to think about it.
If you have a problem with that, it's better to find another hobby.

Wouldn't it better off if a create this said table with select query ?
Would you mind taking a look at the file for me? in my access file there is only one empty table and an empty form with vba code

 

[ebs17]

Wouldn't it better off if a create this said table with select query ?

You can also use any query that produces exactly one record. But if additional effort is added to the actual query, I wouldn't call it better.
(My first approach is query efficiency, fewer short queries or something that is currently being thought of.)

If your hands shake while creating the table by hand:

SELECT 1 AS X
INTO T1Only