Insert two values from a form in a table and check if they already exist like this

[Robin.Fepsy]

Dear Community

I'm not really familiar with VBA or SQL codes. Thats why I need your help!

I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination. I have no idea why my code doesn't works:

Public Function GetID_PatientStudiesGroup&()

GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup]

End Function

Private Sub BPRS_T1_Button_Click()

Call GetID_PatientStudiesGroup&
Dim strSQL As String

strSQL = "INSERT INTO tbl_PatientStudiesGroupTZP (ID_PatientStudiesGroup,ID_TZP) SELECT [GetID_PatientStudiesGroup], 2 FROM tbl_PatientStudiesGroupTZP  WHERE NOT EXISTS (SELECT ID_PatientStudiesGroup, ID_TZP FROM tbl_PatientStudiesGroupTZP WHERE ID_PatientStudiesGroup = [GetIDPatientStudiesGroup]  AND ID_TZP = 2)"

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True


End Sub

As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.

Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". I'm sorry for the long code, but I was not able to find one part out of it, which could be the problem. Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together. I would be very thankful for every kind of help!

Greetings, Robin

 

[TJPoorman]

Try this:

Private Sub BPRS_T1_Button_Click()
Dim rs as Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_PatientStudiesGroupTZP WHERE ID_PatientStudiesGroup = '" & Me.ID_PatientStudiesGroup & "' AND ID_TZP = 2")
If rs.EOF Then
    CurrentDb.Execute("INSERT INTO tbl_PatientStudiesGroupTZP (ID_PatientStudiesGroup, ID_TZP) VALUES('" & Me.ID_PatientStudiesGroup & "', 2)")
End If

'Insert your code here to move to next form

End Sub

 

[Robin.Fepsy]

Thank you so much! You saved me hours if not days of try&error work!!

 

[Robin.Fepsy]

The code for inserting the values works now perfectly. Thank you TJPoorman! Too bad, there's another problem that arised for which I would need your expertise again! There are a couple of similar buttons on this form that only differ by the ID_TZP added to Table tbl_PatientStudiesGroupTZP (they should all refer to the same ID_PatientStudiesGroup, which is included in that table as well). When pressing the button, the user should be forwarded to the next form (with the ID_PatientStudiesGroupTZP that was created before as primary key), so that every button opens it's own form with it's own ID_PatientStudiesGroupTZP. I tried to make this happen with this code:

Dim strSQL As String
Dim strID_PatientStudiesGroupTZP As String

strSQL = "SELECT tbl_PatientStudiesGroupTZP.[ID_PatientStudiesGroupTZP] FROM tbl_PatientStudiesGroupTZP WHERE tbl_PatientStudiesGroupTZP.[ID_PatientStudiesGroup] = " & ID_PatientStudiesGroup & " AND tbl_PatientStudiesGroupTZP.[ID_TZP] = 1"
strPatientStudiesGroupTZP = Me.strSQL.Value

DoCmd.OpenForm "frm_BPRS", acNormal, , "[ID_PatientStudiesGroupTZP]= [strPatientStudiesGroupTZP], , acWindowNormal, Me.ID_PatientStudiesGroupTZP"

As you can see, I tried to select the ID_PatientStudiesGroupTZP with the SQL-sequence and implement it into the VBA-sequence. I'm not able to see, where I made the mistake. Thank you very much for every hint

 

[TJPoorman]

To grab the newly inserted ID use this:

Dim db as DAO.Database
Dim strSQL as String
Dim lngNewRecord as Long

Set db = CurrentDb
strSQL = "INSERT INTO myTable () VALUES()"

db.Execute (strSQL)
lngNewRecord = db.OpenRecordset("SELECT @@IDENTITY")(0)

DoCmd.OpenForm "frm_BPRS", , , "[ID_PatientStudiesGroupTZP]=" & lngNewRecord, , , Me.ID_PatientStudiesGroupTZP

 

[Robin.Fepsy]

