Required field in update query

[spcscooter via AccessMonster.com]

I have an update query with the following code

UPDATE RMAEntry SET RMAEntry.RMANumber = [enter batch number], RMAEntry.
DateOut = [enter batch out date]
WHERE (((RMAEntry.RMANumber) is null) AND ((RMAEntry.DateOut) is null) AND (
(RMAEntry.EnteredBy) is not null));

I need the enter batch rma prompt to be required as well as the enter batch
out date prompt.

Any help would be great.

Thanks

 

[Allen Browne]

The parameter dialog is not really powerful enough to do what you need, so
you will need a form.

Create a little unbound form, with a text box that has properties:
Name txtBatchNum
Format General Number
another text box with properties:
Name txtBatchOut
Format General Date
and a command button with properties:
Name cmdUpdate
On Click [Event Procedure]

Then click the Build button (...) beside the On Click property.
Access opens the code window.
Set up your code like this:

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim strSql As String

If IsNumeric(Me.txtBatchNum) AND IsDate(Me.txtBatchOut) Then
strSql = "UPDATE RMAEntry SET RMAEntry.RMANumber = " & _
Me.txtBatchNum & ", RMAEntry.DateOut = " & _
Format(Me.txtBatchOut, "\#mm\/dd\/yyyy\#") & _
" WHERE ((RMAEntry.RMANumber is null) " & _
"AND (RMAEntry.DateOut is null) AND (RMAEntry.EnteredBy is not
null));"

Set db = CurrentDb()
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) updated."
Set db = Nothing
Else
MsgBox "Number and date required."
End If
End Sub

 

[spcscooter via AccessMonster.com]

Thank you

The parameter dialog is not really powerful enough to do what you need, so
you will need a form.

Create a little unbound form, with a text box that has properties:
Name txtBatchNum
Format General Number
another text box with properties:
Name txtBatchOut
Format General Date
and a command button with properties:
Name cmdUpdate
On Click [Event Procedure]

Then click the Build button (...) beside the On Click property.
Access opens the code window.
Set up your code like this:

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim strSql As String

If IsNumeric(Me.txtBatchNum) AND IsDate(Me.txtBatchOut) Then
strSql = "UPDATE RMAEntry SET RMAEntry.RMANumber = " & _
Me.txtBatchNum & ", RMAEntry.DateOut = " & _
Format(Me.txtBatchOut, "\#mm\/dd\/yyyy\#") & _
" WHERE ((RMAEntry.RMANumber is null) " & _
"AND (RMAEntry.DateOut is null) AND (RMAEntry.EnteredBy is not
null));"

Set db = CurrentDb()
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) updated."
Set db = Nothing
Else
MsgBox "Number and date required."
End If
End Sub

I have an update query with the following code

[quoted text clipped - 11 lines]

Thanks