Okay, here is what I do in this situation:

Create a BOUND form, but set all the fields to Disabled Yes, Locked No (on the data tab of the Properties dialog box).

Then create a combo to allow the users to Find a Record on the Form (use the wizard). This will allow users to search the form based on an ISBN they enter in the combo box.

Then I create a series of Public variables in a Module. In your case, this would be:

Public sISBN, sBkTtl, sPub, sEd, sAuth, sType, sComm As String
Public dCpyrt As Date
Public bSoft As Boolean

Then I create an Update form with text boxes on it. I create an "Update this record" button, and it pops up a modal form with all of the text fields prepopulated, and a Submit changes and cancel changes button.

The Update this record button has this sort of code:

sISBN = Me.ISBN
sBkTtl = Me.Book_Title
... etc
Then I close the form they are on and open the main form for the next operation.

This sets the value for the public variables.

Then for the On Open event of the pop up form, I do this:

Me.Text0 = sISBN
Me.Text2 = sBkTtl
...etc

So when the Pop Up Modal form opens, all the text fields have data from the record the user was on when they opened the form.

Then I allow them to change this data, which is unbound.

On the Cancel button, I have the form they were on reopen to the record they were on when they launched the change form, and give thema msgbox stating the change was cancelled.

On the submit changes button, I use business rules to make sure data was changed correctly. I would lock the ISBN field too.

The code would be:

Dim rst As ADODB.Recordset
Dim sSQL As String

'ISBN is already in the Public sISBN
'Check Book Title for change
If IsNull(Me.Text2) Or Me.Text2 = "" Then
   MsgBox "Please enter a book title", vbCritical
   Exit Sub
Else
   sBkTtl = Me.Text2
End If

etc.

Then

sSQL = "SELECt * FROM tblBookTable WHERE [ISBN] = '" & sISBN & "'"
Set rst = New ADODB.Recordset
rst.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

   rst("Book Title") = sBkTtl
   ...
   rst.Update
rst.Close

If Err = 0 Then
   Msgbox sISBN & " was updated successfully"
Else
   Msgbox "There was an error processing your request."
End If

DoCmd.Close
DoCmd.Close "frmMainForm" 'that they are on while the update is going on
DoCmd.OpenForm "frmYourISBNForm", etc

Then in the On Load event of your ISBN form, put this code:
Dim sFormName As String

sFormName = "frmYourISBNForm"

If sISBN <> "" Then
    DoCmd.GoToRecord acDataForm, sFormName, acGoTo, pRecord
    sISBN = ""
End If

This will take the user back to the record they were on if they make the change or if they cancel the update, and reset the sISBN value to blank so that the next time they open the form it doesn't go back to this record, but to the first record in the record source.

Did that help?

mmcdonal

Sorry, this should be:

If sISBN <> "" Then
    DoCmd.GoToRecord acDataForm, sFormName, acGoTo, sISBN
    sISBN = ""
End If

I left one of my own public variable names in there.

Also, on the error handling for the record submission, you may want to Exit Sub if the record was not submitted properly.


mmcdonal

Okay, I was doing good for the first part, but I want to make sure I have some terminology correct (still learning and I really appreciate your help). You said:
  Now, if my update form bound or unbound? I'm not sure. I think it should be unbound by the sounds of it. Also, I'm guessing that this form (the update form), should be modal as well. I'm just wanting to make sure I'm getting this right.

Also, before I move on, is the "main form", the first form we created where they are looking up by ISBN?

And just so I am understanding what we are doing. We are creating the lookup form with an update button on it. It will then open this other form in modal mode where we will have the opportunity to update the record. I haven't gotten past that part yet because I want to make sure I am understanding each part.

Thanks again for your patience.

The update form would be unbound and contain only data you send to it from the public variables. It would be pop up and modal since you wouldn't want the user to do anything until they were done with this form.

The main form would be any other form except the one they launched from. I do this just to sort the requery issue, and to prevent record locking when the update was pushed. When they reopen the form back to the record they updated, the update will be there automatically.


As to this: "And just so I am understanding what we are doing. We are creating the lookup form with an update button on it. It will then open this other form in modal mode where we will have the opportunity to update the record." That is exactly what we are doing!

HTH



mmcdonal

Okay, I finally got through it all and it works great. I really appreciate your help and reading through some of the comments and your answers here really have helped.

I guess now that I am working through it, I only have one problem, and it is more on my end than anything. It is entirely possible that I can have a field which is null (the comments field). What is the best way to handle that so it does not keep giving me error messages about the field being null?

Thanks again for all of your great help and answers. This should make it easier when I go to work on the other areas now that I have an idea of what I need to do.

Best Regards,
Arholly
:)

If you want to all a Null, then do this:

If IsNull(Me.Comments) = True Then
   sComm = ""
Else
   sComm = Me.Comments
End If


mmcdonal

Thanks, I'm all good (with this).

I appreciate all of your help.

Arholly