Thank you so much for your help again! But to be honest, I'm again a bit lost, because with this code, an error message occurs (Runtime-Error 3134) and says there is a syntax-error in the INSERT INTO-command. When opened with the debugger, the line db.Execute(strSQL) is highlighted. Do you have an idea what the problem could be? I tried out to replace

strSQL = "INSERT INTO myTable () VALUES()"

With

strSQL = "INSERT INTO tbl_PatientStudiesGroupTZP (ID_PatientStudiesGroup, ID_TZP) VALUES(,)"

But it didn't worked as well.

By replacing VALUES() with VALUES('" & Me.ID_PatientStudiesGroup & "', 2), I was able to enter a blank BPRS-Form but it was not possible to re-enter the same form because it obviously created a new one, every time I click the button...

 

[pr2-eugin]

Well the INSERT statement TJ has shown is just a skeleton, you should be aware the structure of the INSERT statement.

[B]INSERT INTO [/B]
    [COLOR=Blue]<yourTableName> [/COLOR]
    [B]([/B][COLOR=Red]<fieldName1>[/COLOR][B],[/B] [COLOR=Red]<fieldName2>[/COLOR][B],[/B] ..... [COLOR=Red]<fieldNameN>[/COLOR][B])[/B]
[B]VALUES [/B]
    [B]([/B][COLOR=DarkOrange]<Value1>[/COLOR][B],[/B] [COLOR=DarkOrange]<Value2>[/COLOR][B],[/B] ..... [COLOR=DarkOrange]<ValueN>[/COLOR][B])

[/B]The bits in fancy colours is what you should be typing in. BOLD bits are the keywords

 

[Robin.Fepsy]

Thank you for your reply! How I understand this code, the target of it would be to insert nothing, but making sure that the part...

lngNewRecord = db.OpenRecordset("SELECT @@IDENTITY")(0)

... chooses the right record. And even if I "filled up" the skeleton, it didn't work properly :/

 

[Robin.Fepsy]

If I adjust the code to this:

Dim db As DAO.Database
Dim strSQL As String
Dim lngNewRecord As Long
 
Set db = CurrentDb

lngNewRecord = db.OpenRecordset("SELECT @@IDENTITY FROM tbl_PatientStudiesGroupTZP WHERE ID_PatientStudiesGroup = " & Me.ID_PatientStudiesGroup & " AND ID_TZP = 1")(0)
 
DoCmd.OpenForm "frm_BPRS", , , "[ID_PatientStudiesGroupTZP]=" & lngNewRecord, , , Me.ID_PatientStudiesGroupTZP

End Sub

I get as a result, that the correct ID_PatientStudiesGroupTZP gets imported to the BPRS_Form, but access creates a new Autovalue everytime and it's not possible to re-enter the same form again. For the purpose of making it a bit clearer: I only want one record per ID_PatientStudiesGroupTZP in the frm_BPRS so it's possible to re-enter the same form after some time. Could this also be a problem with the relations is set up? Thank you very much for your advice!
PS: If it would help, I could upload my database, so you can see it directly.

 

[pr2-eugin]

SELECT @@IDENTITY is a statement that returns the ID of the newly inserted record it does not need FROM or WHERE clauses to it.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Robin.Fepsy]

I now uploaded a sample of my database here, so you can see how everything is connected and maybe where I could have made an error. What I'm trying to do is the last step in the whole menu-navigation, so it's the last step where I need complex codes... I would be really happy if someone could help, because I'm not making any progress since more than a week...

For the purpose of a better understanding of the database: There are different Patients beeing into different study-groups... With the first two forms I can do this properly! In the third form (frm_PatientStudiesGroupTZP) I have to create a unique ID_PatientStudiesGroupTZP for every testing point (T0, T1, T2, etc.) by clicking the button that directs you to the form with the actual questionnaire (I so far only created a sample questionnaire named BPRS). So in the end, every questionnaire (for every ID_PatientStudiesGroupTZP) should be filled out once, but it has to be possible to re-enter the same questionnaire multiple times! I hope this makes it a little bit clearer, otherwise please don't hesitate to ask questions

Thanks a lot for your help!