How to write a procedure to test if a record exists in a table

[Helena666]

Hi

Its been a while since I have built a database using access and vba and am a bit rusty. I am using a command button on a form to write a record to a table, using an append query. However I need to test if a "live" record exists in that table and if it does to let the user know that the record has not been written. The append query will only write the record to the table if one doesn't exist already.
The criteria is emp_id and a field called "record active". eg. if emp_id exists and record Active = "Y" don't write the record.

I have tried to edit the "on error" bit of the procedure attached to the command buttone but it doesn't work.

Any ideas?

[JKing]

Hi. Could you post the code you're currently using so we can what you've attempted and help you to fix it up?

[Helena666]

Hi. Could you post the code you're currently using so we can what you've attempted and help you to fix it up?

This is the code connected to the command button on the form

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim stDocName As String


stDocName = "permit update for issued permits"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command24_Click:
Exit Sub


Err_Command24_Click:
MsgBox " A live permit already exists, deactivate if necessary", vbOKOnly, "pleb head"
Resume Exit_Command24_Click

End Sub

The next bit is the query
"INSERT INTO [Permit details] ( Permit_user_id, Permit_decsion, Permit_issue_no, Permit_date_issued )
SELECT DISTINCT CStr(Forms![select staff form]![staff details1f].form!emp_id) AS Expr1, 1 AS Expr2, IIf([MaxOfPermit_issue_no] Is Null,1,[MaxOfPermit_issue_no]+1) AS Expr5, Date() AS Expr3
FROM ([staff details] LEFT JOIN [max permit issue no] ON [staff details].emp_id = [max permit issue no].Permit_user_id) LEFT JOIN livepermits ON [staff details].emp_id = livepermits.Permit_user_id
WHERE (((livepermits.Permit_user_id) Is Null) AND (([staff details].emp_id)=[Forms]![select staff form]![staff details1f].[form]![emp_id]));"

The code is running an append query. In that query I have a subquery that pulls out all "live" permits from the Permits Table. The record will only be appended emp_id does not exist in the "live permits" query. At the moment i have the "action queries" confirm on and all that happens is the message "0 records appended appears".

What I really want to do is run the "live permits" query first and if the "emp_id" exists in this query place a msgbox on the screen that states "This person already has a permit" and then not run the append query. But if it doesn't exist to run the append query and give the user the message "Permit created"

Thanks

Helena

[Peter99]

Hi

Its been a while since I have built a database using access and vba and am a bit rusty. I am using a command button on a form to write a record to a table, using an append query. However I need to test if a "live" record exists in that table and if it does to let the user know that the record has not been written. The append query will only write the record to the table if one doesn't exist already.
The criteria is emp_id and a field called "record active". eg. if emp_id exists and record Active = "Y" don't write the record.

I have tried to edit the "on error" bit of the procedure attached to the command buttone but it doesn't work.

Any ideas?

Try something like this:

Set mdbCurrent = CurrentDb
strSQL = "Select * from [TableName] Where [Field] = SelectionCriteria"
Set Master = mdbCurrent.OpenRecordset(strSQL)
If Master.RecordCount > 0 Then
Do your thing
Else
Append your record
End If

mdbCurrent.Close

[Helena666]

Try something like this:

Set mdbCurrent = CurrentDb
strSQL = "Select * from [TableName] Where [Field] = SelectionCriteria"
Set Master = mdbCurrent.OpenRecordset(strSQL)
If Master.RecordCount > 0 Then
Do your thing
Else
Append your record
End If

mdbCurrent.Close

Thanks - I've written it as below but am getting the following error message

"run time error 3061 too few parameters expected 1" on the following line.

Set Master = mdbCurrent.OpenRecordset(strSQL).

The code is as follows:

Private Sub Command29_Click()
Set mdbCurrent = CurrentDb
strSQL = "Select * from livepermits Where permit_user_id = me!staffcombo" ' staffcombo is on the form
Set Master = mdbCurrent.OpenRecordset(strSQL)
If Master.RecordCount > 0 Then
MsgBox " A live permit already exists, deactivate if necessary", vbOKOnly, "warning"
Else
MsgBox " the code is working ", vbOKOnly, "warning"
End If

mdbCurrent.Close

End Sub

[Peter99]

Thanks - I've written it as below but am getting the following error message

"run time error 3061 too few parameters expected 1" on the following line.

Set Master = mdbCurrent.OpenRecordset(strSQL).

The code is as follows:

Private Sub Command29_Click()
Set mdbCurrent = CurrentDb
strSQL = "Select * from livepermits Where permit_user_id = me!staffcombo" ' staffcombo is on the form
Set Master = mdbCurrent.OpenRecordset(strSQL)
If Master.RecordCount > 0 Then
MsgBox " A live permit already exists, deactivate if necessary", vbOKOnly, "warning"
Else
MsgBox " the code is working ", vbOKOnly, "warning"
End If

mdbCurrent.Close

End Sub

Its probably me!StaffCombo

Try something like this

strSQL = "Select * from livepermits Where permit_user_id = ' " & me!staffcombo & " ' "

You can put in a tempory msgbox to check strSQL - the me!staffcombo should show the value selected in the combo box

msgbox strSQL

[Helena666]

Its probably me!StaffCombo

Try something like this

strSQL = "Select * from livepermits Where permit_user_id = ' " & me!staffcombo & " ' "

You can put in a tempory msgbox to check strSQL - the me!staffcombo should show the value selected in the combo box

msgbox strSQL

Thanks so much it now works.