Adding a a new record to a form with a if else statement that you can edit as well

Question

I'm only guessing this is correct VBA Query Coding for Access. I'm a C# developer, so please bear with me. I currently have a Table and a Form, and I have an event procedure that if you click in the box that is inside the Form, you can edit that specific record. It will close, save and update when I hit the close form button. But, now I have a problem where I can add a new record by clicking on a box with no data, but it sends a syntax error. I'm assuming it's doing this because the VBA query code I used is looking for it to edit and not add a new record. I can click the X on the Message Box and still input a new record. My thought on this is to add an IF ELSE statement. Can I not do it like this? If something needs to be edited, I can. Or do I have to make changes somewhere else? I also read I can edit the message box to say to add a new record, Hit the X in the corner and proceed to add new record. That is a thought also.

If Me.CurrentRecord = "CalibrationKey" Then
DoCmd.OpenForm "Calibration Detail", , , "[CalibrationKey]= " & Me.[CalibrationKey], acFormEdit, acDialog
Else: DoCmd.OpenForm "Calibration Detail", , , "[CalibrationKey]= " & Me.[CalibrationKey], acFormAdd, acDialog
End If
End Sub```

If you want to open form in Add mode, don't apply filter criteria. The If condition does not make sense. CurrentRecord is a form event, not a property. Should you be referencing a field name instead? Such as: If IsNull([CalibrationKey]) Then. — June7

Sola Oshinowo Sola Oshinowo · Accepted Answer · 2020-01-16T12:17:17

The box with or without data is a field in the form? If that is the case, the code you have should be put under the onclick event of the box(field) Make the calibrationkey the name of the box that is been clicked

Also alter the code as follows

If Not IsNull (CalibrationKey.value) Then DoCmd.OpenForm "Calibration Detail", , , "[CalibrationKey]= " & Me.[CalibrationKey], acFormEdit, acDialog Else: DoCmd.OpenForm "Calibration Detail", , , "[CalibrationKey]= " & Me.[CalibrationKey], acFormAdd, acDialog End If End Sub```

Adding a a new record to a form with a if else statement that you can edit as well

1 Answers