Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 14th, 2006, 05:15 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Prompt to Save

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

Reply With Quote
  #2 (permalink)  
Old December 14th, 2006, 09:44 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old December 14th, 2006, 09:57 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old December 15th, 2006, 10:01 AM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old December 15th, 2006, 10:35 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old December 15th, 2006, 11:39 AM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:
Code:
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
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

Reply With Quote
  #7 (permalink)  
Old December 15th, 2006, 11:49 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old December 15th, 2006, 12:12 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old December 15th, 2006, 12:19 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old December 15th, 2006, 12:31 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Disabling second Excel Save prompt j0hnyager VB How-To 3 November 20th, 2007 09:51 AM
command prompt karthisena BOOK: ASP.NET Website Programming Problem-Design-Solution 1 February 27th, 2007 04:11 AM
Command Prompt karthisena General .NET 2 January 25th, 2007 12:29 PM
Excel Save Prompt in javascript interrupt Javascript How-To 2 March 18th, 2005 04:52 AM
Delete Without Prompt MattLeek Excel VBA 3 March 24th, 2004 10:04 AM



All times are GMT -4. The time now is 04:10 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.