Hi guys,
On one form, I need to open another form using a button. The catch is that the code must check whether a record has been created with that Project_ID. If it has, then it must display that record. If not then it must make a new record.
The db is attached. The button is called "Form 1" on the "Contract Details" tab of frm_Project_Details.
I need it to make a new record in tblContract_Record using frm_ContractRec_Details or, if the record already exists, then open that record.
I have searched this forum, trawled the net, and tried many things and hit many frustrating dead ends. It can't be that difficult, surely!
Andy
Full Version: Command button with record checking
Try something like this:
Good luck!
Nate
CODE
Private Sub cmdMakeForm1_Click()
On Error GoTo cmdMakeForm1_Click_Err
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblContract_Records WHERE Project_ID = " & Project_number & ";", dbOpenDynaset, dbSeeChanges)
If Nz(rst.RecordCount, 0) > 0 Then
'there is a record with the same projectId, so open the form to that project.
DoCmd.OpenForm "frm_ContractRec_Details"
Forms!frm_ContractRec_Details!Form.RecordSource = "SELECT * FROM tblContract_Records WHERE Project_ID = " & Project_number & ";"
Else
'there are no matching projectids, so open the form to a new project.
DoCmd.OpenForm "frm_ContractRec_Details", , , , acFormAdd
Forms!frm_ContractRec_Details.Form.RecordSource = "SELECT * FROM tblContract_Records;"
End If
Set db = Nothing
Set rst = Nothing
cmdMakeForm1_Click_Exit:
Exit Sub
cmdMakeForm1_Click_Err:
MsgBox Error$
Resume cmdMakeForm1_Click_Exit
End Sub
On Error GoTo cmdMakeForm1_Click_Err
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblContract_Records WHERE Project_ID = " & Project_number & ";", dbOpenDynaset, dbSeeChanges)
If Nz(rst.RecordCount, 0) > 0 Then
'there is a record with the same projectId, so open the form to that project.
DoCmd.OpenForm "frm_ContractRec_Details"
Forms!frm_ContractRec_Details!Form.RecordSource = "SELECT * FROM tblContract_Records WHERE Project_ID = " & Project_number & ";"
Else
'there are no matching projectids, so open the form to a new project.
DoCmd.OpenForm "frm_ContractRec_Details", , , , acFormAdd
Forms!frm_ContractRec_Details.Form.RecordSource = "SELECT * FROM tblContract_Records;"
End If
Set db = Nothing
Set rst = Nothing
cmdMakeForm1_Click_Exit:
Exit Sub
cmdMakeForm1_Click_Err:
MsgBox Error$
Resume cmdMakeForm1_Click_Exit
End Sub
Good luck!
Nate
Hi
You could do something similar to Nate's suggestion, but condense it down a bit and not have to bother with the 'overhead' of using recordsets. The built in DCount() function could be used. Assuming the form you're opening is bound to the appropriate table/query and that the Project_ID is numerical, something along the lines of
You could do something similar to Nate's suggestion, but condense it down a bit and not have to bother with the 'overhead' of using recordsets. The built in DCount() function could be used. Assuming the form you're opening is bound to the appropriate table/query and that the Project_ID is numerical, something along the lines of
CODE
Private Sub cmdMakeForm1_Click()
On Error GoTo cmdMakeForm1_Click_Err
If DCount("*","tblContract_Records","Project_ID = " & Me.Project_number) > 0 Then
'there is a record with the same projectId, so open the form to that project.
DoCmd.OpenForm "frm_ContractRec_Details", , , "Project_ID = " & Me.Project_number
Else
'there are no matching projectids, so open the form to a new project.
DoCmd.OpenForm "frm_ContractRec_Details", , , , acFormAdd
End If
cmdMakeForm1_Click_Exit:
Exit Sub
cmdMakeForm1_Click_Err:
MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
Resume cmdMakeForm1_Click_Exit
End Sub
On Error GoTo cmdMakeForm1_Click_Err
If DCount("*","tblContract_Records","Project_ID = " & Me.Project_number) > 0 Then
'there is a record with the same projectId, so open the form to that project.
DoCmd.OpenForm "frm_ContractRec_Details", , , "Project_ID = " & Me.Project_number
Else
'there are no matching projectids, so open the form to a new project.
DoCmd.OpenForm "frm_ContractRec_Details", , , , acFormAdd
End If
cmdMakeForm1_Click_Exit:
Exit Sub
cmdMakeForm1_Click_Err:
MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
Resume cmdMakeForm1_Click_Exit
End Sub
Awesome, thanks guys, it worked. As I'm sure you knew it would
Glad we could help
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.