Okay, continuing my saga of converting a bad database to a good one, it came to my attention that I need to have some way of confirming if people want to make the changes on a form. I'm also not sure if this belongs here or in the Access VBA forum.

Background: We've had people go into the form and inadvertantly change something.

Scenario: Have some way that if they change anything on the form, it prompts them when they go to leave the form asking them if they are sure they want to change the record or something to that effect.

Since I haven't created the form yet (I'm still transfering data), I have a clean slate to work with. Any thoughts?

Best Regards and thanks for your continued help.
Arholly

If you don't directly access your table with the form, meaning you use code to write to it, you can have a button that updates the entries into the database. I did this with two separate databases. One that the form directly writes and reads from, and the other that the button writes to.

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblYours", dbOpenDynaset)

'all information in here where "Order#" is a column in your table
rst.AddNew
rst("Order") = [OrderNum]
rst("CustomerName") = strTextifYouNeedHardCodedText
rst.Update

rst.Close

put that code (modify how you need it) in an OnClick button code spot. Make sure you use Primary Keys though otherwise you can get duplicate entries. Or you can check with this code:

bloop=false
Do
   If Not (rst.BOF And rst.EOF) Then
      If (rst.RecordCount <> 0) Then
         If ([OrderNum]= rst![Order]) Then
           bLoop = True
         Else
           rst.MoveNext
         End If
      End If
   End If
while(bloop)

That checks to see if there are any entries based on the text entered (fill it in based on your primary key) in your table.
It's a round about way I came up with to do what I needed.
HTH
ermy

I just thought of another thing, if you want to use just one table, you could use record navigation/search buttons to access one database but it would not write to it unless they click the button I was talking about creating.

For your "next record button":

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblYours", dbOpenDynaset)

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblYours", dbOpenDynaset)
rst.MoveNext
[OrderNum] = rst("Order")
[CustomerName] = rst("CustomerName")
rst.Close

should also work. To move back change rst.MoveNext to rst.MovePrevious
and searching you would use that do while loop and populate your fields after the loop.

Okay, I do use the form directly to the table to edit it. Now, I'm not entirely sure I'm following you completely. So, I am using code from both your posts or just the last one.

I also want them to retain functionality of them being able to search through the records, but if they change the information, I want them to be notified before moving to the next or previous record.

Maybe it's just early and I'm not understanding.

Thanks in advance,
Arholly

I would make the form access the table indirectly. Add buttons for them to click through the records like I was saying in the second post. To add records you would use the code in the first post. To edit, use the same code in the first post but replace rst.AddNew with rst.Edit
In making them use buttons to update, you are essentially forcing them to consciously save the data (in effect, a save button). Your save button would be

rst.Edit
rst("Field1") = [Field1 from Form]
'fill in whatever fields
rst.Update
rst.Close

For this to work, though, you'd have to make the table indirectly accessible - meaning goto your properties for the form and under the data form, leave a blank space.

Okay, so I'm using this code. I am going to use this code, but how do I make it so automatically moves them to a new empty record. Right now, it after clicking on the button it does not allow the form to advance to a new record and I would like to include that functionality.

Thanks again,
Arholly

Since your form is not directly tied to the table, all you have to do is after rst.close (before end sub) set all of your fields = to NULL or False i.e.

[customer #] = NULL
[Order #] = NULL
[Check box] = False
[Radio Button] = False
'etc. until all your fields are empty.

This, in effect, makes an empty slate. When they want to save the information they entered, they just click the "save to table" button (whatever you called it) and it will save to your specified table and clear the form.

Okay, I got the adding new form to work great - thanks for the help!

Now, I'm moving on to the edit form. I understand that I need to make the table inaccessable, but how do I do that and still look up the information?

For example, I have the primary the ISBN number (because no two books have the same). So, I want to look up books by their ISBN number but still have it so they can't save their edits without using the button. I'm not sure how to accomplish the lookup part?

Thanks again.
Arholly

Also, when doing the edit macro, it keeps telling me there is a duplicate primary in the table. It's like it wants to add, not edit when I clearly have rst.Edit in there.

Thanks,
Arholly

To lookup the information I would have them type the ISBN into the text box. Your code would then be:

rst.FindFirst([ISBN])
[Title] = rst("Title")
'etc.

rst.close

As for the rst.Edit acting like it's adding new. It sounds like you're not on the record that you're trying to edit. I.E. you want to edit the record related to ISBN = 1234. In reality, your current record that rst is pointed to is ISBN = 1235 and then it's trying to change the ISBN from 1235 to 1234 thus giving you a PK error. For rst.Edit to work, rst has to be pointed at the record you want to edit, not some random. Just for testing purposes, try putting a text box (non-editable) that displays your current record. If that does not match the ISBN you have in your editable text box then you are trying to change the wrong record. Clear as mud?