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 VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 August 19th, 2007, 03:38 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I delete a record from a form?

I have a work order form. its Record Source is a query(Q_WorkOrder). I've joined my Customer table(T_Client) to it. My form(F_WorkOrder) opens right into a new record. i use a drop down menu to select or type in the customers name. I need to be able to cancel the form without creating a new record in my Work Order Table(T_WorkOrder). Right now i have a cancel Command and it closes out my form but what ever information i have on the form it creates a new record for it in my (T_WorkOrder)table. I need the user to be able to close out of the form without recording any information thats been filled in. What would be the best way to accomplish this? Any help here will be greatly appreciated!!! :)

Reply With Quote
  #2 (permalink)  
Old August 21st, 2007, 07:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This can be problematic. Basically you are going to have to retrieve the control's OldValue. If you do a search in VBA Help for OldValue, you will come up with a nice entry that should give you the idea. I think basically if the user clicks a Cancel button you would do something like:

Me.UnitPrice = Forms!Products!UnitPrice.OldValue

for example.

I usually don't allow users to edit in this situation, and force them to open a second pop up modal form to do entry, and then on that form they have the option of cancelling or submitting. Of course, that is more for record contention issues in SQL Server.

I think returning the OldValue will work for you.

Did that help?


mmcdonal
Reply With Quote
  #3 (permalink)  
Old August 21st, 2007, 12:15 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the more i thought about it. i'm thinking my problem lies in opening my work order form into a new record. do you think if i created a form with a command button that records the information when the user is finished entering the information that would be a better solution? and my next question would be what kind of code would i need to construct to execute my command button?

Reply With Quote
  #4 (permalink)  
Old August 21st, 2007, 12:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That is pretty straightforward. I use this method for Access/SQL.

Assume a single table entry for Customers, with First Name, Last Name, Address, City, State, Zip with corresponging text boxes on your form, but assume the State is a combo box bound to a foreign key table. I will assume you are connected to your data (local or linked tables):

Dim rs As ADODB.Recordset
Dim sLName As String
Dim sFName As String
Dim sAddress As String
Dim sCity As String
Dim iState As Integer
Dim sZip As String
Dim sSQL As String

If IsNull(Me.LastName) Or Me.LastName = "" Then
   MsgBox "Please enter a customer last name."
   Exit Sub
Else
   sLName = Me.LastName
End If

'with business rules all the way down until you get all your variable.

On Error Resume Next
Err.Clear
sSQL = "INSERT INTO Customer (LastName, FirstName, Address, City, State, Zip) " & _
        "VALUES ('" & sLName & "', '" & sFName & "', '" & sAddress & "', '" & sCity & "', " & iState & ", '" & sZip & "')"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Then perhaps a messagebox telling them they were successful, like:
If Err = 0 Then
   MsgBox sLName & ", " & sFName & " was successfully added to Customers."
End If

Then whatever other functions, like closing the current form and perhaps opening the original form again. etc.

Don't forget the cancel button.

Did that help?




mmcdonal
Reply With Quote
  #5 (permalink)  
Old August 23rd, 2007, 05:16 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that did help!!! it got me going in the right direction i believe. but now i have another situation. its probably simple to solve, but my inexperience is hindering me. on my work order form there will be text boxes that will be left empty depending on what the user chooses to fill in based on the information they have at the time. i dont want to require the user to have to fill in every text box cuz sometimes they wont have all the information at the time they create the work order. so now my problem lies in having null values. For example maybe the user doesn't know what to enter in for a zip code, so they need to leave that blank. how can i avoid running into a runtime error '94' - invalid use of null? what do i need to do here when i have possibly multiple text boxes that will contain null values?

Reply With Quote
  #6 (permalink)  
Old August 24th, 2007, 05:31 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I have some code here which may assist you vis a vis your postal code problem. I hope it is of use to you



Two functions placed in the modules area

Display Message
and
Confirm

Public Sub DisplayMessage(strMessage As String)
' Display an important message to the user.

    MsgBox strMessage, vbExclamation, conAppName

End Sub

Public Function Confirm(strMessage As String) As Boolean
' Ask the user to confirm an action, returning True or False.

    Dim bytChoice As Byte

    bytChoice = MsgBox(strMessage, vbQuestion + vbOKCancel, conAppName)

    If bytChoice = vbOK Then
        Confirm = True
    Else
        Confirm = False
    End If

End Function

Code to place in the before update even of your entry form

Private Sub Form_BeforeUpdate(Cancel As Integer)
' If the user entered an address, check for a zip code.

    Dim blnOK As Boolean

    If Not IsNull(Address) And IsNull(PostalCode) Then
        blnOK = Confirm("You didn’t enter a postal code. Save anyway?")

        If Not blnOK Then ' User chose Cancel.
            PostalCode.SetFocus ' Go back to PostalCode field.
            Cancel = True ' Cancel saving the record.
        End If
    End If

End Sub


You might like to check out an Author called Evan Callahan
"Step x Step MS Access 97.

Despite the fact that we are now on 0ffice 2007 or might be higher the code in the above book is still is valid if you are coding in DAO
This just the ticket for the JET engine.

You could probably purchase this book on e-bay for a very small amount. I still find it very usefull

The code above comes that very book

Man Friday

Reply With Quote
  #7 (permalink)  
Old August 24th, 2007, 06:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

For the Null values, instead of prompting the user to fill in the text box, as with this code:

If IsNull(Me.LastName) Or Me.LastName = "" Then
   MsgBox "Please enter a customer last name."
   Exit Sub
Else
   sLName = Me.LastName
End If


Modify it like this to allow Nulls, which you will turn into an emptry string:

If IsNull(Me.LastName) Or Me.LastName = "" Then
   sLName = ""
Else
   sLName = Me.LastName
End If

Did that help?


mmcdonal
Reply With Quote
  #8 (permalink)  
Old August 24th, 2007, 07:26 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Hi

Const conAppName = "Works Order Management"

Apologies there is mention in the previous code I sent to the constant conAppName.

In the Module area you will have to create the Constant as above

MF

Reply With Quote
  #9 (permalink)  
Old August 24th, 2007, 12:09 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yep, that did help!! now i have another issue... first want to send many thanks for all your help here. now my next issue is: i have a cancel button on my order form and so the user can cancel out of the form at any time when they click it. but the problem is its still recording what ever information is left in the form. how do i go about setting that up to remove the record from my table? this is what i have come up with but its giving me an error: says i neen a form argument.

Private Sub CmdHomePage_Click()
On Error GoTo Err_CmdHomePage_Click

    Dim stDocName As String
    Dim rstT_WorkOrder As ADODB.Recordset

    DoCmd.OpenForm stDocName
    DoCmd.Close acForm, "F_WorkOrder"
    rstT_WorkOrder.CancelUpdate

Exit_CmdHomePage_Click:
    Exit Sub

Err_CmdHomePage_Click:
    MsgBox Err.Description
    Resume Exit_CmdHomePage_Click

End Sub

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
Delete a record row, not just the record. Coby Access VBA 1 April 30th, 2007 06:29 AM
Record Delete on Form Opening ermy78 Access VBA 3 October 19th, 2006 02:08 PM
How to delete current record in bounded form? method Access 0 June 19th, 2005 04:37 PM
Trying to delete a record... Can't do it... lguzman Access VBA 11 August 13th, 2004 12:41 PM
Can't delete record Trojan_uk SQL Server 2000 3 November 27th, 2003 01:03 PM



All times are GMT -4. The time now is 06:22 PM.


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